Finding Connector Space objects with a Manager when the Metaverse object has none

I was asked to evaluate the impact of ticking “Allow Nulls” on the AD export flow for ‘manager’ when it was realised that some people had a manager set in AD, while in FIM they had none.

This can be a hard change to test in Dev or Test environments which don’t have the full set of production data. However if you make the change in Production you may well find yourself with a lot of undesired exports queued. So before doing anything I wanted to get an idea about how many people have a manager set in AD, but none in the Metaverse.

The following script starts from a dump of the AD connector space produced using csexport.exe. I then run SQL queries against the FIMSynchronizationService database to fill in the Metaverse half of the story.

Whenever querying the FIMSynchronizationService database it is important to remember the possibility of locking. I ran this script at a time I knew no syncs were going to run. You could also query a recent replica of the database, or modify the queries in the script to use NOLOCK.

Here’s the script:

###
### This script compares Connector Space objects to Metaverse objects for the purpose of finding users with 'manager'
### set in the Connector Space but not set in the Metaverse. This will allow us to judge the impact of enabling
### "Allow Nulls" on the EAF before actually doing it.
###

## csexport.xml must have been produced by running csexport.exe from <Sync Program Folder>\Bin and exporting the connector space.
[xml]$CS = get-content D:\Scripts\csexport.xml

##
## Create a CSV file from the XML which includes only connectors that have a manager in the connector space
##
$CSVFile = "D:\Scripts\CSmanager.csv"
"csid;dn;manager" | out-file $CSVFile -Encoding Default

foreach ($connector in $CS."cs-objects"."cs-object" | where {$_."object-type" -eq "user" -and $_.connector -eq "1"})
{
    if ($connector."unapplied-export-hologram".entry."dn-attr" | where {$_.name -eq "manager"})
    {
        $manager = ($connector."unapplied-export-hologram".entry."dn-attr" | where {$_.name -eq "manager"})."dn-value".dn
        $dn = $connector."cs-dn"
        $csid = $connector.id.Replace("{","").Replace("}","")
        $csid + ";" + $dn + ";" + $manager | add-content $CSVFile
    }
}

##
## Next we query SQL for each line in the CSV. As we are querying the Metaverse tables this should only be done when
## no sync operations are running. Or take a copy of the tables referenced and put them in another DB to query them there,
## or modify the queries to use NOLOCK.
##

## Load SQL snapins. SQL Management tools must be installed, or you can copy the CSV to the SQL server
## and run this bit of the script there.
if(@(get-pssnapin | where-object {$_.Name -eq "sqlserverprovidersnapin100"} ).count -eq 0) {add-pssnapin sqlserverprovidersnapin100}
if(@(get-pssnapin | where-object {$_.Name -eq "sqlservercmdletsnapin100"} ).count -eq 0) {add-pssnapin sqlservercmdletsnapin100}

## Path to your FIMSynchronizationService database
set-location SQLSERVER:\SQL\MySQLServer\Default\Databases\FIMSynchronizationService

## Import CSV file created above
$CSV = import-csv $CSVFile -Delimiter ";"

## Create a new CSV file with an extra column showing if manager exists in the Metaverse
$CSVFile = "D:\Scripts\CSmanager-CompareMV.csv"
"csid;dn;manager;InMV" | out-file $CSVFile -Encoding Default

foreach ($row in $CSV)
{
    ## Get metaverse objectid from CS objectid
    $SQLGetMVID = "select object_id from dbo.mms_metaverse join dbo.mms_csmv_link on object_id = mv_object_id where cs_object_id = '{0}'" -f $row.csid
    $mvid = (Invoke-SQLCmd $SQLGetMVID -SuppressProviderContextWarning)."object_id"
    $mvid.Guid

    if ($mvid)
    {
        ## Queries Metaverse reference table and returns 0 for no manager and 1 if there is a manager. 
        $SQLHasManager = "select count (*) from dbo.mms_mv_link where object_id = '{0}' and attribute_name = 'manager'" -f $mvid.Guid
        $count = (Invoke-SQLCmd $SQLHasManager -SuppressProviderContextWarning).Column1
        $count
    }
    else {$count = "0"}

    $row.csid + ";" + $row.dn + ";" + $row.manager + ";" + $count | add-content $CSVFile
}