Though FIM CM has rich built capabilities I missed two features related to Certificate Lifecycle Management reporting in my recent ILM2007 and FIM2010 deployments:

• Generate FIM CM reports automatically without requiring any Certificate Manager or Administrator actions.
• The capability to specify the output of the report individually, not relying on the built in reports that come with the FIM CM set up.

The following example proposes a solution for both requirements. Hereby the solution relies on a core FIM CM & SQL installation exclusively and avoids setting up additional components, such as SQL Reporting Services, which may provide even smarter solutions. Reason for that is that I wasn’t allowed to use such components in my recent projects.

The scenario

In the example FIM CM manages three types of soft certificates: Remote Access Certificates, Web Server Certificates and Software Signing Certificates. The FIM CM enrollment model is manager-based: any user who requires one of the three certificates types must contact the CM Hotline, which checks the request and enrolls a certificate requests via the FIM CM Portal, if all checks have been passed. FIM CM distributes a one-time password to the requesting end user, finally the end user can execute the request and install the certificate on its own – a very common FIM CM deployment scenario.

In this scenario the IT management demands a statistic about the CM Hotline workload. On a weekly basis a report should represent the IT management how many certificate requests have been managed by the CM Hotline. The CM hotline itself asks for FIM CM usage reports on a daily, weekly and monthly basis, distinguished by the three different certificate types. The reports should show how many of the initiated certificate requests have been already executed by the end users and how many of those requests are still pending. Furthermore it is of interest for the CM Hotline to see how requests are distributed over the different FIM CM request types, such as the enrollment-, revocation- or renewal-process.

Design the report

In a first step we’ll collect all data items and dependencies, which should be taken into account by the report. This helps us to map the requirements with available information hold in the FIM CM database. The requested reports can be summarized as follows:

The report should only contain information about requests dependent on…

• …predefined observation windows
• …one of the three managed FIM CM profile templates
• …a given FIM CM request type aka a FIM CM policy
• …state of a given FIM CM request

Now we investigate the FIM CM database design. The database exists of 11 tables. Each table contains one of the following information:

FIM CM database table	Table content
dbo.CertificateAuthority	All Certification Authorities connected with FIM CM, usually registered by FIM CM exit modules
dbo.Certificates	Details about all certificates , either issued via FIM CM or manually imported from a connected Certification Authorities using clmutil.exe command line tool
dbo.DatabaseSchemaVersion	Simply the current database schema version
dbo.EventHistory	Records about all actions executed in FIM CM workflows, such as initiating, approve and execute requests – the History in the details of a request in the FIM CM Portal
dbo.ExternalRequests	Details about FIM CM requests initiated using the FIM CM SQL API
dbo.ProfileCertificates	The mapping between a certificate (stored in dbo.Certificates) and a FIM CM certificate profile (stored in dbo.Profiles)
dbo.Profiles	All FIM CM certificate profiles as displayed in FIM CM Portal via the Find a user to view or manage their information page
dbo.ProfileTemplateHistory	The change log of the FIM CM profile templates
dbo.Requests	Details about all FIM CM requests initiated from the FIM CM Portal
dbo.Smartcards	Details about all smartcard devices issued by FIM CM and details about the related FIM CM workflow, which issued the smartcard or token device
dbo.UsernameCache	objectGUIDs and samAccountNames of AD user accounts recently used by FIM CM workflows

As we can see the table of interest for our report is the table dbo.requests. The following screenshot shows the column design of the dbo.requests table:

 

Now we can map the report requirements with the request information available in the database table:

 

Report Requirement	dbo.Requests column
predefined observation windows	req_submitted_dt
one of the three managed FIM CM profile templates	req_profile_template_uuid
a given FIM CM request type	req_type
state of a given FIM CM request	req_status

 

 

Develop the solution

Based on the mapping we can develop a SQL Stored Procedure (SP) that executes a SQL query and delivers the requested data. The Stored Procedure should have four input and two output variables: 

SP variable	Direction	Description
@HistoryDays	Input	Defines the observation windows –execution date of the SP until n days of the past. The variable must be a negative Integer to query past requests.
@req_profile_uuid	Input	The FIM CM profile template UUID
@req_Status	Input	The current request status in the FIM CM database
@req_Type	Input	The current request type in the FIM CM database
@AllRequests	Output	The resulting row count for all requests, independent from their state
@CompletedRequests	Output	The resulting row count for all requests with the request state “Completed"

