2Introduction:-

I have had the opportunity to work with ForeFront Identity Manager for a long time and in all that time I have never had sufficient requirements to deploy the Systems Center Service Manager Data Warehouse integration.

Specifically as the most common requirement is to do long-time archival of all requests for auditing and compliance, I have been using a stored procedure to extract the FIM requests and store these in a dedicated FIM Request Archive and  Reporting database.

Occasionally, the FIM deployment may have some requirements around object reporting and although there may be a lot of warnings and caveats around running custom SELECT statements against the FIMService database its is, my opinion, that querying the FIMService database directly is both the most efficient mechanism and, to date, has not resulted in any visible impact on operation of the FIM Web Service or Portal.

Furthermore, it is interesting that Microsoft has not provided a more light weight reporting solution other than the Systems Center Data Warehouse integration service for the following two reasons:-

  1. Deploying Systems Center Data Warehouse (SCDW) components requires more effort and infrastructure investments than benefits delivered.
  2. There are only a few canned SCDW reports and its highly likely that a FIM architect or analyst will be forced to write custom SQL anyway for even the most logical and basic reports.

Therefore, the tutorial below will assist FIM 2010 architects and analysts in understanding the FIM 2010 FIMService database schema (current) and explain how to query and report on objects and their associated attributes WITHOUT having to query the FIM Web Service.

It is worth noting that without adding a custom ‘WhenModified’ attribute for objects of interest OR parsing requests first, that there is no mechanism for doing delta extracts from the FIM Web Service into a table Reporting Services can leverage within any resonable period of time and, again, it is my experience that running big data extracts against the web service did have  a performance impact on the FIM portal.

Unfortunately due to spamming I do not allow comments but if you have alternate reference designs or feel passionately one way or another around meeting FIM customer reporting requirements or this tutorial, I would appreciate you sharing them with me using my details at the end of the tutorial. Thank you in advance.

Considerations:-

  • According to this post (found here) Microsoft do not support queries against the FIMService database. The official reporting notes can be found here.
  • This means that at any time Microsoft may change the FIMService database schema which may invalidate your custom reports.
  • You need to consider the performance and security repercussions / risk of each unique environment.
  • This tutorial runs against the live FIMService Database. If your reporting requirements are such that there will be large, commonly executed reports the recommendation is to create copies of the relevant tables but ensure the Schema (Table names, Columns names etc) remain the same. This article provides an example table merging in SQL server.
  • Ideally you will create views and restrict those views to the reporting user account. Security should be considered at all times.
  • This tutorial does not cover how to deploy, configure or customize Microsoft SQL Server, SharePoint Foundation, ForeFront Identity Manager or SQL Reporting Services. The focus is on creating the relevant queries and ultimately rendering a report. If you need a hand with any of those aspects please get in touch to see how we can assist further.

Database Schema Explained:-

First we will look at the key SQL Tables and begin to store all the required variables needed to Select an Object and its Attributes for reporting purposes. When learning its recommended to read the entire article and build your knowledge up one concept at a time.

Before we look at the tables themselves there are three very import columns and keys you will need to understand.

1 The ObjectTypeKey is an integer value that relates to a specific object (class). For example 16 equals the Group object.

3

2 The AttributeKey is an integer value that relates to a specific attribute of an object (class). For example the DisplayName attribute associated with the Group (ObjectTypeKey=16) object is 66.

4

3 The ObjectKey is an integer value that relates to a specific instance of an object (class). For example a search for all Group Objects (ObjectTypeKey=16) shows each Groups ObjectKey.

Note: You will also see the ObjectID you are used to working with in regards to the FIM Web Service.

5

The first table we will look at is the Binding Internal table. Below you will see the table schema.

1

This table is absolutely critical in determining the ObjectTypeKey and AttributeKey. These may appear as static values but depending on your FIM Service Schema configuration these values can differ considerably so we always need to query for the integer.

Storing the ObjectTypeKey for an object:-

-- Create a variable to store the ObjectTypeKey 
--    for the User [Person] Object
DECLARE @User_ObjectTypeKey BIGINT
--  Store the ObjectTypeKey for the User [Person] Object
SET @User_ObjectTypeKey =
    (SELECT DISTINCT(ObjectTypeKey)
        FROM FIMService.fim.BindingInternal 
            WHERE     FIMService.fim.BindingInternal.ObjectType = 'Person')

