I needed to remove multiple users with more than one record...

[matthew_magee1]

Recently, I noticed our sys_user table was growing more than I expected it should be. Turns out our Transform Map was coalescing on the wrong field. So when someone changed their name/job title, a new record was being inserted. So I needed a way to go through the table, find users w/ duplicates, and remove the oldest record(s).

Here is what I came up with. Thankfully the user records I am removing do not have any records/tasks associated with them since they are external to our application.

var ga = new GlideAggregate('sys_user');
ga.addAggregate('COUNT','employee_number'); //unique value for each employee, this never changes
ga.addHaving('COUNT','>',1);
ga.query();
gs.log('the sys_user table has ' + ga.getRowCount() + ' duplicates');
while(ga.next()){
deleteDuplicate(ga.employee_number);
}
funciton deleteDuplicate(number); {
var gr = new GlideRecord('sys_user');
gr.addQuery('employee_number',number);
gr.orderBy('sys_updated_on');
gr.setLimit(1);
gr.query();
if(gr.next()){
gr.setWorkflow(false);
gs.log('This sys_id will be deleted: ' + gr.sys_id + ' with this date: ' + gr.sys_updated_on);
gr.deleteRecord();
}
}