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

[kiki330611]

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!

[kiki330611]

Yes that's right. We want to know how many users logged on per day since day 1.

[Chuck Tomasi · Accepted solution]

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);
}   

[kiki330611]

In the end the client would not allow us to change the table structure. But this is still very helpful!

Thank you very much!!