Interface Down report

rjg5050

Since I dont see an out of the box report that shows interfaces down on a network device I am in the process of creating one myself... the issue is I only want interfaces that are down on nodes that are up, if that makes sense... so I am using the tables dbo.interfaces and dbo.NodesOLD from the NetPerfMon database and depending on how I structure the query (with an inner join on NodeID) I either get an Ambiguous column name error, or a multi part could not be bound error...

This one gives the Ambiguous error:

SELECT TOP 1000 [NodeID]

      ,[InterfaceID]

      ,[InterfaceName]

      ,[FullName]

      ,[Status]

  FROM [NetPerfMon].[dbo].[Interfaces] inner join [NetPerfMon].[dbo].[NodesOLD] on NodesOLD.NodeID = Interfaces.NodeID

  where Interfaces.status = 2 and NodesOLD.status = 1

and if I delineate out the full table name for NodeID and Status that is when I get the multi part could not be bound error..... thoughts??

zackm

Since you have the 'NodesOLD' table, I am going to assume that you are on NPM 11.5.

If that's the case, you could make this easier with the web report writer GUI by selecting all interfaces with Status = Down

The trick is to know that interfaces are inherently dependent on their parent nodes. Meaning that if a node goes down, the interfaces go into an 'Unreachable' status (not down). It is not possible with the current version to have a down interface on a down node. (unless there is a bug I am unaware of )

That being said, if you want to proceed with SQL, the next thing to learn is that the NodesOLD table is no longer in use. For your reference, the Nodes table was split into 3 distinct tables, NodesData, NodesStatistics, and NodesCustomProperties. To retain functionality with older queries, SolarWinds was kind enough to create a new view called 'Nodes' that has all of the details from NodesOLD.

tl;dr - You can still use 'Nodes' to reference whatever you need for your reports.

As seen here, whether I look for only down interfaces, or only down interfaces with nodes that are not down; I still get 81 results.

And finally, regarding your errors; the ambiguos name error is because the NodeID field exists in both the Interfaces and NodesOLD tables. So you have to tell your query which one you care about. The multi part could not be bound error is similarly because you didn't give a table name to the 'Status' part of the SELECT statement.

Ambiguous = You didn't give ANY table names for duplicate column names.

Multi Part... = You gave table names for some, but not ALL duplicate column names.

-ZackM

Loop1 Systems: SolarWinds Training and Professional Services

• LinkedIN: Loop1 Systems
• Facebook: Loop1 Systems
• Twitter: @Loop1Systems