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
Store

Multiple IP address results - SWQL Query question

From a previous question, I was able to get the IP address(s) in question however I ran into another issue. The results which should provide an IP address along with an "unknown" IP address based on the two interfaces that I filtered against instead show multiple results for the same two interfaces roughly 39 times. I believe this may be related to timing or historical data.

Does anyone know how to restrict the results to the most recent or something similar? I found the link below so I am doing a SELECT or JOIN on something that is grabbing additional detail. Any help is greatly appreciated.

(+) SWQL Query to pull IP Info - Forum - Network Performance Monitor (NPM) - THWACK (solarwinds.com)

SELECT TOP 100 Nodes.interfaces.InPercentUtilizationThreshold.CurrentValue AS InPercentUtilizationThreshold_CurrentValue
     , Nodes.interfaces.OutPercentUtilizationThreshold.CurrentValue AS OutPercentUtilizationThreshold_CurrentValue
     , Nodes.interfaces.CustomProperties.Comments AS CP_Comments
     , Nodes.interfaces.Caption
     , Nodes.interfaces.Index
     , round((Nodes.interfaces.Inbandwidth / 1073741824.0),2) as TotalSize
     , Nodes.interfaces.StatusDescription
     , Nodes.interfaces.DetailsUrl
     , Nodes.NodeIPAddresses.IPAddress AS IP
     , Nodes.NodeIPAddresses.SubnetMask
     , Nodes.Caption AS Device_Name

FROM Orion.Nodes as Nodes

WHERE  Nodes.interfaces.CustomProperties.Comments LIKE 'XXXX'

Find more posts tagged with

swql query

dashboard

SWQL

SWQL Studio

orion sdk

Accepted answers

All comments

smalleggo

Everything works if I remove the IPAddress and SubnetMask SELECT statements which correctly shows the two interfaces I am filtering against. Once I add either of those SELECT statements I get the 70-90 results. For reference the Interface Details page for each shows either the single IP address or an Unknown. I can get the IP/Subnet mask using another query shown below (from the link above) but that filters out the "unknown" trunk port. If I find out how to resolve this myself I'll update the post.

SELECT 
n.caption AS [Caption]
,n.sysname AS [System Name]
,ni.IPAddress AS [IP Addresses]
,ni.SubnetMask AS [Subnet]
,ni.Interface.caption AS [Description]
FROM Orion.Nodes n
JOIN Orion.NodeIPAddresses ni ON n.nodeid=ni.nodeid

WHERE n.sysname LIKE 'XXXXXXX%'

mesverrum

You probably should do a manually defined join instead of just using the navigation properties across all those tables. When you bring in the Nodes.NodeIPAddresses tables then you are getting a row for every IP * every interface because it is just joining at the nodeid level. A DBA would call it a cartesian product if you are curious to learn more about what is jamming you up there.

You might be able to fix that with minimal modifying of your existing query by adding a where condition with something like

where Nodes.NodeIPAddresses.InterfaceIndex = Nodes.interfaces.Index

smalleggo

That makes sense to me. I figured it was something like that.

What you provided helps however one of the interfaces is a trunk with no IP address so I need to figure out a different table in this instance as there is not InterfaceIndex related to it. Might need to add at least two JOIN statements to get this working.