ServiceNow Admin 101: How to Do a UNION Query Using GlideRecord

[sabell2012]

NOTE: MY POSTINGS REFLECT MY OWN VIEWS AND DO NOT NECESSARILY REPRESENT THE VIEWS OF MY EMPLOYER, ACCENTURE.

DIFFICULTY LEVEL:    BEGINNER
Assumes good beginner level knowledge and/or familiarity of Scripting in ServiceNow.

---

In my previous article: ServiceNow Admin 101: You Too Can Do DISTINCT Queries Using GlideRecord, I demonstrated how to do a variety of DISTINCT queries using GlideRecord.  Here, I will show how you can do something similar to do a UNION query.

So, what is a UNION query? It is where you take data from two unrelated tables and bring them together into a single record set.

For example, to do this in SQL you might write something like this to obtain a list of Assigned To users from both the Asset and CMDB CI tables. Notice that these fields are not linked in any way. In other words the final list contains two different lists combined.   Both of these fields are reference fields to the sys_user table. I put in the "type" field to show what table the user name is coming from.

SELECT DISTINCT user1.name as name, 'ASSET' as type 
FROM alm_asset asset JOIN sys_user user1 ON asset.assigned_to = user1.sys_id 
WHERE asset.assigned_to IS NOT NULL 
GROUP BY user1.name
UNION
SELECT DISTINCT user2.name, 'CI' as type 
FROM cmdb_ci ci JOIN sys_user user2 ON ci.assigned_to = user2.sys_id   
WHERE ci.assigned_to IS NOT NULL 
GROUP BY user2.name

Now using Scripts — Background we have to translate all of this into GlideRecord script like this:

var userList = [];

// Asset names
var asset = new GlideRecord('alm_asset');
asset.addNotNullQuery('assigned_to');
asset.orderBy('assigned_to');
asset.setLimit(100);
asset.query();

while (asset.next()) {
     userList.push(asset.assigned_to.getDisplayValue());
}

// now DISTINCT the name list
userList = new ArrayUtil().unique(userList);

gs.info('---> asset length: ' + userList.length); // 24 for my instance
// just print off the first 10 for verification
for (var i=0; i<10; i++) {
    gs.info('---> assigned_to: ' + userList[i]);
}

// CMDB_CI names
var cmdbCI = new GlideRecord('cmdb_ci');
cmdbCI.addNotNullQuery('assigned_to');
cmdbCI.orderBy('assigned_to');
cmdbCI.setLimit(100);
cmdbCI.query();

while (cmdbCI.next()) {
     userList.push(cmdbCI.assigned_to.name + '');
}

// now re-sort (order by name) the array and DISTINCT the name list
userList = new ArrayUtil().unique(userList.sort());

gs.info('---> asset length: ' + userList.length); // 24 for my instance
// just print off the first 20 for verification
for (var i=0; i<20; i++) {
    gs.info('---> assigned_to: ' + userList[i]);
}

The result should look something like this:

• Part 1 is the first GlideRecord.
• Part 2 is the merging of the second GlideRecord with Part 1.
• I have highlighted some of the additions in Part 2.

Obviously, this is just one of many possible UNION queries. Some other possibilities are a combined list of:

• IP Phones, and Printers using the cmdb_ci_ip_phone and cmdb_ci_printer tables
• Business Systems and Applications using the cmdb_ci_systems and cmdb_ci_appl tables
• Routers and Switches using cmdb_ci_ip_router and cmdb_ci_ip_switch

And so on!

BTW, ArrayUtil has a "union" function where you can join two arrays together.

Enjoy!

Steven Bell.

If you find this article helps you, don't forget to log in and mark it as "Helpful"!

---

NOTE: ON APRIL 1, 2016 ACCENTURE COMPLETED THE ACQUISITION PROCESS OF CLOUDSHERPAS.   AT THAT TIME THE CLOUDSHERPAS BLOG SITES WERE DOWNED FOREVER.

THIS IS THE RE-PUBLICATION OF MY ARTICLE FROM December 11, 2014 ON THE CLOUDSHERPAS SERVICENOW SCRIPTING 101 BLOG.

---

Originally published on: <u+200e>05-23-2016 07:18 AM

I updated the code and brought the article into alignment with my new formatting standard.

---