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

*

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