Query the MIM Service Database for Group Membership
Got a MIM Service database backup? You should. Got it configured in Azure with a retention policy? Got it encrypted?
Ran into an issue with MIM Hybrid Reporting a couple years ago and came to the conclusion that the MIM Service database is the ultimate source for MIM audit data because it contains the MIM Service request history.
To guard against losing that we can configure backup retention for a long period.
With that many backups of the MIM Service database it also becomes possible to query the database to see what objects looked like at the time of the backup. This has come in handy when investigating incidents, to see what an object looked like at different points in time.
With just a database backup you could restore the MIM Service and point it at the database but it is sometimes easier to just query the data directly from SQL.
Here is an example that gets group membership for a user given the ObjectID of their Person object:
WARNING do not do this in production as it could interfere with the MIM Service
SELECT OVSGroupAccountName.ValueString AS GroupAccountName, OVSGroupDisplayName.ValueString AS GroupDisplayName FROM fim.objects AS O JOIN fim.ObjectValueReference AS OVR ON OVR.ObjectKey = O.ObjectKey AND OVR.AttributeKey = 40 -- 40 is ComputedMember JOIN fim.Objects AS MO ON OVR.ValueReference = MO.ObjectKey JOIN fim.ObjectValueString AS OVSGroupAccountName ON O.ObjectKey = OVSGroupAccountName.ObjectKey AND OVSGroupAccountName.AttributeKey = 1 -- 1 is AccountName JOIN fim.ObjectValueString AS OVSGroupDisplayName ON O.ObjectKey = OVSGroupDisplayName.ObjectKey AND OVSGroupDisplayName.AttributeKey = 66 -- 66 is DisplayName WHERE MO.ObjectID = 'A0A09E7A-4AE8-4E70-81C5-8008503ED2A3' /* GroupAccountName GroupDisplayName endo Mountain Bike Trail Hikers foo The Fight is Real bar Self Pacification */