Does anyone have an asset report to show duplicate CI's?

[ladrake]

We have duplicate CI records that we need to purge from the system, but I'm not sure of how to build a report or script to identify these duplicates.   It seemed that there was a previous method in place for versions older than Fuji.

[Ken_Michelson]

Larry,

It would be very difficult to provide a "blanket" report that would apply to all situations of finding duplicates in the CMDB, which is probably why ServiceNow removed the functionality. I can make suggestions, but you would really need to define what it means to be a duplicate.

Here's a quick Database View that can start you on your way.

WHERE clause of c2:

c2_serial_number = c1_serial_number && c2_sys_id != c1_sys_id

Add only the fields you need on each table (I added sys_id, serial_number, and name) and here is the result [noting that sample data is horrible:

-Ken Michelson

[ladrake]

Hi Ken,

Thanks for the quick response.   I tried the new database view and it definitely got me moving in the right direction.   Somewhere along the way we received some poor data and I'll be able to use this as a starting point for cleaning it up.

Thanks again,

Larry

[Ken_Michelson]

Good luck. you can script it as well, but for something like this you can keep referencing and even write a report off of the Database View.

[mjdoolit1]

Hi Larry,

Here is a modified script from another person's response for a single field. You can add as many columns as you need to the [fields] array:

var fields = ['name', 'ip_address', 'manufacturer'];

gs.print(getDuplicates('cmdb_ci', fields));

function getDuplicates(tablename,val) {

  var dupRecords = [];

  var gaDupCheck = new GlideAggregate(tablename);

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

  gaDupCheck.addQuery('active','true');

  gaDupCheck.addAggregate('COUNT',val[i]);

  gaDupCheck.addNotNullQuery(val[i]);

  gaDupCheck.groupBy(val[i]);

  gaDupCheck.addHaving('COUNT', '>', 1);

  gaDupCheck.query();

  while (gaDupCheck.next()) {

  dupRecords.push(gaDupCheck[val[i]].toString());

  }

  }

  return dupRecords;

}