Please use docs.servicenow.com for the latest documentation.

This site is for reference purposes only and may not be accurate for the latest ServiceNow version

GlideAggregate

From Wiki Archive
Jump to: navigation, search
Note
Note: This article applies to Fuji. For more current information, see GlideAggregate at http://docs.servicenow.com

The ServiceNow Wiki is no longer being updated. Please refer to http://docs.servicenow.com for the latest product documentation.


{{Topic|Scripting Glide and Jelly


Note
Note: This functionality requires a knowledge of JavaScript.

Overview

The GlideAggregate class is an extension of GlideRecord and allows database aggregation (COUNT, SUM, MIN, MAX, AVG) queries to be done. This can be helpful in creating customized reports or in calculations for calculated fields. GlideAggregation is an extension of GlideRecord and its use is probably best shown through a series of examples.

Note
Note: The global Glide APIs are not available in scoped scripts. There are scoped Glide APIs for use in scoped scripts. The scoped Glide APIs provide a subset of the global Glide API methods. For information on scoped applications see Application Scope, scripting in scoped applications, and the list of scoped APIs.


Examples

Here is an example that simply gets a count of the number of records in a table: <source lang="javascript"> var count = new GlideAggregate('incident'); count.addAggregate('COUNT'); count.query(); var incidents = 0; if (count.next())

  incidents = count.getAggregate('COUNT');

</source> There is no query associated with the above example but if you wanted to get a count of the incidents that were open then you simply add a query just as is done with GlideRecord. Here is an example to get a count of the number of active incidents. <source lang="javascript"> var count = new GlideAggregate('incident'); count.addQuery('active', 'true'); count.addAggregate('COUNT'); count.query(); var incidents = 0; if (count.next())

  incidents = count.getAggregate('COUNT');

</source> To get a count of all of the open incidents by category the code is:

<source lang="javascript"> var count = new GlideAggregate('incident'); count.addQuery('active', 'true'); count.addAggregate('COUNT', 'category'); count.query(); while (count.next()) {

  var category = count.category;
  var categoryCount = count.getAggregate('COUNT', 'category');
  gs.log("The are currently " + categoryCount + " incidents with a category of " + category);

} </source>

The output is:

       *** Script: The are currently 1.0 incidents with a category of Data  
       *** Script: The are currently 11.0 incidents with a category of Enhancement
       *** Script: The are currently 1.0 incidents with a category of Implementation
       *** Script: The are currently 197.0 incidents with a category of inquiry
       *** Script: The are currently 13.0 incidents with a category of Issue
       *** Script: The are currently 1.0 incidents with a category of 
       *** Script: The are currently 47.0 incidents with a category of request

Below is an example that shows that you can ask for multiple aggregations. We are asking to see how many times records have been modified and we want the MIN, MAX, and AVG values.

<source lang="javascript"> var count = new GlideAggregate('incident'); count.addAggregate('MIN', 'sys_mod_count'); count.addAggregate('MAX', 'sys_mod_count'); count.addAggregate('AVG', 'sys_mod_count'); count.groupBy('category'); count.query(); while (count.next()) {

  var min = count.getAggregate('MIN', 'sys_mod_count');
  var max = count.getAggregate('MAX', 'sys_mod_count');
  var avg = count.getAggregate('AVG', 'sys_mod_count');
  var category = count.category.getDisplayValue();
  gs.log(category + " Update counts: MIN = " + min + " MAX = " + max + " AVG = " + avg);

} </source>

The output is:

       *** Script: Data Import Update counts: MIN = 4.0 MAX = 21.0 AVG = 9.3333
       *** Script: Enhancement Update counts: MIN = 1.0 MAX = 44.0 AVG = 9.6711
       *** Script: Implementation Update counts: MIN = 4.0 MAX = 8.0 AVG = 6.0
       *** Script: inquiry Update counts: MIN = 0.0 MAX = 60.0 AVG = 5.9715
       *** Script: Inquiry / Help Update counts: MIN = 1.0 MAX = 3.0 AVG = 2.0
       *** Script: Issue Update counts: MIN = 0.0 MAX = 63.0 AVG = 14.9459
       *** Script: Monitor Update counts: MIN = 0.0 MAX = 63.0 AVG = 3.6561
       *** Script: request Update counts: MIN = 0.0 MAX = 53.0 AVG = 5.0987

Here is a somewhat more complex example that shows how to compare activity from one month to the next.

<source lang="javascript"> var agg = new GlideAggregate('incident'); agg.addAggregate('count','category'); agg.orderByAggregate('count', 'category'); agg.orderBy('category'); agg.addQuery('opened_at', '>=', 'javascript:gs.monthsAgoStart(2)'); agg.addQuery('opened_at', '<=', 'javascript:gs.monthsAgoEnd(2)'); agg.query(); while (agg.next()) {

 var category = agg.category;
 var count = agg.getAggregate('count','category');
 var query = agg.getQuery();  
 var agg2 = new GlideAggregate('incident');   
 agg2.addAggregate('count','category');
 agg2.orderByAggregate('count', 'category');
 agg2.orderBy('category');
 agg2.addQuery('opened_at', '>=', 'javascript:gs.monthsAgoStart(3)');
 agg2.addQuery('opened_at', '<=', 'javascript:gs.monthsAgoEnd(3)');
 agg2.addEncodedQuery(query);
 agg2.query();
 var last = "";
 while (agg2.next()) {
    last = agg2.getAggregate('count','category');      
 }
 gs.log(category + ": Last month:" + count + " Previous Month:" + last);

} </source>

The output is:

 *** Script: Monitor: Last month:6866.0 Previous Month:4468.0
 *** Script: inquiry: Last month:142.0 Previous Month:177.0
 *** Script: request: Last month:105.0 Previous Month:26.0
 *** Script: Issue: Last month:8.0 Previous Month:7.0
 *** Script: Enhancement: Last month:5.0 Previous Month:5.0
 *** Script: Implementation: Last month:1.0 Previous Month:0

Here is an example to get distinct count of a field on a group query.

<source lang="javascript"> var agg = new GlideAggregate('incident'); agg.addAggregate('count'); agg.addAggregate('count(distinct','category'); agg.addQuery('opened_at', '>=', 'javascript:gs.monthsAgoStart(2)'); agg.addQuery('opened_at', '<=', 'javascript:gs.monthsAgoEnd(2)'); // agg.groupBy('priority'); agg.query(); while (agg.next()) { // Expected count of incidents and count of categories within each priority value (group) gs.info('Incidents in priority ' + agg.priority + ' = ' + agg.getAggregate('count') +

           ' (' + agg.getAggregate('count(distinct','category') + ' categories)');

} </source> The output is:

