SCCM Collection Query to get Duplicate Hostnames in SCCM console

Wherever I have worked I have seen the problem with duplicate hosts caused the machine count un-reliable. The records won’t match with asset management in a controlled environment.

If someone asks me to get the machine count, first I will check the duplicate host names and remove / resolve them before getting the final count of the machines.

I have created a new collection using below SQL Query to get the duplicate machines from the environment and selected “Dynamically add new resources”. So as soon as a new duplicate record creates that will add to this collection.
********************************************
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  and R.Client = null
********************************************

More SCCM collection queries can be found here