Skip to content

A Basic Requests Reporting Method

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 which is, in my usually simplistic DIY fashion, a rip of data direct from the FIMService DB using queries.

Note: this workaround should become redundant with R2 which includes archiving of request data. In the meantime I’ve also added another method to archive details about Approvals.

Create Reporting Tables

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”):

USE [FIMReporting]
CREATE TABLE [dbo].[fim_requests_new](
	[ObjectKey] [nvarchar](50) NULL,
	[Attribute] [nvarchar](50) NULL,
	[Value] [nvarchar](max) NULL
) ON [PRIMARY]

 

USE [FIMReporting]
CREATE TABLE [dbo].[fim_requests_log](
	[ObjectKey] [nvarchar](150) NULL,
	[Creator] [nvarchar](150) NULL,
	[CreatedTime] [nvarchar](150) NULL,
	[CommittedTime] [nvarchar](150) NULL,
	[Operation] [nvarchar](150) NULL,
	[Target] [nvarchar](150) NULL,
	[TargetObjectType] [nvarchar](150) NULL,
	[ManagementPolicy] [nvarchar](500) NULL,
	[RequestStatus] [nvarchar](150) NULL,
	[RequestParameter] [nvarchar](max) NULL
) ON [PRIMARY]

Extract recent requests

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:

truncate table FIMReporting.dbo.fim_requests_new;

insert into FIMReporting.dbo.fim_requests_new
select o.ObjectKey, 'ObjectKey' as Attribute, o.ObjectKey as Value
from FIMService.fim.Objects o
left outer join dbo.fim_requests_log l
on o.ObjectKey = l.ObjectKey
inner join FIMService.fim.ObjectValueString s
on o.ObjectKey = s.ObjectKey
where o.ObjectTypeKey = 26
and l.ObjectKey is null
and s.AttributeKey = 66;

insert into FIMReporting.dbo.fim_requests_new
select v.ObjectKey, a.Name as Attribute, CAST(v.ValueBoolean as nvarchar) as Value
from FIMService.fim.ObjectValueBoolean v
join FIMService.fim.AttributeInternal a
on v.AttributeKey = a.[Key]
join FIMReporting.dbo.fim_requests_new n
on v.ObjectKey = n.ObjectKey
where n.Attribute = 'ObjectKey';

insert into FIMReporting.dbo.fim_requests_new
select v.ObjectKey, a.Name as Attribute, CAST(v.ValueDateTime as nvarchar) as Value
from FIMService.fim.ObjectValueDateTime v
join FIMService.fim.AttributeInternal a
on v.AttributeKey = a.[Key]
join FIMReporting.dbo.fim_requests_new n
on v.ObjectKey = n.ObjectKey
where n.Attribute = 'ObjectKey';

insert into FIMReporting.dbo.fim_requests_new
select v.ObjectKey, a.Name as Attribute, CAST(ValueInteger as nvarchar) as Value
from FIMService.fim.ObjectValueInteger v
join FIMService.fim.AttributeInternal a
on v.AttributeKey = a.[Key]
join FIMReporting.dbo.fim_requests_new n
on v.ObjectKey = n.ObjectKey
where n.Attribute = 'ObjectKey';

insert into FIMReporting.dbo.fim_requests_new
select v.ObjectKey, a.Name as Attribute, name.ValueString as Value
from FIMService.fim.ObjectValueReference v
join FIMService.fim.AttributeInternal a
on v.AttributeKey = a.[Key]
join FIMReporting.dbo.fim_requests_new n
on v.ObjectKey = n.ObjectKey
join FIMService.fim.Objects ref
on v.ValueReference = ref.ObjectKey
join FIMService.fim.ObjectValueString name
on ref.ObjectKey = name.ObjectKey
where n.Attribute = 'ObjectKey'
and name.AttributeKey = 66;

insert into FIMReporting.dbo.fim_requests_new
select v.ObjectKey, Name as Attribute, ValueString as Value
from FIMService.fim.ObjectValueString v
join FIMService.fim.AttributeInternal a
on v.AttributeKey = a.[Key]
join FIMReporting.dbo.fim_requests_new n
on v.ObjectKey = n.ObjectKey
where n.Attribute = 'ObjectKey';

