Delete large number of duplication of CI's (computer)

[Service now11]

Hi,

I need to delete duplicates of cmdb_ci_computer. It is having large number of records. But The difficult part is I don't know the unique column to identify. Because the name of the CI having many serial numbers, IP address etc...As the name also it has many records. I am not sure about the correlation ID. The correlation ID may be unique. So I need the column i.e Install Status should not retired. So Kindly help on this and provide me a solution.

Thanks.

[Ratnakar7]

Hi @Service now11 ,

Deleting duplicate records of cmdb_ci_computer where the Install Status is not retired can be challenging without a clear unique identifier. However, you can use a combination of fields to identify potential duplicates and then decide on a strategy to delete them. Here's a general approach:

1. Identify potential unique identifiers: Review the structure of the cmdb_ci_computer table and analyze which combination of fields could potentially serve as unique identifiers. Look for fields that are less likely to have duplicates, such as serial number, model ID, or manufacturer.

2. Write a query to identify potential duplicates: Use the identified fields to group records and count the number of occurrences. This will help you identify potential duplicates.

3. Analyze the results: Review the results of your query to determine which records are likely duplicates. Pay attention to fields like name, serial number, and other identifying information.

4. Decide on a deletion strategy: Once you've identified potential duplicates, decide on a strategy for deleting them. This could involve keeping only one record for each unique combination of identifiers or selecting a single record based on criteria such as creation date or correlation ID.

5. Test your deletion strategy: Before executing any deletion commands, test your strategy in a non-production environment to ensure it produces the desired results and does not unintentionally delete important data.

6. Execute the deletion: Once you're confident in your deletion strategy, execute the necessary SQL commands to delete the duplicate records from the cmdb_ci_computer table.

Thanks,

Ratnakar

[Sohail Khilji]

Hi @Service now11 

Refer my article : ServiceNow Duplicate CIs Solution / Script / Report 

I hope this helps...