FIM 2010: How to Use PowerShell to Create a CSV of FIM/MIM Metaverse Connections
FIM ScriptBox Item
Summary
Knowing your Metaverse objects and their corresponding connections is a key to a healthy identity solution. Provisioning, de-provisioning, join rules (or lack thereof), use of the Join tool, and inconsistent data from your source systems can create Metaverse objects that are without needed MA connections.
The Synchronization Manager Tool allows you to pull up each Metaverse object and look at connections in terms of rows:
The information is helpful, but wouldn't it be great to have the MA listed in columns and look at the entire Metaverse at a global level? You could create a unique Metaverse attribute for each MA and configure each MA to flow a value into the associated new Metaverse attribute, but sometimes you don't have that luxury due to lack of Metaverse attribute space, change control, or organizational red tape. Further, you still need to get the data out into a file for analysis.
Listed below are two scripts, a PowerShell script and accompanying SQL script, which will export each Metaverse object and its Management Agent connections in CSV format. Each row is a Metaverse "person" object and the columns are each Management Agent that you want to determine connection status.
I should note that the SQL script is querying the Synchronization Service database directly, therefore you should stop all your MA runs prior to running the script. For added protection, the first two lines of the script are to stop and disable the Synchronization Service. I leave it up to you to enable and start up the service when you are done or you can add it to the script.
Usage
Copy the two scripts to C:\Scripts, and launch PowerShell with credentials that have access to query the FIMSynchronizationService database, then type:
.\export-connectors.ps1
The file connectors.csv should be created and can be opened in Excel for sorting, filtering, and analysis.
Enable and start the FIM Synchronization Service when complete.
Script Code
export-connectors.sql
Modify the variable ma.ma_name with the management agent name as specified in the Synchronization Manager Management Agent tool.
| 001
002 003 004 005 006 007 008 009 010 011 012 013 014 015 016 017 018 019 020 021 022 023 024 025 026 027 028 029 |
DECLARE @i INT
DECLARE @NumRows INT DECLARE @ObjID UNIQUEIDENTIFIER SET @i = 1 SET @NumRows = (SELECT COUNT(*) from mms_metaverse) IF @NumRows > 0 WHILE (@i <= @NumRows) BEGIN SET @ObjID = ( SELECT object_id FROM ( SELECT ROW_NUMBER() OVER (order by object_id) as RowNumber, * FROM mms_metaverse ) AS MetaVerse WHERE RowNumber = @i ) SELECT MAX(CASE WHEN ma.ma_name = 'HR' then cs.rdn END) AS "HR System" , MAX(CASE WHEN ma.ma_name = 'AD' then cs.rdn END) AS "Active Directory" , MAX(CASE WHEN ma.ma_name = 'MIM' then cs.rdn END) AS "MIM Service" FROM [FIMSynchronizationService].[dbo].[mms_csmv_link] AS "csmv" JOIN [FIMSynchronizationService].[dbo].[mms_connectorspace] AS "cs" ON csmv.cs_object_id = cs.object_id JOIN [FIMSynchronizationService].[dbo].[mms_management_agent] AS "ma" ON cs.ma_id = ma.ma_id WHERE mv_object_id = @ObjID SET @i = @i + 1 END |
export-connectors.ps1
Modify the -ServerInstance with the SQL Server running the FIMSynchronizationService database. If the database is on a SQL instance you would enter Server\Instance.
| 001
002 003 |
Stop-Service FIMSynchronizationService
Set-Service FIMSynchronizationService -StartupType "Disabled" Invoke-Sqlcmd -Database FIMSynchronizationService -ServerInstance SQL_SERVER_NAME_HERE -InputFile C:\Scripts\export-connectors.sql | export-csv -NoTypeInformation C:\Scripts\connectors.csv |
Note
To provide feedback about this article, create a post in the FIM TechNet Forum.
For more FIM related Windows PowerShell scripts, see the FIM ScriptBox 
See Also