While this customization may still be necessary in certain situations, ServiceNow handles this issue in Aspen builds or later without any customization.

I

‘ve written before on SNCGuru about how ‘before query’ business rules can (and should) be used to secure row-level read access to records in Service-now. While this usually works perfectly, there is one issue that I’ve seen come up continually that there hasn’t been a good fix for. Over the past few weeks, I’ve seen several incidents and questions about inactive users disappearing from reference fields in Service-now systems. You may have noticed this yourself when you’ve de-activated users or groups in your system. The culprit in these cases is the ‘user query’ or ‘group query’ business rule.

The recommended (but really not great) solution up until this point is to turn the business rule off and use a reference qualifier on the reference field that you need to see the user in. The reason this solution is a bad one is that there are over 300 user reference and list fields in your system! Not only is that a big pain (and a bad idea) to add that reference qualifier to all of those places, but it also does nothing for the countless places (modules, filters, reports, etc.) that have UI elements that work like reference fields but cannot be filtered with a reference qualifier! This isn’t a new problem, but I’ve come up with a new (and extremely simple) solution.

‘Before Query’ business rules usually serve one of two purposes…

  1. Preventing read access to a group of records (security)
  2. Removing records from view so you don’t have to look at irrelevant data in lookups

The problem I’ll touch on in this post arises when you use ‘before query’ business rules to deal with the second point above. A prime example is the ‘user query’ business rule on the ‘User (sys_user)’ table out-of-box. That business rule looks like this…

if (!gs.hasRole("admin")) {
   current.addQuery("active", "true");
}

What this business rule says, in essence, is that users with the ‘admin’ role can read all user records in the system, and everybody else can only read active user records. On the surface, this seems like it’s exactly what you want. There’s no reason for inactive user records to be getting in the way all of the time for your technicians and end users to have to filter through. Even if they can deal with that extra nuisance, you don’t want somebody accidentally logging a ticket for, or assigning a CI to, the ‘John Smith’ that left your company 2 years ago.

Although the goal is a good one, the end result can actually end up being a big mess. If you’ve ever had one of your ‘itil’ users open up an old incident for that long-since-inactive ‘John Smith’ account you’ve seen what happens. ANY of the potentially thousands of reference field entries or filter conditions that reference that inactive user will appear to be blank for anybody without the ‘admin’ role!

Here’s an example screenshot I created of an incident record. In this case, the same user (ITIL User) opened up an incident and is also the Caller and Assignee. Right after the incident was created however, ITIL User was made inactive. The next non-admin user to open the incident would see a record with blank entries in reference fields wherever the user was listed. The values (sys_ids) are still there, but the system isn’t able to query for the inactive user because the user performing the query isn’t an admin! This results in what looks like an empty field because there isn’t a display value shown for the referenced record.

Service-now user_query Problem

How to fix the problem…

So, the obvious question becomes, “How do I fix it?”. The fix is actually a pretty simple one, but I think it helps to understand how ‘before query’ business rules work first. When the system performs a query to display a list of records, a lookup filter, or a reference field display value, it constructs the query the same way you would if you were to write a GlideRecord query in your own script.

current = new GlideRecord('sys_user');
current.addQuery(QUERY_CONDITIONS_HERE);
current.query();

A ‘before query’ business rule on a given table gets run immediately before the ‘current.query()’ line…when the query actually gets executed. So a ‘before query’ business rule gets run before the query execution, but NOT before the initial query conditions get built. What this means is that you have a chance in your ‘before query’ business rule to evaluate whatever query conditions exist and make certain decisions about how to modify the query!
For this particular case, it means that I can check to see if the query is a generic query to bring up a list, or a specific query on a SYS_ID value to pull up a specific record. When the system queries for user records to display in reference fields, filters, and report conditions the query actually looks like this at the time it hits the ‘user_query’ business rule.

current = new GlideRecord('sys_user');
current.addQuery('sys_id', USER_RECORD_SYS_ID);

Notice the absence of the ‘current.query()’ line. When a ‘before query’ business rule is run, that line hasn’t happened yet. Poking around the GlideRecord API a bit, I was able to find a method that allowed me to get the encoded query and evaluate it. What I discovered is that any time the system makes queries for reference fields, etc. the encoded query string always starts with ‘sys_id=’.

So, here’s the fix. Simply wrap your ‘addQuery’ lines in your ‘before query’ business rule in an ‘if’ statement as shown below. Any specific record queries (like those for reference fields, filters, and report conditions) will be allowed. Any other list query (like a standard list of users in a record list or reference popup) will be filtered just like normal!

if (!gs.hasRole("admin")) {
   //Check to see if this is a query for a specific ID
   if (current.getEncodedQuery().indexOf('sys_id=') != 0) {
      current.addQuery("active", "true");
   }
}