How to delete multiple duplicate records from cloud resource table.

[HrishabhKumar]

I have 50000, records in cloud resource [cmdb_ci_cmp_resource] table.

There are multiple records with duplicate Object ID [object_id], I have to remove all the duplicates and keep one record for each Object ID i.e. the latest created one.

I want to know all the possible ways to do it, and which would be the best way to do it.

Thanks 

[Sneha KH]

Hello @HrishabhKumar ,
Best way to remove duplicates is using Duplication remediator tool by solving de-duplication tasks.
Refer this: https://support.servicenow.com/kb?id=kb_article_view&sysparm_article=KB0869861

[Tanushree Maiti]

Hi @HrishabhKumar 

DeDuplication task is the best approch for your requirement .

Refer this post: https://www.servicenow.com/community/cmdb-forum/duplicate-ci-cleanup-in-cmdb/m-p/225681#:~:text=Can%....

https://www.servicenow.com/docs/r/api-reference/server-api-reference/CMDBDuplicateTaskUtilsAPI.html

[shubhamseth]

@HrishabhKumar @I can think of two options- 

1. if you have proper logic to filter out the one which you want to target to delete then best option to use “Delete Job” to delete the record from the table using filter condition. 

2. Try building one time script with your proper logic to get the list of devices that you want to clean up and run the fix script to clean up. That you can roll back also if something happens.