Removing duplicated serial numbers in serial table

[BK14]

ServiceNow has created numerous duplicated records in Serial table where Serial number + Serial type + CI (combined) are the same. We need to retain the latest record and remove the rest of the duplicated records. For instance in this chart, we want to keep the records in row 1 and 7, and remove the rest.

we will need 2 scripts, the first one to identify the dups and prints the records so we can analyze the data and the second one is to delete them. Please advise if you can provide the scripts to us.

Thank you very much for your help,

bk

[BK14 · Accepted solution]

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;