6

Storing the AttributeTypeKey for an Attribute associated with an object:-

-- Create a variable to store the ObjectTypeKey 
--    for the User [Person] Object
DECLARE @User_ObjectTypeKey SMALLINT
--  Store the ObjectTypeKey for the User [Person] Object
SET @User_ObjectTypeKey =
    (SELECT DISTINCT(ObjectTypeKey)
        FROM FIMService.fim.BindingInternal 
            WHERE     FIMService.fim.BindingInternal.ObjectType = 'Person')
-- Create a variable to store the DisplayName AttributeKey 
--    for the User [Person] Object
DECLARE @User_DisplayName_AttributeKey SMALLINT
--  Store the AttributeKey for the DisplayName of the User [Person] Object
SET @User_DisplayName_AttributeKey =
    (SELECT DISTINCT(ObjectTypeKey)
        FROM FIMService.fim.BindingInternal 
            WHERE FIMService.fim.BindingInternal.ObjectTypeKey = @User_ObjectTypeKey
                    AND FIMService.fim.BindingInternal.AttributeName = 'DisplayName')

7

Now we will look at the various tables which store the various values for each AttributeTypeKey. These tables are all arranged by data type and all start with ObjectValue[DataType]. These are shown below.

8

Note: If you are interested in casting for the data type by attribute an example of how to do this is shown below. This is optional to the overall tutorial but will make your queries resilient especially when porting into different FIM environments and working with custom attributes and is therefore worth demonstrating.

-- Create a variable to store the ObjectTypeKey 
--    for the User [Person] Object
DECLARE @User_ObjectTypeKey SMALLINT
--  Store the ObjectTypeKey for the User [Person] Object
SET @User_ObjectTypeKey =
    (SELECT DISTINCT(ObjectTypeKey)
        FROM FIMService.fim.BindingInternal 
            WHERE     FIMService.fim.BindingInternal.ObjectType = 'Person')
-- Create a variable to store the DisplayName AttributeKey 
--    for the User [Person] Object
DECLARE @User_DisplayName_AttributeKey SMALLINT
--  Store the AttributeKey for the DisplayName of the User [Person] Object
SET @User_DisplayName_AttributeKey =
    (SELECT DISTINCT(AttributeKey)
        FROM FIMService.fim.BindingInternal 
            WHERE FIMService.fim.BindingInternal.ObjectTypeKey = @User_ObjectTypeKey
                    AND FIMService.fim.BindingInternal.AttributeName = 'DisplayName')
-- Create a variable to store the DataTypeKey for the DisplayName AttributeKey 
--    for the User [Person] Object
DECLARE @DataTypeKey tinyint
--  Store the DataTypeKey for the DisplayName Attribute of the User [Person] Object
SET @DataTypeKey =
    (SELECT DISTINCT(DataTypeKey)
        FROM FIMService.fim.AttributeInternal
            WHERE FIMService.fim.AttributeInternal.[KEY] = @User_DisplayName_AttributeKey)
-- Create a variable to store the appropriate date type table name
DECLARE @ValueTable VARCHAR(255)
-- Create a variable to store the appropriate date type value colum name
DECLARE @ValueColumn VARCHAR(255)
-- Create a variable to store the SQL query containing variables
DECLARE @query nvarchar(MAX)
-- Create a variable to store the result count.
DECLARE @COUNT INT
--  Store the Value Table Name for corresponding DataTypeKey
SET @ValueTable =
(SELECT
CASE 
      WHEN @DataTypeKey = 0 THEN 'FIMService.fim.ObjectValueBinary' 
      WHEN @DataTypeKey = 1 THEN 'FIMService.fim.ObjectValueBoolean' 
      WHEN @DataTypeKey = 2 THEN 'FIMService.fim.ObjectValueDateTime'  
      WHEN @DataTypeKey = 3 THEN 'FIMService.fim.ObjectValueInteger'  
      WHEN @DataTypeKey = 4 THEN 'FIMService.fim.ObjectValueReference'  
      WHEN @DataTypeKey = 5 THEN 'FIMService.fim.ObjectValueString'  
      WHEN @DataTypeKey = 6 THEN 'FIMService.fim.ObjectValueText'  
END)
--  Store the Value Table Name for corresponding DataTypeKey
SET @ValueColumn =
(SELECT
CASE 
      WHEN @DataTypeKey = 0 THEN 'ValueBinary' 
      WHEN @DataTypeKey = 1 THEN 'ValueBoolean' 
      WHEN @DataTypeKey = 2 THEN 'ValueDateTime'  
      WHEN @DataTypeKey = 3 THEN 'ValueInteger'  
      WHEN @DataTypeKey = 4 THEN 'ValueReference'  
      WHEN @DataTypeKey = 5 THEN 'ValueString'  
      WHEN @DataTypeKey = 6 THEN 'ValueText'  
END)
SELECT @ValueTable -- Table Name
SELECT @ValueColumn -- Value Column

