Fixing the Fatal Flaw of Before Query Business Rules

///Fixing the Fatal Flaw of Before Query Business Rules

Fixing the Fatal Flaw of Before Query Business Rules

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");
   }
}
By | 2018-07-09T15:00:02-06:00 January 5th, 2011|Categories: Business rules|Tags: , , , |22 Comments

About the Author:

22 Comments

  1. Richard January 5, 2011 at 5:14 am - Reply

    Brilliant. Thank you! Thank you! Thank you! Exactly what we need.

  2. valor January 5, 2011 at 5:47 am - Reply

    In a word, awesome.

  3. valor January 5, 2011 at 5:49 am - Reply

    Just a note, this would allow people to see the “inactive” user’s record details as well. Make sure your ACLs are set appropriately so that people that shouldn’t be updating those records, can’t (like users without the “user_admin” role, for example).

    • Brian Broadhurst January 5, 2011 at 6:38 am - Reply

      This is a good point. It would be nice if we could suppress the reference/hover icon based on (scripted) conditions (such as active=false). I’ve come across this requirement a few times, but have never found a satisfactory solution. Ideally, I would like something like an ACL with access type of “click-through” that I could script or assign to specific roles. Do you know any way of doing this, Mark?

      Brian

      • Mark Stanger January 5, 2011 at 7:24 am - Reply

        I wish I knew of a way. I’ll keep that in mind though and see if I can come up with something. Much of the reference icon logic is buried so any solution would probably be a table-by-table client script to hide the icons. It would also be nice to have an easy way to SHOW a reference icon on a readonly field, but it’s not available yet.

  4. Jay January 5, 2011 at 7:07 am - Reply

    What a timely post!!! Someone just asked me about this Monday. Glad I saw this before I went fumbling around trying to fix it myself. Thanks Mark! and Valor for the good point on making sure an ACL is in place!

  5. Nikita January 17, 2011 at 1:31 am - Reply

    Cool. It works!

  6. Rick Mann January 31, 2011 at 2:22 am - Reply

    Excellent fix. Added the if statement and completed validation. Thank you.

    Rick

  7. Omar Pera March 30, 2011 at 3:37 am - Reply

    Really good fix.

    I have a question related, I will really appreciate your help.

    We duplicated the before query Business rule of Incident for another table that needs approvals. Now, a user without roles can’t see the ticket they are approving for. As we don’t have access to the current record we cannot use isApprovalMine, also the addOrCondition filtering the sys_id of their approvals allow all records (only limits with addQuery…), another option was ACL table.read.row, but we cannot access the current from there (and we need to put the logic of the business rule again…).

    So, in conclusion, I tried everything and don’t have a clue. This seems a common thing to do, isn’t it?

    Thank you very much in advanced.

    Omar

    • Mark Stanger March 30, 2011 at 8:30 am - Reply

      I think you’ll probably need to use something like I have described in this article. Since people will be able to see these records if they navigate to them specifically, you’ll probably want to supplement the before query rule with some field-level ACLs.

  8. Michael S July 8, 2011 at 3:35 pm - Reply

    This fix looks great. Just today I got tasked with looking into this issue.

  9. Andreas June 1, 2012 at 9:08 am - Reply

    In our production instance (build: 02-24-201_0902) the fix is still needed as the activity formatter displays only the sys_id instead of the e-mail address for a received e-mail. Also all referenced fields displayed as empty.

    I also checked our dev instance (build: Aspen 04-30-2012_2149) and the fix is still needed for referenced fields; the activity formatter as well as the sys_email table show inactive referenced records properly.

    Parallel tried it in a demo instance (build Aspen 05-18-2012_1143) and there it already works for the caller field but not yet for the opened_by.

    Seems like it will be fully implemented soon but so far: Thx for the fix 🙂

    • Mark Stanger June 1, 2012 at 9:44 am - Reply

      Great feedback! Hopefully we’ll see this fully implemented soon :).

  10. sandy January 10, 2013 at 12:47 am - Reply

    Hi Mark,
    Sometime Process SLAs business rule (async) is getting triggered twice and creating duplicating rows in task_sla table.
    I understood that this is happening because two instances of Process SLAs are created. Please can you tell me how to prevent this from happening and why is this happening.
    We have a report based on SLAs and because of this issue we are getting duplicate data in that report.
    Thanks so much for your help!
    Sandy

  11. Troy October 21, 2013 at 10:52 am - Reply

    Hi Mark,

    This solution works great in our instance! However, I do need to allow inactive users to be selected on one of our catalog items. A comment above stated inactive records can be seen, but not in ours. We have a condition on ours “gs.getSession().isInteractive()”. What would I add so this rule does not run on one specific catalog item? I’ve tried several conditions and can’t seem to get the right one. Thanks for your help!

    Troy

    • Mark Stanger October 21, 2013 at 10:58 am - Reply

      The query business rule will override everything else so there’s not really a way to do what you’re looking to do unless you disable the business rule or allow other roles the ability to see inactive users. If you do that, then you would need to go back to your reference fields and add ‘active=true’ to every other place. That’s probably not very practical, but that’s what you would need to do unless you can adjust your process somehow.

      • Billy Matthews November 11, 2016 at 9:10 am - Reply

        There actually is a way to do this, though it’s a bit of a hack. On the OOB ‘user query’ business rule, change the script to the following:

        if (current.getEncodedQuery().indexOf(‘allow_inactive=true’) != 0) {
        current.addActiveQuery();
        }

        Then on any user references where you explicitly want to allow inactive users to be visible set the start of the reference qualifier to be ‘allow_inactive=true’. Because this attribute doesn’t exist the system essentially ignores this value (thought there’s a system property for strict glide record queries which may give you problems here if you have it enabled but it’s off by default) but we can still detect its existence in our Business Rule to accomplish our goal.

        Now we can explicitly allow inactive users where we want them, without having to turn it off system wide.

  12. Gregg Tolman January 30, 2017 at 10:25 am - Reply

    Thank you Mark and Billy. We used a similar implementation, but explicitly set active=true and active=false in the sys_user reference qualifier. If those are both there then skip the addActiveQuery.

    var encodedQuery = current.getEncodedQuery();
    var indexActiveFalse = encodedQuery.indexOf('active=false');
    var indexActiveTrue = encodedQuery.indexOf('active=true');
    if (indexActiveFalse == -1 || indexActiveTrue == -1 ) {
        current.addActiveQuery();
    }
  13. Josh April 23, 2017 at 5:44 pm - Reply

    Is there any mechanism of doing two sets of query conditions and combining them in a before query business rule?

    We tried doing an encoded query and the ^NQ makes everything go bonkers – which we later found out was a ServiceNow issue but they consider it normal behavior.

    Example:

    We want to show all Incidents where a Sensitive flag is true and the user is the caller OR where a Sensitive flag is false and the user is a member of the currently assigned group.

Leave A Comment