Returning multiple values with GlideAggregate

[Rohit Sharman]

Hello,

I am working on the Software Installations table (cmdb_sam_sw_install) and trying to report the count of Unique Products (based on Display name).

The below script works fine for this.

var gr = new GlideRecord('cmdb_sam_sw_install'); //GlideAggregate query
gr.addEncodedQuery("my query here");
gr.addAggregate('count'); //Count aggregate (only necessary for a count of items of each product)
gr.orderByAggregate('count'); //Count aggregate ordering
gr.groupBy('display_name'); //Group aggregate by the 'display_name' field
gr.query();

while(gr.next()){
   var prodCount = gr.getAggregate('count'); //Get the count of the Product group
   //Print the Product name and count of items with that Product
   gs.print('Distinct Product: ' + gr.display_name + ': ' + prodCount);
}

Now, we have an additional requirement to also get the 'Publisher' for the respective products.  When I edit the last line to "  gs.print('Distinct Product: ' + gr.display_name  + gr.publisher + ': ' + prodCount);  , it shows the same results as the previous script without the Publisher.

What changes should I make to the code to also get the Publisher name along with the Product ? And what would be the best way to export these to CSV in the format - Column 1 - Product, Column 2 - Publisher, Column 3 - Count of iterations for the product. Currently, I copy paste the results to notepad and then to Excel.

Any help would be appreciated.

Thank you.

[Iraj Shaikh · Accepted solution]

Hi @Rohit Sharman 

To include the 'Publisher' information in your GlideAggregate query, you will need to group by the 'publisher' field as well. Here's how you can modify your script to include the 'Publisher':

var gr = new GlideAggregate('cmdb_sam_sw_install'); // Use GlideAggregate for aggregation queries
gr.addEncodedQuery("my query here");
gr.addAggregate('COUNT'); // Count aggregate (only necessary for a count of items of each product)
gr.groupBy('display_name'); // Group aggregate by the 'display_name' field
gr.groupBy('publisher'); // Also group by the 'publisher' field
gr.query();

while (gr.next()) {
    var prodCount = gr.getAggregate('COUNT'); // Get the count of the Product group
    // Print the Product name, Publisher, and count of items with that Product
    gs.print('Distinct Product: ' + gr.display_name + ', Publisher: ' + gr.publisher + ', Count: ' + prodCount);
}

To export these results to a CSV file, you can use the following script which will create a CSV string that you can then output to a file or use as needed:

var csvString = '"Product","Publisher","Count"\n'; // CSV header
var gr = new GlideAggregate('cmdb_sam_sw_install');
gr.addEncodedQuery("my query here");
gr.addAggregate('COUNT');
gr.groupBy('display_name');
gr.groupBy('publisher');
gr.query();

while (gr.next()) {
    var prodCount = gr.getAggregate('COUNT');
    // Append each line to the CSV string
    csvString += '"' + gr.display_name + '","' + gr.publisher + '","' + prodCount + '"\n';
}

// Now you have a CSV string in csvString. You can output it to a file or use it as needed.
// For example, to print it to the ServiceNow logs:
gs.print(csvString);

// To create and download a CSV file, you can use the following:
var fileName = "software_installations.csv";
var file = new global.GlideSysAttachment();
var sysId = file.write(gr.getTableName(), fileName, 'text/csv', csvString);

// Provide a link to download the file (you can use this in a UI Page or similar)
gs.print('Download CSV: ' + gs.getProperty('glide.servlet.uri') + 'sys_attachment.do?sys_id=' + sysId);

Please note that the above script for creating a CSV file assumes you are running this in a context where you can create attachments (like a server-side script). If you are running this in a different context (like a scheduled job or background script), you may need to adjust the method for handling the CSV data accordingly.

Remember to replace `"my query here"` with your actual encoded query.

Please mark this response as correct or helpful if it assisted you with your question.

[Rohit Sharman]

Worked like a charm, Many thanks 🙂