*** Script: Incidents in priority 1 = 13 (3 categories)
*** Script: Incidents in priority 2 = 10 (5 categories)
*** Script: Incidents in priority 3 = 5 (3 categories)
*** Script: Incidents in priority 4 = 22 (6 categories)

Method Summary

Method Summary
Return Value Details
void addEncodedQuery(String query)
Adds a query to the Aggregate. Adds an encoded query to the other queries that may have been set for this aggregate.
void addHaving(String name, String operator, String value)
Adds a "having" element to the aggregate e.g. select category, count(*) from incident group by category HAVING count(*) > 5
void addAggregate(String agg, String name)
Adds an aggregate.
void addTrend(String fieldName, String timeInterval)
Adds a trend for a field.
String getAggregate(String agg, String name)
Gets the value of an aggregate from the current record.
String getQuery()
Gets the query necessary to return the current aggregate.
int getTotal(String agg, String name)
Gets the total number of records by summing an aggregate.
String getValue(String name)
Gets the value of a field.
void groupBy(String name)
Provides the name of a field to use in grouping the aggregates. May be called numerous times to set multiple group fields.
void orderBy(String name)
Provides the name of a field that should be used to order the aggregates. The field will also be added to the group-by list.
void orderByAggregate(String agg, String name)
Provides the name of an aggregate that should be used to order the result set.
void query()
Issues the query and get some results.
void setGroup(Boolean b)
Sets whether grouping is true or false.

Method Detail

addEncodedQuery

public void addEncodedQuery(String query)

Adds a query to the Aggregate. Adds an encoded query to the other queries that may have been set for this aggregate.
Parameters:
query - An encoded query string to add to the aggregate.
Example:
<source lang="javascript">

var agg = new GlideAggregate('incident'); agg.addAggregate('count','category'); agg.orderByAggregate('count', 'category'); agg.orderBy('category'); agg.addQuery('opened_at', '>=', 'javascript:gs.monthsAgoStart(2)'); agg.addQuery('opened_at', '<=', 'javascript:gs.monthsAgoEnd(2)'); agg.query(); while (agg.next()) {

 var category = agg.category;
 var count = agg.getAggregate('count','category');
 var query = agg.getQuery();  
 var agg2 = new GlideAggregate('incident');   
 agg2.addAggregate('count','category');
 agg2.orderByAggregate('count', 'category');
 agg2.orderBy('category');
 agg2.addQuery('opened_at', '>=', 'javascript:gs.monthsAgoStart(3)');
 agg2.addQuery('opened_at', '<=', 'javascript:gs.monthsAgoEnd(3)');
 agg2.addEncodedQuery(query);
 agg2.query();
 var last = "";
 while (agg2.next()) {
    last = agg2.getAggregate('count','category');      
 }
 gs.log(category + ": Last month:" + count + " Previous Month:" + last);

} </source>


