Counting with GlideAggregate

Tutorial on using GlideAggregate to count.

#GlideAggregate , #Server Side Scripting , #Scripting , #Count

Ben Sweetser

10 minute read

We are experimenting with new training content delivery methods. This tutorial blog post is one of those experiments. We are very interested in your feedback. Please let us know what you think about this format and the content in the comments below.

Introduction

As my youngest child was learning to count, he would always miss number 14. “…eleven, twelve, thirteen, fifteen, sixteen…” Now he is six years old and counts by ones like a pro. We read a book the other night about an elementary school class that was counting pumpkin seeds. One group decided to count by twos, another group by fives, and the last group decided to count by tens. When it was time for the groups to tally up their pumpkin seeds by counting, one group certainly had an easier time than the others. The group that counted by tens was able to get their result more efficiently.

Just as you can count physical objects differently, you can count objects in a database differently. And just as some methods of counting physical objects are more efficient than others, some methods for counting objects in a database are more efficient.

The GlideRecord class includes a method called getRowCount() that counts the number of rows in a query result. As each record is loaded, ServiceNow updates the count. A more efficient way to get this same information, though, is through data aggregation with the GlideAggregate class. GlideAggregate gets the database to count and returns the result.

Whatis.com defines data aggregation as any process in which information is gathered and expressed in a summary form, for purposes such as statistical analysis. A common aggregation purpose is to get more information about particular groups based on specific variables such as age, profession, or income.

The GlideAggregate class allows developers to create data aggregation queries. The topics in this post build on concepts in the GlideRecord concepts in the Server-side Scripting module.

In this post, you create Business Rules that use GlideAggregate to count records.

  • Guided: Create a Business Rule for new Incidents to count active Incidents from the same caller and post an information message with the count of the caller’s active Incidents. You begin with a simple count and explore ways to get the desired aggregate.
  • Challenge:
    • Option 1: Create a similar Business Rule for new NeedIt requests.
    • Option 2: Create a similar Business Rule for new Change Requests.

NOTE: This exercise uses demo data that may vary in your instance.

Create an Incident Business Rule to Count Incidents

  1. Log in to your ServiceNow instance as a System Administrator.
  2. View Incidents where Rick Berzle is the Caller.
    1. Use the Application Navigator to open Incident > Open.
    2. Change Go to to Caller and set the search value to *Berzle. Make note of how many records have Rick Berzle as the Caller.
  3. Create a Business Rule.

    1. Right-click any column header and select the Configure > Business Rules menu item.
    2. Click the New button.
    3. Configure the Business Rule.
      1. Name: Count Open Incidents by Same Caller
      2. Advanced: selected (checked)
    4. Configure the When to run section.
      1. When: after
      2. Insert: selected (checked)
    5. Configure the Advanced section.

      1. Replace the contents of the script field with this script.

        
            (function executeRule(current, previous /*null when async*/) {
        
                // Create an aggregate object  
                var aggIncident = new GlideAggregate('incident');
        
                // Add aggregate  
                aggIncident.addAggregate('COUNT');
        
                // Execute query 
                aggIncident.query();
        
                // Process returned records
                while(aggIncident.next()){
                    // Create a message to display the count of the Caller's active Incidents
                    var msg = current.caller_id.name + " has " + aggIncident.getAggregate('COUNT') + " active incidents." ;
                    gs.addInfoMessage(msg);
                }
        
            })(current, previous);
        
        
    6. Click the Submit button.

  4. Test the Business Rule.

    1. Create an Incident with Rick Berzle as the Caller.
      1. Use the Application Navigator to open Incident > Create New.
      2. Configure the Incident.
        1. Caller: Rick Berzle
        2. Short description: Received phishing email
      3. Click the Submit button. An information message shows an incident count.

The Info Message shows Rick Berzle as having 70 active Incidents.

QUESTION: Is the count for Rick Berzle’s active incidents correct? Why or why not? If you are not sure, scroll to the Answers section at the bottom of this page.

QUESTION: Does the count of incidents include the incident just submitted? If you are not sure, scroll to the Answers section at the bottom of this page.

