archive details about Approvals<\/a>.<\/p><\/blockquote>\n<\/p>\n
Create Reporting Tables<\/h3>\n
Start by creating the following tables in the database you use for reporting (ie NOT one of the DBs installed by FIM. I have a dedicated DB called “FIMReporting”):<\/p>\n
USE [FIMReporting]\r\nCREATE TABLE [dbo].[fim_requests_new](\r\n\t[ObjectKey] [nvarchar](50) NULL,\r\n\t[Attribute] [nvarchar](50) NULL,\r\n\t[Value] [nvarchar](max) NULL\r\n) ON [PRIMARY]<\/pre>\n <\/p>\n
USE [FIMReporting]\r\nCREATE TABLE [dbo].[fim_requests_log](\r\n\t[ObjectKey] [nvarchar](150) NULL,\r\n\t[Creator] [nvarchar](150) NULL,\r\n\t[CreatedTime] [nvarchar](150) NULL,\r\n\t[CommittedTime] [nvarchar](150) NULL,\r\n\t[Operation] [nvarchar](150) NULL,\r\n\t[Target] [nvarchar](150) NULL,\r\n\t[TargetObjectType] [nvarchar](150) NULL,\r\n\t[ManagementPolicy] [nvarchar](500) NULL,\r\n\t[RequestStatus] [nvarchar](150) NULL,\r\n\t[RequestParameter] [nvarchar](max) NULL\r\n) ON [PRIMARY]<\/pre>\nExtract recent requests<\/h3>\n
Once an hour, at the end of the regular sync cycle, I run this SQL script to copy out the requests I haven’t yet logged:<\/p>\n
truncate table FIMReporting.dbo.fim_requests_new;\r\n\r\ninsert into FIMReporting.dbo.fim_requests_new\r\nselect o.ObjectKey, 'ObjectKey' as Attribute, o.ObjectKey as Value\r\nfrom FIMService.fim.Objects o\r\nleft outer join dbo.fim_requests_log l\r\non o.ObjectKey = l.ObjectKey\r\ninner join FIMService.fim.ObjectValueString s\r\non o.ObjectKey = s.ObjectKey\r\nwhere o.ObjectTypeKey = 26\r\nand l.ObjectKey is null\r\nand s.AttributeKey = 66;\r\n\r\ninsert into FIMReporting.dbo.fim_requests_new\r\nselect v.ObjectKey, a.Name as Attribute, CAST(v.ValueBoolean as nvarchar) as Value\r\nfrom FIMService.fim.ObjectValueBoolean v\r\njoin FIMService.fim.AttributeInternal a\r\non v.AttributeKey = a.[Key]\r\njoin FIMReporting.dbo.fim_requests_new n\r\non v.ObjectKey = n.ObjectKey\r\nwhere n.Attribute = 'ObjectKey';\r\n\r\ninsert into FIMReporting.dbo.fim_requests_new\r\nselect v.ObjectKey, a.Name as Attribute, CAST(v.ValueDateTime as nvarchar) as Value\r\nfrom FIMService.fim.ObjectValueDateTime v\r\njoin FIMService.fim.AttributeInternal a\r\non v.AttributeKey = a.[Key]\r\njoin FIMReporting.dbo.fim_requests_new n\r\non v.ObjectKey = n.ObjectKey\r\nwhere n.Attribute = 'ObjectKey';\r\n\r\ninsert into FIMReporting.dbo.fim_requests_new\r\nselect v.ObjectKey, a.Name as Attribute, CAST(ValueInteger as nvarchar) as Value\r\nfrom FIMService.fim.ObjectValueInteger v\r\njoin FIMService.fim.AttributeInternal a\r\non v.AttributeKey = a.[Key]\r\njoin FIMReporting.dbo.fim_requests_new n\r\non v.ObjectKey = n.ObjectKey\r\nwhere n.Attribute = 'ObjectKey';\r\n\r\ninsert into FIMReporting.dbo.fim_requests_new\r\nselect v.ObjectKey, a.Name as Attribute, name.ValueString as Value\r\nfrom FIMService.fim.ObjectValueReference v\r\njoin FIMService.fim.AttributeInternal a\r\non v.AttributeKey = a.[Key]\r\njoin FIMReporting.dbo.fim_requests_new n\r\non v.ObjectKey = n.ObjectKey\r\njoin FIMService.fim.Objects ref\r\non v.ValueReference = ref.ObjectKey\r\njoin FIMService.fim.ObjectValueString name\r\non ref.ObjectKey = name.ObjectKey\r\nwhere n.Attribute = 'ObjectKey'\r\nand name.AttributeKey = 66;\r\n\r\ninsert into FIMReporting.dbo.fim_requests_new\r\nselect v.ObjectKey, Name as Attribute, ValueString as Value\r\nfrom FIMService.fim.ObjectValueString v\r\njoin FIMService.fim.AttributeInternal a\r\non v.AttributeKey = a.[Key]\r\njoin FIMReporting.dbo.fim_requests_new n\r\non v.ObjectKey = n.ObjectKey\r\nwhere n.Attribute = 'ObjectKey';\r\n\r\ninsert into FIMReporting.dbo.fim_requests_new\r\nselect v.ObjectKey, Name as Attribute, ValueText as Value\r\n from FIMService.fim.ObjectValueText v\r\njoin FIMService.fim.AttributeInternal a\r\non v.AttributeKey = a.[Key]\r\njoin FIMReporting.dbo.fim_requests_new n\r\non v.ObjectKey = n.ObjectKey\r\nwhere n.Attribute = 'ObjectKey';<\/pre>\nPivot query<\/h3>\n
I then have to run another SQL script to pivot the data so I end up with one line per request in my log table:<\/p>\n
use FIMReporting\r\n\r\ninsert into dbo.fim_requests_log\r\n\r\nSELECT ObjectKey,Creator,CreatedTime,CommittedTime,Operation,[Target],\r\n TargetObjectType,ManagementPolicy,RequestStatus,RequestParameter\r\nFROM\r\n (select * from dbo.fim_requests_new\r\n\twhere ObjectKey in (\r\n\tselect ObjectKey from dbo.fim_requests_new\r\n\twhere Attribute = 'RequestStatus'\r\n\tand Value in ('Completed','Failed','Denied','PostProcessingError') )) as src\r\nPIVOT\r\n( MAX(Value) FOR Attribute\r\n IN ( Creator,CreatedTime,CommittedTime,Operation,[Target],TargetObjectType,ManagementPolicy,RequestStatus,RequestParameter)\r\n) AS pvt;<\/pre>\nPruning<\/h3>\n
As I don’t want the data in this log to build up and up I have a third script which I run once a day to prune records. I do this in two steps – first I get rid of requests made by the service accounts, and later I delete all requests.<\/p>\n
use FIMReporting\r\n\r\ndeclare @pruneDays as int\r\ndeclare @deleteDays as int\r\nset @pruneDays = 50 \/* Must be greater than number of day kept in FIMService DB *\/\r\nset @deleteDays = 180\r\n\r\ndeclare @today as nvarchar(50)\r\nset @today = GetDate()\r\n\r\ndelete from dbo.fim_requests_log\r\nwhere datediff(day,CONVERT(datetime,CreatedTime),@today) > @deleteDays\r\n\r\n\/* Prune service account requests. YOUR ACCOUNT NAMES WILL VARY. *\/\r\ndelete from dbo.fim_requests_log\r\nwhere datediff(day,CONVERT(datetime,CreatedTime),@today) > @pruneDays\r\nand Creator in ('s-fimportal', 'Forefront Identity Manager Service Account', 'Built-in Synchronization Account')<\/pre>\n","protected":false},"excerpt":{"rendered":"There were a few FIM reporting sessions at TEC, none of which I managed to make it to, though I hope the presenters will be making their solutions generally available as they are undoubtably more correct and complete than what I’ve been doing. However a couple of people did ask that I post my method…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","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,9,61,5],"tags":[],"class_list":["post-1411","post","type-post","status-publish","format-standard","hentry","category-fim-2010","category-logs","category-reporting","category-sql"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/pkp1o-mL","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/posts\/1411","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=1411"}],"version-history":[{"count":13,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/posts\/1411\/revisions"}],"predecessor-version":[{"id":1499,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/posts\/1411\/revisions\/1499"}],"wp:attachment":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/media?parent=1411"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/categories?post=1411"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/tags?post=1411"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}