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.