insert into FIMReporting.dbo.fim_requests_new
select v.ObjectKey, Name as Attribute, ValueText as Value
 from FIMService.fim.ObjectValueText v
join FIMService.fim.AttributeInternal a
on v.AttributeKey = a.[Key]
join FIMReporting.dbo.fim_requests_new n
on v.ObjectKey = n.ObjectKey
where n.Attribute = 'ObjectKey';

Pivot query

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:

use FIMReporting

insert into dbo.fim_requests_log

SELECT ObjectKey,Creator,CreatedTime,CommittedTime,Operation,[Target],
       TargetObjectType,ManagementPolicy,RequestStatus,RequestParameter
FROM
    (select * from dbo.fim_requests_new
	where ObjectKey in (
	select ObjectKey from dbo.fim_requests_new
	where Attribute = 'RequestStatus'
	and Value in ('Completed','Failed','Denied','PostProcessingError') )) as src
PIVOT
( MAX(Value) FOR Attribute
    IN ( Creator,CreatedTime,CommittedTime,Operation,[Target],TargetObjectType,ManagementPolicy,RequestStatus,RequestParameter)
) AS pvt;

Pruning

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.

use FIMReporting

declare @pruneDays as int
declare @deleteDays as int
set @pruneDays = 50 /* Must be greater than number of day kept in FIMService DB */
set @deleteDays = 180

declare @today as nvarchar(50)
set @today = GetDate()

delete from dbo.fim_requests_log
where datediff(day,CONVERT(datetime,CreatedTime),@today) > @deleteDays

/* Prune service account requests. YOUR ACCOUNT NAMES WILL VARY. */
delete from dbo.fim_requests_log
where datediff(day,CONVERT(datetime,CreatedTime),@today) > @pruneDays
and Creator in ('s-fimportal', 'Forefront Identity Manager Service Account', 'Built-in Synchronization Account')

Post a Comment

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

Spam comments will be blocked by Akismet

*

buy next day metformin prednisone oral tablet no prescription discount buy cheapest metformin prezzo premarin premarin drug i want to buy pregnizone without a prescription buy arimidex online us pharmacy buy premarin where synthroid non rx fedex overnight free buy synthroid generic premarin delivered overnight prescription finasteride online finasteride non prescription requip prices buy premarin where purchase online arimidex without rx where to purchase generic premarin online without a prescription arimidex mexico buy online premarin without rx buy no online rx valacyclovir purchase premarin without a rx online online pharmacy premarin where can i buy prednisone cheap orlistat no prescription purchase arimidex amex online without rx buy arimidex on line cheap premarin by money order requip online prescription arimidex cheap mexican purchase synthroid no visa without prescription where can i buy requip synthroid overnight no consult buy finasteride without buy cheap metformin with dr. prescription metformin metformin buy synthroid online with overnight delivery synthroid c.o.d. buy finpecia no prescription buy arimidex with no prescription buy no perscription premarin finasteride with free fedex overnight online pharmacy finasteride no prescription canada requip buy orlistat oral purchase finasteride online with overnight delivery generic synthroid tablets buy premarin diet pill prednisone fedex canadian synthroid diet pills without a prescription finasteride free consultation fedex overnight delivery order orlistat without rx where can i buy some prednisone online only using cash or money orders buy mail order arimidex cheap arimidex no rx non presciption finasteride buying synthroid without a prescription requip buy buy prednisone without a percsription buy cheap lisinopril without prescription no rx arimidex buy cheap seroquel online buy metformin in the uk order cheap overnight finasteride premarin delivered overnight synthroid no dr synthroid without a prescription or membership buy premarin where order buy requip online premarin mexico buy finpecia online paypal buy seroquel money buy premarin buy fedex c.o.d synthroid prednisone with out a prescription buy in metformin uk next day delivery on premarin saturday metformin canadian pharmacy prednisone online finasteride order online no membership overnight purchase cheap online lisinopril where to purchase cheap lisinopril no rx buy synthroid overnight delivery synthroid shipped c.o.d. buy prednisone no prescription valacyclovir requip