Query the FIM Synchronization Service Database using SQL

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

daily cialis medication interactions https://vivianschilling.com/film/essay-about-computer-games-advantages-and-disadvantages/61/ https://easternpropane.com/savings/clomid-in-the-morning-or-night/87/ batek of malaysia gender relations essay viagra and deep vein thrombosis kamagra cukriky writing the intro of an essay argumentative essay supporting euthanasia go to site https://iaace.com/annual/suny-purchase-creative-writing-portfolio-requirements/92/ i'm in college how can the navy help indian alternative viagra thesis statement proposal essay can you take cialis with antidepressants motorcycle maintenance essay lady era viagra ap world essay questions 2012 essay om uret tikker silk https://stonecottagegardens.com/fda/levitra-gnrique-pas-cher/14/ trimix viagra https://mswwdb.org/report/essay-cultural-criticism-society/96/ get link https://ardelyx.com/news-releases/viagra-efectos-embarazo/197/ enter site source link go here https://complextruths.org/case/commonwealth-supported-postgraduate-coursework-programs/68/ blog sul cialis an essay that a student submits to a college us army get viagra prescription resume writing service kitchener planes trains essay 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

FIM Synchronization Service - List of All Connectors and the Number of Users that have that Connector

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

FIM Synchronization Service - Total counts of Metaverse and Connector Objects

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

FIM Synchronization Service - List of Users Missing a Connector to the AD MA

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!