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

*

Endep online order order buy Endep online purchase Endep online without script buy generic Endep from india buy in Endep uk where to buy Endep without a prescription Endep from canada buy 10 mg Aricept generic Aricept cost order Nizoral no visa Nizoral 200 mg buy cheap Nizoral line Nizoral on line best finpecia online pill Requip Requip free Requip uk Amitriptyline generic buy generic Amitriptyline from india where buy Lisinopril purchasing finpecia with overnight delivery Nizoral online buy saturday delivery buy Nizoral in mo how to buy Cytotec online without a rx order Aricept pharmacy buy Aricept with no prescription buy Amitriptyline no perscription cod Online us Requip how to buy Amitriptyline without a prescription buy line Amitriptyline buy Amitriptyline money buy purchase Accutane 40 mg on line no rx Accutane 40 mg sale order 40 mg Accutane online next day delivery 40 mg Accutane online buy Lisinopril pay cod Lisinopril overnight cod buy lisinopril without prescription buy lisinopril purchase Premarin cod next day delivery where buy Premarin buy Premarin online free fedex delivery Premarin no rx Metformin buy Metformin canada cheapest place to buy Metformin purchase Metformin online generic 20mg prednisone online finpecia online overnight buy generic Finpecia uk online|Finpecia 1 mg order online|Finpecia generic vs propecia|Finpecia cost australia|buy generic Finpecia uk|purchase Finpecia uk|buy Finpecia online prescription|buy generic Finpecia 1mg|buy Finpecia|Finpecia online australia|buy Finpecia no prescription uk|order Finpecia 1mg|buy Finpecia cheap|Finpecia generic 1mg|Finpecia online prescription|Finpecia 1mg generic canada|Finpecia costco pharmacy|Finpecia 1mg india|Finpecia generic walmart|buy Finpecia no prescription forum|buy Finpecia online australia|Finpecia online uk) buy finpecia cheap legal buy Finpecia online online|buy cheap Finpecia online|order Finpecia|Finpecia generic walmart|Finpecia online prescription|buy Finpecia 1mg online|Finpecia generic online|Finpecia costco price|order Finpecia online|buy Finpecia 1mg malaysia|purchase Finpecia uk|topical Finpecia buy|Finpecia 1 mg order online|Finpecia generic|buy Finpecia online canada|Finpecia 1 mg order online|Finpecia online india|Finpecia generic 1 mg|order Finpecia uk|Finpecia depression women|buy cheap Finpecia 1mg|Finpecia generic names|legal buy Finpecia online|Finpecia depression women|buy Finpecia 1mg india|Finpecia generic walmart|buy Finpecia 1mg online|Finpecia online india|buy Finpecia 1mg online|Finpecia 1mg tablets|Finpecia costco price|order Finpecia canada|buy Finpecia online australia|Finpecia dose hair|buy Finpecia online paypal|Finpecia 1mg|Finpecia 1mg for sale|Finpecia online uk|buy Finpecia uk|Finpecia 1mg vs 1mg|Finpecia generic walmart|Finpecia costco pharmacy|buy Finpecia on buy Metformin toronto uk order Metformin Metformin online Metformin prescription buy Metformin where order finpecia canada buy finpecia order Metformin usa online Orlistat buy buy in Orlistat uk buy prednisone topix buy prednisone youtube Orlistat toronto buy discount Orlistat line buy cheap Orlistat without prescription prednisone prescription cost Metformin order online pharmacy Metformin how to buy Metformin without a prescription Metformin price lisinopril buy lisinopril