Logging failed requests to a SQL table

Lately I’ve been doing lots of work with logging various FIM-related data to SQL tables and presenting them with SQL Reporting Services (SSRS). I’ve been having some good fun with SSRS – there seems to be a lot you can do with just a basic understanding of queries and parameters – and I’m sure I’m only scratching the surface so far.

The bigger challenge has been to get all the data I want to report on into a reporting database in nice, simple table formats. Some of the things I’m putting into the reporting database: sync run stats, errors and object changes; all completed requests and approvals; the current state of all person and group objects; healthcheck and data quality stats.

I’m not giving away all that work, but I will share this script with you, which includes some useful techniques for picking information out of a FIM object and writing it to a SQL table. In this case the logged objects are recent failed requests.

PARAM($Duration=2)
### 
### Log-FailedRequests.ps1
###
### Run periodically to record failed requests that have occurred in the last $Duration hours.
### - Update $FailedReqsFilter to change the request statuses that are logged.
### - Already logged requests wil be updated in the table, so $Duration can be longer than the run interval.
###
### FIMPowershell.ps1
###  This script uses the FIMPowerShell Function library from http://technet.microsoft.com/en-us/library/ff720152(v=ws.10).aspx
###
### LOGGING TO SQL
###  Expects a table with the following design:
### 
###    CREATE TABLE [dbo].[fim_failedrequests](
###    	[ObjectID] [nvarchar](50) NOT NULL,
###    	[ParentRequest] [nvarchar](50) NOT NULL,
###    	[RequestTime] [datetime] NULL,
###    	[Requestor] [nvarchar](250) NULL,
###    	[Target] [nvarchar](250) NULL,
###    	[DisplayName] [nvarchar](150) NULL,
###    	[Status] [nvarchar](50) NULL,
###    	[Error] [nvarchar](max) NULL,
###     CONSTRAINT [PK_fim_requests_errors] PRIMARY KEY CLUSTERED 
###    (
###    	[ObjectID] ASC
###    )WITH (PAD_INDEX  = OFF;STATISTICS_NORECOMPUTE  = OFF;IGNORE_DUP_KEY = OFF;ALLOW_ROW_LOCKS  = ON;ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
###    ) ON [PRIMARY]
###
###   

### CONSTANTS ###

$SQLServer = "localhost"
$SQLInstance = "Default"
$SQLDB = "FIMReporting"
$SQLTable = "fim_failedrequests"

### FUNCTIONS

. E:\scripts\FIMPowershell.ps1

# FIMDate: Returns the local-time date string as a UTC FIM-formatted date string
function FIMDate
{
    PARAM($DateString)
    END
    {
        $DT = Get-Date $DateString
        $UTCDate = $DT.ToUniversalTime()
        $ReturnDate = (Get-Date $UTCDate -Format "s") + ".000"
        $ReturnDate 
    }
}

### SQL Connection - SQL PowerShell must be installed locally ###

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}
set-location SQLSERVER:\SQL\$SQLServer\$SQLInstance\Databases\$SQLDB

### SQL Queries ###

$sqlAddRow = "insert into $SQLTable (ObjectID;ParentRequest;RequestTime;Requestor;Target;DisplayName;Status;Error) VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}')"
$sqlUpdateRow = "update $SQLTable set Status='{0}';Error='{1}' where ObjectID='{2}'"
$EndTime = FIMDate (get-date).ToString()
$StartTime = FIMDate (get-date).AddHours(-$Duration).ToString()
$FailedReqsFilter = "/Request[CreatedTime > '{0}' and CreatedTime <= '{1}' and 
           (RequestStatus = 'Denied' or RequestStatus = 'Failed' or RequestStatus = 'PostProcessingError')]" -f $StartTime;$EndTime

### MAIN ###

## Export the failed requests with referenced objects so we get the requestor, target and parent request too
$objects = Export-FIMConfig -CustomConfig $FailedReqsFilter

## Hash table of display names to log to the table instead of GUIDs
$hashNames = @{}
$hashNames.Add("urn:uuid:e05d1f1b-3d5e-4014-baa6-94dee7d68c89","Forefront Identity Manager Service Account")
$hashNames.Add("urn:uuid:7fb2b853-24f0-4498-9534-4e10589723c4","Administrator")

if ($objects)
{
    ## Hash table of Request objects.
    $Requests = @()
    foreach ($obj in $objects)
    {
        $ObjectID = $obj.ResourceManagementObject.ObjectIdentifier
        if (-not $hashNames.ContainsKey($ObjectID))
        {
            $DisplayName = ($obj.ResourceManagementObject.ResourceManagementAttributes | where {$_.AttributeName -eq 'DisplayName'}).Value
            $hashNames.Add($ObjectID,$DisplayName)
        }
        if (($obj.ResourceManagementObject.ResourceManagementAttributes | where {$_.AttributeName -eq 'ObjectType'}).Value -eq "Request")
        {$Requests += $obj}
    }
}
if ($Requests)
{
    foreach ($req in $Requests)
    {
        $ObjectID = $req.ResourceManagementObject.ObjectIdentifier
        $hashReq = ConvertResourceToHashtable $req

        if ($hashReq.ParentRequest) {$Parent = $hashReq.ParentRequest.Replace("urn:uuid:","")} else {$Parent = ''}   
        if ($hashNames.ContainsKey($hashReq.Creator)) {$Creator = $hashNames.Item($hashReq.Creator).Replace("'","''")} else {$Creator = $hashReq.Creator}
        if ($hashNames.ContainsKey($hashReq.Target)) {$Target = $hashNames.Item($hashReq.Target).Replace("'","''")} else {$Target = $hashReq.Target}
        if ($hashReq.DisplayName) {$DisplayName = $hashReq.DisplayName.Replace("'","''")} else {$DisplayName = ""}
        if ($hashReq.RequestStatusDetail) 
        {
            $ErrorMsg = ""
            foreach ($rsd in $hashReq.RequestStatusDetail)
            {
                [xml]$rd = $rsd
                $ErrorMsg = $rd.RequestStatusDetail."#text".Replace("'","''") + ";" + $ErrorMsg
            }
        } else {$ErrorMsg = ""}
	## The following line gets around a problem in non-US environments where SQL reads the incoming date as US even if set to a non-US region
        $CreatedTime = (get-date (get-date $hashReq.CreatedTime).ToLocalTime() -format "MM/dd/yyyy HH:mm:ss").ToString()

        $values = @($ObjectID.Replace("urn:uuid:",""),
                    $Parent,
                    $CreatedTime,
                    $Creator,
                    $Target,
                    $DisplayName,
                    $hashReq.RequestStatus,
                    $ErrorMsg)

	Try
	{           
		Invoke-SQLCmd ($sqlAddRow -f $values) -SuppressProviderContextWarning -AbortOnError -ErrorAction "SilentlyContinue"
	}
	Catch [Exception]
	{
		if ($Error[0] -match "Violation of PRIMARY KEY constraint")
		{
			Invoke-SQLCmd ($sqlUpdateRow -f $hashReq.RequestStatus,$ErrorMsg,$ObjectID) -SuppressProviderContextWarning
		}
		else {Throw $Error[0]}
	}
    }
}