How to group by date portion of sys_created_on in a GlideAggregate

[gjz1]

I have an audit log that may have multiple rows per day per person and I need to find the last row per person per day.  For example:

Person A: 2020-01-21 09:00:00
Person A: 2020-01-21 08:45:00
Person A: 2020-01-21 07:00:00
Person B: 2020-01-21 09:00:00

Person A: 2020-01-20 08:45:00
Person B: 2020-01-20 07:00:00
Person B: 2020-01-20 06:59:00

Person A: 2020-01-19 09:00:00
Person B: 2020-01-19 09:00:00

What I want to see in the output is

Person A: 2020-01-21 09:00:00
Person B: 2020-01-21 09:00:00
Person A: 2020-01-20 08:45:00
Person B: 2020-01-20 07:00:00
Person A: 2020-01-19 09:00:00
Person B: 2020-01-19 09:00:00

I want to group by person and date (not time) - but I can't figure out how to group by the date portion of sys_created_on.  Does anyone know how to do that?

Here's my query, which works fine except for agg.groupBy('sys_created_on').  I have an encoded query which will be removed in the final version, I just wanted to limit the rows while testing.

var agg = new GlideAggregate('u_hourly_employee_time_tracking');
agg.addEncodedQuery("sys_created_onBETWEENjavascript:gs.dateGenerate('2020-01-19','00:00:00')@javascript:gs.dateGenerate('2020-01-21','09:01:00')");
agg.addAggregate('MAX', 'sys_created_on');
agg.groupBy('u_employee_name');
agg.groupBy('u_status');
agg.groupBy('sys_created_on');
agg.query();
while(agg.next()) {
    answer = agg.getAggregate('MAX', 'sys_created_on');
    gs.print('row: ' + agg.u_employee_name + '  ' + answer + '  ' + agg.u_status);
}

[Community Alums]

Honestly I think you'll have to make a new column which could be a calculated field (date only) based on the sys_created_on (to extract the date). That will give you a nice clean field to aggregate on.

The other option is to not use glide aggregate and calculate it manually by script with arrays, but that's not recommended.

[Balaji Jagannat]

Once after getting the GlideRecord output, you can something like to below to create an object array where you can modify the datetime to date and then get the unique values. Refer this thread for more details

var incidents = new GlideRecord('incident');

incidents.addQuery('active', true);

incidents.addNotNullQuery('caller_id.name');

incidents.orderBy('caller_id.name');

incidents.orderBy('category');

incidents.query();

gs.print('---> Before unique: ' + incidents.getRowCount());

var callerIdList = [];

while(incidents.next()) {

    var incident = {};

    incident.caller_id = incidents.caller_id.name.trim();

    incident.category = incidents.category + '';

    incident.priority = incidents.priority.getDisplayValue();

    incident.description = incidents.short_description + '';

    incident.key = incident.caller_id + '|' + incident.category + '|' + incident.priority;

    callerIdList.push(incident);

}

callerIdList = uniqueObjectList(callerIdList);

gs.print('---> After unique: ' + callerIdList.length);

for (var caller in callerIdList) {

    var callerId = callerIdList[caller].caller_id;

    var category = callerIdList[caller].category;

    var priority = callerIdList[caller].priority;

    var description = callerIdList[caller].description;

    gs.print(callerId + ' - ' + category + ' - ' + priority + ' - ' + description);

}

function uniqueObjectList(callerList) {

    for( var i = 0; i < callerList.length; i++){

            for( var j = i + 1; j < callerList.length; j++){

                    if( callerList[j].key == callerList[i].key ) {

                            callerList.splice(j, 1);   // delete the duplicate

                            --j;   // reduce the array length by one

                    }

            }

    }

    return callerList;

}

[gjz1]

It turns out you can't do this per ServiceNow.  I ended up pulling the data into a different reporting tool and creating a calculated field on the date to get it to show what I wanted.

[petercawdron]

As the others have mentioned, there's no easy way to query on date as part of a date/time, the best approach is to split it up into multiple queries. For example, here's a background script that will show how many log entries there are on the first 15 days of the month.

for(var getDate = 1; getDate<16; getDate++){

  var agg = new GlideAggregate('syslog');
  agg.addEncodedQuery(  "sys_created_onBETWEENjavascript:gs.dateGenerate('2021-03-"+getDate+"','00:00:00')@javascript:gs.dateGenerate('2021-03-"+getDate+"','23:59:59')^typeINlist,form,report^NQ" + 
                        "sys_created_onBETWEENjavascript:gs.dateGenerate('2021-03-"+getDate+"','00:00:00')@javascript:gs.dateGenerate('2021-03-"+getDate+"','23:59:59')^type=rest^urlLIKEportal_id");						
  agg.addAggregate('COUNT', 'level'); 
  agg.query();
  var logCount = 0;
  while (agg.next()) {  
    logCount += parseInt(agg.getAggregate('COUNT', 'level'));  
  }
  gs.info(getDate + ' => ' + logCount);
}