Finding duplicates with GlideAggregate

[Alex North]

This afternoon I was helping out Customer Support assist a customer with identifying any Cis which happened to have the same serial number. The following principals could be followed for any table/field combination. Normal SQL hygiene applies – if you can reduce the record sets by including an addActiveQuery clause, then do so. Obviously if you are cleaning up data inconsistencies, you may have to query the whole table to make sure you don’t miss records.

The following example was based on OOB demo data. It shows how you can find the duplicates and extract relevant information directly from the MySQL console, as well as performing the same using Glide functionality. Although our hosted customers don't have access to the MySQL console, my hope is that seeing the SQL will help illustrate what I'm talking about.

A pure SQL view

1) Get a list of distinct serial_numbers where more than one CI has the same serial number:

select count(serial_number), serial_number from cmdb group by serial_number having count(serial_number) > 1;

2) Get some additional information using a sub-query (yuck):

select sys_id, name, serial_number from cmdb where serial_number in (select serial_number from cmdb group by serial_number having count(serial_number) > 1);

In Glide this can be achieved thus: 

// 1) Get the duplicated values
 
function getDupes(theTable, dpField) { 
    var ga = new GlideAggregate(theTable);
    ga.addAggregate('COUNT', dpField);
    ga.addHaving('COUNT', dpField, '>', '1');
    ga.query(); 
    var arDupes = new Array();
    while (ga.next()) { 
      arDupes.push(ga.getValue(dpField));    
    }
    return arDupes;
}
 
 
var theTable = "cmdb";
var dpField = "serial_number"
 
gs.print(getDupes(theTable, dpField));
 

// 2) Get all the records which have the duplicated field. Iterate through and do as you will.

var strQuery = "serial_numberIN" + getDupes(theTable, dpField); 
var gr = new GlideRecord(theTable);
gr.addEncodedQuery(strQuery);
gr.query();
 
while (gr.next()) {
      gs.print(gr.sys_id + " " + gr.name + " "+ gr.serial_number);
}

Caution:

Make sure the field you are checking for duplicates on is indexed. Both the aggregate count and the subsequent glide record query will make use of an index on that field if it exists.

Also worth pointing out that the glide example does _not_ perform an SQL sub-query. The getDupes function means we don't have to go in for that nastiness.