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

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.

Table of Contents

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…

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 Int, @req_Type INT, @AllRequests INT OUTPUT, @CompletedRequests INT OUTPUT
AS
    -- 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 enumerating 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

  • 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:

    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.writel>"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) "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 x = 0 to ubound(HistoryArray)
                HistoryDays = HistoryArray(x)
      
                Set objADOCommand = CreateObject("ADODB.Command")
    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