Querying for FIM CM Profiles and Requests

The FIM CM MA uses SQL to query for the CM Profile and Request objects.  Turns out this isn’t simple to do with the FIM CM Provision API, so going to the database seems justifiable in this case.

The PowerShell snippets below uses the .NET SQL Client to query the FIM CM database using queries similar to the full import from the FIM CM MA.

Some differences include:

Delegation

The FIM CM MA does not execute the SQL from the FIM Sync box.  Instead it asks a proxy object on the FIM CM server to do this.  While this is elegant from a security POV (we are just doing what the CM server already does, instead of pulling the SQL back to the FIM Sync box) it is a pain in production environments where best practice dictates we don’t run FIM CM and SQL on the same server, introducing a kerberos delegation scenario.

The script below initiates the query from ‘localhost’ but could easily be modified to run from a remote server, eliminating the kerberos delegation issue.

Finding the SQL Server

The FIM CM MA by default asks FIM CM where the CM database is located.  This data is stored in the registry on the CM server, and the FIM CM MA queries for it.  The script below simply hard codes the database name and database server name.

Query for CM Requests

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 030 031 032 033

### ### Query for CLM Requests ### Filter out all completed (8), denied(4), failed(9) and canceled(17) requests ### $sqlQuery = @" SELECT Requests.* , UserNameCache.unc_user_nt4_name AS req_target_user_name FROM Requests WITH (NOLOCK) LEFT OUTER JOIN UserNameCache     ON Requests.req_target_user_uuid = UserNameCache.unc_user_uuid WHERE req_status <> 8 AND req_status <> 4 AND req_status <> 9 AND req_status <> 17 "@ $sqlServer = "localhost" $sqlCatalog = "FIMCertificateManagement" $sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnection.ConnectionString = "Server = $SqlServer; Database =$SqlCatalog; Integrated Security = True" $sqlCmd = New-Object System.Data.SqlClient.SqlCommand $sqlCmd.CommandText = $sqlQuery $sqlCmd.Connection = $sqlConnection $sqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $sqlAdapter.SelectCommand = $sqlCmd $dataSet = New-Object System.Data.DataSet $sqlAdapter.Fill($dataSet) $sqlConnection.Close() $dataSet.Tables[0].Rows

Query for CM Profiles

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

### ### Query for CLM Profiles ### $sqlQuery = @" Select Profiles.* , UserNameCache.unc_user_nt4_name AS pr_assigned_user_name, sc_status, sc_serial_number, sc_permanent_sc_uuid FROM Profiles with (NOLOCK) LEFT OUTER JOIN UserNameCache     ON Profiles.pr_assigned_user_uuid = UserNameCache.unc_user_uuid LEFT OUTER JOIN Smartcards     ON Profiles.pr_sc_uuid = Smartcards.sc_uuid "@ $sqlServer = "localhost" $sqlCatalog = "FIMCertificateManagement" $sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnection.ConnectionString = "Server = $SqlServer; Database =$SqlCatalog; Integrated Security = True" $sqlCmd = New-Object System.Data.SqlClient.SqlCommand $sqlCmd.CommandText = $sqlQuery $sqlCmd.Connection = $sqlConnection $sqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $sqlAdapter.SelectCommand = $sqlCmd $dataSet = New-Object System.Data.DataSet $sqlAdapter.Fill($dataSet) $sqlConnection.Close() $dataSet.Tables[0].Rows

Dirty SQL Query to Get MV Attribute Details

First a warning: querying the sync database directly is a worst practice.  The following script was run on a test server, and will never see my production servers.  If you really want to run this on a product server the copy off the database to another server and experiment there.

DO NOT USE THIS ON PRODUCTION SERVERS

Okay, on to business.  I recently had to answer the question: how many times will MA X be precedent for a given attribute?  Unfortunately the consulting answer (it depends) did not fly so I went to the database to find out.  The dirty little script below shows how to dig out the answer.  It is only interesting because it requires the UNPIVOT statement which is not straight forward unless you happen to be David Lundell.

