Synch Active Directory Users’ Status with ServiceNow Users – With Efficiency and Pizzazz

I found something peculiar with the way our LDAP user import was set up (or at least I felt it was a bit peculiar). We have never synched our ServiceNow users’ statuses (active/locked out) with the associated status in active directory. This rarely came up until I was asked to create a unique notification report for HR that required the knowledge if a user was active or not. This is my saga to find a way to ensure the users’ statuses in ServiceNow matched their status in active directory.

The back story (Note this was all done in our dev environment using an update set first [just in case you imagined I was doing this the fast way – directly in production.]):

I commenced my research with LDAP data source. It, I found, used a LDAP target (also referred to as an LDAP OU Definition elsewhere in ServiceNow) that only retrieved active users from active directory. “Well why in the heck are we doing that?!” I asked myself. “Why don’t we just change that to retrieve all users then set their status accordingly?” It was a good question and because there already existed a definition that pulled all the users, I thought I solved the problem! Woot!

I was reaching over to get my genius hat so I could don it, when the second part of the problem tapped on my screen “tap-tap-tap!”

Look at me…no down here. Remember you need me to get the data from the ldap_import temporary table into the sys_user table. This was the Transform Map reminding me of its importance in this process.

I gently dropped my genius hat, and clicked on the Transform Map link. What I found was a bit confusing.

I noticed that the perfect spot in this process to determine and then set a ServiceNow user status was in the onBefore script, but … but, this was set to inactive. Scratching my head I wondered aloud, “why the heck would we do that?!” “Well, that’s an easy fix!” I offered myself. “I’ll just activate that script and use the userAccountControl value from active directory to set a users active/locked out status.” You see, our LDAP expert already told me that ‘514’ meant inactive and ‘512’ meant active, so I just modified the script to check for these 2 values and set ServiceNow user statuses accordingly (the script originally was converting the value to a hex then back to a string then getting the last character – and if it was as “2” then it was inactive — I felt checking for 514 or 512 was easier. But, I copied the original code and added it at the bottom in a comment … just in case!).

Problem solved – or so I thought.

I went to the scheduled import for the LDAP data source and clicked the execute now button – fully expecting to see a boatload of inactivated users (you see in a Retail business, there’s lots of turnover so there had to be a bunch of terminated employees).

No inactivated users! How the heck did that happen? I was logging results in the script, but not seeing the script hit the section where it checked for ‘514.’

So I went to the transform history to see the data coming in from from the data source. I clicked on the import set to look at the values for user records coming in from the data source. I scrolled down to the import set rows and clicked on that dates for a few users. What did I see?

No values for userAccountControl. “Hmmm… that’s strange!” I ruminated. How do I get that value from active directory? Simple enough I discovered.

In the LDAP Server, there is a field called “Attributes” which is basically a comma-separated list of active directory values the process should retrieve. Know what I found? Yep! userAccountControl wasn’t there – so I added it.

I re-executed the scheduled import and Holy Transform Batman! it worked. About half of my users were now marked as inactive and locked out. I did a search in the active directory database (using Apache Directory Studio) and they all seemed to be matching.

Woot! problem solved! “Not so fast!” my mind nagged me. “This seemed a bit too easy” my mind toyed with me. “What am I missing?” (Volume – it turned out! — see below).

So I decided to ask a bunch of smart people at an upcoming ServiceNow developers meetup if I was missing anything — and boy, did I get some great feedback.

The main challenge I didn’t account for was the idea of having to pull all users every time the scheduled import ran. We only have 9,000 users in active directory, but some of the folks in the meetup indicated that they had over 100,000 users and pulling them every time would be inefficient. Another offered that I could pull only those active directory user records that changed over the past few days – BINGO! That’s the answer!

So I thanked the smart people in the meetup and wrote notes for the next morning’s activities at work.

The next morning around 5:30 (I have a 45-mile commute, so I get up at 3:50 and ride my motorcycle early to beat the traffic – at least in the morning) I set this solution in motion. It required simply a new LDAP OU Definition (LDAP target in the data source record) that retrieved the records in active directory (using the whenChanged field) over the past 3 days. Simple right – hold on there pardner! Not so fast.

You see the Filter field in the LDAP OU Definition IS A TEXT field — how the heck am I going to dynamically calculate a new 3-days-in-the-past date every day? So, I launched the ServiceNow Slack channel (sndevs.slack.com) and asked the question. In mere minutes I get this response from Zechariah Harvey. “I don’t think you’re going to be able to get it dynamic without something tricky like a scheduled job to update the query string within the definition record and saving the changes before executing the LDAP pull?”

Whoa … mind blown!

So that’s what I did. I created a new scheduled job to run a script of my choosing. In that script I calculate the LDAP query I needed to and then use a GlideRecord to update the filter field in the LDAP OU Definition record. See the code below.

So here’s how I’m fixing this to efficiently get only the active directory records that changed within the past 3 days, checking their status and using that to update the ServiceNow user records:

1. The scheduled job to update the LDAP OU Definition’s filter field runs every night at 11:55 pm.
2. Our scheduled LDAP import runs hourly and uses the LDAP OU Definition I created to get only those records that have changed in the past 3 days.
3. The transform map runs the onBefore script which checks for the incoming userAccountControl value and if 514 sets the user’s record to inactive/locked out. If 512 sets the status to active/not locked out (this second part helps us account for the myriad rehires we have).

And that’s it. Problem solved with an efficient, pizzazz-field process to ensure our ServiceNow user records match the status in active directory.

Here’s the code I’m using in the scheduled job to calculate the 3-days-in-the-past date, format it in LDAP query formatting and updating the LDAP OU definition’s filter field: