FIM ScriptBox Item

Summary

One of the most frustrating parts of with Forefront Identity Manager Synchronization Service is not being able to look at multiple connector space object's attributes side by side to evaluate the proper flow of values. I've spent the last few months authoring a PowerShell module that includes cmdlets that bring the information displayed in the FIM Synchronization Service UI into PowerShell. I present here a small subset that will allow you to specify a Metaverse GUID, a connector space object GUID, or a DN and then  will display the connectors side by side in an out-gridview. This command can be run remotely or locally.

Usage

Save this script and then dot-source this script to load the functions into the current powershell session. To dot-source a script, simply type a period then a space before the path to the script and it will tell PowerShell not to unload any variables or functions that the script loads. Alternatively you can copy paste this into a powershell session.

To properly execute this script use the following template:

Pivot-Object (Get-FimCSObjects -ComputerName "FQDN" -MVGuid "{12345678-1234-1234-1234-123456789012}"| Get-FimCSObjectAttributes) | Out-GridView

Pivot-Object (Get-FimCSObjects -ComputerName "FQDN" -CSObjectId "{12345678-1234-1234-1234-123456789012}"| Get-FimCSObjectAttributes) | Out-GridView

Pivot-Object (Get-FimCSObjects -ComputerName "FQDN" -DN "DN of Object"| Get-FimCSObjectAttributes) | Out-GridView


Script Code

function Invoke-SqlCmd

{

 

    <#

        .SYNOPSIS

            This script is used to issue SQL commands to servers remotely and returns the results as a collection

 

        .PARAMETER  Server

            FQDN of the server to check

 

        .PARAMETER  Database

            Database to execute the sql command on

 

        .PARAMETER  Query

            SQL Query to execute

 

        .PARAMETER  UserName

            Username to use when connecting to the database

 

        .PARAMETER  Password

            Password to use when connecting to the database.

 

        .PARAMETER  QueryTimeout

            Timeout in seconds to wait for the query to complete.

 

        .PARAMETER  ConnectionTimeout

            Timeout in seconds to wait for the database to initially respond to the connection request.

 

        .EXAMPLE

            PS C:\> Invoke-SQLCmd -Server localhost -Database DatabaseName -Query "select * from table where field = '{FFFFFFFF-FFFF-FFFF-FFFF-FFFFFFFFFFFF}'"

 

        .EXAMPLE

            PS C:\> Invoke-SQLCmd -Server localhost -Database DatabaseName -Query "select * from table where field = '{FFFFFFFF-FFFF-FFFF-FFFF-FFFFFFFFFFFF}'" -Username "DOMAIN\UserName" -Password "Mypassword123!" -QueryTimeout 6000 -ConnectionTimeout 15

    #>

 

    param(

    [Parameter(Position=0, Mandatory=$true, ValueFromPipelineByPropertyName=$true)][alias("ServerInstance", "ComputerName", "CN")] [string]$Server,

    [Parameter(Position=1, Mandatory=$true)] [string]$Database,

    [Parameter(Position=2, Mandatory=$true)] [string]$Query,

    [Parameter(Position=3, Mandatory=$false)] [string]$UserName,

    [Parameter(Position=4, Mandatory=$false)] [string]$Password,

    [Parameter(Position=5, Mandatory=$false)] [Int32]$QueryTimeout=60,

    [Parameter(Position=6, Mandatory=$false)] [Int32]$ConnectionTimeout=15

    )

 

    try {

        $ds = new-object System.Data.DataSet

 

        if ($Username)

        { $ConnectionString = "Server={0};Database={1};User ID={2};Password={3};Trusted_Connection=False;Connect Timeout={4}" -f $Server,$Database,$Username,$Password,$ConnectionTimeout }

        else

        { $ConnectionString = "Server={0};Database={1};Integrated Security=True;Connect Timeout={2}" -f $Server,$Database,$ConnectionTimeout }

 

        $conn=new-object System.Data.SqlClient.SQLConnection

        $conn.ConnectionString=$ConnectionString

        $conn.Open()

 

        $cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn)

        $cmd.CommandTimeout=$QueryTimeout

 

        $da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)

        [void]$da.fill($ds)

        Write-Output ($ds.Tables[0])

    }

    finally {

        $conn.Dispose()

    }

 

} #Invoke-SqlCmd

 

function Get-FIMInfo

