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!

[Chuck Tomasi]

Let's do this a little at a time to be safe... First, create your new Date field on on the u_mt_log,

Next, elevate your priviledges (security_admin) and run this script in Scripts - Background.

var myTable = 'incident';

var gr = new GlideRecord(myTable);
// Add query filters here if you like
// Ex: gr.addQuery('active', true);   // get all active records
gr.query();

while (gr.next()) {
   gr.setWorkflow(false); // do not trigger business rules/workflows for this update
   gr.autoSysFields(false); // Do not update the updated by, updated on, mod count fields
   gr.u_created = gr.getValue('sys_created_on');
   gr.update();
}

Substitute your table name in myTable.

Finally, again in Scripts - Background, run this script to test if you can group by date (again, using u_mt_log instead of incident for myTable.)

var myTable = 'incident';

var count = new GlideAggregate(myTable);   
count.addQuery('active', 'true');   
count.addAggregate('COUNT', 'u_created');   
count.query();         
while (count.next()) {   
     var created = count.u_created;   
     var createCount = count.getAggregate('COUNT', 'u_created');   
     gs.log("The are currently " + createCount + " records created on " + created);   
}   

If you get that far with results counting records with a specific date, let me know.

[kiki330611]

Thanks ctomasi!

I managed to copy the date to the new field and use the second script to group the records into the specific day there were created.

I have a question about the first script though. Is it necessary to have the following two lines of code?

gr.setWorkflow(false); // do not trigger business rules/workflows for this update  

gr.autoSysFields(false); // Do not update the updated by, updated on, mod count fields  

So now we can group the records by username?

[Chuck Tomasi]

Hi Kiki,

Yes, those two lines are necessary to a) prevent the business rules from triggering on update. For example, you don't want to send notifications to people about records that were closed months ago. b) do not update the sys_update_on and mod_count fields. If you're doing any kind of metrics, SLAs, reporting, or generally sorting in a list, these fields would otherwise show they were all updated when you ran the script. The second line prevents that.

I'll work on a new script that groups by user & date. Is that correct. For example:

Jerry Jones had 10 records created on 2016-03-16

Marla Morgan had 3 records created on 2016-03-16

Anna Anderson had 1 records created on 2016-03-16

Jerry Jones had 3 records created on 2016-03-17

...

Is that about right, or would you prefer something simpler like a CSV output?

Jerry Jones,10,2016-03-16

Marla Morgan,3,2016-03-16

[kiki330611]

Thanks ctomasi! Now I understand!

Actually what I am after is not how many times per user has logged on a day, but how many users log on per day.

So no matter how many times a user logged on in one day the count for that user would always be one.

Is it still do-able?

For the output, what format would allow me to export the data into excel?

Any of those format is fine.

Thanks!

[Chuck Tomasi]

Hi Kiki,

Just to make sure I'm hearing this correctly, you want something more like:

Date                               #of Users

2016-03-15           15

2016-03-16             10

Something like that?