Finding duplicate systems in SCCM
Fire up SQL Management Studio and use som of the following queries:
Find duplicate MAC addresses
SELECT dbo.v_RA_System_MACAddresses.MAC_Addresses0, Count(dbo.v_R_System.Name0) AS SystemCount FROM dbo.v_R_System RIGHT OUTER JOIN dbo.v_RA_System_MACAddresses ON dbo.v_R_System.ResourceID = dbo.v_RA_System_MACAddresses.ResourceID GROUP BY dbo.v_RA_System_MACAddresses.MAC_Addresses0 ORDER BY SystemCount DESC
Find ResourceID for the duplicate MAC address
select * from dbo.v_RA_System_MACAddresses where MAC_Addresses0 = '00:00:00:00:00:00'
Find Name0 for the duplicate MAC addresses
select * from dbo.v_R_System where ResourceID = '12345678' OR resourceid = '12345678'
Find duplicate computer names
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
Find duplicate HardwareID's
SELECT Name0, Hardware_ID0, Count(Hardware_ID0) AS SystemCount FROM dbo.v_R_System GROUP BY Hardware_ID0, Name0 ORDER BY SystemCount DESC
