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.
Example Background Scripts
If you aren’t sure what a Background Script is, or how to run one, check out this blog post.
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
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.
Categories: Developers, Scripting Tips
GarrettNow 
Leave a Reply