Blog

Use Excel to query Duplicate CI Relationships

I have another article about duplicate record scripts.  That post covers how to find a duplicate in one column of table in ServiceNow.

However for the CI Relationship table, there are two columns (Parent and Child) that determine unique.  Here is a trick using excel to find duplicates in the CI Relationship table.

Step 1: ServiceNow Data Export

  1. In the left navigator, type cmdb_rel_ci.list

  2. Build your filter that you want to check for duplicates.

  3. Right click header bar Export > Excel

Step 2: Excel Find Uniques

  1. Open exported file in Excel

  2. Ribbon > Data > Sort

  3. Sort by Parent and then by Child

  4. Click Ok

  5. Add Column, Label: Unique

  6. Select Parent and Child Columns

  7. Ribbon > Data > Advanced

  8. Click "Unique" Checkbox

  9. Click Ok

  10. Add an "X" to all the unique rows

  11. Ribbon > Data > Clear

Step 2: Excel Find Duplicates

  1. In Excel, Ribbon > Filter

  2. On the Unique column you created, use the filter to find the blanks

Now you have a list of duplicates.  You can now manually deactivate or remove these duplicates.  For a large list, you can build a OR gliderecord query to remove them..

Hope that helps,

Mike