Query Business Rules: A Definitive Guide
Contents
Introduction
This blog will take us on a deep dive into Query Business Rules. This guide is going to attempt to explain what Query Rules actually do, stepping through every part of the form and explaining what each part does (and doesn't) do.
A Before Query rule is defined in the Product Documentation as follows:
Before a query for a record or list of records is sent to the database.
Typically you should use query for before business rules.Product Documentation | How Business Rules Work
ServiceNow has depicted the order of Business Rule types as shown below:
Business Rule Processing Flow | ServiceNow Product Documentation
A picture tells a thousand words, but you might be reading this far but still wondering, what does a Before Query rule actually do and what is it used for?
Let's create our own Before Query Business Rule to really understand what is going on when one of these things runs.
Create a new Business Rule as shown below. Don't forget to check Advanced!
Let's add some code to the Script field in the Advanced Tab.
(function executeRule(current, previous /*null when async*/) {
gs.addInfoMessage("Before query: " + current.getEncodedQuery());
})(current, previous);As per the Product Documentation, this rule runs whenever a list or record is queried from the Database.
Before Query Info Message
From a List
I navigate to a List of Incidents:
You can see the filter and order is there in the Encoded Query. If you would like to know more about encoded queries, I'd suggest reading my blog Decoding Encoded Queries.
The thing that really sets this apart from other Business Rules is that it doesn't always run in the context of a record, but can run in the context of a list! The context is the query, which is a very important concept to wrap your head around when playing around with Query Rules.
From a Record
And I navigate to an Incident:
Notice that my query changes when I open an individual Incident. It has the sys_id of the record.
In Code
You can see that it is run before the query() function is called in ad-hoc code.
var gr = new GlideRecord('incident');
gr.addActiveQuery();
gs.log('Before query: ' + gr.getEncodedQuery());
gr.query();
Output
Before query: active=trueFrom the experiments above you can see that Before Query Business Rules can be used to modify the query before the records are returned.
Continuing with our example Business Rule, let's add a query to filter out all inactive records.
Take note on an Inactive Incident:
Let's add some code to the script field in our sample Business Rule:
(function executeRule(current, previous /*null when async*/) {
current.addQuery('active',true);
gs.addInfoMessage("Before query: " + current.getEncodedQuery());
})(current, previous);From a List
Let's open our list of Incidents again. This time we will not use any filters.
An 'active=true' check has now been added to all Incident queries. This means I can no longer see inactive Incidents, no matter how hard I try.
From a Record
Let's try to navigate to an inactive record INC0009004
We can't! The record no longer exists.
Couldn't we have just done this with Access Control Lists (ACLs)?
After adding Active=true to all row-level ACL's
Let's see what it would have looked like:
From a List
From a Record
The form loads, but I can only see the tabs. I suspect there are some Task level ACL's that are still providing visibility to the record.
Similar to ACL's, Before Query rules can be used to enforce security without the user knowing how many records are hidden from them. That is their primary use.
Now that we understand what Before Query rules do, let's have a play around with the different options on the form. This is where it gets a little weird.
Below shows all the aspects of Business Rules we will go through in this blog, marked in red. This guide is categorized into by these aspects.
When
So, what does the role of When play, if Query rules happen before after, before and display rules?
As you can see, they only ever run before a database update. If you change this value to after or async, your rule will not work as expected.
Interestingly, there are 1 async and 8 display Query rules OOTB!
Let's look at Display rule 'Warn date validation Issues'
It appears to be doing date validation on the Beginning and End of a CI outage.
We can see this on the following Outage record:
If I remove the 'Query' checkbox, it appears to make absolutely no difference what-so-ever.
The message still shows!
Comment below if you know of a Use Case for a Query rule that is not When=Before!
TLDR: When using Business Rules exclusively for Queries, set When to Before.
Order
Let's modify our Business Rule to print out the current order. Something like this:
(function executeRule(current, previous /*null when async*/) {
//current.addQuery('active',true);
gs.addInfoMessage("100 Before query: " + current.getEncodedQuery());
})(current, previous);Let's create a copy of our existing Business Rule and change the order to 200. Change the Info Message to 200 as well and use our old friend 'Insert and Stay' to make this quick.
After a quick test:
It looks like the Order is respected.
TLDR: Order works just as it does for any other type of Business Rule.
Filter Conditions
You might think that because Filter Conditions are on the form, that they might have an effect on your Business Rule.
What if we only wanted the query to run for records that meet a particular condition?
We need to remind ourself that these rules are run in the context of a Glide Record Query, not a record like every other Business Rules. Does it even make sense for a Before Query rule to have conditions? Maybe it will add that to the query?
But let's try it out!
Returning to a List of Incidents...
Adding filter conditions to a Query rule
From a List
From a Record
And our 200 order Query is officially broken.
It is a common misconception that the filter condition we set will be added to the current query.
Filter Conditions do not append to the current Query!
Again, this is because the record in context is a GlideRecord object that has not been initialised or queried.
In Code
What is the value of Active at this time? Let's use XPlore to find out.
Running this code:
var gr = new GlideRecord('incident');
gr.addActiveQuery();
gr.getValue('active');Output
nullOur Business Rule is running on the condition that null==true. No wonder it isn't working.
TLDR: Don't use Filter conditions.
Roles
Let's have a look at Roles. Surely this should work?
Add the role 'itil' to our new rule with an order of 200.
Be sure to remove any previous Filter Conditions. And to save the record, of course!
After impersonating an itil user, I can confirm that it works as expected.
TLDR: You can still use the 'Role conditions' field as you would in any other Business Rule.
Actions
Let's try adding some field values to the 'Set field values' tab.
Setting Field Values
From a List
By now you have probably worked out that setting field values doesn't make any sense, given the context we are in.
But what about from a record?
From a Record
The value has not changed.
In Code
It would be like writing this in code and actually expecting it to change the value of impact
var gr = new GlideRecord('incident');
gr.addQuery('impact','!=','1'); // To make sure we don't get any impact 1 tickets
gr.impact = 1;
gr.query();
gr.next()
gr.getValue('impact');Output
2Do I really need to talk about Abort action or Set Message? I'm only using addInfoMessage() for learning purposes here, I don't think it would have any practical use case.
TLDR: Actions do nothing in Query rules.
Advanced
The Advanced tab is where we write our Script to manipulate the query.
Let's say that we are given the following requirements:
- Hide all inactive Incidents
- UNLESS you are the 'Calle'r or 'Assigned to' user
Take note of how many Inactive records there are. There were 29 Inactive Incidents in our system.
Let's start by building this as a list filter so we can copy the encoded query.
We are going to have to use a New Query Or for this. If you would like to know more about encoded queries, check out my blog Decoding Encoded Queries.
Take note of the number of matching records (56). We should see this number after we apply our Query rule.
By right-clicking on the breadcrumbs and selecting 'Copy query' we are given the following encoded query:
active=true^NQcaller_idDYNAMIC90d1921e5f510100a9ad2572f2b477fe^ORassigned_toDYNAMIC90d1921e5f510100a9ad2572f2b477feWe can copy this query into our code like this
(function executeRule(current, previous /*null when async*/) {
var encQry = "active=true^NQcaller_idDYNAMIC90d1921e5f510100a9ad2572f2b477fe^ORassigned_toDYNAMIC90d1921e5f510100a9ad2572f2b477fe";
current.addEncodedQuery(encQry);
gs.addInfoMessage("200 Before query: " + current.getEncodedQuery());
})(current, previous);Save your changes and test it out.
Adding Queries
From a List
56 results. Perfect.
Let's not forgot though, Query rules are applied when just a single record is opened too.
Let's check out INC0009009.
From a Record
Wait...
What?
INC00090001!
Let's try INC0009005.
Now I am shown INC0009009!
Take a look at the query in full:
sys_id=ed92e8d173d023002728660c4cf6a7bc^active=true^NQcaller_idDYNAMICjavascript:gs.getUserID()^ORassigned_toDYNAMICjavascript:gs.getUserID()^EQ
Because we added a New Query, the active=true has been added to the sys_id check, THEN the new Query begins.
If the record we are trying to get to is not active, we will instead get served something from the second part of the query - a record where I am the caller or Assigned to User.
If you are going to add a New Query to a Query Rule, you must be very cautious and test both for the individual records and lists.
We can get this to work as desired by changing our query as follows:
active=true^ORcaller_idDYNAMIC90d1921e5f510100a9ad2572f2b477fe^ORassigned_toDYNAMIC90d1921e5f510100a9ad2572f2b477feUsing this in our Query rule
(function executeRule(current, previous /*null when async*/) {
var newQry = "^active=true^ORcaller_idDYNAMIC90d1921e5f510100a9ad2572f2b477fe^ORassigned_toDYNAMIC90d1921e5f510100a9ad2572f2b477fe^";
current.addEncodedQuery(newQry);
gs.addInfoMessage("200 Before query: " + current.getEncodedQuery());
})(current, previous);will now show the correct results when clicking through to individual records.
TLDR: Beware of adding New Queries (^NQ) in Query Rules!
Getting Around Before Query
Once you have set up your Before Query, is there ever a way to get around it?
Every time .query() is called Before Query rules jump into action. It will add it to whatever query you have.
Global Scope
It is possible to disable Business Rules with the following API:
current.setWorkflow(true);GlideRecord.setWorkflow() | ServiceNow Legacy API New York
We also know that Query rules are a little different from the other Business Rules, so will it work for this Use Case?
OOTB, the User table has a Query Business Rule that hides all inactive user records, unless you have admin or itil_admin roles.
Let's impersonate an ITIL user and try the following ad-hoc code:
function outputResults(gr) {
var resultCount = gr.getRowCount();
var encodedQuery = gr.getEncodedQuery();
gs.log('qry: ' + encodedQuery + " returns " + resultCount + " records.");
}
var grUser = new GlideRecord('sys_user');
grUser.query();
gs.log("Query rule takes affect:");
outputResults(grUser);
var grUser = new GlideRecord('sys_user');
grUser.setWorkflow(false);
grUser.query();
gs.log("After setWorkflow(false):");
outputResults(grUser);
Output below:
Query rule takes affect:
qry: active=true returns 688 records.
After setWorkflow(false)
qry: returns 689 records.You can see through the eyes of an admin that the results check out:
.setWorkflow(false) works to bypass Query Business Rules, just like all others.
Application Scopes
.setWorkflow(false) is not allowed in Scope.
As not to repeat myself, I have documented the workaround for bypassing Query Rules using REST in my other blog Not Allowed In Scope: The Only Workaround Guide You Need
TLDR: To bypass Query rules, use .setWorkflow(false) in Global or REST if you are in an Application Scope.
Closing Notes
Query rules are interesting creatures.
Below is a screenshot of the Query Business Rule form, with all the useless fields obfuscated:
When I started writing this blog, I felt that they didn't even belong under the category of Business Rules. After all, filter conditions and set values don't do anything and often break your rules completely. Order isn't really necessary as generally you only need one Query rule, however, I do think it is good practice to create one rule per role that you are applying the query to. They are very different from every other type of Business Rule and I thought they would have made more sense as being a special rule on the Table itself.
After exploring the many facets of Query Rules, it makes perfect sense for them to be Business Rules. I wasn't thinking about Query rules the wrong way - I was thinking about Business Rules the wrong way! They are not rules that are performed on a record, but rules applied to the GlideRecord object, which could be just one record - or a query of record(s). Then it starts to make more sense.
I've never used Query rules for any other purposes than 'Security by Obscurity' - hiding records from users, including the number of results users, do not have access to.
I feel it would just be easier if ServiceNow provided the option for ACL's to obscure the number of results the user can't see. As far as I can tell, that would eliminate the need for Query rules altogether.
Perhaps their days are numbered if my Idea to remove it gets approved for inclusion in a future release...
TLDR: Query rules are uniquely different from Insert, Update & Delete Business rules.
If you enjoyed this blog, please see here for my full series of ServiceKnow-How blogs!
8 Comments
