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.<\/p>\n
<\/p>\n
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.<\/p>\n
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.<\/p>\n
PARAM($Duration=2)\r\n### \r\n### Log-FailedRequests.ps1\r\n###\r\n### Run periodically to record failed requests that have occurred in the last $Duration hours.\r\n### - Update $FailedReqsFilter to change the request statuses that are logged.\r\n### - Already logged requests wil be updated in the table, so $Duration can be longer than the run interval.\r\n###\r\n### FIMPowershell.ps1\r\n### This script uses the FIMPowerShell Function library from http:\/\/technet.microsoft.com\/en-us\/library\/ff720152(v=ws.10).aspx\r\n###\r\n### LOGGING TO SQL\r\n### Expects a table with the following design:\r\n### \r\n### CREATE TABLE [dbo].[fim_failedrequests](\r\n### \t[ObjectID] [nvarchar](50) NOT NULL,\r\n### \t[ParentRequest] [nvarchar](50) NOT NULL,\r\n### \t[RequestTime] [datetime] NULL,\r\n### \t[Requestor] [nvarchar](250) NULL,\r\n### \t[Target] [nvarchar](250) NULL,\r\n### \t[DisplayName] [nvarchar](150) NULL,\r\n### \t[Status] [nvarchar](50) NULL,\r\n### \t[Error] [nvarchar](max) NULL,\r\n### CONSTRAINT [PK_fim_requests_errors] PRIMARY KEY CLUSTERED \r\n### (\r\n### \t[ObjectID] ASC\r\n### )WITH (PAD_INDEX = OFF;STATISTICS_NORECOMPUTE = OFF;IGNORE_DUP_KEY = OFF;ALLOW_ROW_LOCKS = ON;ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]\r\n### ) ON [PRIMARY]\r\n###\r\n### \r\n\r\n### CONSTANTS ###\r\n\r\n$SQLServer = \"localhost\"\r\n$SQLInstance = \"Default\"\r\n$SQLDB = \"FIMReporting\"\r\n$SQLTable = \"fim_failedrequests\"\r\n\r\n### FUNCTIONS\r\n\r\n. E:\\scripts\\FIMPowershell.ps1\r\n\r\n# FIMDate: Returns the local-time date string as a UTC FIM-formatted date string\r\nfunction FIMDate\r\n{\r\n PARAM($DateString)\r\n END\r\n {\r\n $DT = Get-Date $DateString\r\n $UTCDate = $DT.ToUniversalTime()\r\n $ReturnDate = (Get-Date $UTCDate -Format \"s\") + \".000\"\r\n $ReturnDate \r\n }\r\n}\r\n\r\n### SQL Connection - SQL PowerShell must be installed locally ###\r\n\r\nif(@(get-pssnapin | where-object {$_.Name -eq \u00e2\u20ac\u0153sqlserverprovidersnapin100\u00e2\u20ac\u009d} ).count -eq 0) {add-pssnapin sqlserverprovidersnapin100}\r\nif(@(get-pssnapin | where-object {$_.Name -eq \u00e2\u20ac\u0153sqlservercmdletsnapin100\u00e2\u20ac\u009d} ).count -eq 0) {add-pssnapin sqlservercmdletsnapin100}\r\nset-location SQLSERVER:\\SQL\\$SQLServer\\$SQLInstance\\Databases\\$SQLDB\r\n\r\n### SQL Queries ###\r\n\r\n$sqlAddRow = \"insert into $SQLTable (ObjectID;ParentRequest;RequestTime;Requestor;Target;DisplayName;Status;Error) VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}')\"\r\n$sqlUpdateRow = \"update $SQLTable set Status='{0}';Error='{1}' where ObjectID='{2}'\"\r\n$EndTime = FIMDate (get-date).ToString()\r\n$StartTime = FIMDate (get-date).AddHours(-$Duration).ToString()\r\n$FailedReqsFilter = \"\/Request[CreatedTime > '{0}' and CreatedTime <= '{1}' and \r\n (RequestStatus = 'Denied' or RequestStatus = 'Failed' or RequestStatus = 'PostProcessingError')]\" -f $StartTime;$EndTime\r\n\r\n### MAIN ###\r\n\r\n## Export the failed requests with referenced objects so we get the requestor, target and parent request too\r\n$objects = Export-FIMConfig -CustomConfig $FailedReqsFilter\r\n\r\n## Hash table of display names to log to the table instead of GUIDs\r\n$hashNames = @{}\r\n$hashNames.Add(\"urn:uuid:e05d1f1b-3d5e-4014-baa6-94dee7d68c89\",\"Forefront Identity Manager Service Account\")\r\n$hashNames.Add(\"urn:uuid:7fb2b853-24f0-4498-9534-4e10589723c4\",\"Administrator\")\r\n\r\nif ($objects)\r\n{\r\n ## Hash table of Request objects.\r\n $Requests = @()\r\n foreach ($obj in $objects)\r\n {\r\n $ObjectID = $obj.ResourceManagementObject.ObjectIdentifier\r\n if (-not $hashNames.ContainsKey($ObjectID))\r\n {\r\n $DisplayName = ($obj.ResourceManagementObject.ResourceManagementAttributes | where {$_.AttributeName -eq 'DisplayName'}).Value\r\n $hashNames.Add($ObjectID,$DisplayName)\r\n }\r\n if (($obj.ResourceManagementObject.ResourceManagementAttributes | where {$_.AttributeName -eq 'ObjectType'}).Value -eq \"Request\")\r\n {$Requests += $obj}\r\n }\r\n}\r\nif ($Requests)\r\n{\r\n foreach ($req in $Requests)\r\n {\r\n $ObjectID = $req.ResourceManagementObject.ObjectIdentifier\r\n $hashReq = ConvertResourceToHashtable $req\r\n\r\n if ($hashReq.ParentRequest) {$Parent = $hashReq.ParentRequest.Replace(\"urn:uuid:\",\"\")} else {$Parent = ''} \r\n if ($hashNames.ContainsKey($hashReq.Creator)) {$Creator = $hashNames.Item($hashReq.Creator).Replace(\"'\",\"''\")} else {$Creator = $hashReq.Creator}\r\n if ($hashNames.ContainsKey($hashReq.Target)) {$Target = $hashNames.Item($hashReq.Target).Replace(\"'\",\"''\")} else {$Target = $hashReq.Target}\r\n if ($hashReq.DisplayName) {$DisplayName = $hashReq.DisplayName.Replace(\"'\",\"''\")} else {$DisplayName = \"\"}\r\n if ($hashReq.RequestStatusDetail) \r\n {\r\n $ErrorMsg = \"\"\r\n foreach ($rsd in $hashReq.RequestStatusDetail)\r\n {\r\n [xml]$rd = $rsd\r\n $ErrorMsg = $rd.RequestStatusDetail.\"#text\".Replace(\"'\",\"''\") + \";\" + $ErrorMsg\r\n }\r\n } else {$ErrorMsg = \"\"}\r\n\t## 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\r\n $CreatedTime = (get-date (get-date $hashReq.CreatedTime).ToLocalTime() -format \"MM\/dd\/yyyy HH:mm:ss\").ToString()\r\n\r\n $values = @($ObjectID.Replace(\"urn:uuid:\",\"\"),\r\n $Parent,\r\n $CreatedTime,\r\n $Creator,\r\n $Target,\r\n $DisplayName,\r\n $hashReq.RequestStatus,\r\n $ErrorMsg)\r\n\r\n\tTry\r\n\t{ \r\n\t\tInvoke-SQLCmd ($sqlAddRow -f $values) -SuppressProviderContextWarning -AbortOnError -ErrorAction \"SilentlyContinue\"\r\n\t}\r\n\tCatch [Exception]\r\n\t{\r\n\t\tif ($Error[0] -match \"Violation of PRIMARY KEY constraint\")\r\n\t\t{\r\n\t\t\tInvoke-SQLCmd ($sqlUpdateRow -f $hashReq.RequestStatus,$ErrorMsg,$ObjectID) -SuppressProviderContextWarning\r\n\t\t}\r\n\t\telse {Throw $Error[0]}\r\n\t}\r\n }\r\n}<\/pre>\n","protected":false},"excerpt":{"rendered":"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…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"footnotes":"","jetpack_publicize_message":"","jetpack_is_tweetstorm":false,"jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":[]},"categories":[42,60,23,61,5],"tags":[],"class_list":["post-2590","post","type-post","status-publish","format-standard","hentry","category-fim-2010","category-fim-2010-r2","category-powershell","category-reporting","category-sql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/pkp1o-FM","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/posts\/2590","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/comments?post=2590"}],"version-history":[{"count":12,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/posts\/2590\/revisions"}],"predecessor-version":[{"id":2602,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/posts\/2590\/revisions\/2602"}],"wp:attachment":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/media?parent=2590"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/categories?post=2590"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/tags?post=2590"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}