How do I add "date" as dynamic filter condition in GlideAggregate to retrieve counts of record from a table?

kiki330611 · ‎05-05-2016

I am trying to write a piece of script that will query a single table and come back with the matching record count everyday (from a specific start day to today).

The table I want to query is a log that take record of the usage of a server from all the users.

Whenever a user log on to the server, a new record of username (sys_created_by) get inserted so potentially one username can show up multiple times a day.

What I need is writing a code that returns the total number of user log on per day (returns two fields: 'Date' and 'Count', if a user log on multiple times still count as one).

I only managed to write the following code which return the count of only a specific day (in this case 2015-11-24😞

var gr = new GlideAggregate('u_mt_log');

gr.addEncodedQuery('sys_created_on2015-11-24@javascript:gs.dateGenerate("2015-11-24","start")@javascript:gs.dateGenerate("2015-11-24","end")');

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

gr.addHaving('COUNT','>=',1);

gr.query();

while (gr.next()){

var DailyCount = {};

DailyCount.date = "2015-11-24"

DailyCount.count = gr.getRowCount();

My question is how do I replace all the 2015-11-24 with dynamic dates and loop through all the dates from 2015-11-24 till today and get all the daily counts back at once?

(something like "date: 2015-11-24, count: 5", "date:2015-11-25, count: 10"...)

Thanks in advance!

Chuck Tomasi · ‎05-09-2016

Hi Kiki,

Sure. The following script will do that.

var myTable = 'incident';

var dateField = 'u_created';

var userField = 'caller_id';


var count = new GlideAggregate(myTable);   

count.addAggregate('COUNT(distinct', userField); 

count.orderBy(dateField);

count.groupBy(dateField);

count.query();


while (count.next()) {

   var c = count.getAggregate('count(distinct', userField);

   gs.log(count.getValue(dateField) + ',' + c);

}

View solution in original post

Chuck Tomasi · ‎05-05-2016

This is going to be a tough one because sys_created_on is a date/time field. You may want to create and maintain a separate "u_created_on" date field to make this easier.

When I tried to just aggregate on sys_created_on (on the incident table), it looked like this:

var count = new GlideAggregate('incident');

count.addQuery('active', 'true');

count.addAggregate('COUNT', 'sys_created_on');

count.query();     

while (count.next()) {

     var created = count.sys_created_on;

     var createCount = count.getAggregate('COUNT', 'sys_created_on');

     gs.log("The are currently " + createCount + " incidents created on " + created);

}

*** Script: The are currently 1 incidents created on 2016-02-12 23:37:35

*** Script: The are currently 1 incidents created on 2016-02-19 22:27:13

*** Script: The are currently 1 incidents created on 2016-02-22 23:08:24

*** Script: The are currently 1 incidents created on 2016-02-24 23:41:00

*** Script: The are currently 1 incidents created on 2016-02-27 23:07:12

*** Script: The are currently 1 incidents created on 2016-03-01 23:06:52

*** Script: The are currently 1 incidents created on 2016-03-09 13:48:24

*** Script: The are currently 1 incidents created on 2016-03-09 23:51:35

*** Script: The are currently 1 incidents created on 2016-03-12 01:00:44

*** Script: The are currently 1 incidents created on 2016-03-13 12:57:24

*** Script: The are currently 1 incidents created on 2016-03-13 23:07:30

(more records not shown here.)

See how there are multiples on 03-09 and 03-13? That leads me to believe we need a simpler field. Once we can tell how many are on each date, then we can start grouping them by person.

Let me know if this is how you want to go and I'll keep working on a script for you.

kiki330611 · ‎05-05-2016

Thanks ctomasi!

I thought about the same thing : to create another date field. I believe that will make the whole script easier but the table is a live table the client use on a daily basis. I want to put changing the table as the last solution and see if there is any way to do this without touching the original log table.

Chuck Tomasi · ‎05-05-2016

Hi Kiki,

Adding a field would require one script to populate the new field for existing records and then a trivial business rule to keep it up to date as new records are created. System performance should not be an issue.

On the other hand, if you continue to use getRowCount(), it very well could be. GlideAggregate is very efficient at counting and grouping records because it relies on the native database functionality where as getRowCount() does a select statement, retrieves all the records to the system, then counts them manually. Retrieving thousands or millions of records can be painfully slow whereas retrieving a count is milliseconds.

kiki330611 · ‎05-06-2016

Thanks so much! Yes I understand if I create another field I will need to write one script to populate the new field for existing records.

I am not sure where to write this script and how though. Can you please indicate how I should do it?

Replacing getRowCount() with GlideAggregate would be a good idea if that improves the performance!

Thanks!