This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.

You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Easy Node IP and Interface IP SWQL Query

designerfx over 4 years ago

Today with the help of wluther I was able to create a very simple and useful query:

SELECT IPAddress as [interfaceIP] --IP Assigned to the interface specifically
,IpAddresses.Interfaces.InterfaceDescription
,IPAddresses.Interfaces.Interfacealias as [Alias]
,IPAddresses.Interfaces.Node.NodeCaption as [Node]
,IPAddresses.Interfaces.Node.AgentIP as [PollingNodeIP]--IP Assigned to relevant node
FROM Cirrus.IpAddresses
WHERE (IPAddresses.Interfaces.Node.YOURCUSTOMPROPERTY LIKE 'VALUE' AND IPAddress LIKE '172.17.%') OR (IPAddresses.Interfaces.Node.YOURCUSTOMPROPERTY LIKE 'VALUE' AND IPAddress LIKE '172.18.%')

This is possibly the shortest form of this type of query I've created but will let you grab an interface, it's caption, it's IP address, the node's IP and it's caption, and smush it all together reasonably nicely to search for things with problems. I use this to find when a WAN link was misconfigured.

I'm about 99% sure this requires NCM, thus this is posted there.Result using SiteID/Alias, but hiding PollingNodeIP:

Top Replies

0 zackm over 4 years ago

Love it! You can make this report outside of NCM as well, for those who don't have the module:

SELECT
ip.Node.Caption AS [Node]
,ip.Node.DetailsUrl AS [_LinkFor_Node]
,ip.Node.IP_Address AS [PollingNodeIP]
,ip.Interface.Caption AS [Interface]
,ip.Interface.DetailsUrl AS [_LinkFor_Interface]
,ip.IPAddress AS [InterfaceIP]
,ip.Interface.TypeName AS [InterfaceType]
FROM Orion.NodeIPAddresses AS [ip]
-- only return interfaces we are actively monitoring
WHERE ip.Interface.InterfaceID IS NOT NULL 
ORDER BY [Node], [Interface]

0 designerfx over 4 years ago

I believe nickzourdos specifically wanted that.
Cancel
Vote Up 0 Vote Down

Cancel
0 nickzourdos over 4 years ago

Yeah, I'm looking for a way to apply a SWQL filter to a custom chart that uses this idea. I only want to chart specific interfaces based on their IP. I'm trying to learn enough about SWQL to be able to apply this kind of thing to a filter
Cancel
Vote Up +1 Vote Down

Cancel
0 designerfx over 4 years ago

nickzourdos in the sample above I included how to filter for custom properties (multiple, even). You can do where IN ('value','value','value') if you don't need wildcards, but otherwise it's where X LIKE (value) and where Y like (value) or (value). Thus parenthesis above.
Cancel
Vote Up 0 Vote Down

Cancel

0 nickzourdos over 4 years ago

Turns out it's a lot more involved. zackm birthed this beautiful baby yesterday:

SELECT
i.Traffic.ObservationTimestamp AS [TIME]
,i.FullName AS [INTERFACE]
,AVG(i.Traffic.OutMaxbps) AS [MAX XMT]
FROM Orion.NPM.Interfaces AS [i]
JOIN (
SELECT TOP 10
a.Traffic.InterfaceID
,AVG(a.Traffic.OutMaxbps) AS [xmt]
FROM Orion.NPM.Interfaces AS [a]
WHERE a.IPAddress.IPAddress LIKE '10.%.0.99'
AND a.Traffic.ObservationTimestamp > ADDHOUR(-2,GETUTCDATE())
GROUP BY a.Traffic.InterfaceID
ORDER BY [xmt] DESC
) AS x ON x.InterfaceID = i.InterfaceID
AND i.Traffic.ObservationTimestamp > ADDHOUR(-2,GETUTCDATE())
GROUP BY i.Traffic.ObservationTimestamp, i.FullName

0 designerfx over 4 years ago

nickzourdos yeah if we're talking NTA this is an entirely different beast.
Cancel
Vote Up 0 Vote Down

Cancel