Addition to my basic Requests archiving – archiving Approval info

A long time ago now I posted a basic SQL method of archiving the Requests history to another database. I’m still using this with FIM 2010 and have updated the method now to also grab info about Approvals.

Note I haven’t tested this with R2.

Create the tables

I have a database called “FIMReporting” in which I create the following two tables:

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

 

CREATE TABLE [dbo].[fim_approvals_log](
	[ObjectKey] [nvarchar](150) NULL,
	[Creator] [nvarchar](150) NULL,
	[CreatedTime] [nvarchar](150) NULL,
	[Request] [nvarchar](150) NULL,
	[Decision] [nvarchar](50) NULL,
	[Reason] [nvarchar](max) NULL
) ON [PRIMARY]

Extract Unarchived Approvals

The following script updates the fim_approvals_log table with info about Approvals that don’t yet appear in that table.

truncate table FIMReporting.dbo.fim_approvals_new;

insert into FIMReporting.dbo.fim_approvals_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 = 2 or o.ObjectTypeKey = 3)
and l.ObjectKey is null
and s.AttributeKey = 66;

insert into FIMReporting.dbo.fim_approvals_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_approvals_new n
on v.ObjectKey = n.ObjectKey
where n.Attribute = 'ObjectKey';

insert into FIMReporting.dbo.fim_approvals_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_approvals_new n
on v.ObjectKey = n.ObjectKey
where n.Attribute = 'ObjectKey';

insert into FIMReporting.dbo.fim_approvals_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_approvals_new n
on v.ObjectKey = n.ObjectKey
where n.Attribute = 'ObjectKey';

insert into FIMReporting.dbo.fim_approvals_new
select v.ObjectKey, a.Name + 'DisplayName' 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_approvals_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_approvals_new
select v.ObjectKey, Name as Attribute, ValueReference as Value
from FIMService.fim.ObjectValueReference v
join FIMService.fim.AttributeInternal a
on v.AttributeKey = a.[Key]
join FIMReporting.dbo.fim_approvals_new n
on v.ObjectKey = n.ObjectKey
where n.Attribute = 'ObjectKey';

insert into FIMReporting.dbo.fim_approvals_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_approvals_new n
on v.ObjectKey = n.ObjectKey
where n.Attribute = 'ObjectKey';

insert into FIMReporting.dbo.fim_approvals_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_approvals_new n
on v.ObjectKey = n.ObjectKey
where n.Attribute = 'ObjectKey';

insert into dbo.fim_approvals_log (ObjectKey)
	SELECT n.ObjectKey from dbo.fim_approvals_new n
	left outer join dbo.fim_approvals_log l
	on n.ObjectKey = l.ObjectKey
	where n.Attribute = 'ObjectType'
	and n.Value = 'ApprovalResponse'
	and l.ObjectKey is NULL;

update dbo.fim_approvals_log
set Creator = Value
from dbo.fim_approvals_log l
join dbo.fim_approvals_new n
on l.ObjectKey = n.ObjectKey
and n.Attribute = 'CreatorDisplayName';

update dbo.fim_approvals_log
set CreatedTime = Value
from dbo.fim_approvals_log l
join dbo.fim_approvals_new n
on l.ObjectKey = n.ObjectKey
and n.Attribute = 'CreatedTime';

update dbo.fim_approvals_log
set Request = ap.Value
from dbo.fim_approvals_log l
join dbo.fim_approvals_new ar
on ar.ObjectKey = l.ObjectKey
join dbo.fim_approvals_new ap
on ar.Value = ap.ObjectKey
and ar.Attribute = 'Approval'
and ap.Attribute = 'Request';

update dbo.fim_approvals_log
set Decision = Value
from dbo.fim_approvals_log l
join dbo.fim_approvals_new n
on l.ObjectKey = n.ObjectKey
and n.Attribute = 'Decision';

update dbo.fim_approvals_log
set Reason = Value
from dbo.fim_approvals_log l
join dbo.fim_approvals_new n
on l.ObjectKey = n.ObjectKey
and n.Attribute = 'Reason';

Reporting

The fim_approvals_log table lists the approval details against the internal ObjectKey of the associated Request object. This makes it very easy to join to the fim_requests_log table to get a combined view of the request along with its approval.