Wednesday, December 29, 2010

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

No comments: