I need to create a report that shows all active ports that have nothing connected to them. I am a new-be, any suggestions?
Try this:
SELECT n.Caption, n.IP_Address, i.InterfaceName, i.StatusFROM Nodes nLEFT JOIN Interfaces iON n.nodeID=i.nodeIDWHERE i.AdminStatus = 1ANDi.OperStatus <> 1ORDER 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
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.
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.
OK, It creates a report with Caption, IP_Address, InterfaceNane and Status Column. However there is no data.
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
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
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:
SELECTNodes.Caption, Nodes.IP_Address, Interfaces.InterfaceName, Interfaces.StatusFROM NodesLEFT JOIN InterfacesON Nodes.nodeID=Interfaces.nodeIDWHERE Interfaces.AdminStatus = 1ANDInterfaces.OperStatus <> 1ORDER 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:
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:
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.StatusFROM NodesLEFT JOIN InterfacesON Nodes.nodeID=Interfaces.nodeID
