{"id":827,"date":"2010-06-03T12:03:32","date_gmt":"2010-06-03T12:03:32","guid":{"rendered":"https:\/\/www.wapshere.com\/missmiis\/?p=827"},"modified":"2010-12-15T15:12:06","modified_gmt":"2010-12-15T15:12:06","slug":"phase-one-joins-and-data-matching","status":"publish","type":"post","link":"https:\/\/www.wapshere.com\/missmiis\/phase-one-joins-and-data-matching","title":{"rendered":"Phase One Joins and Data Matching"},"content":{"rendered":"

I’ve just posted a new Greatest Hits article<\/a> on the ILM forum on the subject of how ILM\u00c2\u00a0(or the FIM Sync Service) can be used to clean up the mess of existing accounts, before you can actually get \u00c2\u00a0on to the more interesting tasks of provisioning and updating. With the way FIM codeless sync works, needing an existing attribute to match on, and only allowing simple matching rules, it will be more important than ever to start from a position of tidy directories with correctly identified existsing accounts.\u00c2\u00a0Here’s the article…\u00c2\u00a0<\/p>\n

Phase One Joins and Data Matching<\/h2>\n

The tedious truth is that most IdM projects must begin with a phase of data matching and cleaning. Before you can start to automate management of identities, you need a predictable
\ndata set around which to base your rules. Many organizations today, whether due to changes in IT personnel, company mergers, variable naming conventions or lack of guidance on handling resignations, have existing user account bases that can only be described as a mess.<\/p>\n

This document covers some of the methods you can use with ILM to get through that first project phase. Unfortunately there are no magic bullets here \u00e2\u20ac\u201c eye-straining, brain-numbing trawls through long lists of unmatched accounts cannot usually be avoided. What you can do is try to extract quality data, and construct your lists as helpfully as possible, so they can be targeted at the people whose eyeballs and brains are most likely to give the best response.<\/p>\n

It must be added that this is a very big topic, and the methods you choose will depend on the data you are faced with, and the aims of your project.<\/p>\n

Joins<\/h2>\n

Really, it\u00e2\u20ac\u2122s all about joins.<\/p>\n

Once existing accounts are joined to their correct data source (such as matching an HR record to an AD account) you can begin to flow updates.<\/p>\n

Once you have a clear idea who does and does not have an account in a target directory, you can begin to make provisioning and deprovisioning decisions.<\/p>\n

But be careful \u00e2\u20ac\u201c your joins must be reliable! The last thing you want is to kill the credibility of your fledgling IdM project by updating someone\u00e2\u20ac\u2122s account with another person\u00e2\u20ac\u2122s details or, even worse, deleting their account because you thought they\u00e2\u20ac\u2122d left!\u00c2\u00a0<\/div>\n
\u00c2\u00a0<\/strong><\/div>\n

\u00c2\u00a0<\/p>\n

<\/strong><\/p>\n

Your eventual aim is a simple Join Rule<\/h3>\n

When you first start data matching, you will use a lot of different join rules, and you will make joins manually and with CSV files (more on that below). But keep
\nthis in mind:\u00c2\u00a0<\/p>\n

Any join made manually, or with extra effort, should be considered temporary.<\/strong>\u00c2\u00a0<\/p>\n

There are various situations in ILM which can only be reliably rectified by a clear-out and re-import of a connector space.<\/p>\n

Always plan for this.<\/p>\n

Ideally, when you re-import a connector space, you will have a single, direct join rule which effortlessly re-joins all your objects. And to achieve this we use…\u00c2\u00a0<\/p>\n

Breadcrumbing<\/h3>\n

Once the join is verified you should export a uniquely identifying attribute, such as an employee number, to the target directory.<\/p>\n

After that a simple \u00e2\u20ac\u0153employeeID = employeeID<\/em>\u00e2\u20ac\u009d type join rule is all you will need.\u00c2\u00a0<\/p>\n

\"\"\u00c2\u00a0<\/p>\n

Sometimes you are faced with an import-only system. For either technical or political reasons you are not able to export the breadcrumb attribute.<\/p>\n

There are a couple of things you can do:\u00c2\u00a0<\/p>\n

    \n
  1. \u00c2\u00a0Import the DN or other identifying attribute from the target directory into an attribute on the Metaverse object.But be warned<\/strong>, you could still lose these joins if you had to repopulate your Metaverse.<\/li>\n
  2. \u00c2\u00a0To be on the safe side you should also \u00e2\u20ac\u0153save\u00e2\u20ac\u009d these joins by exporting an identifying pair somewhere else \u00e2\u20ac\u201c for example using a database or text file
    \nMA, as pictured below.<\/li>\n<\/ol>\n

    \"\"\u00c2\u00a0<\/p>\n

    Understanding Join Rules<\/h3>\n

    There are some key points to note about Join Rules:\u00c2\u00a0<\/p>\n

      \n
    1. Join rules operate on the CS object. It takes one CS object and attempts to find a match among all Metaverse objects of the correct type, even if they are already joined.<\/li>\n
    2. Components within one rule are AND-ed together \u00e2\u20ac\u201c they must all match.<\/li>\n
    3. Multiple rules are evaluated top down, so put your strongest rules at the top.<\/li>\n
    4. While you can offer variations on a CS objects attribute using an Advanced Join Rule, you have to find an exact match with an attribute already on a Metaverse
      \nobject. There are no \u00e2\u20ac\u0153StartsWith<\/em>\u00e2\u20ac\u009d or \u00e2\u20ac\u0153Contains<\/em>\u00e2\u20ac\u009d comparisons.<\/li>\n<\/ol>\n

      Resolve Join<\/h2>\n

      At the bottom of the Join Rule configuration you will see a check box \u00e2\u20ac\u0153Use rules extension to resolve<\/em>\u00e2\u20ac\u009d.<\/p>\n

      Here you can link to code you write under the ResolveJoinSearch<\/em> subroutine in the MA extension.<\/p>\n

      The Resolve rule is used when ILM finds multiple possible matches in the Metaverve (including already-joined objects).<\/p>\n

      All the possible matches into the collection rgmventry and your job, in the code, is to check through them, looking for the best possible match.<\/p>\n

      If your code finds an ideal match you return the index number of the object in imventry, and set the value of ResolveJoinSearch to true.<\/p>\n

      The following example only joins if a single, unjoined Metaverse object was found.\u00c2\u00a0<\/p>\n

      \n
      Public Function ResolveJoinSearch(ByVal joinCriteriaName As String, ByVal csentry As CSEntry, ByVal rgmventry() As MVEntry, ByRef imventry As Integer, ByRef MVObjectType As String) As Boolean Implements IMASynchronization.ResolveJoinSearch\r\n   <\/span>Select Case joinCriteriaName\r\n      <\/span>Case \"Resolve_NotYetJoined\"\r\n         <\/span>If rgmventry.Length = 1 AndAlso _\r\n   <\/span>      <\/span>   <\/span>rgmventry(0).ConnectedMAs(\"AD\").Connectors.Count = 0  Then\r\n      <\/span>   <\/span>   <\/span>imventry = 0\r\n            <\/span>Return True\r\n         <\/span>Else\r\n            <\/span>Return False\r\n         <\/span>End If\r\n   <\/span>End Select\r\nEnd Function<\/pre>\n<\/div>\n
      Advanced Join Rules<\/h3>\n
      A simple join rule directly matches a connector space attribute to a Metaverse attribute:<\/p>\n\n\n\n\n\n
      \u00c2\u00a0<\/th>\nConnector Space<\/th>\n\u00c2\u00a0<\/th>\nMetaverse<\/th>\n\u00c2\u00a0<\/th>\n<\/tr>\n
      \u00c2\u00a0<\/td>\ngivenName = Kathryn<\/td>\n\u00c2\u00a0<\/td>\nFirstName = Kathryn<\/td>\n\u00c2\u00a0<\/td>\n<\/tr>\n
      \u00c2\u00a0<\/td>\nsn = Bigalow<\/td>\n\u00c2\u00a0<\/td>\nLastname = Bigalow<\/td>\n\u00c2\u00a0<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
      With an Advanced Join Rule you construct a list of possible values with which to find an exact match in the Metaverse:<\/p>\n\n\n\n\n\n
      \u00c2\u00a0<\/th>\nConnector Space<\/th>\n\u00c2\u00a0<\/th>\nMetaverse<\/th>\n\u00c2\u00a0<\/th>\n<\/tr>\n
      \u00c2\u00a0<\/td>\ngivenName = KathryngivenName = KategivenName = Kathy<\/td>\n\u00c2\u00a0<\/td>\nFirstName = Kate<\/td>\n\u00c2\u00a0<\/td>\n<\/tr>\n
      \u00c2\u00a0<\/td>\nsn = Bigalow<\/td>\n\u00c2\u00a0<\/td>\nLastname = Bigalow<\/td>\n\u00c2\u00a0<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
      Sometimes you can use code rules to make your list of possible matches (eg., presenting a phone number in different formats); other times you have to use long
      \nlook-up lists of possible variations (try genealogy websites for name-variation lists).<\/p>\n
      The following example uses a lookup file of aliases, where each line has the possible variations on a name.<\/p>\n
      If a match to the first name is found on the connector space object, the whole line is added to the possible values to search for in the Metaverse.\u00c2\u00a0<\/p>\n
      Elizabeth,Liz,Beth,Betty
      \nDavid,Dave,Davey
      \nJerome,J\u00c3\u00a9r\u00c3\u00b4me
      \n…\u00c2\u00a0<\/p>\n
      \n
      Public Class MAExtensionObject\r\n   <\/span>Implements IMASynchronization\r\n   <\/span>Dim fileAliases As System.IO.StreamReader\r\n   <\/span>Dim arrAliases As String()\r\n   <\/span>Dim i As Integer\r\n\r\n   <\/span>Public <\/span>Sub Initialize() Implements IMASynchronization.Initialize\r\n      <\/span>fileAliases = New System.IO.StreamReader(\"C:\\aliases.txt\",  System.Text.Encoding.Default)\r\n      <\/span>i = 0\r\n      <\/span>While Not fileAliases.EndOfStream\r\n         <\/span>ReDim Preserve arrAliases(i)\r\n         <\/span>arrAliases(i) = fileAliases.ReadLine\r\n         <\/span>i = i + 1\r\n      <\/span>End While\r\n\r\n     <\/span>fileAliases.Close()\r\n   <\/span>End Sub\r\n\r\n   <\/span>Public Sub MapAttributesForJoin(ByVal FlowRuleName As String, ByVal csentry As CSEntry, ByRef values As ValueCollection) Implements IMASynchronization.MapAttributesForJoin\r\n      <\/span>Select FlowRuleName\r\n         <\/span>Case \"Join_aliases\"\r\n            <\/span>Dim aliasList As String\r\n            <\/span>Dim value As String\r\n\r\n            <\/span>For Each aliasList In arrAliases\r\n               <\/span>If aliasList.Contains(csentry(\"givenName\").Value) Then\r\n                  <\/span>For Each value In aliasList.Split(\",\".ToCharArray)\r\n                  <\/span>   <\/span>values.Add(value)\r\n                  <\/span>Next\r\n\t      <\/span>End If\r\n            <\/span>Next\r\n         <\/span>End Select\r\n     <\/span>End Sub\r\nEnd Class<\/pre>\n<\/div>\n
      Using CSV Files<\/h2>\n
      A lot of the difficult account matching will probably be done outside ILM.<\/p>\n
      It is therefore useful to be able to \u00e2\u20ac\u0153export\u00e2\u20ac\u009d lists of possible matches, and later, \u00e2\u20ac\u0153import\u00e2\u20ac\u009d the joins from a CSV file.<\/p>\n
      Be careful when writing to files from extension code \u00e2\u20ac\u201c the DLL doesn\u00e2\u20ac\u2122t unload for five minutes after the MA run completes, which means you may have to wait for it
      \nto finish writing to the file. If you\u00e2\u20ac\u2122re in a hurry, recompiling the code will force the DLL to finish writing to the file.\u00c2\u00a0<\/p>\n
      Export Possible Matches to a CSV file<\/h3>\n
      You can use the Resolve rule to export possible matches to a CSV file. The following example resolves the join rule \u00e2\u20ac\u0153sn | Direct | lastname\u00e2\u20ac\u009d. As a match on the last
      \nname alone is too weak for an immediate join, we just write the possible matches to the text file.\u00c2\u00a0<\/p>\n
      \n
      Public Class MAExtensionObject <\/span>Implements IMASynchronization\r\n   <\/span>Dim fileMatches As System.IO.StreamWriter\r\n\r\n   <\/span><\/span>Public <\/span>Sub Initialize() Implements IMASynchronization.Initialize\r\n      <\/span>fileMatches = New System.IO.StreamWriter(\"C:\\possible matches.txt\", System.Text.Encoding.Default)\r\n   <\/span>End Sub\r\n\r\n   <\/span>Public Sub Terminate() Implements IMASynchronization.Terminate\r\n      <\/span>fileMatches.Close()\r\n   <\/span>End Sub\r\n\r\n   <\/span>Public Function ResolveJoinSearch(ByVal joinCriteriaName As String, ByVal csentry As CSEntry, ByVal rgmventry() As MVEntry, ByRef imventry As Integer, ByRef MVObjectType As String) As Boolean Implements IMASynchronization.ResolveJoinSearch\r\n      <\/span>Select Case joinCriteriaName\r\n         <\/span><\/span>Case \"Resolve_Lastname\"\r\n            <\/span>Dim MAName As String = csentry.MA.Name\r\n            <\/span>Dim mvobject As MVEntry\r\n            <\/span>Dim cFirstname, mFirstname As String\r\n\r\n            <\/span>If csentry(\"givenName\").IsPresent Then\r\n               <\/span>cFirstname = csentry(\"givenName\").StringValue\r\n            <\/span>Else\r\n            <\/span>   <\/span>cFirstname = \"UNKNOWN\"\r\n            <\/span>End If\r\n\r\n            <\/span>If mvobject(\"firstname\").IsPresent Then\r\n            <\/span>   <\/span>mFirstname = mvobject(\"firstname\").StringValue\r\n            <\/span>Else\r\n            <\/span>   <\/span>mFirstname = \"UNKNOWN\"\r\n            <\/span>End If\r\n\r\n            <\/span>For Each mvobject In rgmventry\r\n               <\/span>If mvobject.ConnectedMAs(MAName).Connectors.Count = 0  Then\r\n               <\/span><\/span>   <\/span>fileMatches.WriteLine(csentry(\"sn\").StringValue &<\/span> \";\" _\r\n     \t\t   &<\/span> cFirstname &<\/span> \";\" _\r\n     \t\t   &<\/span> mvobject(\"lastname\").StringValue &<\/span> \";\" _\r\n     \t\t   &<\/span> mFirstname)\r\n               <\/span>End If\r\n            <\/span>Next\r\n            <\/span>Return False\r\n         <\/span>End Select\r\n   <\/span><\/span>End Function\r\nEnd Class<\/span><\/pre>\n<\/div>\n
      You will need to adapt this code of course. Firstly, you probably want to export a lot more identifying information in your CSV file \u00e2\u20ac\u201c department, email address,
      \ndn … whatever helps. Next, it can really help to supplement your possible matches with a probability score. This is where you do a series of tests and add points-
      \nthe more points, the higher the chance of the match. For example:\u00c2\u00a0<\/p>\n
        \n
      • Names similar*\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0 +1<\/li>\n
      • Department the same +1<\/li>\n
      • City the same\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0\u00c2\u00a0
        \n+1<\/li>\n<\/ul>\n
        *Some tips for testing if names are similar:\u00c2\u00a0<\/p>\n
          \n
        • Strip out all spaces, dashes and hyphens then compare;<\/li>\n
        • Check if one string is contained in the other (so that \u00e2\u20ac\u0153Sally-Anne\u00e2\u20ac\u009d gets
          \na point for \u00e2\u20ac\u0153Sally\u00e2\u20ac\u009d);<\/li>\n
        • Use a function which compares string similarity (search \u00e2\u20ac\u0153Soundex\u00e2\u20ac\u009d and \u00e2\u20ac\u0153Levenshtein
          \ndistance\u00e2\u20ac\u009d).<\/li>\n<\/ul>\n
          Join from CSV<\/h2>\n
          You can use an Advanced Join Rule to \u00e2\u20ac\u0153import<\/em>\u00e2\u20ac\u009d joins from a CSV file.<\/p>\n
          Firstly, our CSV file must be constructed like this:\u00c2\u00a0<\/p>\n
          CS_identifier;MV_identifier\u00c2\u00a0<\/p>\n
          For example, if we are trying to match an AD account against Metaverse objects imported from the HR system, we populate the CSV with the AD DN and the employeeID:\u00c2\u00a0<\/p>\n
          CN=Fred Bloggs,OU=User,OU=MyOrg,DC=mydomain,DC=com;0012988\u00c2\u00a0<\/p>\n
          Next we create the Advanced Join Rule which will look up the csobject\u00e2\u20ac\u2122s DN in the text file, but use the employeeID to search the Metaverse.\u00c2\u00a0<\/p>\n
          Note that you can\u00e2\u20ac\u2122t actually use the DN in the join rule \u00e2\u20ac\u201c but that\u00e2\u20ac\u2122s ok, just use any attribute that definitely exists. Eg.,\u00c2\u00a0<\/p>\n
          sAMAccountName | Rules extension \u00e2\u20ac\u201c Join_CSV | employeeID\u00c2\u00a0<\/p>\n
          And now for the code :\u00c2\u00a0<\/p>\n
          \n
          Imports Microsoft.MetadirectoryServices\r\n\r\nPublic Class MAExtensionObject <\/span>Implements IMASynchronization\r\n   <\/span>Dim joins As String()\r\n   <\/span>Dim i As Integer\r\n\r\n   <\/span>Public <\/span>Sub Initialize() Implements IMASynchronization.Initialize\r\n      <\/span>Dim fileJoins As System.IO.StreamReader\r\n      <\/span>Dim strLine As String\r\n     <\/span>'Open the csv file and read into an array\r\n      <\/span>fileJoins = New System.IO.StreamReader(\"C:\\joins.csv\", System.Text.Encoding.Default)\r\n      <\/span>i = 0\r\n      <\/span>While Not fileJoins.EndOfStream\r\n      <\/span>   <\/span>ReDim Preserve joins(i)\r\n         <\/span>joins(i) = fileJoins.ReadLine\r\n         <\/span>i = i + 1\r\n      <\/span>End While\r\n      <\/span>fileJoins.Close()\r\n   <\/span>End Sub\r\n\r\n   <\/span>Public Sub MapAttributesForJoin(ByVal FlowRuleName As String, ByVal csentry As CSEntry, ByRef values As ValueCollection) Implements IMASynchronization.MapAttributesForJoin\r\n      <\/span>Select FlowRuleName\r\n         <\/span>Case \"Join_CSV\"\r\n            <\/span>'If the csentry DN is found in the joins array, then\r\n            <\/span>'use the paired employeeID to search the Metaverse.\r\n            <\/span>For i = 0 To joins.Length - 1\r\n               <\/span>If joins(i).Contains(csentry.DN.ToString) Then\r\n               <\/span>   <\/span>values.Add(joins(i).Split(\";\")(1))\r\n               <\/span>End If\r\n         <\/span>Next\r\n\r\n   End Select\r\nEnd Sub<\/pre>\n<\/div>\n
          Reporting<\/h2>\n
          People will ask you questions like \u00e2\u20ac\u0153How many people have you joined in system X but not in Y?\u00e2\u20ac\u009d, \u00e2\u20ac\u0153How sure are you that the joins are correct?\u00e2\u20ac\u009d, \u00e2\u20ac\u0153Which department
          \nhas the most unidentified accounts?\u00e2\u20ac\u009d It\u00e2\u20ac\u2122s best to be prepared for these sorts of questions.\u00c2\u00a0<\/p>\n
          Querying the Metaverse<\/h3>\n
          Once data is in the Metaverse it is a simple matter to access it for reporting, either by exporting it into a reporting table, or by directly querying the underlying
          \ntables (as long as you\u00e2\u20ac\u2122re careful to do it when ILM is idle, or else use NOLOCK).\u00c2\u00a0<\/p>\n
          So consider this: During the data cleaning phase, import all identifying attributes into the Metaverse from all sources<\/strong>.\u00c2\u00a0<\/p>\n
          For example: You\u00e2\u20ac\u2122ve made a join between a user in AD and an HR record.<\/p>\n
          Under normal operations you would consider HR as the master source for the name, and you would only flow it from there.<\/p>\n
          You wouldn\u00e2\u20ac\u2122t bother importing the name attributes from AD \u00e2\u20ac\u201c in fact you\u00e2\u20ac\u2122re more likely to be overwriting them with export flow rules.<\/p>\n\n\n\n\n\n
          \u00c2\u00a0<\/th>\nHR<\/th>\n\u00c2\u00a0<\/th>\n\u00c2\u00a0<\/th>\n\u00c2\u00a0<\/th>\nMetaverse<\/th>\n\u00c2\u00a0<\/th>\n\u00c2\u00a0<\/th>\n\u00c2\u00a0<\/th>\nAD<\/th>\n\u00c2\u00a0<\/th>\n<\/tr>\n
          \u00c2\u00a0<\/td>\nLastname = Powells-Brown<\/td>\n\u00c2\u00a0<\/td>\n-><\/td>\n\u00c2\u00a0<\/td>\nlastname = Powells-Brown<\/td>\n\u00c2\u00a0<\/td>\n-><\/td>\n\u00c2\u00a0<\/td>\nsn = Powells-Brown<\/td>\n<\/tr>\n
          \u00c2\u00a0<\/td>\nFirstname = Joanna<\/td>\n\u00c2\u00a0<\/td>\n-><\/td>\n\u00c2\u00a0<\/td>\nfirstname = Joanna<\/td>\n\u00c2\u00a0<\/td>\n-><\/td>\n\u00c2\u00a0<\/td>\ngivenName = Joanna<\/td>\n\u00c2\u00a0<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
          However, during the data matching phase, you\u00e2\u20ac\u2122re probably not ready to start overwriting attributes, and the information about current values can be very important in your
          \nverification and reporting.<\/p>\n\n\n\n\n\n
          \u00c2\u00a0<\/th>\nHR<\/th>\n\u00c2\u00a0<\/th>\n\u00c2\u00a0<\/th>\n\u00c2\u00a0<\/th>\nMetaverse<\/th>\n\u00c2\u00a0<\/th>\n\u00c2\u00a0<\/th>\n\u00c2\u00a0<\/th>\nAD<\/th>\n\u00c2\u00a0<\/th>\n<\/tr>\n
          \u00c2\u00a0<\/td>\nLastname = Powells-Brown<\/td>\n\u00c2\u00a0<\/td>\n-><\/td>\n\u00c2\u00a0<\/td>\nHR_lastname = Powells-Brown<\/td>\n\u00c2\u00a0<\/td>\n-><\/td>\n\u00c2\u00a0<\/td>\nsn = Powells-Brown<\/td>\n\u00c2\u00a0<\/td>\n<\/tr>\n
          \u00c2\u00a0<\/td>\nFirstname = Joanna<\/td>\n\u00c2\u00a0<\/td>\n-><\/td>\n\u00c2\u00a0<\/td>\nHR_firstname = Joanna
          \nAD_lastname = Powells
          \nAD_firstname = Jo<\/td>\n          		\u00c2\u00a0<\/td>\n<-
          \n<-<\/td>\n          		\u00c2\u00a0<\/td>\ngivenName = Joanna
          \ngivenName = Jo<\/td>\n          		\u00c2\u00a0<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
          Now, if you have a look at the mms_metaverse<\/em> table in the ILM database, you will see how simple it is to query the progress of your joins, and also to judge
          \non what criteria the joins were made.<\/p>\n
          Some example queries\u00e2\u20ac\u00a6\u00c2\u00a0<\/p>\n
          \/* HR person with no join to AD *\/<\/p>\n
          select HR_lastname, HR_firstname, HR_employeeid from mms_metaverse
          \nwhere AD_dn is null\u00c2\u00a0<\/p>\n
          \u00c2\u00a0\/* HR person with join to AD *\/<\/p>\n
          select HR_lastname, HR_firstname, HR_employeeid, AD_lastname,AD_firstname,AD_dn from mms_metaverse
          \nwhere AD_dn is not null\u00c2\u00a0<\/p>\n\n\n\n\n
          \"Caution\" Caution<\/strong><\/span><\/th>\n<\/tr>\n
          As mentioned above you need to be
          \ncareful when directly querying the Metaverse tables.If your system is already in production, and you happen to be adding in
          \na new data source, then you may be better off employing a SQL MA to export
          \nthe data you\u00e2\u20ac\u2122re interested in to another table, where you can query it as
          \nmuch as you like without risk of locking errors.<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
          \u00c2\u00a0\u00c2\u00a0<\/p>\n
          Querying the Connector Space<\/h2>\n
          Unfortunately it is not so simple to query the connector space to, for example, report on that state of your disconnected objects.<\/p>\n
          It\u00e2\u20ac\u2122s a great pity that you can\u00e2\u20ac\u2122t just save results from the Joins page in the Identity Manager GUI, so your options are:\u00c2\u00a0<\/p>\n
          SQL query<\/strong> – The CS table holds data differently to the Metaverse tables.<\/p>\n
          It is possible to query for disconnectors in this way, however you will only be able to retrieve the CN of objects \u00e2\u20ac\u201c which may not be sufficient to identify them.\u00c2\u00a0<\/p>\n
          \n
          select cs.rdn from dbo.mms_connectorspace cs\r\njoin dbo.mms_management_agent ma\r\non cs.ma_id = ma.ma_id\r\nleft outer join dbo.mms_csmv_link mv\r\non mv.cs_object_id = cs.object_id\r\nwhere ma.ma_name = 'My MA'\r\nand mv.mv_object_id is null\r\nand cs.connector_state = 0<\/pre>\n<\/div>\n
          CSExport<\/strong> – The command-line utility csexport.exe, found in the <ILM program>\\bin folder, will allow you to dump connector space objects to an XML file.\u00c2\u00a0<\/p>\n
          Report directly from the data source<\/strong> – Once an object in the data source has been correctly identified you will ideally export a unique attribute out to it. It may then be possible to identify the non-joined objects as those which don\u00e2\u20ac\u2122t possess this attribute.\u00c2\u00a0<\/p>\n
          Project to a different object type<\/strong> – ILM processes joins before projections, so it is fairly simple to project all non-joined objects to a different Metaverse object type \u00e2\u20ac\u201c for example, one called \u00e2\u20ac\u0153disconnectors\u00e2\u20ac\u009d. This may help in reporting on an overall status direct from the Metaverse tables.<\/p>\n
          Note however that to make these objects once again available for joins they will have to be disconnected from the MVExtension provisioning code.\u00c2\u00a0<\/p>\n
          Advanced Tips and Tricks<\/h2>\n
          Join to Multi-value Attribute<\/p>\n
          Sometimes you may need to join to a value in a multi-value attribute. An example is searching through all the proxyAddresses<\/em> for a match against
          \na single email address.\u00c2\u00a0<\/p>\n
          When the multi-valued attribute exists in the connector space, and the single valued attribute is in the Metaverse, this is very easily accomplished. Just use an Advanced Join Rule to break the multi-valued attribute down into the values list used by the join rule.\u00c2\u00a0<\/p>\n
          \n
           Public Sub MapAttributesForJoin(ByVal FlowRuleName As String, ByVal csentry As CSEntry, ByRef values As ValueCollection) Implements IMASynchronization.MapAttributesForJoin\r\n   <\/span>Select FlowRuleName\r\n      <\/span>Case \"Join_proxyAddresses\"\r\n         <\/span>Dim alias As String\r\n         <\/span>For Each alias In csentry(\"givenName\").Values\r\n   <\/span>   <\/span>   <\/span>   <\/span>values.Add(alias)\r\n         <\/span>Next\r\n   <\/span>End Select\r\nEnd Sub<\/pre>\n<\/div>\n
          However, when the multi-valued attribute has already been imported into the Metaverse you will have a problem. While you can join on a multi-valued attribute, you have to join on the whole thing. There is no way that you can match one value out of a multi-valued Metaverse attribute against a single-valued connector space attribute.\u00c2\u00a0<\/p>\n
          Some possible options:\u00c2\u00a0<\/p>\n