The script below hard codes the MA GUID, as well as the attribute I’m interested in.  There may be a way more elegant script for this but I couldn’t find one so hopefully the next person that needs to answer this question will find either my post or be David ;-)

BTW – this is one of the few questions lately where I did NOT have a good PowerShell answer (at least not a supported one).  PowerShell is awesome for slicing and dicing data with Select-Object and Group-Object but in its infancy just doesn’t have an Unpivot-Object cmdlet yet.  This is easily forgivable since it is way cooler than TSQL will ever be, at least for an IT Pro like me.

   1: ---

   2: ---Objects that have UserPrincipalName attribute contributed by Exchange

   3: ---

   4: SELECT mailNickname

   5: FROM mms_metaverse 

   6: WHERE object_id IN

   7: (

   8:     SELECT Object_id

   9:     FROM 

  10:     (

  11:         SELECT Object_id, userprincipalname

  12:         FROM mms_metaverse_lineageguid

  13:     ) p

  14:     UNPIVOT

  15:     (

  16:         LineageID FOR Attribute IN 

  17:         (

  18:             userprincipalname

  19:         )

  20:     )

  21:     AS unpvt

  22:     JOIN mms_lineage_cross_reference on mms_lineage_cross_reference.lineage_id = unpvt.LineageID

  23:     WHERE 

  24:     (

  25:         mms_lineage_cross_reference.ma_id = 'AAAAAAAA-DDDD-4444-8B6A-CD6ACEA5CB49' 

  26:         and 

  27:         attribute = 'userprincipalname'

  28:     )

  29: )

More FIM Awards

FIM seems to be quietly accumulating quite a few accolades…  Does this mean it is on the verge of becoming mainstream?  Am I still cool if I don’t work on obscure and niche products?  Time will tell, but I doubt it will be another decade :-|

Microsoft STB Products Recognized as Industry Winners in 2010

Using the FIM WMI Provider While Syncs Are Running

It’s always been a worst-practice to make external calls from rules extensions in the sync engine, but a bad idea of mine illustrated the reason why today.

In my scenario, I really want my MV code to fire on objects ONLY when they have already been exported and confirmed by the outbound MA.  The right way to do this is to use a constant Import Attribute Flow (IAF) rule on that MA, in which case the scenario looks like this:

1. MV code creates a new CSEntry in the outbound MA
2. Outbound MA runs Export
3. Outbound MA runs Import
4. Outbound MA runs Sync – this is where our IAF rule flows a value to the MVEntry

If the attribute on the MVEntry has a value, then we know the outbound MA has completed the Export and Import successfully.

The rub in my scenario is that I cannot modify the MV schema, so I can’t use this approach.  Grasping at straws I thought I could just look at the CSEntry using the MIIS_CSObject class in WMI.  This is where the wheels came off.

When I put the code in place and ran Preview, the sync service hung.  In my sync cycle, WMI was being called from my MV code which seems harmless enough since WMI is only reading objects, but I knew I was being less than smart, this was just prototyping, really, I’m normally a lot smarter, at least the second or third time.

Anyhow, this exact scenario created what is most likely a deadlock in SQL but the usual MIIS deadlock error didn’t happen.  Unfortunately the service just hung and had to be killed (killed several times in fact, as I was troubleshooting the issue).  Hard to call this a fault of the product since I should know better, but I would have liked the sync cycle to fail with a deadlock error, or the WMI call to fail with something similar.

The moral of the story is simple; follow the best practices as much as possible; don’t make external calls from rules extension code, and don’t query the sync database (even with WMI I guess) while the sync engine is running.

Speaking at TechReady12 in February!

Got lucky with session submissions and will be speaking at TechReady12 in February.  I’ll be talking about PowerShell integration with FIM, and another session about integrating FIM CM with FIM Sync and the FIM Service and Portal.

Hope to see you there!

In Search of Export-Not-Reimported Errors

Lately I’ve been scripted the heck out of the Run-History.  This facility has been in the product forever but now PowerShell makes it a lot easier to slice and dice thanks to the way PowerShell makes WMI and XML very easy.

