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 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