Byte #2: GlideAggregate Examples

Note: This was originally posted on my personal blog back on 2014. However, I’ve reviewed it for accuracy and found that it’s still relevant and London compatible!

Previously, I posted an example of how to get distinct records using GlideAggregate. Today I want to share some further powerful examples of GlideAggregate.

First off, you can grab the JavaScript file for GlideAggregate showing all the functions you can use, from right here: GlideAggregate JS. Alternatively, you can always grab the entire library of Service-Now JavaScript objects and functions.

Key Functions

• addAggregate(type)  - Add a new statistical function to the query, such as Count, AVG, Max

• groupBy(field)  - The field you’re running statistics on. If you want to know how many items are assigned to someone, you’ll group by the field “assigned_to”

• orderByAggregate(field)  - If you’re looking for who is assigned the most items, you would order by “count”

• getAggregate(type)  - Use this inside your while loop to get the Count, AVG, Max statistic that you were querying for

• addHaving(type, operator, value)  - This is a great way to be able to limit your results. For example, I can write a query to only return users that have at least 10 active tasks assigned to them

  Note: addHaving is now only available in the GLOBAL scope.

Example Background Scripts

Activate All Roled Users

var gr, gr2, temp;
gr = new GlideAggregate("sys_user_has_role");
gr.addAggregate("COUNT");
gr.groupBy("user");
gr.query();

while(gr.next()) {
    temp = gr.user;
    gr2 = new GlideRecord("sys_user");
    if(gr2.get(temp)) {
        gr2.active = "true";
        gr2.locked_out = "false";
        gr2.update();
    }

}

Count Incidents Opened This Month

var gr = new GlideAggregate('task');
gr.addQuery('sys_created_on', '>=', gs.beginningOfThisMonth());
var qc = gr.addQuery('sys_class_name', 'Incident');
gr.addAggregate('COUNT');
gr.query();
gr.next();
var total_opened_this_month = gr.getAggregate('COUNT');

Count Incidents Resolved Today

var gr = new GlideAggregate('incident');
gr.addQuery('sys_created_on', '>=', gs.beginningOfToday());
var qc = gr.addQuery('incident_state', '6');
qc.addOrCondition('incident_state', '7');
qc.addOrCondition('incident_state', '20');
gr.addAggregate('COUNT');
gr.query();
gr.next();
var total_resolved_today = gr.getAggregate('COUNT');

Count Incidents Resolved Yesterday

var gr = new GlideAggregate('incident');
gr.addQuery('sys_created_on', '>=', gs.beginningOfYesterday());
gr.addQuery('sys_created_on', '<', gs.beginningOfToday());
var qc = gr.addQuery('incident_state', '6');
qc.addOrCondition('incident_state', '7');
qc.addOrCondition('incident_state', '20');
gr.addAggregate('COUNT');
gr.query();
gr.next();
var total_resolved_yesterday = gr.getAggregate('COUNT');

Find Tasks with more than one SLA of the same type

Note: This will now only work in the GLOBAL scope.

var gr = new GlideAggregate('task_sla');
gr.addQuery('sla.name', 'Incident Priority 1 Resolution');
gr.addQuery('sys_created_on', '>=', '2010-07-26 00:00:00');
gr.addAggregate('COUNT');
gr.addHaving('COUNT', '>=', '2');
gr.groupBy('task');
gr.query();

while(gr.next()) {
    if(gr.getAggregate('COUNT') == 0)
       break;
    gs.log('Task: '+gr.task.number+' -- Opened: '+gr.task.sys_created_on);
}

Helpful Function to get Average Survey Scores

/*
* Looks for stats to use for the Created count.
* @param domain -- The domain to gather information about.
* @param month -- The month we're updating.
* @param year -- The year we're updating.
* @global g_queryTable -- The table that we're using here.
* @global g_queryFieldName -- The table that we're using here.
* @returns Integer -- Number of Created.
*/
function getSurveyCountAndAverages(domain, surveyType, month, year) {
    var gr;

    //Check if stats exist for previous month.
    gr = new GlideAggregate(g_queryTable);
    gr.addQuery('sys_domain', domain.sys_id);
    gr.addQuery('u_task.sys_class_name', surveyType);
    gr.addQuery('sys_created_on', '>=', beginningOfMonth(month, year));
    gr.addQuery('sys_created_on', '<=',endOfMonth(month, year));
    gr.addQuery('u_completed_date', '!=', '');
    gr.addAggregate('COUNT');
    gr.addAggregate('AVG', 'u_competency');
    gr.addAggregate('AVG', 'u_customer_service');
    gr.addAggregate('AVG', 'u_resolution_timeliness');
    gr.addAggregate('AVG', 'u_overall_satisfaction');
    gr.groupBy("sys_domain");
    gr.query();

    if (gr.next()) {
        return gr;
    }
    return false;
}

Have More Examples?

If you have any creative uses of GlideAggregate, please feel free to share them in the comments! And as always, never hesitate to ask any questions you might have.