How to get Routers\Switch information with Service Tag.

I am trying to do a API call to get our routers and switches from solarwinds but can't work out how to join two tables.

I have this query that gets all the devices

SELECT NodeID, IPAddress, Caption, NodeDescription, Description, DNS, SysName, Vendor, IOSImage, IOSVersion, MemoryAvailable, MachineType, IsServer, ChildStatus, TotalMemory, SNMPVersion, Community, RWCommunity, IP, IP_Address, NodeName, EntityType, DisplayName
FROM Orion.Nodes

And I also have this one that gets the ServiceTag

SELECT ParentObjectName, Manufacturer, Model, ServiceTag
FROM Orion.HardwareHealth.HardwareInfoBase

And I want to join them on "ParentObjectName = SysName" But i just can't seem to work out how.

Find more posts tagged with

help

sql

Join

solarwinds

SWQL

servicetag

Accepted answers

All comments

mesverrum

SELECT n.NodeID, n.IPAddress, n.Caption, n.NodeDescription, n.Description, n.DNS, n.SysName, n.Vendor, n.IOSImage, n.IOSVersion, n.MemoryAvailable, n.MachineType, n.IsServer, n.ChildStatus, n.TotalMemory, n.SNMPVersion, n.Community, n.RWCommunity, n.IP, n.IP_Address, n.NodeName, n.EntityType, n.DisplayName
, h.ParentObjectName, h.Manufacturer, h.Model, h.ServiceTag
FROM Orion.Nodes n
join Orion.HardwareHealth.HardwareInfoBase h on h.ParentObjectName = n.sysname

https://www.w3schools.com/sql/sql_join.asp

Thanks @mesverrum that was enough for me to work it out.
It was indeed because I was not matching on a unique field. When i switched to NodeID and ParentObjectID it works.

SELECT n.NodeID, n.IPAddress, n.Caption, n.NodeDescription, n.Description, n.DNS, n.SysName, n.Vendor, n.IOSImage, n.IOSVersion, n.MemoryAvailable, n.MachineType, n.IsServer, n.ChildStatus, n.TotalMemory, n.SNMPVersion, n.Community, n.RWCommunity, n.IP, n.IP_Address, n.NodeName, n.EntityType, n.DisplayName,h.ParentObjectID, h.ParentObjectName, h.Manufacturer, h.Model, h.ServiceTag, c.InstanceType, c.City, c.Comments, c.Contact_Email, c.Environment, c.Latitude, c.Longitude, c.NETWORK_PIN, c.Region, c.Site_Code, c.Support_Team, c.Website_String
FROM Orion.Nodes n
JOIN Orion.HardwareHealth.HardwareInfoBase h ON h.ParentObjectID = n.NodeID
JOIN Orion.NodesCustomProperties c ON c.NodeID = n.NodeID
WHERE n.Vendor = 'CISCO' AND c.NETWORK_PIN <> 'WAN-RTR (Cisco)'