NOTE: MY POSTINGS REFLECT MY OWN VIEWS AND DO NOT NECESSARILY REPRESENT THE VIEWS OF MY EMPLOYER, ACCENTURE.
DIFFICULTY LEVEL: ADVANCED
Assumes having taken the class SSNF and has good intermediate to advanced level of knowledge and/or familiarity with Scripting in ServiceNow.
I thought I would cover a topic that is usually avoided by developers: The GlideAggregate object.
<<insert scary music crescendo here!>>
I'm guilty of this as well. Usually I am of the mind that the GlideRecord object and JavaScript could do a better job of aggregate type functions: SUM, and COUNT. Well, no, they can't. GlideAggregate taps into the MySQL aggregate functions which are very accurate and fast. If you choose to do this type of stuff in JavaScript then beware! JavaScript does a lousy job of summing float values, and you WILL get a creeping error! If there are a lot of iterations in your sum loop you will find that error can become substantial.
The GlideAggregate overcomes this by exposing the underlying MySQL Java COUNT and SUM functionality. The SUM is very accurate, and can be trusted to return the correct values.
GlideAggregate have a shortcoming in that even though the object is based on GlideRecord the result is constrained only to the fields used in the groupBy and orderBy methods.
What I want to cover here is an example of using the GlideAggregate with both the COUNT and SUM functions. I will also show how you can add useful fields back into the results by building lists of keys, then using these keys to retrieve additional information for the overall result.
The Basic GlideAggregate
First let's create a basic GlideAggregate query against the CMDB Computer table. We will simply count the number of records found. We will use the cost field to do this. Actually, any field would suffice, but the cost field provides an interesting example to show what happens with retrieving the count and sum values using the same field.
The SQL for our first example will be:
SELECT COUNT(cost_center) AS cost_count FROM cmdb_ci_computer
We will group and order by the cost_center field. This has the side-benefit of making the cost_center field available for use. In this case we will be echoing back all values that are great than zero. This will give us an ordered list of cost centers and their total number of computers.
var computerSumRecords = new GlideAggregate('cmdb_ci_computer');
computerSumRecords.addAggregate('COUNT', 'cost');
computerSumRecords.groupBy('cost_center');
computerSumRecords.orderBy('cost_center');
computerSumRecords.query();
while (computerSumRecords.next()) {
var deviceCount = computerSumRecords.getAggregate('COUNT', 'cost');
var name = computerSumRecords.cost_center.getDisplayValue();
name = JSUtil.nil(name) ? 'No Name' : name;
if (parseFloat(deviceCount).toFixed(2) > 0.00) {
gs.info('---> cost center: {0}, count: {1}', [name, deviceCount]);
}
}
To test the code use Scripts-Background. The result should look something like this:
*** Script: ---> cost center: No Name, count: 78
*** Script: ---> cost center: Customer Support, count: 293
*** Script: ---> cost center: Engineering, count: 71
*** Script: ---> cost center: Finance, count: 33
*** Script: ---> cost center: Human Resources, count: 21
*** Script: ---> cost center: IT, count: 141
*** Script: ---> cost center: Sales, count: 218
Adding More Fields
Okay, so that was useful, but now we need to add in the total cost of the computer records by cost center. We will add in the SUM method, and also break things out further by type (class) of server. This can be Computer, Server, etc. We will also throw in manufacturer and model name.
The SQL would look something like this:
SELECT COUNT(cost) AS cost_count, SUM(cost) AS cost_sum
FROM cmdb_ci_computer
GROUP BY cost_center, sys_class_name, manufacturer.name, model_id.name
ORDER BY cost_center, sys_class_name, manufacturer.name, model_id.name
The actual GlideAggregate will look like this:
var computerSumRecords = new GlideAggregate('cmdb_ci_computer');
computerSumRecords.addAggregate('COUNT', 'cost');
computerSumRecords.addAggregate('SUM', 'cost');
computerSumRecords.groupBy('cost_center');
computerSumRecords.groupBy('sys_class_name');
computerSumRecords.groupBy('manufacturer.name');
computerSumRecords.groupBy('model_id.name');
computerSumRecords.orderBy('cost_center');
computerSumRecords.orderBy('sys_class_name');
computerSumRecords.orderBy('manufacturer.name');
computerSumRecords.orderBy('model_id.name');
computerSumRecords.query();
var costCenterNameList = []; // list of names (1-D array)
var costCenterList = []; // object array of aggregate records
while (computerSumRecords.next()) {
var deviceCount = computerSumRecords.getAggregate('COUNT', 'cost');
var cost = computerSumRecords.getAggregate('SUM', 'cost');
var name = computerSumRecords.cost_center.getDisplayValue();
name = JSUtil.nil(name) ? 'No Name' : name;
var className = computerSumRecords.getValue('sys_class_name');
var manufacturer = computerSumRecords.getValue('manufacturer.name');
var model = computerSumRecords.getValue('model_id.name');
if (parseFloat(cost).toFixed(2) > 0.00) {
gs.info('---> cost center: {0}, className: {1}, manufacturer: {2}, model: {3}, count: {4}, cost:{5}',
[name, className, manufacturer, model, deviceCount, cost]);
}
}
You can see that things tend to get tedious the more you throw into the groupBy/orderBy list. This gets to be a pain the more you add in. Frankly, I try to keep it to four groupBy's or less. It will be unusual just to have that many!
Note here that we are using both COUNT and SUM with the same field. These then are accessed similarly using the field name. This can be confusing if you are coming into GlideAggregates for the first time it is worth studying.
Also note that you can use dot walking in the group and order by methods. This is really useful when dealing with reference fields. Frankly you could do the same thing just using the base reference field without the dot walk to the name (this would use the underlying sys_id to group/order by against). However, I am doing the deed this way to show the dot walking technique.
I am taking care of null values in the Cost Center name. This sort of thing is a best-practice.
On a comparison of a float value you will need to play the parseFloat game in order to get a value to compare correctly. It does have the benefit of allowing you to set the precision during the comparison.
Again using Scripts-Background your results should look something like this:
*** Script: ---> cost center: No Name, className: cmdb_ci_computer, manufacturer: Apple, model: MacBook Pro 15", count: 13, cost:23399.8700000
*** Script: ---> cost center: No Name, className: cmdb_ci_computer, manufacturer: Asus, model: G Series, count: 36, cost:30239.6400000
*** Script: ---> cost center: No Name, className: cmdb_ci_linux_server, manufacturer: Iris, model: 5875, count: 4, cost:182230.0000000
*** Script: ---> cost center: No Name, className: cmdb_ci_server, manufacturer: Dell Inc., model: PowerEdge C6100 Rack Server, count: 2, cost:20030.0000000
*** Script: ---> cost center: No Name, className: cmdb_ci_server, manufacturer: Dell Inc., model: PowerEdge M710HD Blade Server, count: 8, cost:12960.0000000
*** Script: ---> cost center: No Name, className: cmdb_ci_server, manufacturer: Lenovo, model: ThinkStation S20, count: 2, cost:3399.9800000
*** Script: ---> cost center: No Name, className: cmdb_ci_server, manufacturer: Microsoft, model: XPS 14z, count: 1, cost:1599.9900000
*** Script: ---> cost center: No Name, className: cmdb_ci_unix_server, manufacturer: IBM, model: BladeCenter Blade HS22, count: 3, cost:9599.9700000
*** Script: ---> cost center: No Name, className: cmdb_ci_unix_server, manufacturer: Sun Microsystems, model: x3690 X5 Rack, count: 2, cost:9599.9900000
*** Script: ---> cost center: Customer Support, className: cmdb_ci_computer, manufacturer: Apple, model: MacBook Pro 17", count: 145, cost:362498.5500000
*** Script: ---> cost center: Customer Support, className: cmdb_ci_computer, manufacturer: Dell Inc., model: OptiPlex GX280, count: 1, cost:1200.0000000
*** Script: ---> cost center: Customer Support, className: cmdb_ci_computer, manufacturer: Dell Inc., model: PowerEdge T110 II, count: 15, cost:10425.0000000
*** Script: ---> cost center: Customer Support, className: cmdb_ci_computer, manufacturer: Dell Inc., model: PowerEdge T410, count: 7, cost:9415.0000000
*** Script: ---> cost center: Customer Support, className: cmdb_ci_computer, manufacturer: Dell Inc., model: PowerEdge T610, count: 7, cost:11172.0000000
*** Script: ---> cost center: Customer Support, className: cmdb_ci_computer, manufacturer: Dell Inc., model: Precision T3500 Workstation, count: 16, cost:22064.0000000
*** Script: ---> cost center: Customer Support, className: cmdb_ci_computer, manufacturer: Dell Inc., model: Precision T5500 Workstation, count: 86, cost:114294.0000000
*** Script: ---> cost center: Customer Support, className: cmdb_ci_computer, manufacturer: Dell Inc., model: Precision WorkStation T1600, count: 14, cost:19460.0000000
*** Script: ---> cost center: Customer Support, className: cmdb_ci_computer, manufacturer: IBM, model: A53 Series, count: 1, cost:529.9900000
*** Script: ---> cost center: Customer Support, className: cmdb_ci_computer, manufacturer: IBM, model: ThinkStation C20, count: 1, cost:2249.9900000
*** Script: ---> cost center: Engineering, className: cmdb_ci_computer, manufacturer: Apple, model: MacBook Air 13", count: 1, cost:1599.9900000
*** Script: ---> cost center: Engineering, className: cmdb_ci_computer, manufacturer: Apple, model: MacBook Pro 15", count: 26, cost:46799.7400000
*** Script: ---> cost center: Engineering, className: cmdb_ci_computer, manufacturer: Apple, model: MacBook Pro 17", count: 28, cost:69999.7200000
*** Script: ---> cost center: Engineering, className: cmdb_ci_computer, manufacturer: Cyberpower, model: Gamer Ultra 2098, count: 1, cost:549.9900000
*** Script: ---> cost center: Engineering, className: cmdb_ci_computer, manufacturer: Cyberpower, model: Gamer Xtreme 1311, count: 1, cost:1099.9900000
...
Pretty cool!
Complex Example
Now, with this final example I will introduce how to go and grab additional values AFTER the GlideAggregate has been executed. This allows you to expand on what is brought back with the initial query. Here I am pulling together a list of records that I can place into a fictional billing table.
- Get the GlideAggregate from the previous example.
- Loop through and build a list of Cost Centers. This will be the limiting list we will use to pull back values from the Cost Center table.
- Using our Cost Center list pull all records from the Cost Center that match.
- Build "records" using the original count and sum values, along with the matched Cost Center information.
- Write those records to the fictional table (we will not create this table, but instead will print off the records we created).
And, I will throw in absolutely for free a cool little object-find method I use a lot!
// example of how to set everything up to push the combined records
// into a ficticious billing table
var computerSumRecords = new GlideAggregate('cmdb_ci_computer');
computerSumRecords.groupBy('cost_center');
computerSumRecords.groupBy('sys_class_name');
computerSumRecords.groupBy('manufacturer.name');
computerSumRecords.groupBy('model_id.name');
computerSumRecords.addAggregate('COUNT', 'cost');
computerSumRecords.addAggregate('SUM', 'cost');
computerSumRecords.orderBy('cost_center');
computerSumRecords.orderBy('sys_class_name');
computerSumRecords.orderBy('manufacturer.name');
computerSumRecords.orderBy('model_id.name');
computerSumRecords.query();
var costCenterNameList = []; // list of names (1-D array)
var costCenterList = []; // object array of aggregate records
while (computerSumRecords.next()) {
var deviceCount = computerSumRecords.getAggregate('COUNT', 'cost');
var cost = computerSumRecords.getAggregate('SUM', 'cost');
var name = computerSumRecords.cost_center.getDisplayValue();
name = JSUtil.nil(name) ? 'No Name' : name;
var className = computerSumRecords.getValue('sys_class_name');
var manufacturer = computerSumRecords.getValue('manufacturer.name');
var model = computerSumRecords.getValue('model_id.name');
if (parseFloat(cost).toFixed(2) > 0.00) {
costCenterNameList.push(name);
costCenter = {};
costCenter.name = name;
costCenter.cost = cost;
costCenter.className = className;
costCenter.manufacturer = manufacturer;
costCenter.model = model;
costCenter.deviceCount = deviceCount;
costCenter.cost = cost;
costCenterList.push(costCenter);
}
}
// deduplicate the list
costCenterNameList = new ArrayUtil().unique(costCenterNameList);
gs.info('---> count of names: ' + costCenterNameList.length);
// Now that we have all of our billing information lets go get the
// details of our cost centers and add it all back in
// fictional billing table u_billing_table
// columns: u_cost_center_name, u_class_name, u_manufacturer, u_model, u_device_count, u_account_number, u_manager, u_total_cost
// retrieve all the cost center records in our cost center name list
var costCenterRecords = new GlideRecord('cmn_cost_center');
costCenterRecords.addQuery('name', 'IN', costCenterNameList);
costCenterRecords.query();
var costCenterInfo = [];
while (costCenterRecords.next()) {
var costCenter = {};
costCenter.name = costCenterRecords.getValue('name');
costCenter.account_number = costCenterRecords.getValue('account_number');
costCenter.manager = costCenterRecords.manager.getDisplayValue();
costCenterInfo.push(costCenter);
}
for (var item in costCenterList) {
var costCenter = costCenterList[item];
// find the first occurance of the cost center name in the object list
var foundCostCenter = _findObject(costCenter.name, costCenterInfo, 'name');
// if one was found then work with it
if (JSUtil.notNil(foundCostCenter)) {
costCenter.account_number = foundCostCenter.account_number;
costCenter.manager = foundCostCenter.manager;
var message = '---> Cost Center:{0}\nClass:{1}\nManufacturer:{2}\nModel:{3}\nCount: {4}';
message += '\nAcct Number:{5}\nAcct Manager:{6}\nTotal Cost:{7}';
gs.info(message,
[costCenter.name,
costCenter.className,
costCenter.manufacturer,
costCenter.model,
costCenter.deviceCount,
costCenter.account_number,
costCenter.manager,
parseFloat(costCenter.cost).toFixed(2)]);
}
}
// useful function for finding a specific value in an object property in an object list
// returns the first found object.
function _findObject(source, targetList, field) {
return targetList.filter(function (obj) {
return obj[field] === source;
})[0];
}
Your result should look something like this:
*** Script: ---> count of names: 7
*** Script: ---> Cost Center:Customer Support Class:cmdb_ci_computer Manufacturer:Apple Model:MacBook Pro 17" Count: 145 Acct Number:ACN00001 Acct Manager:Rob Woodbyrne Total Cost:362498.55
*** Script: ---> Cost Center:Customer Support Class:cmdb_ci_computer Manufacturer:Dell Inc. Model:OptiPlex GX280 Count: 1 Acct Number:ACN00001 Acct Manager:Rob Woodbyrne Total Cost:1200.00
*** Script: ---> Cost Center:Customer Support Class:cmdb_ci_computer Manufacturer:Dell Inc. Model:PowerEdge T110 II Count: 15 Acct Number:ACN00001 Acct Manager:Rob Woodbyrne Total Cost:10425.00
*** Script: ---> Cost Center:Customer Support Class:cmdb_ci_computer Manufacturer:Dell Inc. Model:PowerEdge T410 Count: 7 Acct Number:ACN00001 Acct Manager:Rob Woodbyrne Total Cost:9415.00
...
And there you go! You now have a pretty good working example of using a complex GlideAggregate along with building a list to query an associated table for more information. Merging that information with the original results, and then organizing the merged result so that it could hypothetically be used to write records to a custom table.
Enjoy!
Steven Bell.
If you find this article helps you, don't forget to log in and mark it as "Helpful"!
Originally published on: 09-01-2018 07:53 AM
I updated the code and brought the article into alignment with my new formatting standard.
