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

Nisar3 · ‎11-24-2024

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 · ‎11-25-2024

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(', '));
    }
}

View solution in original post

Marandi1995ug · ‎11-24-2024

The only one that has to

Nisar3 · ‎11-25-2024

Sorry, didn't get it.

Runjay Patel · ‎11-24-2024

Hi @Nisar3 ,

You can run a report and do group by created on.

-------------------------------------------------------------------------

If you found my response helpful, please consider selecting "Accept as Solution" and marking it as "Helpful." This not only supports me but also benefits the community.

Regards
Runjay Patel - ServiceNow Solution Architect
YouTube: https://www.youtube.com/@RunjayP
LinkedIn: https://www.linkedin.com/in/runjay

-------------------------------------------------------------------------

Nisar3 · ‎11-24-2024

How would I set a condition where I only consider those incidents where count is greater than 1? i.e. only display it in the report if the system finds more than 1 incident with the same created on value.