addHaving

public void addHaving(String name, String operator, String value)

Adds a "having" element to the aggregate e.g. select category, count(*) from incident group by category HAVING count(*) > 5
Parameters:
String name - the aggregate to filter on (e.g. COUNT).
String operator - for the operator symbol (e.g. <, >, =, !=)
String value to query on (e.g. '5' or '69')

addAggregate

public void addAggregate(String agg, String name)

Adds an aggregate.
Parameters:
String agg - name of aggregate to add.
String name - name of column to aggregate.
Example:
<source lang="javascript">

function doMyBusinessRule(assigned_to, number) {

 var agg = new GlideAggregate('incident');
 agg.addQuery('assigned_to', assigned_to);
 agg.addQuery('category', number);
 agg.addAggregate("COUNT");
 agg.query();
 var answer = 'false';
 if (agg.next()) {
   answer = agg.getAggregate("COUNT");
   if (answer > 0)
     answer = 'true';
   else
     answer = 'false';
 }
 return answer; 

} </source>


addTrend

public void addTrend(String fieldName, String timeInterval)

Adds a trend for a field.
Parameters:
fieldName - The string name of the field for which trending should occur.
timeInterval - The time interval for the trend. The following choices are available:
  • Year
  • Quarter
  • Date
  • Week
  • DayOfWeek
  • Hour
  • Value

getAggregate

public String getAggregate(String agg, String name)

Gets the value of an aggregate from the current record.
Parameters:
agg - String type of the aggregate (e.g. SUM or COUNT)
name - String name of the field to get aggregate from.
Returns:
String - the value of the aggregate
Example:
<source lang="javascript">

function doMyBusinessRule(assigned_to, number) {

 var agg = new GlideAggregate('incident');
 agg.addQuery('assigned_to', assigned_to);
 agg.addQuery('category', number);
 agg.addAggregate("COUNT");
 agg.query();
 var answer = 'false';
 if (agg.next()) {
   answer = agg.getAggregate("COUNT");
   if (answer > 0)
     answer = 'true';
   else
     answer = 'false';
 }
 return answer; 

} </source>


getQuery

public String getQuery()

Gets the query necessary to return the current aggregate.
Returns:
String - the string query.
Example:
<source lang="javascript">

var agg = new GlideAggregate('incident'); agg.addAggregate('count','category'); agg.orderByAggregate('count', 'category'); agg.orderBy('category'); agg.addQuery('opened_at', '>=', 'javascript:gs.monthsAgoStart(2)'); agg.addQuery('opened_at', '<=', 'javascript:gs.monthsAgoEnd(2)'); agg.query(); while (agg.next()) {

 var category = agg.category;
 var count = agg.getAggregate('count','category');
 var query = agg.getQuery();  
 var agg2 = new GlideAggregate('incident');   
 agg2.addAggregate('count','category');
 agg2.orderByAggregate('count', 'category');
 agg2.orderBy('category');
 agg2.addQuery('opened_at', '>=', 'javascript:gs.monthsAgoStart(3)');
 agg2.addQuery('opened_at', '<=', 'javascript:gs.monthsAgoEnd(3)');
 agg2.addEncodedQuery(query);
 agg2.query();
 var last = "";
 while (agg2.next()) {
    last = agg2.getAggregate('count','category');      
 }
 gs.log(category + ": Last month:" + count + " Previous Month:" + last);

} </source>


getTotal

public int getTotal(String agg, String name)

Gets the total number of records by summing an aggregate.
Parameters:
agg - String type of aggregate
agg - String name of field to aggregate from
Returns:
int - the total.

getValue

public String getValue(String name)

Gets the value of a field.
Parameters:
String name - the string name of the field.
Returns:
String value - the string value of the field.

groupBy

public void groupBy(String name)

Provides the name of a field to use in grouping the aggregates. May be called numerous times to set multiple group fields.
Parameters:
name - name of the field to group-by.
Example:
<source lang="javascript">

