H

ere’s a cool tip that I’ve actually wanted to know how to do for a long time. I can’t take credit for it though. I got the idea from a post of a Service-now customer admin, Garrett Griffin. So, thanks to Garrett for the inspiration. I think this is worth sharing with a larger group of users.
I’ve had several questions (one a day or so ago) about how you can return a distinct list of attributes from items in a table in Service-now. This is very simple to do in SQL, but there’s no direct approach to doing this from the Service-now UI. In this post I’ll show you how you can get this type of information both visually, and via script in your Service-now environment.

First, the visual representation. This actually is very simple to get to, although it might not appear like you think it would. Here’s an example of how you could see a distinct list of operating systems used by servers in your ‘cmdb_ci_server’ table. The magic is in the ‘GROUPBY’ query argument in the URL as shown here…

https://demo.service-now.com/cmdb_ci_server_list.do?sysparm_query=GROUPBYos

Clicking this link will produce a result similar to the result shown in this screenshot. As you can see, the list grouping shows the distinct values, as well as a count of distinct values and counts of items underneath individual groupings.

Distinct OS Server Grouping


Of course, this information is often times more useful when you can gather it in a script. The following script example returns the same type of data as shown in the screenshot above. It uses a GlideAggregate query with a ‘groupBy’ argument that returns a distinct list of server operating systems.

var gr = new GlideAggregate('cmdb_ci_server'); //GlideAggregate query
gr.addAggregate('count'); //Count aggregate (only necessary for a count of items of each OS)
gr.orderByAggregate('count'); //Count aggregate ordering
gr.groupBy('os'); //Group aggregate by the 'os' field
gr.query();
while(gr.next()){
   var osCount = gr.getAggregate('count'); //Get the count of the OS group
   //Print the OS name and count of items with that OS
   gs.print('Distinct operating system: ' + gr.os + ': ' + osCount);
}



Running the script above from the ‘Scripts – Background’ module in your system will produce a result similar to this…

Distinct Servers By OS Script

Output CIs by Class

Here’s another method that I’ve used before to give me a quick and dirty output of CIs in a system by class. Use this with extreme caution! It can be run directly from the ‘Scripts – Background’ module.

gs.sql('SELECT sys_class_name,count(*) AS total FROM cmdb_ci GROUP BY sys_class_name ORDER BY total DESC');