Distinct GlideRecord Query

//Distinct GlideRecord Query

Distinct GlideRecord Query

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');
By | 2018-07-09T15:00:02-07:00 January 19th, 2011|Categories: Scripting|Tags: , |12 Comments

About the Author:

12 Comments

  1. Garrett Griffin January 20, 2011 at 1:24 am - Reply

    Glad you found it useful Mark! Great write up on something that can be difficult to figure out when you need it. Now all we’ll have to do is Google it when we need it, and this blog post will show up right on top :-). Thanks!

  2. Angela Doss March 27, 2012 at 12:31 pm - Reply

    Hi,
    This is Angela Doss. I am new to Service Now. I am trying to figure out how to query tables using Gliderecords. I dont have knowledge of Gliderecords but I do have JavaScript experience.

    Any help would be greatly appreciated. Angela

  3. Dennis July 10, 2013 at 8:19 am - Reply

    Is there any way to use this functionality in a List Collector filter? I want to return a list of distinct options from the group member table that users can select (basically show me all groups of which I’m not already a member).

    • Mark Stanger July 10, 2013 at 8:59 am - Reply

      I don’t think so. I think what you could do in that case would be to query for (and display) the groups that the user was already a member of and then move them to the right side of the slushbucket. That would leave the remainder in the left side. It’s not a simple scripting task, but could be done. Here’s a separate post I wrote that might get you started.

      https://www.servicenowguru.com/scripting/client-scripts-scripting/move-list-collector-options/

      Another option would be to set the filter of the list collector to filter out the groups that the user is already a member of, similar to a reference qualifier. You would need to query the sys_user_grmember table and then return a list of sys_id values of the groups the user already belonged to. Then you could set the list collector filter as shown here.

      https://www.servicenowguru.com/scripting/client-scripts-scripting/changing-filter-list-collector-variable-client-script/

      • Dennis July 10, 2013 at 12:33 pm - Reply

        That got me a lot closer to what I’m after but I can’t seem to use a NOTIN filter on a list collector. It works just fine if I tell it to return all results where sys_idIN, that is, it shows me the groups of which I’m already a member. If I change that to sys_idNOTIN then the filter doesn’t show up on the list collector at all and it returns all results.

        If I use the technique you described to push the groups of which I’m already a member to the right side, then how will the recipient of the catalog task know which groups I was already in and which I’m requesting now? I guess they could check themselves but that seems like extra effort.

        • Mark Stanger July 10, 2013 at 12:39 pm - Reply

          I see what you’re saying. It’s a shame, but there is no such thing as a ‘NOTIN’ operator, or anything similar. I think what you’ll need to do is do several ‘!=’ operators with multiple ‘ANDs’ as separators. It will accomplish the same thing but will look a bit uglier. For this situation, you could probably just hide the filter completely and avoid the ugliness as well.

  4. Joe Walters July 29, 2014 at 11:56 pm - Reply

    Hi Mark, adding on to this discussion, do you know of a way via script to then count how many distinct values there are? For instance, in your example above there are 9 distinct values for the OS. I have need to count the number of distinct values and have an idea of how to do it with a GlideRecord query, but for speed/efficiency, I’d prefer to do it with GlideAggregate (if possible).
    Thanks!

    • Mark Stanger July 30, 2014 at 5:23 am - Reply

      There might be a more efficient way, but a simple way would be to use my code above, but then add to a counter variable inside of the while loop instead of printing out the results. Right before the while loop you would initialize your counter variable at zero then right after the while loop you could print out your results.

  5. Debbie December 18, 2014 at 5:50 am - Reply

    Hi, I am trying to use this to create a LOV for a database column. I want to add Class to a change request. I want to create a select on the distinct classes used in cmdb_ci. I haven’t succeeded. Any suggestions would be greatly appreciated. thanks, /D

  6. Martijn March 3, 2016 at 9:03 am - Reply

    I believe gs.sql is discontinued in Geneva.

  7. Kay August 15, 2019 at 4:12 pm - Reply

    This was just what I was looking for, thanks for sharing.

Leave A Comment