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

[jhuston]

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

[poyntzj]

Can't see an easy way to do this. I can see a workaround

What you could do is create a new table that has two columns - Group and Count

Create a Scheduled job that will clear that table and then performs a query against the relevant table (task or one of the extended tables) and populate that

You could even change the table to have numerous count columns and have a column for Incident, Change, Request, Request Item, Catalog Task and so on

You can then create a new report on that new table

Add a sort order and do that on the Count field and choose ascending or descending as you wish

Cheers

[epam]

Hi John,

Looks like you can`t do it with OOTB reports.

The only way to see the bottom of the chart - is the placing all item on the bar chart or list. In that case you will see both top and bottom groups. And, of course, it is not very convenient for huge amount of items on the chart.

Otherwise you need to customize the table (add a custom field with calculated value, etc).

[bernyalvarado]

Hi John, first I was to recognize what a great post/question this is! I'm amazed that ServiceNow does not provide an Order Desc as part of reports and I believe that may be directly related to the fact that it's not provided as part of the GlideAggregate class.

I will soon be posting a solution through script, the one you can use in a scheduled report and perhaps send a notification from it.

Thanks,

Berny

[bernyalvarado · Accepted solution]

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

[bernyalvarado]

The output of this script in my sandbox Dev Instance (almost all OOB) is the following:

This is the complete list of users with incidents assigned

*** Script: ITIL User: 11 60

*** Script: David Loo: 8 60

*** Script: Don Goodliffe: 8 60

*** Script: Beth Anglin: 6 60

*** Script: Bud Richman: 4 60

*** Script: Fred Luddy: 3 60

*** Script: Howard Johnson: 3 60

*** Script: Luke Wilson: 3 60

*** Script: Charlie Whitherspoon: 1 60

This is the LAST 5 users with incidents assigned

*** Script: ******** COUNT for Last 5 ********

*** Script: **** Bud Richman has 4 assigned

*** Script: **** Fred Luddy has 3 assigned

*** Script: **** Howard Johnson has 3 assigned

*** Script: **** Luke Wilson has 3 assigned

*** Script: **** Charlie Whitherspoon has 1 assigned