Find incidents that have the same sys_created_on value (up until the seconds)

[Nisar3]

What would be the best way to find if there are incidents that have the sys_created_on value? For example:

Inc A - sys_created_on 01/03/2024 15:34:35

Inc B - sys_created_on 01/03/2024 15:36:13

Inc C - sys_created_on 01/03/2024 15:31:20

Inc D - sys_created_on 01/03/2024 15:36:13

Inc E - sys_created_on 01/03/2024 15:37:14

Inc F - sys_created_on 01/03/2024 15:37:14

Inc G - sys_created_on 01/03/2024 15:37:14

So I see two sets of incidents here. Inc B and Inc D is one set. The other set is Inc E, F and G. I somehow need to find a list of such incidents. Would script be a good idea or report (although I'm not sure how the grouping will work as I'll need to have a "HAVING count > 1" condition in the report)

[Community Alums · Accepted solution]

Hi Nisar,

please try with this scripts 

 
var ga = new GlideAggregate('incident');
ga.groupBy('sys_created_on');
ga.addAggregate('COUNT');
ga.query();

while (ga.next()) {
    
    var createdon = ga.getValue('sys_created_on');
    var count = ga.getAggregate('COUNT');
    
    gs.print('Created By: ' + createdBy + ', Incident Count: ' + count);
}

// Create an object to store counts of each sys_created_on value
var createdOnMap = {};

// GlideRecord to fetch all incidents
var incidentGR = new GlideRecord('incident');
incidentGR.orderBy('sys_created_on'); // Order by sys_created_on for easier debugging
incidentGR.query();

while (incidentGR.next()) {
    var createdOn = incidentGR.getValue('sys_created_on');
    
    // Count occurrences of each sys_created_on value
    if (!createdOnMap[createdOn]) {
        createdOnMap[createdOn] = [];
    }
    createdOnMap[createdOn].push(incidentGR.getValue('sys_id'));
}

// Find and log duplicates
gs.print('Duplicate Incidents Based on sys_created_on:');
for (var key in createdOnMap) {
    if (createdOnMap[key].length > 1) { // Having condition: count > 1
        gs.print('sys_created_on: ' + key + ', Incidents: ' + createdOnMap[key].join(', '));
    }
}

[Nisar3]

I had it figured it out some time back and it was pretty similar to your first approach. So will accept your post as solution anyway. Thanks.

[Community Alums]

try this 

  
var ga = new GlideAggregate('incident');
ga.groupBy('sys_created_on');
ga.addAggregate('COUNT');
ga.query();

while (ga.next()) {
    
    var createdon = ga.getValue('sys_created_on');
    var count = ga.getAggregate('COUNT');
    
    gs.print('Created By: ' + createdBy + ', Incident Count: ' + count);
}