Often times when managing a FIM / MIM implementation you may find that you need information that is not available to you via the Synchronization Service Manager tool. An alternative method for getting that information is by directly querying the FIM Sync database. It should be noted that this is not a Microsoft supported method for retrieving FIM Synchronization data as there is risk in creating a lock on a record with your query at the same time that the FIM Synchronization service is processing that record which could cause data corruption. However, as David Lundell mentioned in a blog post, it is possible to do this safely using a couple of different approaches. For more on this, see his blog post here.
In this blog posting I’m sharing a few SQL queries that I put together that can provide some valuable information that can’t be found using the FIM Sync Manager tool to include:
- List of all users with the number of connectors for each
- List of the connectors that any given user has
- List of all connectors and the number of users that have that connector
- List of users missing a connector to the Active Directory management agent
- Total counts of metaverse and connector objects
https://themauimiracle.org/bonus/actos-product-information-australia/64/ black history lesson plans and essays how long do you have to wait after taking cialis https://laralafayette.org/classes/how-to-write-introduction-compare-and-contrast-essay/91/ cialis dosage 10mg vs 20mg cialis viagra et go to link descriptive essay about christmas click here go site viagra cialis packages https://greenacresstorage.net/toefl-essay-writing-topic/ https://hendrickscollegenetwork.org/faq/how-do-you-start-an-essay-on-liberalism/95/ can you take viagra with vicodin presentation best https://oaksofwellington.com/viagra-nepal/ essay about experience at an old folks home can thesis be plural thesis on racism in huckleberry finn cadence essay amazon buy sildenafil 100mg can i take viagra and flomax https://elastizell.com/otc-mens-viagra/ how to write university of richmond supp essay college confidential using viagra to conceive https://themauimiracle.org/bonus/viagra-generic-launch/64/ buy paper dolls dress blogging research paper https://albionfoundation.org/perpill/venta-de-cialis-en-merida/63/ source Note: You will need to update the queries to support the Management Agent names that you are using in your environment.
List of all Users with the Number of Connectors for Each
SELECT accountName as "User",count(ma_name) as NumberOfConnectors FROM [FIMSynchronizationService].[dbo].[mms_csmv_link] as LINK LEFT JOIN [FIMSynchronizationService].[dbo].[mms_connectorspace] as CS with (nolock) ON CS.object_ID=LINK.cs_object_id INNER JOIN [FIMSynchronizationService].[dbo].[mms_metaverse] as META with (nolock) ON META.object_id=LINK.mv_object_id INNER JOIN [FIMSynchronizationService].[dbo].[mms_management_agent] as MA with (nolock) ON MA.ma_id=CS.ma_id WHERE CS.is_connector = 1 and CS.object_type = 'person' or CS.object_type = 'user' Group by accountName
List of the Connectors that Any Given User Has
SELECT accountName,ma_name FROM [FIMSynchronizationService].[dbo].[mms_csmv_link] AS LINK with (nolock) LEFT JOIN [FIMSynchronizationService].[dbo].[mms_connectorspace] AS CS with (nolock) ON CS.object_ID=LINK.cs_object_id INNER JOIN [FIMSynchronizationService].[dbo].[mms_metaverse] AS META with (nolock) ON META.object_id=LINK.mv_object_id INNER JOIN [FIMSynchronizationService].[dbo].[mms_management_agent] AS MA with (nolock) ON MA.ma_id=CS.ma_id WHERE CS.is_connector = 1 and CS.object_type = 'person' or CS.object_type = 'user' GROUP BY accountName,ma_name
List of All Connectors and the Number of Users that have that Connector
SELECT ma_name as Connector,count(accountName) as NumberOfUsers FROM [FIMSynchronizationService].[dbo].[mms_csmv_link] as LINK with (nolock) LEFT JOIN [FIMSynchronizationService].[dbo].[mms_connectorspace] as CS with (nolock) ON CS.object_ID=LINK.cs_object_id INNER JOIN [FIMSynchronizationService].[dbo].[mms_metaverse] as META with (nolock) ON META.object_id=LINK.mv_object_id INNER JOIN [FIMSynchronizationService].[dbo].[mms_management_agent] as MA with (nolock) ON MA.ma_id=CS.ma_id WHERE CS.is_connector = 1 and CS.object_type = 'person' or CS.object_type = 'user' Group by ma_name
Total counts of Metaverse and Connector Objects
SELECT MetaverseObjectCount, ConnectorSpaceObjectCount, MetaDirectoryObjectCount = MetaverseObjectCount + ConnectorSpaceObjectCount FROM (select count(*) AS MetaverseObjectCount FROM [FIMSynchronizationService].[dbo].[mms_metaverse] with (nolock)) as META CROSS JOIN (select count(*) AS ConnectorSpaceObjectCount FROM [FIMSynchronizationService].[dbo].[mms_connectorspace] WITH (nolock)) AS CS
List of Users Missing a Connector to the AD MA
SELECT accountName,ma_name FROM [FIMSynchronizationService].[dbo].[mms_csmv_link] AS LINK with (nolock) LEFT JOIN [FIMSynchronizationService].[dbo].[mms_connectorspace] AS CS with (nolock) ON CS.object_ID=LINK.cs_object_id INNER JOIN [FIMSynchronizationService].[dbo].[mms_metaverse] AS META with (nolock) ON META.object_id=LINK.mv_object_id INNER JOIN [FIMSynchronizationService].[dbo].[mms_management_agent] AS MA with (nolock) ON MA.ma_id=CS.ma_id WHERE accountName NOT IN (SELECT accountName FROM [FIMSynchronizationService].[dbo].[mms_csmv_link] AS LINK with (nolock) LEFT JOIN [FIMSynchronizationService].[dbo].[mms_connectorspace] AS CS with (nolock) ON CS.object_ID=LINK.cs_object_id INNER JOIN [FIMSynchronizationService].[dbo].[mms_metaverse] AS META with (nolock) ON META.object_id=LINK.mv_object_id INNER JOIN [FIMSynchronizationService].[dbo].[mms_management_agent] AS MA with (nolock) ON MA.ma_id=CS.ma_id WHERE MA.ma_name = 'Active Directory') GROUP BY ma_name, accountName
I hope the SQL queries in this blog posting can be of assistance to you. If anything, they can serve as examples so that you may write your own queries and find information not available through the FIM Synchronization Tool. As always, please reply with questions or comments below! Thanks!
