5 Replies Latest reply on Oct 15, 2015 8:31 PM by t1n0m3n

    SQL Query Help

    t1n0m3n

      I am trying write a report to find all IP addresses that are 10.10.x.x on all nodes with interfaces that have a status of UP.

      I am putting this in the web report part of Solarwinds.  I am on the latest version of NPM and on SQL 2008R2

       

      SELECT node.Caption, ip.IPAddress, if.Status FROM NodeIPAddresses as IP

      LEFT JOIN NodesData as NODE ON (ip.NodeID=node.NodeID)

      LEFT JOIN Interfaces as IF ON (node.NodeID=if.NodeID)

      WHERE ip.IPAddress LIKE '10.10%' and if.Status=1

      ORDER by node.Caption;

       

      This doesn't seem to be a valid request according to Solarwinds.  What am I doing wrong?

        • Re: SQL Query Help
          njoylif

          "if" is reserved word.  try ifi or just i

            • Re: SQL Query Help
              t1n0m3n

              Ahh, ok that makes sense.  Thanks!

              Marked as correct.

              Thanks!

               

              I now have a different issue:

               

              ----------Original----------

              This query is the base query that I was working from:

              SELECT node.Caption, ip.IPAddress FROM NodeIPAddresses as IP

              LEFT JOIN NodesData as NODE ON (ip.NodeID=node.NodeID)

              WHERE ip.IPAddress LIKE '10.10%'

              ORDER by node.Caption;

               

              This query returns all of the interfaces that have 10.10.x.x and no repeats.

               

              ----------Modified----------

              SELECT node.Caption, ip.IPAddress, int.Status FROM NodeIPAddresses as IP

              LEFT JOIN NodesData as NODE ON (ip.NodeID=node.NodeID)

              LEFT JOIN Interfaces as INT ON (node.NodeID=int.NodeID)

              WHERE ip.IPAddress LIKE '10.10%' and int.Status=1

              ORDER by node.Caption;

               

              This query results in a LOT of duplicate IP addresses.

               

              My intention was to filter out the down and shutdown interfaces from the original query.

                • Re: SQL Query Help
                  Craig Norborg

                  Come to the Dark Side of the force t1n0m3n and learn SWQL, it will make your life easier!!   Here is your requested query in SWQL, note no JOIN's!

                   

                  SELECT IP.Node.Caption, ip.IPAddress, IP.Interface.Status AS IP

                  FROM Orion.NodeIPAddresses as IP

                  WHERE (ip.IPAddress LIKE '10.10%') AND (IP.Interface.Status = 1)

                  ORDER by IP.Node.Caption;

                   

                  You can put this either in a web report or drop it right in a page as a custom resource of type "Custom Query"...

                  1 of 1 people found this helpful
                    • Re: SQL Query Help
                      Craig Norborg

                      Just for the fun of it, if you insist on doing it in SQL, you need to make it so it only returns the interface you want, your query was giving you a response for every interface on a device that had a 10.10% IP address.   ie: if the box had 25 interfaces of which one had the IP 10.10.1.1, it would return 25 rows for that one box.   The key to narrowing it down is specifying it in the join to your interfaces table by specifying the ifindex of the interface you found.   This join is implicit in SWQL with the way I did it above, as well as the other implicit joins I used, which is why its easier with SWQL (and faster!).

                       

                      SELECT node.Caption, ip.IPAddress, int.Status

                      FROM NodeIPAddresses IP

                      LEFT  JOIN NodesData NODE ON (ip.NodeID=node.NodeID)

                      LEFT  JOIN Interfaces INT ON ((IP.NodeID=int.NodeID) AND (IP.InterfaceIndex = INT.InterfaceIndex))

                      WHERE (ip.IPAddress LIKE '10.10.%') and (int.Status = 1)

                      ORDER by node.Caption;

                       

                      But, ignore this and come to the Dark Side!!  :-)

                      • Re: SQL Query Help
                        t1n0m3n

                        Nice!  Dropped this SWQL query in my search and it worked like a champ!  Thanks!

                         

                        I am using a count function, and SWQL allows counting via the web interface instead of having to be a part of the SQL query.