We have found that there are duplicates in our serial number inventory report because it’s including all of the different components of the device (power supply, fan, internet card etc.), and some of the duplicates are individual switches in a switch stack. (A Cisco SW-Stack appears 8 times because there are 8 switches in that stack).
Can anyone please suggest on how to only include the chassis of each device in our query? Or if that is possible?
Many sincere thanks
==========================================================================
select * from(
SELECT --DISTINCT TOP 3000
anodes.NodeID
,Serial
,DeviceType
--,CoreNodeID
,Customer
,NodeCaption
,AgentIP
,Vendor
,SysName
,OSImage
,OSVersion
,SysContact
,SysLocation
,ChangepondNotes
,City
,Rack
,Comments
,Datacenter
,MachineType
,LastBoot
,NodeGroup
,ConfigTypes
,NodeComments
,LoginStatus
,Name Name_Part
,LastInventory
,ReverseDNS
,ResponseTime
,LastUpdateTime
,LastRediscoveryTime
--,ConnectionProfile
,EndOfSupport
,EndOfSales
,AssetTag
,CarrierContactInformation
,CarrierPhone
,EnvironmentType
,HPSBusinessOwner
,HPSBusinessOwnerBackup
,Power
,SLA_Node
,SupportContractDate
,SNMPLevel
,Status
,StatusText
,SystemOID
,SysDescr
FROM SolarWindsOrionDB.dbo.NCM_Nodes anodes
inner join SolarWindsOrionDB.dbo.NCM_Entity_Physical aphysicals on
anodes.NodeID = aphysicals.NodeID
) aa
left join(
select nodeid from(
SELECT --DISTINCT TOP 3000
anodes.NodeID
,Serial
,DeviceType
--,CoreNodeID
,Customer
,NodeCaption
,AgentIP
,Vendor
,SysName
,OSImage
,OSVersion
,SysContact
,SysLocation
,ChangepondNotes
,City
,Rack
,Comments
,Datacenter
,MachineType
,LastBoot
,NodeGroup
,ConfigTypes
,NodeComments
,LoginStatus
,Name Name_Part
,LastInventory
,ReverseDNS
,ResponseTime
,LastUpdateTime
,LastRediscoveryTime
--,ConnectionProfile
,EndOfSupport
,EndOfSales
,AssetTag
,CarrierContactInformation
,CarrierPhone
,EnvironmentType
,HPSBusinessOwner
,HPSBusinessOwnerBackup
,Power
,SLA_Node
,SupportContractDate
,SNMPLevel
,Status
,StatusText
,SystemOID
,SysDescr
FROM SolarWindsOrionDB.dbo.NCM_Nodes anodes
inner join SolarWindsOrionDB.dbo.NCM_Entity_Physical aphysicals
on anodes.NodeID = aphysicals.NodeID
) a group by nodeid having count(1) > 1
) bb
on aa.NodeID = bb.NodeID
where (bb.nodeid is not null and serial <> '')
or (bb.nodeid is null)
