From time to time SCCM generates duplicate CI’s and a SCCM Duplicate CI cleanup is recommended. I put together a WQL query to find all the duplicates within SCCM so I could perform regular cleanup. Post running the query, you can delete all the CI’s results returned. The healthy ones will auto-populate when a system scan is run. But keep in mind, the ones used in a Direct Query, will get affected as they may have membership relationships to inclusion and exclusion collections.

As long as you use Dynamic Queries, than this strategy should work just fine. In the event you use Direct Query, than a further round of assessment would be needed before you delete the CI’s based on attributes like ‘Client Activity’ or ‘Last Activity’.

select R.ResourceID,R.ResourceType,R.Name,R.SMSUniqueIdentifier,R.ResourceDomainORWorkgroup,R.Client 
from SMS_R_System as r 
full join SMS_R_System as s1 on s1.ResourceId = r.ResourceId 
full join SMS_R_System as s2 on s2.Name = s1.Name 
where s1.Name = s2.Name and s1.ResourceId != s2.ResourceId