Group Populator for FIM

By |

There are various commercial solutions for providing automatic group population within FIM (into other directories such as Active Directory) which look at arbitrary attributes in the Metaverse to invent and populate groups.

For example creating a “All People with first name John” group which, given dynamic groups in your directory, would be implemented with an LDAP filter like (givenName=John).

The common method of implementing this is via a reflection MA which talks straight back onto an SQL VIEW of the FIM Metaverse which outputs groups and memberships. This is fine but the SQL MA isn’t known for its speed of Full Imports so the need for a Delta VIEW is high when at scale!

I’ve implemented aDelta VIEW by taking a snapshot of the base view and then my delta import job queries another view which compares the live metaverse data with this snapshot to generate a changelist. It’s not as quick as some commercial solutions but is a lot quicker than a Full Import 🙂

All of this is done in native SQL views/jobs and (almost) code-less FIM Sync Service.

Group view

First create one or more group views, these could construct the group name from something in the metaverse or be pretty simple as below:

CREATE VIEW [dbo].[gp_example] 
AS 
  SELECT object_id    AS member, 
         'All People with first name John' AS [group] 
  FROM   fimsynchronizationservice.dbo.mms_metaverse WITH (nolock) 
  WHERE  ( givenName = 'John' );



I have done it this way to keep different types of groups more manageable rather than having one EPIC view.


Please remember to keep the WITH (nolock) in place as it avoids any SQL layer locking on the metaverse which can cause deadlocks within FIM if missed.


Master SQL view


Once you have some group views, knit them together into a master and a multivalue view using UNION:


Master


CREATE VIEW [dbo].[gp_master] ([object] , [type])
AS






SELECT DISTINCT [group] AS object, 
                  'group' AS [type] 
  FROM   dbo.gp_example
  UNION 
  SELECT Concat('{', object, '}') AS object, 
         type 
  FROM   (SELECT DISTINCT [member] AS object, 
                          'member' AS [type] 
          FROM   dbo.gp_example) t







UNION
...


This should output rows of two types: “member” which as a guid in the object column and “group” which has the real name of the group in “object” column.


Multivalue


CREATE VIEW [dbo].[gp_master_mv] 
AS 
 SELECT [group] AS object, 
 'member' AS attribute, 
 member AS value 
 FROM dbo.gp_example
 UNION
 ...


This view should output rows of the form <group>, “member”, <objectid of member>.


Delta snapshots


Next is the spapshot tables against which we’ll compare live data. These need bootstrapping before we use them. I’ve found the easiest way to do this is to get SQL Server to create them from your views:


SELECT * INTO dbo.gp_master_copy FROM dbo.gp_master;
SELECT * INTO dbo.gp_master_mv_copy FROM dbo.gp_master_mv;



This will create a static copy of your master and multivalue tables.


Using these snapshots, you then need your Delta VIEW:


CREATE VIEW [dbo].[gp_delta] 
AS 
  SELECT mv.object, 
         'group'  AS [type], 
         'Modify' AS change_type 
  FROM   dbo.gp_master_mv mv 
         LEFT OUTER JOIN dbo.gp_master_mv_copy mv_copy 
                      ON mv.object = mv_copy.object 
                         AND mv.attribute = mv_copy.attribute 
                         AND mv.value = mv_copy.value 
  WHERE  mv_copy.object IS NULL 
  UNION 
  SELECT mv_copy.object, 
         'group'  AS [type], 
         'Modify' AS CHANGETYPE 
  FROM   dbo.gp_master_mv_copy mv_copy 
         LEFT OUTER JOIN dbo.gp_master_mv mv 
                      ON mv.object = mv_copy.object 
                         AND mv.attribute = mv_copy.attribute 
                         AND mv.value = mv_copy.value 
  WHERE  mv.object IS NULL 
  UNION 
  SELECT m.object, 
         'member' AS [type], 
         'Add'    AS CHANGETYPE 
  FROM   dbo.gp_master m 
         LEFT OUTER JOIN dbo.gp_master_copy m_copy 
                      ON m.object = m_copy.object 
  WHERE  m.type = 'member' 
         AND m_copy.object IS NULL 
  UNION 
  SELECT m.object, 
         'member' AS [type], 
         'Delete' AS CHANGETYPE 
  FROM   dbo.gp_master m 
         LEFT OUTER JOIN dbo.gp_master_copy m_copy 
                      ON m.object = m_copy.object 
  WHERE  m.type = 'member' 
         AND m.object IS NULL 




This view will output any differences between the live master/multivalue views and the stored copy in a way that FIM expects (tagged with Add, Modify or Delete).


Making it all work


Now you have all your SQL layer stuff working, you need to create a new FIM MA to retrieve the data.


    

  1. Connect to database:

      

    1. Table/View: gp_master
      2. 

    2. Delta VIew: gp_delta
      3. 

    3. Multivalue Table: gp_master_mv
      4. 

    

    2. 

  2. Configure columns:

      

    1. Set anchor to object
      2. 

    2. Configure Delta

        

      1. Change type attribute: change_type
        2. 

      2. Modify=Modify
        3. 

      3. Add=Add
        4. 

      4. Delete=Delete
        5. 

      

      3. 

    3. Multivalue

        

      1. Attribute name column: attribute
        2. 

      2. String attribute column: value
        3. 

      3. Configure attributes -> New

          

        1. Name: member
          2. 

        2. Type: String
          3. 

        3. Reference: yes
          4. 

        

        4. 

      

      4. 

    4. Object type

        

      1. Select Object type column = type
        2. 

      

      5. 

    

    3. 

  3. Configure Join and Projection rules (I’ve chosen the following, amend as you need!)

      

    1. Group: Join on object=cn OR Project
      2. 

    2. Member: Join on object=<object-id> (do not project)
      3. 

    

    4. 

  4. Attribute flow (all of these are Import Attribute Flows, data source to metaverse, direct group -> group)

      

    1. member -> member
      2. 

    2. object -> cn
      3. 

    3. object -> sAMAccountName
      4. 

    

    5. 

  5. Deprovisioning

      

    1. Make them disconnectors
      2. 

    

    6. 



Run a Full Import & Delta Sync (in my environment of ~700 groups, 100,000 users and 170,000 memberships this took hours).


As for then using the Delta view, in order to not miss things, I’ve worked this by having an SQL script to re-do the snapshots which is called after my Delta Import has finished but before any other MAs run:


use MyDB;
DELETE FROM dbo.gp_master_copy WHERE 1=1;
DELETE FROM dbo.gp_master_mv_copy WHERE 1=1;
INSERT INTO dbo.gp_master_copy SELECT * FROM dbo.gp_master;
INSERT INTO dbo.gp_master_mv_copy SELECT * FROM dbo.gp_master_mv;


I then invoke this from the batch script which calls my MA Run Profiles like this:


call runma.cmd DI-DS GP2
sqlcmd -i gp_snapshot.sql


This Delta Import & Delta Sync run then takes about 90 seconds 🙂


What next


Next you’ll need some provisioning code to create these groups in your chosen directory (Active Directory?) and flow “member” into those group objects.

    

    
    


  
  
  	

		
Leave a Reply