Update the Business Rule to Return Active Incidents

GlideAggregate extends GlideRecord. You can use the addQuery() method to filter the incidents returned in the query.

  1. Update the Business Rule to only count active incidents.

    1. Open the Count Open Incidents by Same Caller Business Rule for editing.
    2. Add the query parameters above the // Add Aggregate comment.

      
              // Add query parameters  
              aggIncident.addQuery('active','=',true);
      
      
  2. Test the Business Rule again.

    1. Create an Incident with Rick Berzle as the Caller.
      1. Use the Application Navigator to open Incident > Create New.
      2. Configure the Incident.
        1. Caller: Rick Berzle
        2. Short description: Email not received
      3. Click the Submit button. An information message lists active Incidents with Rick Berzle as the Caller.

The Info Message shows Rick Berzle as having 48 active Incidents.

Update the Business Rule to Count by Caller

The addAggregate('COUNT') in the script counts all records. The addAggregate method accepts a second parameter to count by a specific field. In this section, you update the script to count by caller_id. Every addAggregate has a corresponding getAggregate to get the value. You also update the getAggregate method to return the count results.

  1. Update the Business Rule to only count Rick Berzle’s active incidents.

    1. Open the Count Open Incidents by Same Caller Business Rule for editing.
    2. Change the aggIncident.addAggregate('COUNT'); line to count by Caller.

      
          aggIncident.addAggregate('COUNT','caller_id');
      
      
    3. Update the getAggregate method in the script to return the count by Caller and update current.caller_id.name to return the caller from the aggregate.

      
          var msg = aggIncident.caller_id.name + " has " + aggIncident.getAggregate('COUNT','caller_id') + " active incidents." ;
          gs.addInfoMessage(msg);
      
      
  2. Test the Business Rule again.

    1. Create an Incident with Rick Berzle as the Caller.
      1. Use the Application Navigator to open Incident > Create New.
      2. Configure the Incident.
        1. Caller: Rick Berzle
        2. Short description: Printing issue
      3. Click the Submit button. An information message lists active Incidents with Rick Berzle as the Caller.

QUESTION: Does the information message show Rick Berzle’s active incidents now? If you are not sure, scroll to the Answers section at the bottom of this page.

Return Only the Current Caller’s Active Incident Count

In this section, you review two possible ways to limit the information message to show only the current Caller’s active incident count. Select a script and implement that script. After you decide which script to use, replace the script in the Count Open Incidents by Same Caller Business Rule with the script for the strategy selected.

STRATEGY 1: Add an if statement to only write the message if the aggregate.caller_id is the same as current.caller_id.


    (function executeRule(current, previous /*null when async*/) {

    // Create an aggregate object  
    var aggIncident = new GlideAggregate('incident');

    // Add query parameters
    aggIncident.addQuery('active','=',true);

    // Add Aggregate  
    aggIncident.addAggregate('COUNT','caller_id');

    // Execute query 
    aggIncident.query();

    // Process returned records
    while(aggIncident.next()){
        // Create a message to display the count of the Caller's active Incidents
        if (aggIncident.caller_id == current.caller_id){
            var msg = aggIncident.caller_id.name + " has " + aggIncident.getAggregate('COUNT','caller_id') + " active incidents." ;
            gs.addInfoMessage(msg);
        }
    }

    })(current, previous);

STRATEGY 2: Add a query to only return incidents that have the same Caller as the current incident.


    (function executeRule(current, previous /*null when async*/) {

    // Create an aggregate object  
    var aggIncident = new GlideAggregate('incident');

    // Add query parameters
    aggIncident.addQuery('active','=',true);
    aggIncident.addQuery('caller_id','=',current.caller_id);

    // Add Aggregate  
    aggIncident.addAggregate('COUNT','caller_id');

    // Execute query 
    aggIncident.query();

    // Process returned records
    while(aggIncident.next()){
        // Create a message to display the count of the Caller's active Incidents
            var msg = aggIncident.caller_id.name + " has " + aggIncident.getAggregate('COUNT','caller_id') + " active incidents." ;
            gs.addInfoMessage(msg);
    }

    })(current, previous);

