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