The oddity here is that the CSObject in the Sync engine contains a lot of useful error detail, as well as the holograms.  There’s a lot of data packed into those CSObjects, and pretty much the only facilities for getting at them are WMI and CSExport.exe.

The MIIS_CSObject WMI class allows easy access to the CSObjects but unfortunately the query functionality is very limited.  You need to know the DN or the GUID of the CSObject in order to find it with WMI.  My guess is that this is because the rest of the attributes in the CSObject are packed away as binary blobs in the underlying SQL database.

Anyhow, my point is that there is a lot of detail on the CSObject but the Export-Not-Reimported details are stored in RunHistory, not on the CSObject.

The script below is a sample for getting the run history from the server, then getting the RunDetails XML.  The RunDetails XML can then be parsed to dig out the export-not-reimported errors, which I’ll show in another post.

001 002 003 004 005 006 007 008 009 010 011 012 013 014 015 016 017 018 019 020 021 022 023

### Use WMI to get the Management Agent $managementAgent = Get-WmiObject -Class MIIS_ManagementAgent -Namespace root/MicrosoftIdentityIntegrationServer -Filter "name='HoofHearted'" ### Construct a filter to get the management agent's last run $filter = ("RunNumber='{0}'" -F $managementAgent.RunNumber().ReturnValue) ### Use WMI to get the RunHistory $theRun = Get-WmiObject -Class MIIS_RunHistory -Namespace root/MicrosoftIdentityIntegrationServer -Filter $filter ### Call the RunDetails() method to get the RunHistory RunDetails XML [xml]$runHistoryDetails = $theRun.RunDetails().ReturnValue if ($runHistoryDetails -ne $null) {     Write-Host " ...Success" -ForegroundColor Green } else {     ### There have been bugs with the WMI provider for ILM/FIM     ### When it fails it usually returns null     ### But on rare occasions I've also seen it fail with out-of-memory errors (even though the box had 128GB, and over 100GB free)     Write-Host " ...Error" -ForegroundColor Red     Continue }

Use PowerShell Jobs to Run Management Agents in Parallel

It is pretty easy with PowerShell to execute management agents in FIM.  It is also pretty easy to do it in parallel using PowerShell jobs.
The sample below shows how to use the Start-Job cmdlet to run Management Agents in FIM.  In this sample I am using Get-WmiObject to get all of the Management Agents, then I apply a Where filter to return just the ADMAs.  I could have specified a similar filter using Get-WmiObject, and would have done so if there were a large number of management agents on the server.
The Start-Job cmdlet runs the script block in another PowerShell session.  If you’re watching Task Manager while the script runs you’ll see another PowerShell.exe process start up.  Also, if you’re watching Identity Manager, you’ll see in the Operations pane that all of the ADMAs start at about the same time (cool!).
Keep in mind that THIS IS NOT RECOMMENDED FOR SYNCHRONIZATION RUN PROFILES!!!  The sync engine does not like this one bit, and will likely grind to a halt dealing with SQL deadlocks.

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

### ### Run ADMAs - Full Import - In Parallel ### $admas = Get-WmiObject -Class MIIS_ManagementAgent -Namespace root/MicrosoftIdentityIntegrationServer | where {$_.Type -eq 'Active Directory'} foreach ($ma in $admas) {     Write-host "Starting Full Import on" $ma.Name"..."      Start-Job -ArgumentList $ma.Name -ScriptBlock `         {             param($maName)$ma = Get-WmiObject -Class MIIS_ManagementAgent -Namespace root/MicrosoftIdentityIntegrationServer -Filter "name='$maName'";             $result = $ma.Execute('Full Import').ReturnValue;             Write-Host ("{0} result: {1}" -f $maName, $result)          } | Out-Null } ### Wait for the imports to complete before returning Get-Job | Wait-Job ### Review the output from each job Get-Job | Receive-Job ### Remove the jobs Get-Job | Remove-Job