Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials

SWQL UDT Report - How to pull data from multiple tables?

Hi, looking for some SWQL assistance as a beginner.

I have a working report that I'm using to pull UDT data on phones connected to our network. The phones only have 3 OUIs so I'm matching that. This works great, but now I'd like to supliment the data I'm pulling with lldp data from another table.

I think I'm going to need to JOIN or UNION but I'm very new at this and haven't wrapped my head around it yet.

Here is the base (working) query.

SELECT
m.NodeAccessPoint AS [NodeName],
m.PortName AS [PortName],
m.PortDescription AS [PortDescription],
m.MacAddress AS [MACAddress],
m.IPAddress AS [IPAddess],
m.MACVendor AS [MACVendor],
m.OperationalStatus AS [Status],
m.LastSeen AS [LastSeen]

FROM ORION.UDT.MACCurrentInformation m

WHERE m.MACAddress like '64:16:7F:%'
OR m.MACAddress like '00:90:8F:%'
OR m.MACAddress like '00:04:F2:%'

GROUP BY m.NodeAccessPoint, m.PortName, m.PortDescription, m.MacAddress, m.IPAddress, m.MACVendor, m.OperationalStatus, m.LastSeen

I'd like to suppliment it with

Table ORION.UDT.LldpEntries
Field RemoteSystemName

And I think I should be able to match ORION.UDT.LldpEntries RemotePortId (which is an unformated mac-address value for the LLDP neighbor) with the mac addresses in ORION.UDT.MACCurrentInformation RawMAC.

Am I on the right track here? I've made some attempts but must be structuring it wrong because it keeps throwing an error.

Find more posts tagged with

Accepted answers

kevinsnyder

Got it. I just needed to spend some more time.

First point of confusion was when using Database Manager the table I wanted to used is named differently?

DatabaseManager [UDT_LldpEntries]

ORION.UDT.LldpEntry

Which I found here. http://solarwinds.github.io/OrionSDK/schema/index.html

Then it was just a matter of making sure all the field and syntax names were correct using the schema linked above.

Here is the working SWQL query. If anyone can suggest any optimizations that I could use please let me know.

SELECT
m.NodeAccessPoint AS [NodeName],
m.PortName AS [PortName],
m.PortDescription AS [PortDescription],
m.MacAddress AS [MACAddress],
m.IPAddress AS [IPAddess],
m.MACVendor AS [MACVendor],
s.RemoteSystemName AS [LLDP],
m.LastSeen AS [LastSeen]

FROM ORION.UDT.MACCurrentInformation m

JOIN ORION.UDT.LldpEntry s on s.RemotePortID = m.RawMac

WHERE m.MACAddress like '64:16:7F:%'
OR m.MACAddress like '00:90:8F:%'
OR m.MACAddress like '00:04:F2:%'

All comments

kevinsnyder

Got it. I just needed to spend some more time.

First point of confusion was when using Database Manager the table I wanted to used is named differently?

DatabaseManager [UDT_LldpEntries]

ORION.UDT.LldpEntry

Which I found here. http://solarwinds.github.io/OrionSDK/schema/index.html

Then it was just a matter of making sure all the field and syntax names were correct using the schema linked above.

Here is the working SWQL query. If anyone can suggest any optimizations that I could use please let me know.

SELECT
m.NodeAccessPoint AS [NodeName],
m.PortName AS [PortName],
m.PortDescription AS [PortDescription],
m.MacAddress AS [MACAddress],
m.IPAddress AS [IPAddess],
m.MACVendor AS [MACVendor],
s.RemoteSystemName AS [LLDP],
m.LastSeen AS [LastSeen]

FROM ORION.UDT.MACCurrentInformation m

JOIN ORION.UDT.LldpEntry s on s.RemotePortID = m.RawMac

WHERE m.MACAddress like '64:16:7F:%'
OR m.MACAddress like '00:90:8F:%'
OR m.MACAddress like '00:04:F2:%'

kevinsnyder

Actually had to go one further. Had to make it a LEFT OUTER JOIN and match on the IP address rather than the MAC address to get better results. The mac address field on my lldp table is a unicode mess for maybe half of the entries.

I'll have to go back and check SNMP access and maybe open a support ticket.