How to Report on Anything - The SN Nerd

This article is relevant for the following ServiceNow releases: washington dc, Xanadu, Yokohama.

Reporting Roadblocks

Have you ever tried creating a report but hit one or more of the following roadblocks?

You’ve tried a database view, but you can’t get the results you want
You can’t get all the data you want into a single row
You need to aggregate multiple results together
You need to get the first or last result where something occurred
Don’t have access to Performance Analytics
You want to report on the absence of something (see How to Report on Nothing)
The table is a system table and can’t be reported on*

*You can make these available by adding the table name to the system property glide.ui.permitted_tables)

In trying to create a report recently, I was hit with all of the above!

This Report Tested Me

I wanted to create a dashboard for reporting on ATF Test Health. For any given ATF Test, I wanted to know

When was it last updated (including any test step or parameter)
When was it last executed
When did it last pass
When did it last fail
When did it first pass

There is a feature we are all sleeping on called Remote tables that can help solve this problem. This feature won’t be much help if are not an admin, but hey – next time your System Admin says a report can’t be done, maybe you can suggest this to them!

System Admins, please read on!

What Are Remote Tables?

Remote Tables are ServiceNow’s answer to working with data that lives outside your instance. Their primary mission is to let you connect to external sources—think APIs, other ServiceNow instances, or third-party platforms—and pull in data on-demand, without storing it permanently in your local database. While that may be it’s primary purpose (it is in the name) you can use Remote Tables to report on anything, even data that’s already inside your own instance, giving you ultimate flexibility.

Virtual Tables, Real Power

Creating a Remote Table is just like spinning up a regular table in ServiceNow. You define the schema, set up your columns, and you’re ready to go. The difference? Instead of storing records, you write a script that fetches the data whenever the table is queried—making it a true virtual table. This script can call external APIs, other ServiceNow instances, or even query your local instance for dynamic reporting needs. If this feature was called Virtual Tables, I think we would see a lot more use.

Scripting and GenAI: The Dynamic Duo

The magic happens in the scripting. You define exactly how the data is fetched and shaped, whether it’s pulling from a REST endpoint or running a GlideRecord query internally. And with the plethora of GenAI code generation tools now available, creating these scripts is easier than ever—even if you’re not a scripting wizard.

How I Used Remote Tables

Create a New Table

This process is identical to creating a real table, except this one is virtual. Navigate to System Definition > Remote Tables > Tables and create as per usual, except this one won’t be persistent in the database – it will only live in memory. The data stored in these fields will be persistent in memory based on the Remote Table cache settings. I created a Reference field for the key subject of the report, the ATF Test, and Date/Time fields to store my timestamps.

Dictionary definitions on a virtual remote table

But how does this data get populated?

Populate via Definitions

Create a new definition by navigating to System Definition > Remote Tables > Definitions. You will see here that many OOB tables use this functionality, such as the Popular Items widget in Service Portal. This use case may open up some of the possibilities of Remote tables to you.

The data behind Popular Items

The Popular Items Service Portal Widget that is powered by Remote Tables

Script Away!

Create a new Definition and you will be presented with a script field with the API required will be added in the script comments. You can also check out other OOB definitions as examples to get you started.

(function executeQuery(v_table, v_query) {
// Main API:
// v_table.addRow({ ... }) - adds a row to the result set

// There are also query helper methods
//    v_query.getEncodedQuery() - returns encoded querystring
//    v_query.getCondition(field) - returns encoded querystring for the given field (includes field name, operator, and value)
//    v_query.getParameter(field) - returns parameter for the given field (only includes value for equality conditions)
//    v_query.isGet() - returns whether the query is a single get by sys_id
//    v_query.getSysId() - returns parameter for sys_id field
//    v_query.isTextSearch() - returns whether the query contains a text query parameter
//    v_query.getTextSearch() - returns text search query parameter (internal field name 123TEXTQUERY321)
//    v_query.getFirstRowWanted() - returns the first row to include
//    v_query.getLastRowWanted() - returns the last row to include

// Note: You must define sys_id for each row so that forms and lists for this table work properly

// Your code goes here
// v_table.addRow({...})

})(v_table, v_query);

In essence, you create a JSON object with key-value pairs that match the field names of your table. You populate these with values, and then that object as row to your table. Generate a sys_id for each row to ensure it functions correctly.

var obj = {
u_atf_test: atfTestGR.getUniqueValue(),
u_last_updated: atfTestGR.getValue('sys_updated_on'),
sys_id: gs.generateGUID(),
u_is_certified: false,
u_is_scheduled: false
}

I abstracted my code into a Script Include where I used VSCode and GitHub Copilot (read how to do this in my blog How to use GenAI for SN Code Development for free), using a combination of GlideRecordSecure and GlideAggregate to populate my virtual table.

From this you can see the results to my original requirements

When was it last updated
When was it last executed
When was it last passed
When did it last fail
When was it’s first pass

List of results from a Remote Table

There are several gotchas and best practices to look out for when scripting a Remote Table, which I will look to cover in another blog.

Create a Report

The whole aim of this was to create a report! The last step is to create a beautiful report in Platform Analytics:

Platform Analytics Dashboard powered by Remote Tables

You can see I ended up adding a lot more reporting metrics to the final product. This will be available with the upcoming Regress 3.0 release (see https://regress-atf-recorder.com/)

TL;DR

If you’ve hit reporting roadblocks in ServiceNow—like complex aggregations, missing Performance Analytics, or the need to report on system tables—Remote Tables are your secret weapon. Originally built for external data, Remote Tables let you create virtual tables with scripted data sources, making it possible to report on anything, even tricky data inside your own instance. You define the schema just like a normal table, script how the data is fetched (now easier than ever with GenAI tools), and use it in reports and dashboards. No more data imports, no more limitations—just flexible, powerful reporting.

The post How to Report on Anything appeared first on The SN Nerd.