CI Relationship Duplicate records clean up

[Mihir Mohanta]

Many users come across the scenario where some duplicate records created in ci relationship(cmdb_rel_ci) table.

It is always a difficult task to find out and clean up those duplicate records as thousands of duplicate records might present in ci relationship table.

Below are some methods using which you can clean up duplicate records in ci relationship(cmdb_rel_ci) table.

1. By running background script

Below is the sample code.You can add more filters there if you want to perform it for some specific ci class.

var rel= new GlideAggregate('cmdb_rel_ci');
rel.groupBy('parent');
rel.groupBy('child');
rel.groupBy('type');
rel.addHaving('COUNT', '>', 1);
rel.query();
     while (rel.next()) {
var del= new GlideRecord('cmdb_rel_ci');   
     del.addQuery('parent', rel.parent);
del.addQuery('child', rel.child); 
del.addQuery('type', rel.type);         
     del.query();   
     del.next(); // Skip the first result   
     while (del.next()) { // delete the rest   
             del.deleteRecord();   
     }   
 }

2. Through Export as excel

(This method is not recommended by me)

Other way through which this can be achieved is as follows:

* Go to cmdb_rel_ci.list

* Right click on the column header > Export >Excel

* Open the Excel

*Remove the duplicate records in the excel sheet.

*Now you have the excel sheet which does not contain any duplicate rows.

*Remove all records in the cmdb_rel_ci table.

*Import the excel file to cmdb_rel_ci table through transform map.

Note:

If less numbers of duplicate rows are present in the excel sheet then you can delete the duplicate records manually by comparing them with records of cmdb_rel_ci table.

Below are some product documentation where you can find the duplicate/orphan relationships

Use the CMDB dashboard and health jobs to detect duplicate relationships.

https://docs.servicenow.com/bundle/istanbul-servicenow-platform/page/product/configuration-managemen...

You can enable the job "CMDB Health Dashboard - Relationship Compliance Processor" as follows:

https://docs.servicenow.com/bundle/istanbul-servicenow-platform/page/product/configuration-managemen...

You can write a business rule in cmdb_rel_ci table to restrict duplicate records creation.

Advanced : True

When to run : Before insert/update

Script:

(function executeRule(current, previous /*null when async*/) {
// Add your code here
var ci= new GlideRecord('cmdb_rel_ci');   
     ci.addQuery('parent', current.parent);
ci.addQuery('child', current.child); 
ci.addQuery('type', current.type);         
     ci.query();   
     if (ci.next()) {   
   gs.addErrorMessage("Relationship record already present.Duplicate records cant be created");
           current.setAbortAction(true);
     }   
})(current, previous);

Lastly while using the transform map to bulk import ci relationship records, design the transform map properly so that no duplicate ci relationship records will be created.