How to delete duplicates based on 2 fields

[Dead Blade]

Hi,

So I've scrubbed through the forums and found many scripts to delete duplicate records, but they are all based on 1 field.  Some of which are using GlideAggregate.  So I need a script to delete records based on several field criteria.  Example

Field 1     Field 2

John        Smith

John        Doe

John        Smith

John        Johnson

Delete the Duplicate John Smiths. 

I will be using this script as a fix script running on a schedule as maintenance for an m2ms table.  The fields are reference fields if that matters.

[Dead Blade]

Script Include:

function FindDuplicateRecords() {
var gr=new GlideAggregate('u_m2m_authorize_servers');
var duplicates= [];
gr.addAggregate('COUNT','u_group');
gr.addAggregate('COUNT','u_server');
gr.groupBy('u_group');
gr.groupBy('u_server');
gr.addHaving('COUNT', '>=', 2);
gr.query();
while(gr.next())
{
 var array_Duplicates= {};
array_Duplicates.group= ''+gr.group;
array_Duplicates.server= ''+gr.server;
duplicates.push(array_Duplicates);
 gs.info(JSON.stringify(duplicates));
}

return JSON.stringify(duplicates);
}

Script:

var optionsObj=FindDuplicateRecords.function();// need to edit the name and function of the script Include

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

var gr=new GlideRecord('u_m2m_authorize_servers');
gr.addQuery('u_group',optionsObj.group);
gr.addQuery('u_server',optionsObj.server);
gr.query();
var deleteCount = 0;
while(gr.next()) {
//gr.deleteRecord();
deleteCount++;
gs.print('Record: ' + gr.u_group + gr.u_server + ' Deleted');
}
gs.print('Records Deleted = ' + deleteCount);

}

Error receiving:

Javascript compiler exception: missing name after . operator (null.null.script; line 1) in:
var optionsObj=FindDuplicateRecords.function();// need to edit the name and function of the script Include

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

var gr=new GlideRecord('u_m2m_authorize_servers');
gr.addQuery('u_group',optionsObj.group);
gr.addQuery('u_server',optionsObj.server);
gr.query();
var deleteCount = 0;
while(gr.next()) {
//gr.deleteRecord();
deleteCount++;
gs.print('Record: ' + gr.u_group + gr.u_server + ' Deleted');
}
gs.print('Records Deleted = ' + deleteCount...

[aashishdasari]

Hi,

The Script Include call has to be in this form:

var script_include=new NameofScriptinclude();//NameofScriptinclude-- Should be the name of the Script Include created.
var optionsObj=script_include.FindDuplicateRecords();

***
The Script Include created should be in global scope and it should be accessible across all the application

[Dead Blade]

This is starting to work:

var optionsObj=FindDuplicateRecords();

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

var gr=new GlideRecord('u_m2m_authorize_servers');
gr.addQuery('u_group',optionsObj.group);
gr.addQuery('u_server',optionsObj.server);
gr.query();
var deleteCount = 0;
while(gr.next()) {
gr.deleteRecord();
deleteCount++;
//gs.print('Record: ' + gr.optionsObj.group + gr.optionsObj.server + ' Deleted');
}
//gs.print('Records Deleted = ' + deleteCount);

}

Output:

*** Script: [{"group":"9695a56adb846740829900b5ca961979","server":"535a557adbc46700b825abc5ca961974"}]


BUT, it did not actually delete the duplicate record.

[aashishdasari]

Can you change this line from:

var optionsObj=FindDuplicateRecords();

to 

var optionsObj=JSON.parse(FindDuplicateRecords());

And also can you add a gs.info statement inside the while loop.

[Dead Blade]

Same results, no deletion.

I think find is working.  It appears as though the deletion is not.

Script:

var optionsObj=JSON.parse(FindDuplicateRecords());

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

var gr=new GlideRecord('u_m2m_authorize_servers');
gr.addQuery('u_group',optionsObj.group);
gr.addQuery('u_server',optionsObj.server);
gr.query();
var deleteCount = 0;
while(gr.next()) {
gr.deleteRecord();  
deleteCount++;
gs.info('Record: ' + gr.optionsObj.group + gr.optionsObj.server + ' Deleted');
gs.info('Records Deleted = ' + deleteCount);
}
}

Results:

*** Script: [{"group":"9695a56adb846740829900b5ca961979","server":"535a557adbc46700b825abc5ca961974"}]
*** Script: Pre-Delete: undefined undefined