cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 8

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

Jump to solution

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.

1 Solution
Level 8

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]

vs

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:%'

 

 

 

View solution in original post

2 Replies
Level 8

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]

vs

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:%'

 

 

 

View solution in original post

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.

0 Kudos