Transform Map: LDAP: How do I handle coalescing when the id's are potentially dynamic?

[akaupisch]

We have an LDAP import which is throwing errors. The reason for this is the following scenario, which is fairly common as we are a hospital:

1) User A joins our company: an account is created in Active Directory which assigns them a GUID.

2) ServiceNow has an LDAP listener which tells us a new user needs to be created...we create one and assign that GUID to a custom field

*** time passes, stuff occurs for this user and updates AD, which triggers our LDAP listener...we match on the GUID and update their data accordingly ***

3) User A leaves the company: They are removed from Active Directory. They are inactivated in ServiceNow, so they still 'exist'

4) User A comes back to the company, an account is created in Active Directory which assigns a new GUID

5) LDAP listener triggers, and, since we coalesce off of GUID, the GUIDs don't map...So SN attempts to create a new user, but can't since that username already exists!

We can't simply change the coalesce to trigger off of the username either since their last name can change and the last name is part of the username.

So, it would seem that I need to do this after it does the transform and it has determined that an insert needs to be done, but just prior to the insert, I need to check to see if the username exists, if so, change it from an insert to an update and change the target GUID to be the source GUID...

The question is how. I see 2 script actions, onChoiceCreate and onForeignInsert, but also onAfter...

Before I start hacking away, I wanted to see if anyone has done this before...my initial thought is the following:

onAfter()
if (action == 'insert')
{
         var result = new GlideRecord('sys_user');
         result.addQuery('user_name', source.user_name);
         result.query();
         if (result.next())
         {
                   target.guid = result.guid;
                   action = 'update';
         }
}

is this the correct approach? Is there a better way of doing it?

Edit: I tried this way, and the code is being executed, however the record is not being updated. Setting the action to update doesn't seem to fix this.

[akaupisch]

So I've been experimenting with this and now have a ticket open with SN...Abouland@bkd.com

onAfter doesn't work because onAfter happens AFTER the transform and AFTER it saves it in the DB.

My goal was to have it occur after the transform but before the insert into the DB...which makes sense...but appears to be impossible...thus I was looking at the 'insert' (meaning it didn't find a match and was creating a new record)...

Instead, I have created an OnBefore and here is the following code (it doesn't work though)

if (source.u_samaccountname != '') //make sure there is an account name
{
         var result = new GlideRecord('sys_user');
         result.addQuery('user_name', source.u_samaccountname); // see if a user exists that has that account name
         result.query();
         if (result.next())
         {
                   if (result.u_object_guid != source.u_objectguid) //compare the GUIDs from what is in our records to what is coming in from LDAP
                   {
                             result.u_object_guid = source.u_objectguid; // if they are different, then set our records to the NEW GUID coming in from LDAP
                             result.update(); // updat our record and continue on with the transform...at this point when the transform completes, it should match on GUIDs properly
                   }   
         }
}

note: I hate doing it this way since it will do a Query for every single record that comes in (potential performance hit) and just seems kludgy. It would be nice if there was an onAfterButBeforeSave function...at that point you'd know it's a new record based on the action being sent to 'insert'...you could then override it and make it an update...

[Chuck Tomasi · Accepted solution]

Thanks for sharing Adam. FWIW, a slightly cleaner version. Handy when you are querying for only one parameter.

if (source.u_samaccountname != '') //make sure there is an account name  

{  

        var result = new GlideRecord('sys_user');  

        // see if a user exists that has that account name

        if (result.get('user_name', source.u_samaccountname ))  

        {  

                  if (result.u_object_guid != source.u_objectguid) //compare the GUIDs from what is in our records to what is coming in from LDAP  

                  {  

                            result.u_object_guid = source.u_objectguid; // if they are different, then set our records to the NEW GUID coming in from LDAP  

                            result.update(); // update our record and continue on with the transform...at this point when the transform completes, it should match on GUIDs properly  

                  }      

        }  

}

[Scott Murphy2]

I used the above code and put it in a transform map that runs before the original one.  It will update the GUID while running the first transform map.  When the second transform map runs, it updates the user record properly.