QUESTION: Which strategy is more efficient? Why? If you are not sure, scroll to the Answers section at the bottom of this page.

Did you do the hands-on exercise in this blog? Click here to let us know!

Challenge - Option 1 - Create a NeedIt Business Rule

Create a Business Rule for the NeedIt application from the Developer Portal training that counts open NeedIt requests for the user in the Requested for field. Show the count in an information message.

Starting tips:

If you need help, see the Create a NeedIt Business Rule section in the answers at the bottom of this post for a sample script.

Challenge - Option 2 - Create a Change Business Rule

Create a Business Rule for Changes that counts changes opened for the same Configuration Item. Show the count in an information message.

Starting tips:

  • What is the name of the Change table?
  • What is the name of the Configuration Item field?

If you need help, see the Create a Change Business Rule section in the answers at the bottom of this post for a sample script.

Closing Thoughts

When you need to count records, use the most efficient way available. GlideAggregate counts more efficiently than getRowCount(). getRowCount() should not be used in production due to its ineffecient and resource intensive counting process. When you use GlideAggregate, make your queries efficient so you do not count records you do not need to count. Be sure to come back to the Developer Blog for more hands-on exercises around GlideRecord and GlideAggregate.

Answers

Question: Is the count for Rick Berzle’s active incidents correct? Why or why not?

Answer: The count shows ALL incidents, not just the count of Rick Berzle’s active incidents. No query parameters were added to filter the count to just active incidents where Rick Berzle is the caller.


Question: Does the count of incidents include the incident just submitted?

Answer: The count of incidents includes the incident just submitted because the Business Rule is set to run after the record is submitted. If the Business Rule was configured to run before, the count would not include the incident just submitted because the records are counted before the new incident is committed.


Question: Does the information message show Rick Berzle’s active incidents now?

Answer: Yes, the information messages include Rick Berzle’s active incident count. The Business Rule returns an information message for each user with that user’s number of active incidents, including Rick’s.

The Business Rule creates an information message for each caller with that caller's number of incidents.


Question: Which strategy is more efficient? Why?

Answer: Strategy 2, which queries for records where the Caller matches the current Caller is more efficient. The query limits the records to count with the aggregate. Strategy 1 returns and counts records unnecessarily.


Create a NeedIt Business Rule: Here is an example script for a Business Rule to get the count NeedIt requests for a user and write the count to an information message.


    (function executeRule(current, previous /*null when async*/) {

        // Create an object to store rows from a table
        var aggNeedIt = new GlideAggregate('x_58872_needit_needit');

        // Build query
        aggNeedIt.addQuery('active','=',true);
        aggNeedIt.addQuery('u_requested_for','=',current.u_requested_for);

        // Add aggregate  
        aggNeedIt.addAggregate('COUNT');
        
        // Execute query  
        aggNeedIt.query();

        // Process aggregate
        while(aggNeedIt.next()){
            // add a message with the count
            var msg = "User has a total of " + aggNeedIt.getAggregate('COUNT') + " active NeedIt requests.";
            gs.addInfoMessage(msg);
        }

    })(current, previous);


Create a Change Business Rule: Here is an example script for a Business Rule to get the count of Changes for a Configuration Item and write the count to an information message.


    (function executeRule(current, previous /*null when async*/) {

        // Create an object to store rows from a table
        var aggChange = new GlideAggregate('change_request');

        // Build query
        aggChange.addQuery('active','=',true);
        aggChange.addQuery('cmdb_ci','=',current.cmdb_ci);

        // Add aggregate  
        aggChange.addAggregate('COUNT');

        // Execute query  
        aggChange.query();

        // Process aggregate
        while(aggChange.next()){
            // add a message with the count
            var msg = "Configuration Item has a total of " + aggChange.getAggregate('COUNT') + " active Change requests.";
            gs.addInfoMessage(msg);
        }

    })(current, previous);


Comments