The Good, the Bad and the Ugly: Lessons Learnt using Remote Tables for reporting

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

Lately, I’ve been diving deep into the world of Remote Tables (you can also check out my previous post: How to Report on Anything). When building internal reports—such as tracking Automated Test Framework (ATF) results—you might find yourself relying on remote tables, even when the data technically lives inside the same ServiceNow instance. Remote tables are undeniably powerful, but if not managed carefully, they can lead to some real pitfalls—what I like to call “The Bad” and “The Ugly.” As the saying goes, a smart person learns from their own mistakes, but a wise person learns from the mistakes of others. In this post, we’ll explore the journey of using remote tables through the familiar lens of The Good, The Bad, and The Ugly, helping you make them secure, efficient, and cost-effective for your internal reporting needs.

The Good: Security Done Right

Use GlideRecordSecure

Security is your “good” friend—always. Whether your report is internal or publicly consumed, never shortcut access controls. Using GlideRecordSecure instead of GlideRecord ensures that ACLs are respected *every time* you query data. This prevents accidental data leaks, like exposing sensitive ATF test results to unintended users, especially as code evolves or remote tables get reused.

The Bad: Performance Pitfalls to Avoid

Minimise the Number of Database Queries

Every database query adds latency and resource consumption. The bad? Looping through large datasets and running a new query per row. Here’s an example of what not to do:

The bad

// BAD EXAMPLE: Looping through each ATF test ID and querying separately

var atfTestIds = this._getATFTestIds();

for (var i = 0; i < atfTestIds.length; i++) {
var testId = atfTestIds[i];

var parameterSetGR = new GlideRecordSecure('sys_atf_parameter_set');
// Query per testId — costly and slow for many tests
parameterSetGR.addQuery('test.enable_parameterized_testing', 'true');
parameterSetGR.addQuery('test', testId);
parameterSetGR.orderByDesc('sys_updated_on');
parameterSetGR.query();

while (parameterSetGR.next()) {
    var matchingObj = this.atfTestHealthObjs.find(function(obj) {
        return obj.u_atf_test === testId;
    });
    if (!matchingObj) continue;

    var parameterSetLastUpdated = parameterSetGR.getValue('sys_updated_on');
    if (!matchingObj.u_last_updated || parameterSetLastUpdated > matchingObj.u_last_updated) {
        matchingObj.u_last_updated = parameterSetLastUpdated;
    }
}

}

The good

// GOOD EXAMPLE: Single efficient query for all test IDs

var parameterSetGR = new GlideRecordSecure('sys_atf_parameter_set');
parameterSetGR.addQuery('test.enable_parameterized_testing', 'true');
parameterSetGR.addQuery('test', 'IN', this._getATFTestIds());
parameterSetGR.orderByDesc('sys_updated_on');
parameterSetGR.query();

while (parameterSetGR.next()) {
var testId = parameterSetGR.getValue('test');
var matchingObj = this.atfTestHealthObjs.find(function(obj) {
return obj.u_atf_test === testId;
});
if (!matchingObj) continue;

var parameterSetLastUpdated = parameterSetGR.getValue('sys_updated_on');
if (!matchingObj.u_last_updated || parameterSetLastUpdated > matchingObj.u_last_updated) {
    matchingObj.u_last_updated = parameterSetLastUpdated;
}

}

Avoid Nested Queries

Nested GlideRecord calls inside another GlideRecord’s loop are a recipe for slow queries. For example:

The bad

// BAD EXAMPLE: Nested queries inside a loop

execReport: function(encQry) {
this._getActiveATFTests(encQry);
},

_getActiveATFTests: function(encQry) {
var atfTestGR = new GlideRecordSecure('sys_atf_test');
if (encQry) {
atfTestGR.addEncodedQuery(encQry);
}
atfTestGR.addEncodedQuery('active=trueRLQUERYsys_atf_step.test,>=1ENDRLQUERY');
atfTestGR.orderByDesc('sys_updated_on');
atfTestGR.query();

var objs = [];
while (atfTestGR.next()) {
    var obj = {
        u_atf_test: atfTestGR.getValue('sys_id'),
        // other properties...
    };

    // BAD PRACTICE: For each test, query sys_atf_test_result individually
    var testResultGR = new GlideRecordSecure('sys_atf_test_result');
    testResultGR.addQuery('test', obj.u_atf_test);
    testResultGR.addQuery('status', 'IN', 'success,failure,error,success_with_warnings');
    testResultGR.orderBy('start_time');
    testResultGR.addExtraField('first_failing_step.summary');
    testResultGR.query();

    while (testResultGR.next()) {
        // process results...
    }

    objs.push(obj);
}
this.atfTestHealthObjs = objs;
return objs;

}

The good

// GOOD EXAMPLE: Separate queries for clarity and performance

execReport: function(encQry) {
this._getActiveATFTests(encQry);
this._getLastExecutions();
},

_getATFTestIds: function() {
return this.atfTestHealthObjs.map(function(obj) {
return obj.u_atf_test;
}).join(',');
},

var objs = [];
while (atfTestGR.next()) {
    objs.push({
        u_atf_test: atfTestGR.getValue('sys_id'),
        // other properties...
    });
}
this.atfTestHealthObjs = objs;
return objs;

