Need a script to find Duplicate CI

[Saib1]

Hi,

Need to find the duplicate CI which has different Serial Number 

Need a Script Include . Can any one help me?

[aditya174]

Hi @Saib1 ,

var arraylist = [];

var dup = new GlideAggregate('cmdb_ci');
dup.addAggregate('COUNT','serial_number');
dup.addHaving('COUNT','>', '1');
dup.groupBy('serial_number');
dup.query();
var count =0;
while(dup.next()){
  arraylist.push(dup.getValue('serial_number'));
}
for (i=0; i < arraylist.length; i++) {
 count = count+1;
}
gs.info(count);

this shall give you the count of the CI's.

Please mark my answer correct if it helps.

[Bert_c1]

I do not field a field named 'serial_number' on the alm_hardware (or the parent alm_asset) table.

[Saib1]

need to find the duplicates in Serial number in alm_hardware table .

var r = findDupes("alm_hardware", "ci");
gs.print("Unique duplicates: " + r.length);
for (var i = 0; i < r.length; i++) {
gs.print("" + r[i].value + " : " + r[i].count);
}

function findDupes(alm_hardware, ci) {
var r = [];
var ga = new GlideAggregate('alm_hardware');
ga.addNotNullQuery(ci);
ga.addAggregate("COUNT", ci);
ga.query();
while (ga.next()) {
var c = ga.getAggregate("COUNT", ci);
if (c > 1) {
r.push({
value: ga.getValue(ci),
count: c
});
}
}
return r;
}

Is this will work? help to write exact code

[Bert_c1]

alm_hardware is a child table of alm_asset. the alm_asset has a reference field named 'ci' to the 'cmdb_ci' table.  'cmdb_ci' has the 'serial_number' field.  So adjust your script accordingly.

See Sulabh Garg's response.

[Bert_c1]

The following script logic worked for me (instance demo data has duplicates).

// Find alm_hardware record with duplicate serial_number values
// serial_number field is on cmdb_ci
var dup = new GlideAggregate('cmdb_ci');
dup.addAggregate('COUNT','serial_number');
dup.addHaving('COUNT','>', '1');
dup.groupBy('serial_number');
dup.query();

var arraylist = [];
var queryStr = 'ci.serial_numberIN';
while(dup.next()){
  arraylist.push(dup.getValue('serial_number'));
  queryStr += dup.serial_number + ',';
}
gs.info('Duplicate CIs' + arraylist);
gs.info('queryStr = ' + queryStr);

// Now check for record in alm_hardware where reference field ci.serial_number is one of the duplicates
var almHardware = new GlideRecord('alm_hardware');
almHardware.addEncodedQuery(queryStr);
almHardware.query();
gs.info("Found " + almHardware.getRowCount() + " records in alm_hardware.");
while (almHardware.next()) {
	gs.info('alm_hardware record: sys_id:' + almHardware.sys_id +' name:' + almHardware.display_name + ' is a duplicate.');
}