How to pull a full list of users to certificates and card mappings from FIM CM / MIM CM

If you want to collect a report that combines usernames to certificate serial numbers, linked to the card serial number, along with the date the card was issued, you can use this query on your FIM CM database:

Select
u.unc_user_nt4_name, c.cert_issued_serial_number, s.sc_serial_number,
s.sc_manufacturer_id, q.req_submitted_dt,
replace(replace(replace(replace(replace(s.sc_status,'1','Assigned'), '2', 'Active'), '3', 'Disabled'), '4', 'Suspended'), '5', 'Retired') as Status

from dbo.Certificates as C left join dbo.ProfileCertificates  as p
on c.cert_id = p.pc_cert_id 

right outer join dbo.Profiles as r
on p.pc_profile_uuid = r.profile_uuid

 inner join dbo.UserNameCache as u on r.pr_assigned_user_uuid = u.unc_user_uuid

right outer join dbo.Smartcards as s on
r.pr_sc_uuid = s.sc_uuid

right outer join (select * from dbo.Requests where req_type=1) as q
on q.req_sc_uuid = s.sc_uuid

order by unc_user_nt4_name,sc_serial_number,req_submitted_dt