Contents
You probably already know just how easy it is to report on single tables in ServiceNow. You start by figuring out which data table you want to dive into (remembering the table name is often the hardest part)! Once you’ve got that sorted, choose the report type that fits your fancy – whether it’s a list report, chart report, or pivot report. Now comes the fun part: configuring the report. Pick the columns you want, set up filters, and decide how you want things sorted. And finally, add a personal touch by styling your report. Play around with colours, fonts, and headers to make it easy on the eyes. Make it your own! Single report tables can be set up in a breeze.
But things get more difficult when you only want to show data based on another table. What if you want to report on data from one table only where a reference exists on another? When you want to see all data from table X that has nothing in table Y – That is what I call reporting on nothing, or the absence of something. Let’s look at some examples:
- I want to see all priority 1 incidents with no related incidents.
- I want to know which users have never raised an incident.
- Give me all Certificate CI records with no related CI.
Good news! It might not be as hard as you think!
The Easy Way
Related List Conditions
Alright, let’s talk about related list conditions in ServiceNow reports. With related list conditions, you can dive deeper into your data and get even more specific with your reports. Think of it like filtering on steroids! You can set conditions based on related lists to narrow down your results and focus on the juicy details that matter most.
Whether it’s incidents, tasks, or any other related information, you can fine-tune your reports to capture exactly what you need. It’s like having a secret weapon in your reporting arsenal. So, get ready to level up your reporting game with related list conditions in ServiceNow and uncover hidden (literally) insights that will blow your mind!
You can do these with your GlideRecord queries as well. The syntax is a little funky, but it can really help optimise your code.
Which is the same as an Incident with:
When used in a GlideRecord Query:
var incidentGr = new GlideRecord('incident');
incidentGr.addEncodedQuery("priority=1^RLQUERYincident.parent_incident,=0^ENDRLQUERY");
incidentGr.query();
I want to know which users have never raised an incident
Which is the same as a user record with
When used in a GlideRecord Query:
var userGr = new GlideRecord('sys_user');
userGr.addEncodedQuery('active=true^RLQUERYincident.caller_id,=0^ENDRLQUERY');
userGr.query();
You can report on this as well using Related List Conditions… but you should probably be deferring to CDMB Health, Correctness, and Orphan records, or learning to use the CMDB Query Builder.
The Hard Way
When I was a ServiceNow Administrator, I didn’t know about Related List conditions. So I found another way to “report on nothing” that was a lot harder, but can still be useful. Sometimes, there might not be a related list condition to use. There is occasionally a need to join things that are not joined by a reference field. This is where Database Views come in handy.
Database Views
In ServiceNow, a database view is like a virtual table that combines data from multiple tables into a single, simplified “view” that you can easily access and work with. It’s like creating a custom snapshot of the data you need, without actually changing the underlying tables. With a database view, you can simplify complex queries, improve performance, and have a consolidated view of relevant information from different tables, making it easier to analyze and report on the data you care about. Think of it as a convenient way to see and work with data from different tables as if they were all in one place.
I want to know which users have never raised an incident
As a System Administrator, navigate to System Definition > Database Views and select New. Give your view a name and Save the record.
Create a View Table for the User table with a Variable prefix of su.
Create a View Table for Incident table. Set the variable prefix to inc, order to 200 and a Where clause of inc_caller_id=su_sys_id. This will join the two tables together. Don’t forget to add the Caller field via the View Fields related list below, otherwise ServiceNow will complain the field does not exist.
Navigate back to the Database view. We will need to add the Left join field to the related View Fields list by Personalizing the list columns. Set Left join to true for our Incident View Table.
In ServiceNow, a left join is like combining two sets of data, where you keep all the records from the left table (sys_user) and add any matching records from the right (incident) table, so you don’t lose any important information.
The first time we select Try it, things will look like an absolute mess. Let’s clean that up and only show the fields Name and Sys ID.
This will show us every user in the system, and an incident against them (or not – those will be our nothing!) Find a row with a Sys ID of (empty) and we now have our report of all users who have never raised an incident. If you have also done this the easy way via a report, you will notice the numbers should match.
The Easy or the Hard Way?
There you have it – two methods to report on nothing in ServiceNow. Which one should you use?
If you can relate your two tables through a related list, then that is the way to go. That is unless you want to see records that have a relationship and want to see them side by side – then Database views may be the best option.
We don’t always have the luxury of a clear relationship between the two. Database views give us more flexibility on how we join our data together and can be more efficient for reporting.
And of course, if all else fails – you can always export both tables into a spreadsheet and use a good old-fashioned VLOOKUP and IFNA.
