How can I create a report that shows the low usage end of my data

jhuston · ‎07-20-2015

For example, we track the number of phone calls we receive from each of our clients.

Its quite easy to create a bar graph or something similar that shows the top 10 or 20 clients that call the most.

But I am trying to get some good quick data on who uses us the lease. So sorting the report acceding by group count rather than descending.

It may be easy to do, but I can see to Google the right question to find my answer.

Thanks for any input you may have.

Thanks, John

bernyalvarado · ‎07-23-2015

Hi John, here goes just an example of a script which display the 5 user names that has the less assigned incidents. You can leverage this script to accomplish your goal by setting it as part of a scheduled job and then firing an event that will take care of triggering a notification. Please let me know if you also need help on triggering the event/notification if that's a route you will like to take.

Here goes the script. Run it as a Background Script (System Definition >> Scripts Background) first so that you can see how it works:

var results = [];


var gr = new GlideAggregate('incident');


gr.addAggregate('COUNT','assigned_to');


gr.orderByAggregate('count','assigned_to');


gr.query();


while(gr.next()){


   if (JSUtil.notNil(gr.assigned_to)) {


       results.push(gr.assigned_to.name.toString() + ' has ' + gr.getAggregate('COUNT','assigned_to') + ' assigned '); 


       var eva = gr.getAggregate('COUNT','assigned_to');


       gs.print(gr.assigned_to.name.toString() + ': ' + eva + ' ' + gr.getTotal());


   }


}






var last_results = 5;


gs.print ('******** COUNT for Last ' + last_results + ' ********');






var starting_point = results.length - last_results;


for (var i = starting_point; i < results.length; i++){


   gs.print ('**** ' + results[i]);


}

I hope it's helpful!

Thanks,

Berny

View solution in original post

Robert Duca · ‎09-30-2020

This is great.. I was able to re-purpose it to get the catalog item counts against the requested item table. Can this be altered to run it for a specific data range?

bernyalvarado · ‎09-30-2020

Hi Robert,

I'm glad you find it helpful! Please mark the responses you found helpful so that others later can reference these when facing the same issue.

Indeed can be done. You will just need to filter your Glide Record query by the dates you're interested on.

Thanks,

Berny

Robert Duca · ‎09-30-2020

I also figured it out myself 😉

var results = [];
var gr = new GlideAggregate('sc_req_item');
gr.addEncodedQuery("opened_atBETWEENjavascript:gs.dateGenerate('2019-12-01','00:00:00')@javascript:gs.dateGenerate('2021-02-28','23:59:59')");
gr.addAggregate('COUNT','cat_item');
gr.orderByAggregate('count','cat_item');
gr.query();
while(gr.next()){


   if (JSUtil.notNil(gr.cat_item)) {
       results.push(gr.cat_item.name.toString() + ' has ' + gr.getAggregate('COUNT','cat_item') + ' orders '); 
       var eva = gr.getAggregate('COUNT','cat_item');
       gs.print(gr.cat_item.name.toString() + ': ' + eva + ' ' + gr.getTotal());
   }
}
var last_results = 5;
gs.print ('******** COUNT for Last ' + last_results + ' ********');
var starting_point = results.length - last_results;
for (var i = starting_point; i < results.length; i++){
   gs.print ('**** ' + results[i]);
}

bernyalvarado · ‎09-30-2020

Great 🙂 Glad to hear. That's the main idea here... not to catch the fish for you but to teach you and give you ideas how to go and enjoy fishing! 🙂

Great work Robert and thanks for sharing the script as well!! Your contribution adds to make of this such an awesome community.

All the best @Robert Duca !

Thanks,

Berny