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?

zackm

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