SCCM Configmgr collection SQL identify duplicate computer records with different GUID

I was working on SCCM report for client health dashboard. During this report creation ,found that ,device appear twice with different GUID ID and resource ID but with same hostname.

So i started looking at this issue to see how identify the records with duplicate hostnames.

SCCM clients are uniquely identified by a GUID. A GUID is a combination of the client's media access control (MAC) address and the time when the GUID is assigned.

This combination produces a number that is virtually always unique. The GUID assignment occurs during the client discovery and installation processes.

The GUID is stored in the client's Registry and in a binary file on the client's hard disk into smscfg.ini file (C:\Windows\SMSCFG.INI)

As you see below snapshot ,computer record appear twice with the information that was gathered through inventory/BGB/discovery.

Take a look at the following screenshots with 3 different problems .

Device with different resource ID and Client=Yes

With this information ,i started looking at SQL to write code and convert that to collection ,so it would be easy to cleanup records in automated way.

Device with different resource ID and client =No

Device with different resource ID and client=No

So i went to site hierarchy settings to see the conflict records but the settings applied correctly:

Why did this happens ? Old article but still valid though https://support.microsoft.com/en-us/help/837374/how-to-locate-and-clean-advanced-client-duplicate-guids-in-sms-2003

If you have maintenance task enabled ,these obsolete or inactive stale records taken care by that but do want to wait until the default maintenance task runs ?

Here is the SQL code to find out the list of devices with appear in SCCM console with its count.

select name0 [Device Name],count(*) Total from v_r_system
group by name0
having (count(name0))>1
order by Name0

If you want to see the device that appear maximum times in the top ,use the following query:

select name0 [Device Name],count(*) Total from v_r_system
group by name0
having (count(name0))>1
order by 2 desc

If you want to see the list of all devices with its resource ID ,use the following query:

select sys.name0,sys.ResourceID from v_r_system as sys
full join v_r_system as sys1 on sys1.ResourceId = sys.ResourceId
full join v_r_system as sys2 on sys2.Name0 = sys1.Name0
where sys1.Name0 = sys2.Name0 and sys1.ResourceId != sys2.ResourceId
group by sys.Name0,sys.ResourceID
order by 1

Create WQL Collection with following syntax:

I am making use of SMS_R_System with full join.

select sys.ResourceID,sys.ResourceType,sys.Name,sys.SMSUniqueIdentifier,
sys.ResourceDomainORWorkgroup,sys.Client from SMS_R_System as sys
full join SMS_R_System as sys1 on sys1.ResourceId = sys.ResourceId
full join SMS_R_System as sys2 on sys2.Name = sys1.Name
where sys1.Name = sys2.Name and sys1.ResourceId != sys2.ResourceId

P.S: The above queries are only used to find the computer names appear twice or more with different resource ID,GUID etc

Also note ,this collection includes active/live entry along with inactive entry .I could not find any way/logic to skip the active computers .

you can delete all these records  manually or create a powershell script with schedule to empty the collection . This way ,you loose the inventory of active computers but they send back in the next inventory cycle.

Hope it helps!