Find duplicate values in any table

Duplicates hide in every instance: two users with the same email, several CIs with one serial number. This finds them without looping over the whole table.

var TABLE = 'sys_user';
var FIELD = 'email';

var ga = new GlideAggregate(TABLE);
ga.addNotNullQuery(FIELD);
ga.addAggregate('COUNT', FIELD);
ga.groupBy(FIELD);
ga.addHaving('COUNT', '>', '1');
ga.orderByAggregate('COUNT', FIELD);
ga.query();

var found = 0;
while (ga.next()) {
  found++;
  gs.info(ga.getValue(FIELD) + ' appears ' + ga.getAggregate('COUNT', FIELD) + ' times');
}
gs.info(found + ' duplicated value(s) in ' + TABLE + '.' + FIELD);

How it works

• groupBy() makes one result per distinct value; addHaving() keeps only groups with a count above 1.
• addNotNullQuery() excludes empty values, which would otherwise show up as one huge “duplicate”.

Gotchas

• This only reports. Decide how to merge or retire duplicates per table; never bulk-delete users or CIs blindly.
• Values that differ only by case or spaces aren’t caught. Normalise first if that matters.