Referencing the example in wiki @ Aggregation Support:

 var count = new GlideAggregate('incident');
 count.addAggregate('MIN', 'sys_mod_count');
 count.addAggregate('MAX', 'sys_mod_count');
 count.addAggregate('AVG', 'sys_mod_count');
 count.groupBy('category');
 count.query();   
 while (count.next()) {  
    var min = count.getAggregate('MIN', 'sys_mod_count');
    var max = count.getAggregate('MAX', 'sys_mod_count');
    var avg = count.getAggregate('AVG', 'sys_mod_count');
    var category = count.category.getDisplayValue();
    gs.log(category + " Update counts: MIN = " + min + " MAX = " + max + " AVG = " + avg);
 }

</source>


orderBy

public void orderBy(String name)

Provides the name of a field that should be used to order the aggregates. The field will also be added to the group-by list.
Parameters:
name - name of the field used to order the aggregates.
Example:
<source lang="javascript">

var agg = new GlideAggregate('incident'); agg.addAggregate('count','category'); agg.orderByAggregate('count', 'category'); agg.orderBy('category'); agg.addQuery('opened_at', '>=', 'javascript:gs.monthsAgoStart(2)'); agg.addQuery('opened_at', '<=', 'javascript:gs.monthsAgoEnd(2)'); agg.query(); while (agg.next()) {

 var category = agg.category;
 var count = agg.getAggregate('count','category');
 var query = agg.getQuery();  
 var agg2 = new GlideAggregate('incident');   
 agg2.addAggregate('count','category');
 agg2.orderByAggregate('count', 'category');
 agg2.orderBy('category');
 agg2.addQuery('opened_at', '>=', 'javascript:gs.monthsAgoStart(3)');
 agg2.addQuery('opened_at', '<=', 'javascript:gs.monthsAgoEnd(3)');
 agg2.addEncodedQuery(query);
 agg2.query();
 var last = "";
 while (agg2.next()) {
    last = agg2.getAggregate('count','category');      
 }
 gs.log(category + ": Last month:" + count + " Previous Month:" + last);

} </source>


orderByAggregate

public void orderByAggregate(String agg, String name)

Provides the name of an aggregate that should be used to order the result set.
Parameters:
agg - String type of aggregate (e.g. SUM, COUNT, MIN, MAX)
name - String name of field to aggregate
Example:
<source lang="javascript">

var agg = new GlideAggregate('incident'); agg.addAggregate('count','category'); agg.orderByAggregate('count', 'category'); agg.orderBy('category'); agg.addQuery('opened_at', '>=', 'javascript:gs.monthsAgoStart(2)'); agg.addQuery('opened_at', '<=', 'javascript:gs.monthsAgoEnd(2)'); agg.query(); while (agg.next()) {

 var category = agg.category;
 var count = agg.getAggregate('count','category');
 var query = agg.getQuery();  
 var agg2 = new GlideAggregate('incident');   
 agg2.addAggregate('count','category');
 agg2.orderByAggregate('count', 'category');
 agg2.orderBy('category');
 agg2.addQuery('opened_at', '>=', 'javascript:gs.monthsAgoStart(3)');
 agg2.addQuery('opened_at', '<=', 'javascript:gs.monthsAgoEnd(3)');
 agg2.addEncodedQuery(query);
 agg2.query();
 var last = "";
 while (agg2.next()) {
    last = agg2.getAggregate('count','category');      
 }
 gs.log(category + ": Last month:" + count + " Previous Month:" + last);

} </source>


query

public void query()

Issues the query and gets some results.
Example:
<source lang="javascript">

var agg = new GlideAggregate('incident'); agg.addAggregate('count','category'); agg.orderByAggregate('count', 'category'); agg.orderBy('category'); agg.addQuery('opened_at', '>=', 'javascript:gs.monthsAgoStart(2)'); agg.addQuery('opened_at', '<=', 'javascript:gs.monthsAgoEnd(2)'); agg.query(); while (agg.next()) {

 var category = agg.category;
 var count = agg.getAggregate('count','category');
 var query = agg.getQuery();  
 var agg2 = new GlideAggregate('incident');   
 agg2.addAggregate('count','category');
 agg2.orderByAggregate('count', 'category');
 agg2.orderBy('category');
 agg2.addQuery('opened_at', '>=', 'javascript:gs.monthsAgoStart(3)');
 agg2.addQuery('opened_at', '<=', 'javascript:gs.monthsAgoEnd(3)');
 agg2.addEncodedQuery(query);
 agg2.query();
 var last = "";
 while (agg2.next()) {
    last = agg2.getAggregate('count','category');      
 }
 gs.log(category + ": Last month:" + count + " Previous Month:" + last);

</source>


setGroup

public void setGroup(boolean b)

Sets whether grouping is true or false.
Parameters:
b - true if grouping is true, false if it is false.