How Do You Report on Nothing?

[Community Alums]

Reporting in ServiceNow is often quite straight forward. Select the table, identify the data from that table or related tables you want to report on, and filter the results with conditions. Easy, no?

In most cases, it is. But as I have discovered through extensive work with Asset Management and some reasonably common Asset-related reports, this is not always as straight forward as it may seem. These reports typically take some form of all items with none of a particular type of related items. This may seem a bit abstract, so let me provide a simple example of this from some questions posted in the community and other social outlets:

It is easy to report on the device or devices assigned to a particular user:

Because each device can only have one assigned user, it is even easy to report on devices that have no assigned user:

But because a single user may be assigned multiple devices, there is no easy report to shows you this user with nothing connected:

So the question becomes: How do you report on NOTHING?

This becomes a situation where you need something called a Database View. Database Views in ServiceNow allow you to combine multiple tables together into what appears to the system to be a single table for reporting purposes.

To address the situation above, here are the steps I took to create the view:

1. As System Administrator, navigate to System Definition > Database Views.
2. Create a New Database View as follows:
   Name: u_user_asset
   Label: User Asset
   Plural: User Assets
   Be descriptive with your label, as that is what you see when you create reports from the Database View.
3. Save the record to remain on the form to add Tables to the Database View.
4. Add a new table as follows:
   Table: User [sys_user]
   Variable prefix: user (This is used by the Database View to create the where clause to connect the tables and filter data)
   Order: 100
5. Add a second table as follows:
   Table: Asset [alm_asset] (You may choose to instead use a table that extends this or the Configuration Item table to just check for a subset of assets)
   Variable prefix: asset
   Order: 200
   Where clause: user_sys_id = asset_assigned_to
   Notice how the where clause uses the variable prefixes with an underscore to identify the table and field. This where clause matches the system ID from the user table to the Assigned to value in the asset table.

Almost there! Under Related Links in the Database View record, click Try It. This shows the list view for the new Database View. You may notice it contains a number of fields from the table and not all of them useful. Right-click any column header and select Personalize > List Layout to select the columns you want. These are the columns I selected:

• Name (This field is the full name of the user to make it easier to find the person)
• User ID (This could make it easier to find the user record, particularly if multiple people have the same name)
• Display name (This is the Display name of the asset and includes the Asset tag and the Model for the asset)

As the objective of this exercise was to find all users with no assigned assets, update the filter to show you all records where the Display name is blank.

Does it work??

Ok... full disclosure: I would be very surprised if it did.

There is one little thing left to take care of... if you think back to the where clause, it stated to return all records where the sys_id for the user matches the assigned_to for the asset. That is exactly what it does. It leaves out any records where there is not match!

In this case, you still want to see all the user records, even if they do not have an associated asset. To get all these user records, you use something called a left join.

Wait! Before you let the words "left join" scare you away, take a look at this:

Without a left join in place, you get the records that match only as pictured on the left. With the left join in place, you get all the user records, even if the sys_id does not match the assigned_to value for any asset.

Unfortunately, the Left join field is not exposed by default. Personalize the list layout for the Database Tables related list for the Database View and set Left join to true for the Asset table:

Try the Database View again and apply the filter for records where the Display name is empty. Do you have any records this time? At this point the Database View should do the trick.

I have several other reports that can use this same information, but what thoughts do you have first that you think require a Database View?