Nowadays, it might be tiring for companies to deal with the employee turnover. Whenever an employee departs from the company, certain steps need to be taken. And in the view of departure, it is not just retirement, but also parental leave or long-term sick leave. In all these cases, it is necessary to “move” their responsibilities to another person. Moreover, that needs to happen not only on a piece of paper but also in the system - in the ServiceNow platform.
A user can be the manager of other users, manager of an assignment group, an Assignee of an ongoing incident, Change manager, or Configuration manager of certain HW classes in CMDB… Well, there are quite many places where users can be referenced. So, trying to find all these places manually can be a very long and tedious process. It would be nice to have a “button” to show all these references. Unfortunately, there is no such functionality in the out-of-the-box ServiceNow platform. So let´s create one :) Not a button, but rather a whole process.
When searching for references, we know where to go - Dictionary table. We can get a list of all places, where a user can be referenced with a simple query.
In the clean OOB instance (Rome release), there are more than 4100 dictionary entries with reference to the User [sys_user] table. If you look closely, you will notice the same attributes on all CMDB extended tables. For example, the attribute “attested_by” is there 701 times. We will not cover it here as you are surely familiar with the ServiceNow data model and table extension logic.
So, back to the topic. This “extension” logic means that we do not need to query all 701 tables but only the base table. So, how do we figure out the “base” table for an attribute? Well, there is no clear mark (attribute) we could use, but we can re-use what is already in the system. Do you remember this message when you are trying to update some attributes on CMDB?
With some fast detective work, you can find out how ServiceNow determines when to show this message and simply copy the logic to your script. Now we know where to search for references, and it is time to put leaving person´s sys_id into the mix.
Function “checkTableRecords” is a place where you query for records with built query (owned_by=<user sys_id>). The reason why “type” is passed as well is that reference can be used in “single” reference or “multi” reference - Glide List. Therefore, when querying references in the Glide List attribute, you must not forget to use the operator “CONTAINS”.
So we are at the end, right? Not that fast :) We know where a given user is referenced, but our job is not done yet. We still need to handle actual “replacement”. Can we simply replace all instances of user reference with a new user (sys_id)? Definitely not! The query above will not tell us where the user is actually “responsible” and where it is just a “reference”. As an example, take a look at the incident table. There are user references like “caller_id” and “assigned_to”. When a user leaves the company, it does not mean that we should move the user´s incidents (attribute caller_id) to someone else. Those are historical data and should not be touched. In the case of “assigned_to”, this is more complex. If the incident is closed, it is historical information and should not be changed. But if the incident is still open, a person is waiting for the resolution, and no one is working on that. So here, replacement makes sense.
How do we then determine what references we must replace and what we can ignore? You will not like the answer - you have to do it manually using your knowledge of the platform and common sense. It does not necessarily mean that you have to figure out all references at once. For this, you can use JIT (just in time) approach. When you receive a request to replace the leaving user with the new hiree, check where leaving user is referenced and decide on those places and store the decision somewhere in the system. With the next request for user replacement, there might be a few other places with user references that you did not handle in previous requests. Again, store the action decisions for the next request. By doing so, you will gradually build a list of user references with the required action. It is up to you to decide where to store this information. The easiest solution would be adding a new custom attribute on Dictionary [sys_dictionary] table. Unfortunately, this is not allowed, and you will get an error message when trying so. Another option might be a custom table (if your ServiceNow subscription allows you to) or using System property to store the data in some structure (e.g. JSON) - be careful, as here you can hit the allowed limit of 4000 chars for the attribute “value”. For the purpose of this example, we will create a custom table “User references” with 2 attributes: reference to sys_dictionary (using dot-walking, we will get all needed information - table name, attribute name, type of reference) and choice “Action”.
Now when we have a storage for the user references, we should define an action for each of them. Take this as a starting point rather than a complete list of actions.
- Ignore - references like “incident.caller_id” are historical data, and no action is required.
- Replace - sys_id of the user is replaced with new sys_id (e.g. group manager, owner of a Configuration item)
- Clear - user reference is cleared on a record (e.g. assignee on the incident)
- Remove - the whole record where a user is referenced is deleted (e.g. group membership or any m2m table)
Few notes to the above actions.
- When bulk modifying records, make sure you have enough rights to do so.
- When replacing the user in Glide List reference, make sure the new user is not already there. ServiceNow does not like duplicate sys_ids in Glide List attributes.
- Test different approaches (speed + reliability) to update of multiple records. Using a while loop with thousands of records might be slow. Also, decide if business rules shall be triggered during the replacement process as it might end with an unpredictable result (some validation in BR might stop the update of the record). More importantly, it might have a significant impact on platform performance.
- When clearing the user´s sys_id on record, you might hit the wall as the attribute can be set as mandatory on the Data policy level.
Now, we are almost at the end. We have logic and scripts ready. It is time to put it all in a process - Request fulfillment. The reasons are obvious - clear evidence about who is the requestor, when it was requested, who approved it. The creation of a new catalog item with two variables, “Leaving person” and “Replacement”, will take just a few minutes. What will be more time-consuming is Flow / Workflow behind. Such requests cannot be done without a proper approval process. As a fail-safe, multiple (sequential) approvals are highly recommended. Once approved, the fulfillment process can be split into 3 parts:
- Execute a script to check all tables where the given user is referenced and add the result in “User Reference” if not there yet. Attribute “Action” is kept empty on the insert.
- Evaluate any new records in the “User Reference” table with empty action and fill in the action.
- Execute a script that will handle actual User replacement based on defined action in the “User Reference” table.
As you can see, steps #1 and #3 can be fully automated in (work)flow without any human (support team/admins) interaction. The outcome of automation can be logged into Work notes. Step #2 can be a conditional step, and human interaction will be required less and less with every processed order.
There is one more important thing that has not been covered yet – the backup of data. It is, of course, fully up to you, but it is highly recommended prior to step #3, to do a backup of all data that are going to be changed/deleted. It depends on your needs, but at least a list of sys_ids of changed records could be stored in Work notes (attachment). A more complex approach would be XML/JSON backup of changed records.
Thank you for your time
Martin
Senior ServiceNow Consultant