9

Finally we will store the specific ObjectKey for a User [Person] object instance based on a search criteria involving the content of the Objects DisplayName attribute.

-- Create a variable to store the ObjectTypeKey 
--    for the User [Person] Object
DECLARE @User_ObjectTypeKey SMALLINT
--  Store the ObjectTypeKey for the User [Person] Object
SET @User_ObjectTypeKey =
    (SELECT DISTINCT(ObjectTypeKey)
        FROM FIMService.fim.BindingInternal 
            WHERE     FIMService.fim.BindingInternal.ObjectType = 'Person')
-- Create a variable to store the DisplayName AttributeKey 
--    for the User [Person] Object
DECLARE @User_DisplayName_AttributeKey SMALLINT
--  Store the AttributeKey for the DisplayName of the User [Person] Object
SET @User_DisplayName_AttributeKey =
    (SELECT DISTINCT(AttributeKey)
        FROM FIMService.fim.BindingInternal 
            WHERE FIMService.fim.BindingInternal.ObjectTypeKey = @User_ObjectTypeKey
                    AND FIMService.fim.BindingInternal.AttributeName = 'DisplayName')
-- Create a variable to store the objectKey [Unique Identifier]
--    for the User [Person] instance returned in the result
DECLARE @User_ObjectKey BIGINT
-- Select the objectKey [Unique Identifier] for the instance associated with a specific
--    DisplayName
SET @User_ObjectKey =
(SELECT DISTINCT(ObjectKey)
    FROM FIMService.fim.ObjectValueString
        WHERE FIMService.fim.ObjectValueString.AttributeKey = @User_DisplayName_AttributeKey
            AND FIMService.fim.ObjectValueString.ValueString = 'Alphonse Capone')
SELECT @User_ObjectKey AS [Person Object / Alphonse Capone]

If you look at the WHERE clauses used to store each variable you will notice it looks like:-

Person/ DisplayName / Alphonse Capone

And ultimately this is what was retrieved! The main thing to take away from this tutorial is that with an ObjectKey and AttributeKey or an ObjectTypeKey and AttributeKey it becomes possible to find objects in the FIM Service database and return attribute values.

In the next section I will demonstrate queries that use two temporary tables to ultimately render a single record set containing all the objects and attribute values required. It is this recordset which will be rendered by the reports Data Source.

SQL Examples:-

This section will give the reader some working examples which can be used immediately in an SSRS report (Data Source) or a learning sample.

  • This is a fast and simple SQL query to return data without using complex temp tables or variables

 

SELECT
 
       ObjectKey,
 
       ObjectID,
 
       (SELECT OVS.ValueString
 
       FROM FIMService.fim.ObjectValueString OVS
 
       WHERE OVS.ObjectKey = OBJ.ObjectKey AND OVS.AttributeKey = (SELECT ATTR.[KEY] FROM FIMService.fim.Attributes ATTR WHERE ATTR.Name = 'DisplayName')),
 
       (SELECT OVS.ValueString
 
       FROM FIMService.fim.ObjectValueString OVS
 
       WHERE OVS.ObjectKey = OBJ.ObjectKey AND OVS.AttributeKey = (SELECT ATTR.[KEY] FROM FIMService.fim.Attributes ATTR WHERE ATTR.Name = 'AccountName')),
 
       (SELECT OVT.ValueText
 
       FROM FIMService.fim.ObjectValueText OVT
 
       WHERE OVT.ObjectKey = OBJ.ObjectKey AND OVT.AttributeKey = (SELECT ATTR.[KEY] FROM FIMService.fim.Attributes ATTR WHERE ATTR.Name = 'EmployeeID'))
 
FROM FIMService.fim.Objects OBJ
 
