Queries to determine if two devices are duplicates

Owned by Former user (Deleted)
Oct 31, 2018

In certain circumstances, we can end up with a duplicate device entry in the database.

The following queries can be useful in determining if two apparently duplicated entries are indeed duplicates

Find hosts with matching hostname and identifier

You might want to uncomment the where identifier=...and customize the identifier to narrow the search scope.

Find duplicate devices by Hostname
Select  d.Hostname, ui.ProtocolID, ui.Identifier, COUNT(*)
from model.t_Device d
join model.t_ExtensibleObjectLink eol on eol.ExtensibleObjectID = d.DeviceID and eol.ExtensibleObjectType = N'model.t_Device'
join model.t_UniqueIdentifier ui on ui.UniqueIdentifierID = eol.LinkObjectID and eol.LinkObjectType = N'model.t_UniqueIdentifier'
-- WHERE Identifier = 'xWsi/vt1KuW6HsmRiZF5Dg=='
Group by d.Hostname, ui.ProtocolID, ui.Identifier
Having COUNT(*) > 1

Quick test for name uniqueness

Uniqueness tests
Select 'Unique DeviceID' Test, Count(Distinct DeviceID) devs from model.t_Device d
UNION
Select 'Unique UniversalID' Test, Count(Distinct UniversalID) devs from model.t_Device d
UNION
Select 'Unique Hostname' Test, Count(Distinct Hostname) devs from model.t_Device d
UNION
Select 'Unique Qualifier' Test, Count(Distinct d.DeviceID) devs from model.t_Device d -- 
       join model.v_DeviceQualifiedName dq on dq.DeviceID = d.DeviceID 
UNION
Select 'Unique UID' Test, Count(Distinct DeviceID) devs from model.t_Device d
       join model.t_ExtensibleObjectLink eol on eol.ExtensibleObjectID = d.DeviceID and eol.ExtensibleObjectType = N'model.t_Device'
       join model.t_UniqueIdentifier ui on ui.UniqueIdentifierID = eol.LinkObjectID and eol.LinkObjectType = N'model.t_UniqueIdentifier'