{

        [CmdletBinding()]

        param(

            [Parameter(Position=0, Mandatory=$false, ValueFromPipelineByPropertyName=$true)][alias("CN","MachineName","Server")]$ComputerName = "localhost"

        )

   

        $FIMProperties = New-Object System.Object

        $ServerKey = [Microsoft.Win32.RegistryKey]::OpenRemoteBaseKey([Microsoft.Win32.RegistryHive]::LocalMachine, $ComputerName)

        $services = $serverkey.OpenSubKey("System\CurrentControlSet\Services")

 

        if ($services.openSubKey("FIMSynchronizationService") -ne $null)

        {  

            $fimServiceConfig = $services.openSubKey("FIMSynchronizationService")

        }

        elseif ($services.openSubKey("miiserver") -ne $null)

        {

            $fimServiceConfig = $services.openSubKey("miiserver")

        }

        else

        {

            Write-Error -foregroundcolor red "FIM/ILM is not installed!"

            throw

        }

 

        $fimPath = $fimServiceConfig.GetValue("ImagePath")

 

        $FIMProperties | Add-Member -MemberType NoteProperty -Name "Path" -Value ($fimPath.toLower().replace("\bin\miiserver.exe","").replace("""",""))

        $FIMProperties | Add-Member -MemberType NoteProperty -Name "SQLServer" -Value ($fimServiceConfig.OpenSubKey("Parameters").GetValue("Server"))

        $FIMProperties | Add-Member -MemberType NoteProperty -Name "DBName" -Value ($fimServiceConfig.OpenSubKey("Parameters").GetValue("DBName"))

        Write-Output $FIMProperties

 

}

 

function Get-FimCSObjects

{

    [CmdletBinding()]

    [OutputType([PSCustomObject[]])]

    Param

    (

        # Param1 help description

        [Parameter(Mandatory=$false,ValueFromPipelineByPropertyName=$true)][alias("CN","MachineName","Server")]$ComputerName = "localhost",

        [Parameter(Mandatory=$true,ValueFromPipelineByPropertyName=$true,ParameterSetName="DN")][String]$DN,

        [Parameter(Mandatory=$true,ValueFromPipelineByPropertyName=$true,ParameterSetName="MVGuid")][GUID]$MVGuid,

        [Parameter(Mandatory=$true,ValueFromPipelineByPropertyName=$true,ParameterSetName="CSObjectId")][GUID]$CSObjectId

    )

 

    Begin

    {

               

    }

    Process

    {

        $FimInfo = Get-FIMInfo -ComputerName $ComputerName

        if ($DN)

        {

            $csQuery = @"

                        DECLARE @dn nvarchar(2000), @dnEsc nvarchar(2000), @rdn nvarchar(500)

                        SELECT @dn = '$DN'

                        SELECT @dnEsc = replace(@dn, '\\', '{&b&}')

                        SELECT @dnEsc = replace(@dnEsc, '\,', '{&c&}')

                        SELECT @rdn = CASE CHARINDEX(',', @dnEsc) WHEN 0 THEN @dnEsc ELSE LEFT(@dnEsc, CHARINDEX(',', @dnEsc)-1) END

                        SELECT @rdn = REPLACE(@rdn, '{&c&}', '\,')

                        SELECT @rdn = REPLACE(@rdn, '{&b&}', '\\')

                        ;WITH CTE_cs_object_id (ma_id, cs_id, [object_id], [pobject_id], dn)

                        AS

                        (

                            SELECT cs.ma_id, cs.[object_id] AS id, cs.[object_id], cs.[pobject_id], cast(rdn as nvarchar(500))

                            FROM dbo.mms_connectorspace (nolock) cs WHERE cs.rdn = @rdn

                            UNION ALL

                            SELECT pcs.ma_id, pcs.cs_id, cs.[object_id], cs.[pobject_id], cast(pcs.dn+','+cs.rdn as nvarchar(500))

                            FROM mms_connectorspace (nolock) cs INNER JOIN CTE_cs_object_id pcs ON cs.[object_id] = pcs.[pobject_id]

                        ),

                        CTE_mcs_object_id (ma_name, ma_id, cs_id, mv_object_id)

                        AS

                        (

                            SELECT ma.ma_name, ma.ma_id, isnull(csmvl.cs_object_id,cs.cs_id), csmv.mv_object_id 

                            FROM CTE_cs_object_id cs LEFT OUTER JOIN dbo.mms_csmv_link csmv (nolock) ON csmv.cs_object_id = cs.cs_id

                                                        LEFT OUTER JOIN dbo.mms_csmv_link csmvl (nolock) ON csmvl.mv_object_id = csmv.mv_object_id

                                                        LEFT OUTER JOIN dbo.mms_connectorspace mcs (nolock) ON mcs.object_id = csmvl.cs_object_id

                                                        JOIN dbo.mms_management_agent ma (nolock) ON ma.ma_id = mcs.ma_id OR (ma.ma_id = cs.ma_id AND mcs.ma_id is NULL)

                            WHERE cs.pobject_id  = 0x0 AND cs.dn = @dn

                        )

                        SELECT

                            csmv.mv_object_id as MVGuid,

                            csmv.lineage_date,

                            cs.is_connector,

                            cs.connector_state,

                            cs.last_import_modification_date,

                            cs.Last_export_modification_date,

                            c.ma_name [maName],

                            c.ma_id [maGuid],

                            c.cs_id [csGuid],

                            cs.import_operation,

                            cs.object_type,

                            cs.rdn

                         FROM

                            CTE_mcs_object_id c

                            JOIN mms_connectorspace cs ON c.cs_id = cs.object_id

                            JOIN mms_csmv_link csmv on csmv.cs_object_id = cs.object_id

                            --WHERE c.ma_id = '$MAID'

"@

        }

        if ($MVGuid)

        {

            $csQuery = @"

                         SELECT

                            csmv.mv_object_id as MVGuid,

                            csmv.lineage_date,

                            cs.is_connector,

                            cs.connector_state,

                            cs.last_import_modification_date,

                            cs.Last_export_modification_date,

                            ma.ma_name [maName],

                            cs.ma_id [maGuid],

                            cs.object_id [csGuid],

                            cs.import_operation,

                            cs.object_type,

                            cs.rdn

                         FROM

                            mms_metaverse mv

                            JOIN mms_csmv_link csmv on csmv.mv_object_id = mv.object_id

                            JOIN mms_connectorspace cs ON csmv.cs_object_id = cs.object_id

                            JOIN mms_management_agent ma on ma.ma_id = cs.ma_id

                         WHERE

                            mv.object_id = '$($MVGuid.ToString("D"))'

"@

        }

        if ($CSObjectId)

        {

            $csQuery = @"

                         SELECT

                            csmv.mv_object_id as MVGuid,

                            csmv.lineage_date,

                            cs.is_connector,

                            cs.connector_state,

                            cs.last_import_modification_date,

                            cs.Last_export_modification_date,

                            ma.ma_name [maName],

                            cs.ma_id [maGuid],

                            cs.object_id [csGuid],

                            cs.import_operation,

                            cs.object_type,

                            cs.rdn

                         FROM

                            mms_metaverse mv

                            JOIN mms_csmv_link csmv on csmv.mv_object_id = mv.object_id

                            JOIN mms_connectorspace cs ON csmv.cs_object_id = cs.object_id

                            JOIN mms_management_agent ma on ma.ma_id = cs.ma_id

                         WHERE

                            csmv.cs_object_id = '$($CSObjectId.ToString("D"))'

"@

        }

 

        $CSObjectsData = Invoke-sqlcmd -ServerInstance $FimInfo.SQLServer -Database $FimInfo.DBName -Query $csQuery

        $CSObjects = @()

        $CSObjectsData | %{

                    $CS = $_

                    $CSObject = "" | Select MVGuid, MAName, MAID, CSObjectId, RDN, ModificationType, ObjectType, LastImportChange, LastExportChange, ObjectState, ConnectionOperation, Date, @{N="ComputerName";E={$ComputerName}}

 

                    $CSObject.MVGuid = $CS.mvGuid

                    $CSObject.MAID = $CS.maGuid

                    $CSObject.CSObjectId = $CS.csGuid

                    $CSObject.RDN = $CS.RDN

                    $CSObject.MAName = $CS.MaName

                    switch ($CS.import_operation)

                    {

                        "2" {$CSObject.ModificationType = "Update"}

                        "1" {$CSObject.ModificationType = "Add"}

                        "0" {$CSObject.ModificationType = "None"}

                        default {$CSObject.ModificationType = "Unknown"}

                    }

                    $CSObject.ObjectType = $CS.object_type

                    $CSObject.LastImportChange = $CS.last_import_modification_date

                    $CSObject.LastExportChange = $CS.last_export_modification_date

                    If ($CS.is_connector -eq 1)

                    {

                        $CSObject.ObjectState = "Connector"

                    }

                    else

                    {

                        if ($CS.connector_state -eq 0)

                        {

                            $CSObject.ObjectState = "Normal disconnector"

                        }

                        else

                        {

                            $CSObject.ObjectState = "Explicit disconnector"

                        }

                    }

                    $CSObject.ConnectionOperation = $null

                    $CSObject.Date = (new-object -TypeName DateTime -ArgumentList @(([DateTime]$CS.lineage_date).Ticks, [DateTimeKind]::Utc)).ToLocalTime()

                    $CSObjects += $CSObject

                }

   

        if (@($CSObjects).count -ne 0)

        {

            Write-Output $CSObjects

        }

 

    }

} #Get-FimCSObjects

 

function Get-FimCSObjectAttributes

{

    [CmdletBinding()]

    [OutputType([PSCustomObject[]])]

    Param

    (

        # Param1 help description

        [Parameter(Mandatory=$false,ValueFromPipelineByPropertyName=$true)][alias("CN","MachineName","Server")]$ComputerName = "localhost",

        [Parameter(Mandatory=$false,ValueFromPipelineByPropertyName=$true)][guid]$CSObjectId,

        [Parameter(Mandatory=$false,ValueFromPipelineByPropertyName=$true)][String]$DN,

        [Parameter(Mandatory=$false,ValueFromPipelineByPropertyName=$true)][GUID]$MVGuid

    )

 

    Begin

    {

    }

    Process

    {

        if ($CSObjectId -eq $null)

        {

            if ($DN)

            {

                $CSObjectId = (Get-FimCSObject -DN $DN).CsObjectId

            }

            if ($MVGuid)

            {

                $CSObjectId = (Get-FimCSObject -MVGuid $MVGuid).CsObjectId

            }

        }

        if ($CSObjectId -eq $null)

        {

            throw "No CSObject Provided"

        }

        $FIM = Get-FimInfo -ComputerName $ComputerName

        $sb = @"

            `$fimUiUtils = [Reflection.Assembly]::LoadFrom("$($Fim.Path)\UiShell\UiUtils.dll")

            `$fimProperties = [Reflection.Assembly]::LoadFrom("$($Fim.Path)\UIShell\PropertySheetBase.dll")

            `$fimWS = new-object Microsoft.DirectoryServices.MetadirectoryServices.UI.WebServices.MMSWebService

            Write-Output `$fimWS.GetCSAllAttributes("$($CSObjectId.ToString("B").ToUpper())")

"@

        $scriptblock = [ScriptBlock]::Create($sb)

        $xml = [xml] $(Invoke-Command -ComputerName $ComputerName -ScriptBlock $scriptblock)

        $attributes = New-Object PSCustomObject

        $attributes | Add-Member -MemberType NoteProperty -Name DN -Value $xml.'cs-objects'.'cs-object'.'cs-dn'

        $xml.'cs-objects'.'cs-object'.'synchronized-hologram'.entry.attr | % {

            if ($_.multivalued -eq $true)

            {

                $value = @()

                $_.value | % { $value += $_ }

                $attributes | Add-Member -MemberType NoteProperty -Name $_.name -Value $value

            }

            else

            {

                $attributes | Add-Member -MemberType NoteProperty -Name $_.name -Value $_.value

            }

        }

        $xml.'cs-objects'.'cs-object'.'synchronized-hologram'.entry.'dn-attr' | % {

            $value = @()

            $_.'dn-value' | % { $value += $_.dn}

            $attributes | Add-Member -MemberType NoteProperty -Name $_.name -Value $value

        }

        Write-Output $attributes | select *, @{N="CSObjectID";e={$CSObjectId}}, @{N="ComputerName";e={$ComputerName}}

    }

}

 

function Pivot-Object

{

[CmdletBinding()]

param(

    [Parameter(ValueFromPipeline=$true)][PSObject[]]$InputObject

)

    $x = 2

    $results = @();

    $InputObject | % {

        $_.psobject.Properties | % {

            $name = $_.name

            if (($results | ? {$_.name -eq $name}) -eq $null)

            {

                $results += $_ | select name, value

            }

            else

            {

                $results | ? {$_.name -eq $name} | Add-Member -MemberType NoteProperty -Name "Value$x" -Value $_.value

            }

           

        }

        $x++

    }

    Write-Output $results

}

  

note Note

To provide feedback about this article, create a post on the FIM TechNet Forum.

For more FIM related Windows PowerShell scripts, see the  FIM ScriptBox

 



See Also