Search for Duplicates & Delete Based on 2 Columns

[Ankur Bawiskar]

You must have seen many articles on community informing about how to find duplicates in particular table with single column.

Links: https://www.servicenowelite.com/blog/2013/11/22/duplicate-record-scripts

The above is widely referred link.

But those links won't be helpful if you want to search for duplicates using multiple columns i.e. 2 columns etc

Real Time Scenario: There could be a scenario that Group Membership Table (sys_user_grmember) has duplicate records for User and Group. Ideally this should not be allowed.

Example: User A belongs to Group A; There could be 2 records for the same User + Group combination.

Here is the script which you can use to search for duplicates and delete those records if required. You can enhance the script for 3 columns etc.

As of now this doesn't work in scoped application since function addHaving() is not allowed in scope. This should run smoothly in global scope.

Ensure you print the information first before deleting the actual records.

deleteDuplicates('sys_user_grmember', 'user', 'group');

function deleteDuplicates(tableName, field1, field2){

// declare an array
var dupRecords = [];
var duplicateCheck = new GlideAggregate(tableName);
duplicateCheck.addNotNullQuery(field1);
duplicateCheck.addNotNullQuery(field2);
duplicateCheck.groupBy(field1);
duplicateCheck.groupBy(field2);
duplicateCheck.addHaving('COUNT', '>', 1); // addHaving func won't work in scope app
duplicateCheck.query();
while(duplicateCheck.next()) {
var jsonObj = {}; // declare a json object
jsonObj[field1] = duplicateCheck[field1].toString();
jsonObj[field2] = duplicateCheck[field2].toString()
dupRecords.push(jsonObj);
}

var jsonString = JSON.stringify(dupRecords); // convert json object to string

var parser = new JSONParser();
var parsedData = parser.parse(jsonString);
var length = parsedData.length;

for(var i=0; i<length; i++){

var encodedQuery = field1 + '=' + parsedData[i][field1] + '^' + field2 + '=' + parsedData[i][field2];

var tableRec = new GlideRecord(tableName);
tableRec.addEncodedQuery(encodedQuery);
tableRec.query();
if(tableRec.next()){
gs.info('Repeated Data is: User -> ' + tableRec.getDisplayValue('user') + ' Group -> ' + tableRec.getDisplayValue('group'));
tableRec.deleteRecord();
}
}
}

Kindly do not forget to like or bookmark this post if it helps you.

Kindly input your suggestions if any once you use this.