0 Replies Latest reply on Dec 4, 2017 11:12 AM by hpstech

    SERIAL NUMBER INVENTORY QUERY - EXCLUDE COMPONENTS

    hpstech

      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)