WHERE (ObjectTypeKey = (SELECT ObjectTypeKey FROM fim.SchemaObject WHERE Displayname = 'User'))

 

  • List all user objects Display Name, Account Name and Department

-- Check and Drop the Temporary Tables
IF OBJECT_ID('tempdb..#Objects') IS NOT NULL
DROP TABLE #Objects
IF OBJECT_ID('tempdb..#FinalResults') IS NOT NULL
DROP TABLE #FinalResults
-- Store Variables
DECLARE @User_ObjectTypeKey SMALLINT
DECLARE @User_DisplayName_AttributeKey SMALLINT
DECLARE @User_AccountName_AttributeKey SMALLINT
DECLARE @User_Department_AttributeKey SMALLINT
DECLARE @Current_ObjectKey BIGINT
DECLARE @User_DisplayName VARCHAR(500)
DECLARE @User_AccountName VARCHAR(500)
DECLARE @User_Department VARCHAR(500)
--  Store the ObjectTypeKey for the User [Person] Object
SET @User_ObjectTypeKey =
    (SELECT DISTINCT(ObjectTypeKey)
        FROM FIMService.fim.BindingInternal 
            WHERE     FIMService.fim.BindingInternal.ObjectType = 'Person')
--  Store the AttributeKey for the DisplayName of the User [Person] Object
SET @User_DisplayName_AttributeKey =
    (SELECT DISTINCT(AttributeKey)
        FROM FIMService.fim.BindingInternal 
            WHERE FIMService.fim.BindingInternal.ObjectTypeKey = @User_ObjectTypeKey
                    AND FIMService.fim.BindingInternal.AttributeName = 'DisplayName')
--  Store the AttributeKey for the AccountName of the User [Person] Object
SET @User_AccountName_AttributeKey =
    (SELECT DISTINCT(AttributeKey)
        FROM FIMService.fim.BindingInternal 
            WHERE FIMService.fim.BindingInternal.ObjectTypeKey = @User_ObjectTypeKey
                    AND FIMService.fim.BindingInternal.AttributeName = 'AccountName')
--  Store the AttributeKey for the Department of the User [Person] Object
SET @User_Department_AttributeKey =
    (SELECT DISTINCT(AttributeKey)
        FROM FIMService.fim.BindingInternal 
            WHERE FIMService.fim.BindingInternal.ObjectTypeKey = @User_ObjectTypeKey
                    AND FIMService.fim.BindingInternal.AttributeName = 'Department')
-- Store All Users In the Users Temp Table 
SELECT ObjectKey
    INTO #Objects 
        FROM FIMService.fim.Objects
            WHERE ObjectTypeKey = @User_ObjectTypeKey
 
 
