Find Duplicate Records Using Script

Amit Kumar6
Tera Contributor

‎02-24-2017 02:54 AM

I have a requuirement to find out dupliacate records in cmdb_ci table.

There are combination of unique fields using which I need to find out duplicate.Unique fields which is to be checked are-:serial number,asset tag,category.

Actually I have one script which finds out duplicate records, but it is considering only one field. I want to check all the above fields mentioned.Below is the script

which is considering only fields but my requiirement is to find out duplicate in combination of above field.

  1. gs.print(getDuplicates('cmdb_ci','serial_number','asset_tag','model_id','u_asset_id','fqdn','model_number','model_id');

function getDuplicates(tablename,val,val1,val2,val3,val4,val5,val6) {

var dupRecords = [];

var gaDupCheck = new GlideAggregate(tablename);

  1. gaDupCheck.addQuery('active','true');
  2. gaDupCheck.addAggregate('COUNT',val);
  3. gaDupCheck.addAggregate('COUNT',val1);
  4. gaDupCheck.addAggregate('COUNT',val2);
  5. gaDupCheck.addAggregate('COUNT',val3);
  6. gaDupCheck.addAggregate('COUNT',val4);
  7. gaDupCheck.addAggregate('COUNT',val5);
  8. gaDupCheck.addAggregate('COUNT',val6);

// gaDupCheck.addNotNullQuery(val);

  1. gaDupCheck.groupBy(val);
  2. gaDupCheck.addHaving('COUNT', '>', 1);

  1. gaDupCheck.query();

while (gaDupCheck.next()) {

  1. dupRecords.push(gaDupCheck[val].toString());
  2. gs.print('Serial Number is-:'+ gaDupCheck[val].toString());

}

return dupRecords;

}

Thanks,

Amit

Labels:
0 Helpfuls
  • 3,230 Views
1 REPLY 1

rafael_merces2
Tera Guru

‎02-24-2017 05:08 AM

Hi Amit,



Instead of:



gaDupCheck.addAggregate('COUNT',val);


gaDupCheck.addAggregate('COUNT',val1);


gaDupCheck.addAggregate('COUNT',val2);


gaDupCheck.addAggregate('COUNT',val3);


gaDupCheck.addAggregate('COUNT',val4);


gaDupCheck.addAggregate('COUNT',val5);


gaDupCheck.addAggregate('COUNT',val6);



try



gaDupCheck.groupBy(val);


gaDupCheck.groupBy(val1);


gaDupCheck.groupBy(val2);


gaDupCheck.groupBy(val3);


gaDupCheck.groupBy(val4);


gaDupCheck.groupBy(val5);


gaDupCheck.groupBy(val6);



You have to group by this combination of columns, and not COUNT them. Please let me know if it works.


0 Helpfuls