_getLastExecutions: function() {
var testResultGR = new GlideRecordSecure('sys_atf_test_result');
testResultGR.addQuery('test', 'IN', this._getATFTestIds());
testResultGR.addQuery('status', 'IN', 'success,failure,error,success_with_warnings');
testResultGR.orderBy('start_time');
testResultGR.addExtraField('first_failing_step.summary');
testResultGR.query();

while (testResultGR.next()) {
    // process results...
}

}

Avoid Unnecessary Lookups

Dot-walking (e.g., gr.parent.test_suite.name) can trigger hidden lookups in ServiceNow, causing extra DB queries behind the scenes. Instead, use [addExtraField()](https://developer.servicenow.com/dev.do#!/reference/api/xanadu/server%5Flegacy/c%5FGlideRecordAPI#GlideRecord-addExtraField%5FS?navFilter=addextrafield) to tell GlideRecord to fetch related fields in the same query.

The bad

// BAD EXAMPLE: Dot-walking inside a loop triggers many hidden lookups

var grTestResult = new GlideRecordSecure('sys_atf_test_result');
grTestResult.addQuery('test_suite', '!=', '');
grTestResult.query();

while (grTestResult.next()) {
var testSuiteName = grTestResult.parent.test_suite.name + ''; // hidden lookup per record!!
gs.info('Test Suite Name (BAD): ' + testSuiteName);
}

The good

// GOOD EXAMPLE: Pre-fetch related fields with withField()

var grTestResult = new GlideRecordSecure('sys_atf_test_result');
grTestResult.addQuery('test_suite', '!=', '');
grTestResult.addExtraField('parent.test_suite.name'); // Pre-load the field data
grTestResult.query();

while (grTestResult.next()) {
var testSuiteName = grTestResult.getValue('parent.test_suite.name');
gs.info('Test Suite Name (GOOD): ' + testSuiteName);
}

Please not that [addExtraField()](https://developer.servicenow.com/dev.do#!/reference/api/xanadu/server%5Flegacy/c%5FGlideRecordAPI#GlideRecord-addExtraField%5FS?navFilter=addextrafield) method is not available in application scopes.

Get Only the Data You Need

Remote tables support query parameters — use encoded queries or filter objects (like v_query) to fetch only the data your report requires. This avoids pulling all historical ATF test runs when you may need only recent or filtered results.

Example query with refined filters

// Example refining query with existing v_query

var encQry = v_query.getEncodedQuery();
var atfTestHealthUtil = new ATFTestHealthUtil();

// Parse and optimize query parts for ATF test table
var atfTestEncQry = atfTestHealthUtil._parseEncodedQuery(encQry);
var atfTestSysIdsObj = atfTestHealthUtil.execReport(atfTestEncQry);

atfTestSysIdsObj.forEach(function addRow(obj) {
v_table.addRow(obj);
});

Leverage GlideAggregate for Summaries

Need to count or summarize test results (e.g., “When was this test last executed?”)? GlideAggregate queries are a scalable way to let the database do the heavy lifting, instead of iterating over all records in script.

The bad

// BAD EXAMPLE: Running a query per test for max update date

var atfTestIds = this._getATFTestIds();

for (var i = 0; i < atfTestIds.length; i++) {
var testId = atfTestIds[i];

var grStep = new GlideRecord('sys_atf_step');
grStep.addQuery('test', testId);
grStep.orderByDesc('sys_updated_on');
grStep.setLimit(1);
grStep.query();

while (grStep.next()) {
    gs.info('Test ID: ' + testId + ' Last Updated: ' + grStep.sys_updated_on);
}

}

The good

// GOOD EXAMPLE: Use GlideAggregate to get max updated time in one query

var ga = new GlideAggregate('sys_atf_step');
ga.addQuery('test', 'IN', this._getATFTestIds());
ga.groupBy('test');
ga.addAggregate('MAX', 'sys_updated_on');
ga.query();

while (ga.next()) {
var testId = ga.getValue('test');
var maxUpdated = ga.getAggregate('MAX', 'sys_updated_on');

var matchingObj = this.atfTestHealthObjs.find(function(obj) {
    return obj.u_atf_test === testId;
});

if (matchingObj) {
    matchingObj.u_last_updated = maxUpdated;
}

}

The Ugly: Cost and Support Burdens

Here lies the “ugly” — the hidden and sometimes obvious costs lurking in remote table use.

Intangible Costs

Slow reports frustrate users and waste valuable time. If your remote table queries or scripts are unclear or undocumented, future developers will spend hours untangling your logic instead of delivering value.

Tangible Costs

When remote tables interface via REST or IntegrationHub, every call may consume transaction credits, impacting your license. Make sure you are aware of these costs and be mindful how often you query (use the right caching strategy!) Additionally, exposing data across applications without proper entitlement checks can expose you to compliance risks. Always verify record scope and user permissions!

Conclusion

The “Good” of remote tables is solid ServiceNow best practice applied respectfully—prioritize security, minimize database hits by batching and aggregating queries, and cache wisely. The “Bad” is usually performance anti-patterns like nested queries and unnecessary lookups, which you can avoid. The “Ugly” is the cost of neglect—both in user experience and support burden, and hard license constraints that can be expensive and compliance-risky if ignored.In essence, most remote table best practices are solid ServiceNow server-side coding fundamentals applied with care. If you haven’t yet, dive into the ServiceNow server scripting best practices—they’re your guide to taming remote tables effectively.

The post The Good, the Bad and the Ugly: Lessons Learnt using Remote Tables for reporting appeared first on The SN Nerd.