The Stored Procedure exists of two Select statements. Both Select statements search for all requests in the table dbo.Requests with the conditions defined in the report design. Hereby the first Select statement queries the database table independent from the request status and stores the number of found rows in the output variable @AllRequests. The second Select statement queries the database table and stores all found requests with the status “Completed” in the output variable @AllCompletedRequests

 

The following code shows the proposed SP. If you want to evaluate the solution and you’re not familiar with SQL Stored Procedures the following KB-Article may help you to create the SP (http://msdn.microsoft.com/en-us/library/ms345415.aspx)

Stored Procedure code

USE [FIMCertificateManagement]

-- The default database in FIM 2010

-- ILM 2007 uses the default database name [CLM]

GO

/****** Object:  StoredProcedure [dbo].[custom_RequestStatistic]    Script Date: 07/27/2011 09:16:55 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: Heil, Matthias

-- Create date: 25/7/2011

-- Description: Enumerates certificate requests of the past

-- The input parameter HistoryDays must be negative !

-- =============================================

CREATE PROCEDURE [dbo].[custom_RequestStatistic]@HistoryDays INT , @req_profile_uuid NVARCHAR(256), @req_Status Int, @req_Type INT, @AllRequests INT OUTPUT, @CompletedRequests INT OUTPUT

AS

BEGIN

    -- SET NOCOUNT ON added to prevent extra result sets from

    -- interfering with SELECT statements.

    SET NOCOUNT ON;

Select a.request_uuid 

From Requests a

Where a.req_profile_template_uuid = @req_profile_uuid

And a.req_submitted_dt >  dateadd(day,@HistoryDays ,GETDATE())

And a.req_type = @req_Type

Set @AllRequests = @@ROWCOUNT 

  

Select a.request_uuid 

From Requests a

Where a.req_profile_template_uuid = @req_profile_uuid

And a.req_submitted_dt >  dateadd(day,@HistoryDays ,GETDATE())

and a.req_status = @req_Status

And a.req_type = @req_Type

Set @CompletedRequests = @@ROWCOUNT 

  

Select @AllRequests as 'All Requests', @CompletedRequests as 'Completed Requests'

END

 

Automate the solution

Now that we’ve set up the Stored Procedure we can automate the solution. In this example I use a small vbScript, which executes the Stored Procedure and writes the output in a tab separated text file (a very simple approach indeed, smarter targets are feasible if you're running the script with, for example, Excel in the background).

The vbScript exists of three arrays, defining…

• … the FIM CM Request types

• … the three FIM CM profile templates

• … different observation windows

The fourth array, which defines the possible request status types, is not used in the script, because the report should enumerate only completed requests. For simplicity the correlating objADOInputParam3 is hard coded with ‘8’.

The objADOInputParam2 specifies the UUID of the FIM CM profile template. You can find these UUIDs in your environment by enuerating the objectGUID of the corresponding FIM CM profile template object below the Active Directory container CN=Profile Templates,CN=Public Key Services,CN=Services,CNConfiguration, CN=myDomain,DC=com

To completely automate the solution the vbScript can be triggered by a scheduled task on a daily basis for example. Running the script as a scheduled task requires three settings:

• Run the scheduled task under a dedicated user account
• Create a SQL Login for this use account
• Grant the SQL Login Execute permissions on the Stored Procedure

As a result you should get a report similar to this one:

 

vbScript-Code

Const adExecuteNoRecords = &H00000080

Const adCmdStoredProc = 4

Const ForAppending = 8

Const ForWriting = 2

Const strFIMCM_Host = "FIMCM_Host.myDomain.com"

  

Dim RequestTypeArray(10,1)

RequestTypeArray(0,0) = 1

RequestTypeArray(0,1) = "Enroll"

RequestTypeArray(1,0) = 2

RequestTypeArray(1,1) = "Recover"

RequestTypeArray(2,0) = 3

RequestTypeArray(2,1) = "Renew"

RequestTypeArray(3,0) = 4

RequestTypeArray(3,1) = "Disable"

RequestTypeArray(4,0) = 5

RequestTypeArray(4,1) = "Unblock"

RequestTypeArray(5,0) = 6

RequestTypeArray(5,1) = "Duplicate"

RequestTypeArray(6,0) = 7

RequestTypeArray(6,1) = "Retire"

RequestTypeArray(7,0) = 8

RequestTypeArray(7,1) = "Recover on behalf"

RequestTypeArray(8,0) = 9

RequestTypeArray(8,1) = "Suspend or Reinstate"

RequestTypeArray(9,0) = 10

RequestTypeArray(9,1) = "Online Update"

RequestTypeArray(10,0) = 11

RequestTypeArray(10,1) = "Enroll Temporary"

  

Dim HistoryArray(4)

HistoryArray(0) = 1

HistoryArray(1) = 3

HistoryArray(2) = 7

HistoryArray(3) = 30

HistoryArray(4) = 9000

  

Dim ProfileArray(2,1)

ProfileArray(0,0) = "83f9d844-c214a-7792-3e71-bbfc6cdf5b52"

ProfileArray(0,1) = "Remote Access Certificates"

ProfileArray(1,0) = "5941414c-c267-2a63-94f9-d9567d969881"

ProfileArray(1,1) = "Web Server Certificates "

ProfileArray(2,0) = "99d94f2b-64a9-421c-bde8-gaab15884451"

ProfileArray(2,1) = "Software Signing Certificates "

  

  

'Dim RequestStatusArray(3,1)

'RequestStatusArray(0,0) = 4

'RequestStatusArray(0,1) = “Denied”

'RequestStatusArray(1,0) = 8

'RequestStatusArray(1,1) = “Completed”

'RequestStatusArray(2,0) = 9

'RequestStatusArray(2,1) = “Failed”

'RequestStatusArray(3,0) = 17

'RequestStatusArray(3,1) = “Canceled”

  

Dim WshShell

Set WshShell = WScript.CreateObject("WScript.Shell")

strcurDir = WshShell.CurrentDirectory

  

strADOConnect = "Driver={SQL Server};Server=" & strFIMCM_Host & ";Database=FIMCertificateManagement;Trusted_Connection=yes"

Set fso = CreateObject("Scripting.FileSystemObject")

Set f1 = fso.OpenTextFile(strcurDir & "\FIMCM_Statistics.xls", ForWriting, True)

f1.writeline("Certificate-Request Statistics from: " & date & " " & time)

  

Set ADOCon= CreateObject("ADODB.Connection")

ADOCon.Open strADOConnect

  

For z = 0 to ubound(RequestTypeArray)

    RequestType = RequestTypeArray(z,0)

    f1.writeline("Certificate Type (" & RequestTypeArray(z,1) & ")" & vbTab & "Requested" & vbTab & "Completed" & _

                      vbTab & "Requested" & vbTab & "Completed" & vbTab & "Requested" & vbTab & "Completed" & vbTab & _

                       "Requested" & vbTab & "Completed" & vbTab & "Requested" & vbTab & "Completed" & vbTab)

    f1.writeline(vbTab & "Last day" & vbTab & vbTab & "Last 3 days" & vbTab & vbTab & "Last week" & _

                    vbTab & vbTab & "Last month" & vbTab & vbTab & "Last quarter")

  

    For y = 0 to ubound(ProfileArray)

        ProfileUUID = ProfileArray(y,0)

        f1.write(ProfileArray(y,1) & vbTab)

      

        For x = 0 to ubound(HistoryArray)

            HistoryDays = HistoryArray(x)

  

            Set objADOCommand = CreateObject("ADODB.Command")

            Set objADOCommand.ActiveConnection = ADOCon

  

            objADOCommand.commandText = "custom_RequestStatistic"

            objADOCommand.CommandType = adCmdStoredProc

  

            Set objADOInputParam1 = objADOCommand.CreateParameter("HistoryDays", 3, 1, , "-" & HistoryDays)

            objADOCommand.Parameters.Append objADOInputParam1

  

            Set objADOInputParam2 = objADOCommand.CreateParameter("req_profile_uuid", 200, 1, 256, ProfileUUID)

            objADOCommand.Parameters.Append objADOInputParam2

  

            Set objADOInputParam3 = objADOCommand.CreateParameter("req_Status", 3, 1, , "8")

            objADOCommand.Parameters.Append objADOInputParam3

  

            Set objADOInputParam4 = objADOCommand.CreateParameter("req_Type", 3, 1, , RequestType)

            objADOCommand.Parameters.Append objADOInputParam4

  

            Set objADOOutputParam1 = objADOCommand.CreateParameter("AllRequests", 3, 2)

            objADOCommand.Parameters.Append objADOOutputParam1

  

            Set objADOOutputParam2 = objADOCommand.CreateParameter("CompletedRequests", 3, 2)

            objADOCommand.Parameters.Append objADOOutputParam2

      

            objADOCommand.Execute , , adExecuteNoRecords

      

            f1.write(objADOOutputParam1.Value & vbTab & objADOOutputParam2.Value & vbTab)

        next

        f1.writeline()

    next

    f1.writeline()

next

  

f1.close