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.

[Chuck Tomasi]

Hi Adam,

A typical approach is to use an employee number as a UUID for the user. If you re-assign them when employees leave and return, then how about the email address? Those don't change (unless someone gets married and takes on a new name.) Email is a very common coalesce field.

Yes, there will always be corner cases. Unless you get someone to cough up the rights to their social security number (not likely), we have to use the majority rule. 🙂

[akaupisch]

yea, one would think that employee numbers would be unique, however, if they leave and come back, their employee number can change. Likewise the username/email can't be used since both of them reference last name, which can change as a normal update (which occurs about as frequently as them leaving and coming back). A good example is students, they leave but then are hired after they graduate.

[ABouland]

The situation at my company is very similar with interns who later come back to full time employment when they graduate from school.   Haven't come up with a solution yet.   I'd love to use employee number as a coalesce field as ours never change, but unfortunately this field doesn't get populated in AD until several days after the AD account is created.

That said, when active employees undergo a name change (got married, etc) our AD should have guid and employee number populated the same as would be seen in ServiceNow.   Would Adam's example onAfter script effectively result in an update of existing SN account if AD account was renamed vs. inserting a new record (pointing to either guid or employee_number)?

Thanks,
Andrew

[Chuck Tomasi]

His query is looking at the login name (user_name) so if someone's login ID changes it would insert a new record. If they change their first+last name and the login remains consistent, then it updates.