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.
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
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