Keep Active Directory (AD) and SNow groups in sync

Sarah Powell · ‎08-01-2016

We currently use LDAP to import our AD groups into a custom table. We are not using AD groups for roles or assignments within ServiceNow, which is why we chose a custom table vs sys_user_group. The import works fine and I get a count of over 7000 groups each time.

My struggle is that the groups don't actually stay in sync. When I say sync, I mean when a group is deleted in AD the group remains in ServiceNow. I need the two systems to be a mirror of on another.

Does anyone have any ideas on how I can get AD and ServiceNow to stay in sync?

tstocking · ‎08-09-2016

Hi Sarah,

Just looked into this myself since we are setting up ServiceNow for the first time and came up with the following solution based on a SN Wiki Article. The problem with just using the LDAP listener is that if a group or user is deleted, ServiceNow won't know about it. The below method is just an example so use at your own risk. It is also set up for users but you could do the same for groups. Hope this helps!

1. Add a Date field to the sys_user table, we called ours "u_last_date_refreshed_from_ad"

2. Update your transform map for LDAP to include a field map for the new field, and then just use a script as the field source to set the date:

var gDT = new GlideDate();

return gDT;

3. Create a Scheduled Load to execute as often as you like, could be once a day or a few times a day depending on how near real time you want it

4. Then just add a post import script, for example:

var cleanUpActiveDirectoryUsers = function() {


var gr = new GlideRecord('sys_user');

var gdt = new GlideDateTime();

gdt.addDays(-1);

gr.addQuery('u_last_date_refreshed_from_ad', '<', gdt);

gr.addEncodedQuery('u_last_date_refreshed_from_ad!=NULL');

gr.query();

while (gr.next()){

   gr.active = false;

   gr.locked_out = true;

   gr.update();

   gs.log(gr.name + ' was set to inactive and locked out because their last Active Directory refresh was: ' + gr.u_last_date_refreshed_from_ad);

}

};


cleanUpActiveDirectoryUsers();

View solution in original post

Sarah Powell · ‎08-09-2016

Todd,

I tried your solution above and my import completes but with errors:

Unable to format 2016-08-09 using format string yyyy-MM-dd hh:mm:ss for field u_refresh_date

Did you experience this issue too?

Thanks,
Sarah

tstocking · ‎08-09-2016

Yes I did, change the date format to yyyy-mm-dd and you should be all set!

Sarah Powell · ‎08-10-2016

Todd,

I was able to tweak your setup a little to match what we need and it seems to be working fine. I did a few manual imports yesterday then let our scheduled import run last night. The groups were set active/inactive accordingly.

I do have one last question... The first group was refreshed at 00:00:28 and the last was refreshed at 02:30:09. The import was taking only minutes to run until I added the transform script. Did you experience a long run time like this? I was going to open a HI ticket to see if they could explain but thought it wouldn't hurt to ask you first.

Thanks!

Sarah

tstocking · ‎08-10-2016

Ours ran from 11:00:06PM to 11:00:19PM so it took about 13 seconds. We have roughly 150 groups. When you do the "Execute Now" on the scheduled load does it complete fairly quickly?

Sarah Powell · ‎08-10-2016

We have a little over 8000 groups coming across. Unfortunately using "Execute Now" doesn't change the run time either. I can open a separate browser to view the table data directly, and the active state is set within seconds but the refreshed date takes the 2 1/2 hours to completely update.