script for deleting duplicate records

[sbhayani]

Hello Experts,

I want to delete the duplicate records from router interface table, I scripted following code, but not sure, why isnt it working,

Can you help please?

var dup = new GlideAggregate('dscy_router_interface');
 dup.addAggregate('COUNT', 'mac_address');
 dup.addHaving('COUNT', 'mac_address', '>', '1'); //returns only records having more than one active instance of dupeField (duplicates)
 dup.query();
 var listOfDupes = []; //build array to push the results into
 while (dup.next()) {
  listOfDupes.push(dup.getValue('mac_address')); //Push the value of the dupe field to the array
 }
var dup1 = new GlideRecord('dscy_router_interface');
dup1.addQuery('sys_id','IN',listofDupes.toString());
dup1.orderByDesc('sys_created_on');
dup1.query();
dup1.next();
while(dup1.next())
 dup1.deleteRecord();

[Chris Sanford1]

Try this. Note I haven't tested it, so you may want to comment out 'dup.delete()' and put a 'gs.print(dup.mac_address)' or something similar on the first run to make sure I don't have any mistakes.

var gr = new GlideRecord('dscy_router_interface');
gr.orderByDesc('sys_created_on');
gr.query();

var macArray = [];
var sysArray = [];
var i = 0;
var mac = '';

while(gr.next()) {
   mac = gr.mac_address;
   i = macArray.indexOf(mac);
   if(i == -1) {
      macArray.push(mac);
   } else {
      sysArray.push(gr.sys_id);
   }
}

var dup = new GlideRecord('dscy_router_interface');
dup.addQuery('sys_id', 'IN', sysArray.toString());
dup.query();
while(dup.next()) {
   dup.delete();
}

[Kamal17]

Made some changes to your code, this may help you.

var dup = new GlideAggregate('dscy_router_interface');
dup.addAggregate('COUNT', 'mac_address');
dup.addHaving('COUNT', 'mac_address', '>', '1'); //returns only records having more than one active instance of dupeField (duplicates)
dup.query();

while (dup.next()) {

var dup1 = new GlideRecord('dscy_router_interface');
dup1.addQuery('mac_address',dup.getValue('mac_address'));
dup1.orderByDesc('sys_created_on');
dup1.query();
dup1.next();

while(dup1.next())
{
gs.print('deleting = '+dup1.name);
//dup1.deleteRecord();
}
}

Regards,

Udhay

[Baala T]

Please try this,

var dup = new GlideAggregate('dscy_router_interface');
dup.groupBy('mac_address');
dup.addHaving('COUNT', '>', 1);
dup.query();

while (dup.next()) {
	var i = 0;

	var g = new GlideRecord('dscy_router_interface');
	g.addEncodedQuery('sys_id=' + dup.getValue('mac_address'));
	g.orderByDesc('sys_created_on');
	g.query();
	while (g.next()) {
		if (i == 0) {
			i = i + 1;
			//gs.print('Not to Delete <> ' + g.getDisplayValue('mac_address') + ' <> ' + g.getValue('sys_created_on'));
		} else {
			g.deleteRecord();
			//gs.print('Delete <> ' + g.getDisplayValue('mac_address') + ' <> ' + g.getValue('sys_created_on'));
		}
	}
}

[sbhayani]

Hello All,

Thanks a lot for taking time and posting some good codes. I did try the code provided by Shishir and it worked. hence didnt try rest.

Now my next challange is to build a code for finding duplicates, based on same name and mac address. and removing the duplicate.

[Shishir Srivast]

may be you can give a try with below script,

var dup = new GlideAggregate('dscy_router_interface');
dup.groupBy('mac_address');
dup.groupBy('name');
dup.query();
while(dup.next()) {
	var dup1 = new GlideRecord('dscy_router_interface');
	dup1.addQuery('mac_address', dup.mac_address);
	dup1.addQuery('name', dup.name);
	dup1.query();
	dup1.next();
	while(dup1.next())
		dup1.deleteRecord();
}