Pulling IP Addresses with Node and Interface Data

Hi, So I'm not sure if you're looking at NCM Tables because you only want to gather this information for devices you have in NCM, or is there something else we can use to narrow down the results?

This might get you started:

SELECT
n.NodeID
,n.Caption AS [Node Name]
,i.InterfaceID
,i.Name AS [Interface Name]
,ip.IPAddress AS [IP]
FROM Orion.NPM.Interfaces i
INNER JOIN Orion.NodeIPAddresses ip ON i.NodeID = ip.NodeID
INNER JOIN Orion.Nodes n ON i.NodeID = n.NodeID
WHERE ip.IPAddressType = 'IPv4'
ORDER BY n.Caption ASC

This will just give you a list of all the IP addresses in use on all of your SolarWinds, I have added a filter for IPv4 just as an example.

The objective is to get all live IP addresses in the SolarWinds database, so I am not interested in narrowing the results. I am only trying to get node and interface information to accompany as many of the IP addresses as I can. Said differently, I am only joining other tables to Orion.NodeIPAddresses to add node and interface names.

The problem is one of the tables that seems to carry the interface information (Cirrus.Interfaces) uses different keys than Orion.NodeIPAddresses and Orion.Nodes. I am having trouble finding a key common to that table to use in a JOIN or a translation table to use in translating one key (NodeID) to the other.

I should point out that Cirrus.Interfaces and Orion.NPM.Interfaces have different information. To get as much data about the IP addresses as I can, It appears that I need to JOIN both tables into the query. I am struggling on finding a key on which to JOIN them.

Sorry I think I misunderstood, do you mean something like this

SELECT ci.NodeID,ci.InterfaceID, ci.InterfaceIndex,ci.InterfaceDescription,n.Caption
FROM Cirrus.Interfaces ci
INNER JOIN Orion.Nodes n ON n.NodeID = ci.Node.CoreNodeID

If you post your progress so far I'll try and tweak it when I get home.

Hi mjperkins

Would something like this give you all the information you need?

SELECT
 ni.Node.Caption AS [Node Name] --This is inherited from the Orion.Nodes table
,ni.IPAddress AS [IP Address]
,ni.SubnetMask AS [Subnet Mask]
,ni.Interface.Caption AS [Interface Name] --This is inherited from the Orion.NPM.Interfaces table
FROM Orion.NodeIPAddresses ni

UNION ALL

(SELECT
 ci.Interfaces.Node.NodeCaption AS [Node Name] --This is inherited from the Cirrus.Nodes table which we have jumped to via the Cirrus.Interfaces table
,ci.IPAddress AS [IP Address]
,ci.SubnetMask AS [Subnet Mask]
,ci.Interfaces.InterfaceDescription AS [Interface Name] --This is inherited from the Cirrus.Interfaces table
FROM Cirrus.IpAddresses ci)

ORDER BY [Node Name] ASC, [IP Address] ASC

Let me know how you get on

Thanks for helping, dgsmith80​.

I am not seeing the inheritances you describe. I don't see an indication of them looking in SWQL Studio. How should I be able to see them? They do not show when expanding the tree in SWQL Studio's left panel or when using the "Generate Select Statement (with Inherited Properties)" command.

In fact, I think we are using different versions of something. My Orion.IPAddresses SWQL table has no SubnetMask field, only NodeID (System.Int32), InterfaceIndex (System.Int32), IPAddress (Ssytem.String), IPAddressN (System.Guid), and IPAddressType (System.String). It also has inherited properties Description (System.String), DisplayName (System.String), InstanceType (System.Type), and Uri (System.String). That does not match your query. I can say similar about my Cirrus.IPAddresses table. I could also have said from your earlier entry from 9/27 about the JOIN. I have no CoreNodeID field in my Cirrus.Interfaces table.

It seems we're looking at two different databases with different schemas. It seems if I had your schema, I might not have needed to ask this question.

FYI, I will likely be away from Thwack! until Wednesday, and unable to reply until then. Have a good weekend!

What version of Orion Core are you using, and what version of SWQL Studio?

So the way the relationships with Cirrus and Orion works is that NCM/Cirrus used to be a completely separate product and used GUIDs instead of the integer based id's.  To translate between NPM and Cirrus you can do something like this.  You might need to do some left or right joins depending on what you are looking for, but this is the general idea of the joins

SELECT whateveryouneed

FROM Cirrus.Interfaces ncmi

join cirrus.Nodes ncmn on ncmn.NodeID=ncmi.NodeID

join orion.npm.Interfaces npmi on npmi.index=ncmi.InterfaceIndex and npmi.nodeid=ncmn.CoreNodeID

I had some customers asking for a way to search for what node is using an IP address, and also on what interface. And we wanted a widget to show that. (yes, actually two customers on the same day asked that!!)

So I created below widget that lists all IP addresses that is used on all nodes, what interface that is bound to if the interface is monitored. Also a hint on if the IP is the one we poll the node on. (A * after the address)

 All that with links to node and interface details page - giving us that hoover popup menus also magically works.

Insert a "Custom Query widget" and add the code....

SELECT
N.Caption AS [Node],
ISNULL(i.caption,'-- Interface not monitored --') AS [Interface],
CASE
WHEN IP.IPAddress=N.IPAddress THEN CONCAT(IP.IPaddress,' *')
ELSE IP.IPaddress
END AS [IPAddress],
IP.Subnetmask AS [Mask],
N.DetailsUrl AS [_LinkFor_Node],
I.DetailsUrl AS [_LinkFor_Interface],
CONCAT('/NetPerfMon/Images/Vendors/',N.Icon) AS [_ICONFor_Node]
from Orion.NodeIPAddresses AS IP
LEFT OUTER JOIN Orion.NPM.Interfaces I on
(IP.nodeid = I.nodeid AND IP.InterfaceIndex = I.interfaceindex)
INNER JOIN Orion.Nodes N ON IP.Nodeid=N.NodeID
Order by N.Caption

-- Search query:

SELECT
N.Caption AS [Node],
ISNULL(i.caption,'-- Interface not monitored --') AS [Interface],
CASE
WHEN IP.IPAddress=N.IPAddress THEN CONCAT(IP.IPaddress,' *')
ELSE IP.IPaddress
END AS [IPAddress],
IP.Subnetmask AS [Mask],
N.DetailsUrl AS [_LinkFor_Node],
I.DetailsUrl AS [_LinkFor_Interface],
CONCAT('/NetPerfMon/Images/Vendors/',N.Icon) AS [_ICONFor_Node]
from Orion.NodeIPAddresses AS IP
LEFT OUTER JOIN Orion.NPM.Interfaces I on
(IP.nodeid = I.nodeid AND IP.InterfaceIndex = I.interfaceindex)
INNER JOIN Orion.Nodes N ON IP.Nodeid=N.NodeID
WHERE (IP.IPAddress LIKE '%${SEARCH_STRING}%' OR i.caption LIKE '%${SEARCH_STRING}%' OR N.Caption LIKE '%${SEARCH_STRING}%')
Order by N.Caption

Hope you like it!

An interesting mod (if possible) would be to input a series of IP's at once.  I'm thinking if I had a traceroute of 5 hops, it would be awesome to drop that list in the search string to return that data.

Has anyone tried this?