7 Replies Latest reply on Sep 28, 2018 10:34 PM by mesverrum

    Pulling IP Addresses with Node and Interface Data

    mjperkins

      I am trying to write a query to pull all IP addresses in an instance of SolarWinds and associate it with the correct node and interface information.

       

      I can get the list of IP Addresses from the Orion.NodeIPAddresses (SWQL) / NodeIPAddresses (SQL) table.

      I can join that on the numeric NodeID to the Orion.Nodes (SWQL) / NodesData (SQL) table to pick up the node name (Caption)

       

      There appear to be table(s) with interface names, like Cirrus.Interfaces (SWQL) / NCM_Interfaces (SQL) and Orion.NPM.Interfaces (SWQL) / NPM_Interfaces (SQL). It appears I could join the NPM Interface tables to the query via InterfaceIndex, but would not have interface names for all the interfaces. The Cirrus/NCM interface tables have far more entries, but use long, alphanumeric with dashes 'unique ids' that I cannot relate to the decimal NodeID and have InterfaceIndexes far longer than the other tables.

       

      Does anyone know

      1. How to translate the decimal NodeID and InterfaceIndex on most of the above tables to the unique id NodeID and longer InterfaceIndex on the Cirrus/NCM tables?
      2. What is the difference between interfaces listed on the NPM and NCM interface tables?
        • Re: Pulling IP Addresses with Node and Interface Data
          David Smith

          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.

            • Re: Pulling IP Addresses with Node and Interface Data
              mjperkins

              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.

                • Re: Pulling IP Addresses with Node and Interface Data
                  David Smith

                  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.

                    • Re: Pulling IP Addresses with Node and Interface Data
                      David Smith

                      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

                        • Re: Pulling IP Addresses with Node and Interface Data
                          mjperkins

                          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!

                      • Re: Pulling IP Addresses with Node and Interface Data
                        mesverrum

                        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