9 Replies Latest reply on Aug 7, 2013 3:08 PM by stuartwhyte

    report that shows all active ports that have nothing connected

    gualtira

      I need to create a report that shows all active ports that have nothing connected to them.  I am a new-be, any suggestions?

        • Re: report that shows all active ports that have nothing connected
          zackm

          Try this:

           

          SELECT n.Caption,
                 n.IP_Address,
                 i.InterfaceName,
                 i.Status
          FROM Nodes n
          LEFT JOIN Interfaces i
          ON n.nodeID=i.nodeID
          WHERE i.AdminStatus = 1
          AND
          i.OperStatus <> 1
          ORDER BY n.Caption
          
          

           

          So (line by line)

          1) Device Hostname

          2) Device IP Address

          3) Interface Description

          4) Interface Status (2 = DOWN)

          5) Pull data from the Nodes table, variable 'n' on lines 1,2,7

          6) Join that data to the Interfaces table, variable 'i' on lines 3,4,6,8,10

          7) Match the NodeID from the Nodes table to the NodeID on the Interfaces table

          8) Only return results where the Interface is Administratively UP

          9/10) AND, Only return results where the Interface does not have an UP state of Operation

          11) Sort results alphabetically by n.Caption (hostname)

           

          You can edit as you see fit, but this should give you a pretty good starting point.

           

          also, some people will say that you don't need a variable on columns that are distinct. ie; the Caption column is not on the Interfaces table, so you don't technically need to identify it as 'n.Caption', just putting 'Caption' would work. (Those people would be correct) However, I put a variable on every column I call out of habit. Point is, it works either way.

           

          Message was edited by: Zack Mutchler

            • Re: report that shows all active ports that have nothing connected
              gualtira

              I was looking for something I could create with Orion Report Writer if this is

              possible.  I do not know what to choose as Selected Fields and Filter Results

              to get a report that will show all ports that are active but have nothing

              connected to them.  I have over 6000 ports that are active.

              Thanks.

                • Re: report that shows all active ports that have nothing connected
                  zackm

                  1) Open Orion Report Writer

                  2) Click on Create New Report

                  3) Choose Report Type "Advanced SQL"

                  4) Fill out the General Tab as you wish

                  5) Click on the SQL tab and copy/paste the script I provided above

                  6) Field Formatting tab will allow you to edit how the results are output

                  7) Save the report and then run as needed.

                    • Re: report that shows all active ports that have nothing connected
                      gualtira

                      OK, It creates a report with Caption, IP_Address, InterfaceNane and Status Column.  However there is no data.

                       

                      Thanks.

                        • Re: report that shows all active ports that have nothing connected
                          zackm

                          Can you verify a particular node/port combo where you know for sure the interface is "Up/Down"? I know for us, all unused interfaces would be placed in Admin down status. With this query/report, we are only listing devices that are Up/Down. Past that, it would be up to the engineer to identify ports that actually have nothing plugged in versus ports that have actual problems that should be addressed.

                           

                          Or, it could be that you have 6000+ interfaces and not a single problem

                            • Re: report that shows all active ports that have nothing connected
                              gualtira

                              My apologizes, I do not know SQL.  Please explain what i and n are.  I have 30 plus nodes and over 6000 ports and what I need to determine is which ports are active and have nothing connected to them so that they can be turned off.

                              Thank You

                                • Re: report that shows all active ports that have nothing connected
                                  zackm

                                  i and n are just variables used to tell the script which table to look into. You could just as easily use the following script:

                                   

                                  SELECT
                                  Nodes.Caption,
                                      Nodes.IP_Address,
                                      Interfaces.InterfaceName,
                                      Interfaces.Status
                                  FROM Nodes
                                  LEFT JOIN Interfaces
                                  ON Nodes.nodeID=Interfaces.nodeID
                                  WHERE Interfaces.AdminStatus = 1
                                  AND
                                  Interfaces.OperStatus <> 1
                                  ORDER BY Nodes.Caption
                                  

                                   

                                  I would recommend a little time spent learning SQL. It makes NPM management 1,000X easier   Here is a pretty good starting point for you: SQL Tutorial

                                   

                                  For now, try to visualize this:

                                  SQL is a database software

                                       'SolarWinds' is the name of a database on a SQL server

                                            'Nodes' and 'Interfaces' are tables on the SQL Database (There are a LOT more as seen below)

                                                 'Caption', 'IP_Address', and 'NodeID' are columns on the 'Nodes' table

                                                 'InterfaceName', 'Status', 'NodeID', 'AdminStatus', and 'OperStatus' are all columns on the 'Interfaces' table

                                   

                                  Visual Database Hierarchy:

                                  Capture1.JPG

                                   

                                  So (line by line)

                                  1) SELECT is telling the query what columns we are pulling data from (this is a VERY basic overview)

                                  2-5) These are the columns we will pull data from, and the order that we list them in the the order that our results will be displayed in from left to right

                                  6) FROM is telling the query what initial table we should pull the data from

                                  7) LEFT JOIN combines the data we pull from Nodes with the data we will pull from Interfaces

                                  8) ON is telling the query what column we are relating to on each table (for this example, we are only looking for results where the NodeID from the Nodes table matches the NodeID from the Interfaces table

                                  9) WHERE is a limiting statement. For this example, we are only looking for results where the AdminStatus of the Interface = '1' (up)

                                  10-11) AND combines a second limitation to the WHERE statement; specifically: once we parse all interfaces with AdminStatus = 1, we then go through those results and only parse the interfaces where the OperStatus does not equal (<>) 1 (is NOT up)

                                  12) ORDER BY is telling the query to display the results alphabetically, A to Z, based on the Caption (hostname) found in the Nodes table (if this were a numerical result, it would be ascending as well)

                                   

                                  Visual Results:

                                  Capture.JPG

                                   

                                  If you are getting 0 results, but the columns are showing up; then the query is connecting to the database properly and not erroring out; the problem may be a variable we are calling on.

                                   

                                  Try this one and the scroll through the results and see if there are any values = '2' in the STATUS column

                                   

                                  SELECT Nodes.Caption,
                                      Nodes.IP_Address,
                                      Interfaces.InterfaceName,
                                      Interfaces.Status
                                  FROM Nodes
                                  LEFT JOIN Interfaces
                                  ON Nodes.nodeID=Interfaces.nodeID