CREATE TABLE #FinalResults(
DisplayName VARCHAR(MAX),
AccountName VARCHAR(MAX),
Department VARCHAR(MAX))
-- Parse the Users and Generate the final results table
WHILE EXISTS (SELECT * FROM #Objects)
BEGIN
    -- Get another value from the temp db
    SELECT @Current_ObjectKey = (SELECT TOP 1 ObjectKey
                                    FROM #Objects
                                        ORDER BY ObjectKey ASC)
    -- Store the User Display Name
    SELECT @User_DisplayName =(SELECT ValueString
                                FROM FIMService.fim.ObjectValueString
                                    WHERE ObjectKey = @Current_ObjectKey
                                            AND AttributeKey = @User_DisplayName_AttributeKey)
    
    -- Store the User Account Name
    SELECT @User_AccountName =(SELECT ValueString
                                FROM FIMService.fim.ObjectValueString
                                    WHERE ObjectKey = @Current_ObjectKey
                                            AND AttributeKey = @User_AccountName_AttributeKey)
 
    -- Store the User Department
    SELECT @User_Department =(SELECT ValueString
                                FROM FIMService.fim.ObjectValueString
                                    WHERE ObjectKey = @Current_ObjectKey
                                            AND AttributeKey = @User_Department_AttributeKey)
    
    -- Update the final Results temporary table
    INSERT INTO #FinalResults (DisplayName,AccountName,Department)
    SELECT @User_DisplayName, @User_AccountName, @User_Department
 
    -- Remove the current value from the temp db
    DELETE #Objects
    WHERE ObjectKey = @Current_ObjectKey
END
-- Return the Final Results
SELECT * FROM #FinalResults

10
  • List all groups and their owners

-- Check and Drop the Temporary Tables
IF OBJECT_ID('tempdb..#Objects') IS NOT NULL
DROP TABLE #Objects
IF OBJECT_ID('tempdb..#FinalResults') IS NOT NULL
DROP TABLE #FinalResults
-- Store Variables
DECLARE @User_ObjectTypeKey SMALLINT
DECLARE @User_DisplayName_AttributeKey SMALLINT
DECLARE @Group_ObjectTypeKey SMALLINT
DECLARE @Group_DisplayName_AttributeKey SMALLINT
DECLARE @Group_Owner_AttributeKey SMALLINT
DECLARE @Current_ObjectKey BIGINT
DECLARE @Group_DisplayName VARCHAR(500)
DECLARE @Group_Owner VARCHAR(500)
 
--  Store the ObjectTypeKey for the User [Person] Object
SET @User_ObjectTypeKey = 
    (SELECT DISTINCT ObjectTypeKey 
        FROM FIMService.fim.BindingInternal 
            WHERE ObjectType = 'Person')
--  Store the AttributeKey for the DisplayName of the User [Person] Object
SET @User_DisplayName_AttributeKey =
    (SELECT DISTINCT(AttributeKey)
        FROM FIMService.fim.BindingInternal 
            WHERE FIMService.fim.BindingInternal.ObjectTypeKey = @User_ObjectTypeKey
                    AND FIMService.fim.BindingInternal.AttributeName = 'DisplayName')
--  Store the ObjectTypeKey for the Group Object
SET @Group_ObjectTypeKey =
    (SELECT DISTINCT(ObjectTypeKey)
        FROM FIMService.fim.BindingInternal 
            WHERE     FIMService.fim.BindingInternal.ObjectType = 'Group')
--  Store the AttributeKey for the DisplayName of the Group Object
SET @Group_DisplayName_AttributeKey =
    (SELECT DISTINCT(AttributeKey)
        FROM FIMService.fim.BindingInternal 
            WHERE FIMService.fim.BindingInternal.ObjectTypeKey = @Group_ObjectTypeKey
                    AND FIMService.fim.BindingInternal.AttributeName = 'DisplayName')
--  Store the AttributeKey for the Owner of the Group Object
SET @Group_Owner_AttributeKey =
    (SELECT DISTINCT(AttributeKey)
        FROM FIMService.fim.BindingInternal 
            WHERE FIMService.fim.BindingInternal.ObjectTypeKey = @Group_ObjectTypeKey
                    AND FIMService.fim.BindingInternal.AttributeName = 'Owner')
 
-- Store All Groups In the Temp Table 
SELECT ObjectKey
    INTO #Objects 
        FROM FIMService.fim.Objects
            WHERE ObjectTypeKey = @Group_ObjectTypeKey
 
 
CREATE TABLE #FinalResults(
DisplayName VARCHAR(MAX),
[Owner] VARCHAR(MAX))
-- Parse the Users and Generate the final results table
WHILE EXISTS (SELECT * FROM #Objects)
BEGIN
    -- Get another value from the temp db
    SELECT @Current_ObjectKey = (SELECT TOP 1 ObjectKey
                                    FROM #Objects
                                        ORDER BY ObjectKey ASC)
    -- Store the Group Display Name
    SELECT @Group_DisplayName =(SELECT ValueString
                                FROM FIMService.fim.ObjectValueString
                                    WHERE ObjectKey = @Current_ObjectKey
                                            AND AttributeKey = @Group_DisplayName_AttributeKey)
    
-- Update the Final Results Temporary Table 
    INSERT INTO #FinalResults (DisplayName,[Owner])
    SELECT  @Group_DisplayName,
            
        (SELECT ValueString
            FROM FIMService.fim.ObjectValueString
                WHERE ObjectKey = ValueReference
                    AND AttributeKey = @User_DisplayName_AttributeKey)
        
        FROM FIMService.fim.ObjectValueReference
            WHERE ObjectKey = @Current_ObjectKey
                AND AttributeKey = @Group_Owner_AttributeKey
 
    -- Remove the current value from the temp db
    DELETE #Objects
    WHERE ObjectKey = @Current_ObjectKey
END
-- Return the final results
SELECT * FROM #FinalResults

11