Skip to content

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]}
	}
    }
}

{ 1 } Comments

  1. Brian Desmond | April 24, 2013 at 4:20 pm | Permalink

    Carol-

    Two thoughts come to mind:

    – Why not use UniqueIdentifier columns for all the GUIDs? Along the same line, did you match the string column lengths to their maximums in the FIM Service database?

    – For the SQL insert, if you use a parameterized query, you will not have to worry about the injection issue with the single quotes.

    Also, if you use the native .Net SQL classes, you don’t have to take a dependency on having SQL PowerShell installed locally

Post a Comment

Your email is never published nor shared. Required fields are marked *

Spam comments will be blocked by Akismet

*

Tamoxifen for sale Furosemide 40 mg without prescription Cytotec no prescription to buy buy Requip 2mg order Seroquel online canada buy Clomiphene australia buy Seroquel generic order no prescription Metformin online Requip buy Seroquel purchase overnight delivery Seroquel no prescription needed 50mg buy seroquel online without prescription from canada no prescription generic seroquel seroquel for sale canadian pharmacy no prescription seroquel buy seroquel online uk buy seroquel 25 mg seroquel online 50mg how to buy seroquel online seroquel online uk seroquel 50mg canada buy generic seroquel seroquel no script seroquel online without a prescription can you buy seroquel online buy seroquel usa buy seroquel in canada Prednisone on line buy Prednisone overnight buy cheap Prednisone online free consult buy Prednisone on line amex uk buy Prednisone buy cod Prednisone order overnight Prednisone medikament Prednisone Prednisone order online buy Prednisone no scams buy Prednisone mastercard online purchase Prednisone buy cheap Prednisone no prescription buy Prednisone amex online without rx buy Prednisone canada purchasing Prednisone without a script where to buy generic Prednisone online without a prescription Prednisone drug non rx cheap Prednisone order Prednisone cheap overnight fedex Prednisone overnight without a prescription buy Prednisone 20 mg buy Prednisone where Prednisone without prescription overnight shipping purchasing Prednisone without a script Prednisone without rx overnight shipping where to purchase Prednisone no prescription no fees Prednisone online overnight delivery cod Prednisone prescription order order Prednisone without rx from us pharmacy buy Prednisone pills in toronto buy Prednisone online online pharmacies Prednisone buy Prednisone no prescriptions buy Prednisone once a day order Prednisone pay pal online without rx buy Prednisone where buy line Prednisone best finpecia online pill buy Maxalt us pharmacy Maxalt buy Maxalt with visa Cytotec purchase overnight delivery where buy Lisinopril purchasing finpecia with overnight delivery no prescription requip buy requip over the counter where can i order requip online how to get arimidex arimidex cheap online buy Requip online australia generic requip no prescription arimidex ordering requip no rx in us requip buying requip online without prescription Requip express online i need to order Requip without a prescription buy Requip from the uk purchase Arimidex order requip without rx arimidex online order where can i buy arimidex without prescriptions buy arimidex generic requip for sale without prescription buy generic arimidex buy requip no prescription needed order requip online overnight shipping cheap generic arimidex no prescription requip buy online no prescription Lisinopril overnight cod cheap order rx metformin prednisone 40 mg cheapest place to buy Metformin purchase Metformin online Maxalt no prescription needed 10mg Maxalt 10mg canada where to buy Maxalt uk buy generic Maxalt canada buy metformin cod discount Cytotec order Cytotec uk purchase Cytotec online no membership overnight shipping best Cytotec online pill Valacyclovir Cytotec want to buy Cytotec in usa Cytotec online overnight delivery cod buy cytotec with a mastercard order Cytotec no rx how to order Cytotec online without a rx how to buy Metformin without a prescription Metformin price buy Alli without prescription