{"id":308,"date":"2016-01-08T09:14:32","date_gmt":"2016-01-08T09:14:32","guid":{"rendered":"http:\/\/blogs.kent.ac.uk\/unseenit\/?p=308"},"modified":"2016-01-08T09:14:32","modified_gmt":"2016-01-08T09:14:32","slug":"group-populator-for-fim","status":"publish","type":"post","link":"https:\/\/blogs.kent.ac.uk\/unseenit\/group-populator-for-fim\/","title":{"rendered":"Group Populator for FIM"},"content":{"rendered":"

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.<\/p>\n

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)<\/code>.<\/p>\n

The common method of implementing this is via a reflection<\/em> 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\u00a0is high when at scale!<\/p>\n

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 \ud83d\ude42<\/p>\n

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

Group view<\/h2>\n

First create one or more group views, these could construct the group name from something in the metaverse or be pretty simple as below:<\/p>\n

\n
\n
CREATE<\/span> VIEW<\/span> [dbo<\/span>].[gp_example<\/span>] <\/span>\r\n<\/span>AS<\/span> <\/span>\r\n<\/span>  SELECT<\/span> object_id<\/span>    AS<\/span> member<\/span>, <\/span>\r\n<\/span>         'All People with first name John'<\/span> AS<\/span> [group<\/span>] <\/span>\r\n<\/span>  FROM<\/span>   fimsynchronizationservice<\/span>.dbo<\/span>.mms_metaverse<\/span> WITH<\/span> (nolock<\/span>) <\/span>\r\n<\/span>  WHERE<\/span>  ( givenName<\/span> = 'John'<\/span> );<\/span>\r\n<\/pre>\n<\/div>\n<\/div>\n
<\/pre>\n
I have done it this way to keep different types of groups more manageable rather than having one EPIC view.<\/p>\n
Please remember to keep the WITH (nolock)<\/code> in place as it avoids any SQL layer locking on the metaverse which can cause deadlocks within FIM if missed.<\/p>\n
Master SQL\u00a0view<\/h2>\n
Once you have some group views, knit them together into a master and a multivalue view using UNION:<\/p>\n
Master<\/h3>\n
CREATE VIEW [dbo].[gp_master] ([object] , [type])\r\nAS<\/pre>\n
\n
\n
SELECT<\/span> DISTINCT<\/span> [group<\/span>] AS<\/span> object<\/span>, <\/span>\r\n<\/span>                  'group'<\/span> AS<\/span> [type<\/span>] <\/span>\r\n<\/span>  FROM<\/span>   dbo<\/span>.gp_example<\/span><\/span>\r\n<\/span>  UNION<\/span> <\/span>\r\n<\/span>  SELECT<\/span> Concat<\/span>('{'<\/span>, object<\/span>, '}'<\/span>) AS<\/span> object<\/span>, <\/span>\r\n<\/span>         type<\/span> <\/span>\r\n<\/span>  FROM<\/span>   (SELECT<\/span> DISTINCT<\/span> [member<\/span>] AS<\/span> object<\/span>, <\/span>\r\n<\/span>                          'member'<\/span> AS<\/span> [type<\/span>] <\/span>\r\n<\/span>          FROM<\/span>   dbo<\/span>.gp_example<\/span>) t<\/span><\/span>\r\n<\/pre>\n<\/div>\n<\/div>\n
<\/span>UNION\r\n...<\/pre>\n
This should output rows of two types: “member” which as a guid in the object\u00a0column\u00a0and “group” which has the real name of the group in “object” column.<\/p>\n
Multivalue<\/h3>\n
CREATE VIEW [dbo].[gp_master_mv] \r\nAS \r\n SELECT [group] AS object, \r\n 'member' AS attribute, \r\n member AS value \r\n FROM dbo.gp_example\r\n UNION\r\n ...<\/pre>\n
This view should output rows of the form <group>, “member”, <objectid of member>.<\/p>\n
Delta snapshots<\/h2>\n
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:<\/p>\n
SELECT * INTO dbo.gp_master_copy FROM dbo.gp_master;\r\nSELECT * INTO dbo.gp_master_mv_copy FROM dbo.gp_master_mv;\r\n<\/pre>\n
This will create a\u00a0static copy of your master and multivalue tables.<\/p>\n
Using these snapshots, you then need your Delta VIEW:<\/p>\n
CREATE VIEW [dbo].[gp_delta] \r\nAS \r\n  SELECT mv.object, \r\n         'group'  AS [type], \r\n         'Modify' AS change_type \r\n  FROM   dbo.gp_master_mv mv \r\n         LEFT OUTER JOIN dbo.gp_master_mv_copy mv_copy \r\n                      ON mv.object = mv_copy.object \r\n                         AND mv.attribute = mv_copy.attribute \r\n                         AND mv.value = mv_copy.value \r\n  WHERE  mv_copy.object IS NULL \r\n  UNION \r\n  SELECT mv_copy.object, \r\n         'group'  AS [type], \r\n         'Modify' AS CHANGETYPE \r\n  FROM   dbo.gp_master_mv_copy mv_copy \r\n         LEFT OUTER JOIN dbo.gp_master_mv mv \r\n                      ON mv.object = mv_copy.object \r\n                         AND mv.attribute = mv_copy.attribute \r\n                         AND mv.value = mv_copy.value \r\n  WHERE  mv.object IS NULL \r\n  UNION \r\n  SELECT m.object, \r\n         'member' AS [type], \r\n         'Add'    AS CHANGETYPE \r\n  FROM   dbo.gp_master m \r\n         LEFT OUTER JOIN dbo.gp_master_copy m_copy \r\n                      ON m.object = m_copy.object \r\n  WHERE  m.type = 'member' \r\n         AND m_copy.object IS NULL \r\n  UNION \r\n  SELECT m.object, \r\n         'member' AS [type], \r\n         'Delete' AS CHANGETYPE \r\n  FROM   dbo.gp_master m \r\n         LEFT OUTER JOIN dbo.gp_master_copy m_copy \r\n                      ON m.object = m_copy.object \r\n  WHERE  m.type = 'member' \r\n         AND m.object IS NULL \r\n\r\n<\/pre>\n
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).<\/p>\n
Making it all work<\/h2>\n
Now you have all your SQL layer stuff working, you need to create a new FIM MA to retrieve the data.<\/p>\n
    \n
  1. Connect to database:\n
      \n
    1. Table\/View: gp_master<\/li>\n
    2. Delta VIew: gp_delta<\/li>\n
    3. Multivalue Table: gp_master_mv<\/li>\n<\/ol>\n<\/li>\n
    4. Configure columns:\n
        \n
      1. Set anchor to object<\/li>\n
      2. Configure Delta\n
          \n
        1. Change type attribute: change_type<\/li>\n
        2. Modify=Modify<\/li>\n
        3. Add=Add<\/li>\n
        4. Delete=Delete<\/li>\n<\/ol>\n<\/li>\n
        5. Multivalue\n
            \n
          1. Attribute name column: attribute<\/li>\n
          2. String attribute column: value<\/li>\n
          3. Configure attributes -> New\n
              \n
            1. Name: member<\/li>\n
            2. Type: String<\/li>\n
            3. Reference: yes<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n<\/li>\n
            4. Object type\n
                \n
              1. Select Object type column = type<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n<\/li>\n
              2. Configure Join and Projection rules (I’ve chosen the following, amend as you need!)\n
                  \n
                1. Group: Join on object=cn OR Project<\/li>\n
                2. Member: Join on object=<object-id> (do not project)<\/li>\n<\/ol>\n<\/li>\n
                3. Attribute flow (all of these are Import Attribute Flows, data source to metaverse, direct group -> group)\n
                    \n
                  1. member -> member<\/li>\n
                  2. object -> cn<\/li>\n
                  3. object -> sAMAccountName<\/li>\n<\/ol>\n<\/li>\n
                  4. Deprovisioning\n
                      \n
                    1. Make them disconnectors<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n
                      Run a Full Import & Delta Sync (in my environment of ~700 groups, 100,000 users and 170,000 memberships this took hours<\/strong>).<\/p>\n
                      As for then using the Delta view, in order to not miss things, I’ve worked this by having an SQL script\u00a0to re-do the snapshots which is called after my Delta Import has finished but before any other MAs run:<\/p>\n
                      use MyDB;\r\nDELETE FROM dbo.gp_master_copy WHERE 1=1;\r\nDELETE FROM dbo.gp_master_mv_copy WHERE 1=1;\r\nINSERT INTO dbo.gp_master_copy SELECT * FROM dbo.gp_master;\r\nINSERT INTO dbo.gp_master_mv_copy SELECT * FROM dbo.gp_master_mv;<\/pre>\n
                      I then invoke this from the batch script which calls my MA Run Profiles like this:<\/p>\n
                      call runma.cmd DI-DS GP2\r\nsqlcmd -i gp_snapshot.sql<\/pre>\n
                      This Delta Import & Delta Sync run then takes about 90 seconds \ud83d\ude42<\/p>\n
                      What next<\/h2>\n
                      Next you’ll need some provisioning code to create these groups in your chosen directory (Active Directory?) and flow “member” into those group objects.<\/p>\n","protected":false},"excerpt":{"rendered":"
                      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 … Read more<\/a><\/p>\n","protected":false},"author":13,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[28943,28936,862],"tags":[],"_links":{"self":[{"href":"https:\/\/blogs.kent.ac.uk\/unseenit\/wp-json\/wp\/v2\/posts\/308"}],"collection":[{"href":"https:\/\/blogs.kent.ac.uk\/unseenit\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blogs.kent.ac.uk\/unseenit\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blogs.kent.ac.uk\/unseenit\/wp-json\/wp\/v2\/users\/13"}],"replies":[{"embeddable":true,"href":"https:\/\/blogs.kent.ac.uk\/unseenit\/wp-json\/wp\/v2\/comments?post=308"}],"version-history":[{"count":2,"href":"https:\/\/blogs.kent.ac.uk\/unseenit\/wp-json\/wp\/v2\/posts\/308\/revisions"}],"predecessor-version":[{"id":311,"href":"https:\/\/blogs.kent.ac.uk\/unseenit\/wp-json\/wp\/v2\/posts\/308\/revisions\/311"}],"wp:attachment":[{"href":"https:\/\/blogs.kent.ac.uk\/unseenit\/wp-json\/wp\/v2\/media?parent=308"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blogs.kent.ac.uk\/unseenit\/wp-json\/wp\/v2\/categories?post=308"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blogs.kent.ac.uk\/unseenit\/wp-json\/wp\/v2\/tags?post=308"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}