Thank you Rahul for the script. I also got another script from a former colleague that works too. Here is the script:
// *********************************** SETUP ****************************************************************************************************************
var dryRun = true; // SET THIS TO FALSE TO ACTUALLY DELETE RECORDS
var tableName = 'cmdb_serial_number'; // TARGET TABLE
var encodedQuery = ‘cmdb_ci'=; // APPLY ANY FILTER CONDITIONS HERE
var identifierFields = cmdb_ci.name='XXXXXXX’; //USED TO IDENTIFY THE DUPLICATES
var batch = 100; //SETS THE LIMIT FOR HOW MANY RECORDS ARE IN THE GLIDERECORD
// **********************************************************************************************************************************************************
var identifiers = identifierFields.split(','); //split up the groupby fields, we'll use this one later
var agg = new GlideAggregate(tableName);
agg.addQuery(encodedQuery);
agg.setLimit(batch);
// ADD THE COMBINATION OF UNIQUE IDENTIFIERS HERE
for(var i = 0; i < identifiers.length; i++) {
agg.groupBy(identifiers[i]);
}
// DON'T BOTHER READING ANY RECORDS THAT AREN'T DUPLICATE
agg.addAggregate('COUNT');
agg.addHaving('COUNT','>','1'); // only where 1 or more duplicates exist
agg.query();
// AT THIS POINT WE ONLY HAVE DUPLICATE RECORDSETS (WHERE THERE IS MORE THAN ONE RECORD WITH THE SAME IDENTIFYING FIELDS)
// NOW LOOP THROUGH ALL OF THOSE RECORDSETS, KEEPING ONLY THE OLDEST RECORD AND DELETING THE REST
var resultString = '\nUnique Set\tRecord to keep\tRecord(s) to delete\n'; // In order to avoid using many gs.print() statements we simply add all the results to a string and display them at the end.
var resultCount = 0; //Count the duplicates
while(agg.next()) {
resultString += getSetDetailsString(agg); //log the set that brought us here
var dup = new GlideRecord(tableName);
dup.addQuery(encodedQuery);
dup.setLimit(batch);
//get the records that match the set
for(var i = 0; i < identifiers.length; i++) {
dup.addQuery(identifiers[i],agg[identifiers[i]]);
}
// order by date ascending. the first record is the oldest and the one we want to preserve. Change for 'orderByDesc' to preserve the newest record and remove the rest
dup.orderBy('sys_created_on');
dup.query();
// skip the first record (original) and log it
dup.next();
resultString += (dup.sys_created_on + ' - '+ dup.sys_id + '\t');
// loop through and delete the remaining records for this set. Only deletes if dryRun = false
while(dup.next()) {
resultString += (dup.sys_created_on + ' - '+ dup.sys_id + ', ');
resultCount++;
if(!dryRun) { //CAREFUL
dup.setWorkflow(false);
dup.deleteRecord();
}
}
resultString += '\n';
}
//Display results in TAB SEPARATED FORMAT
gs.print('Duplicate count: ' + resultCount);
gs.print(resultString);
//helper method to dynamically create the SET string for logging
function getSetDetailsString(gRecord) {
var returnString = '(';
for(var i = 0; i < identifiers.length; i++) {
returnString += identifiers[i] + ': ' + gRecord[identifiers[i]] + ', ';
}
returnString += ')\t';
return returnString;
