2 Replies Latest reply on Apr 23, 2015 4:24 PM by rjg5050

    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??

        • Re: Interface Down report
          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

           

          2015-04-16_16-14-54.jpg

           

          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.

           

          2015-04-16_16-18-03.jpg

           

          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

            • Re: Interface Down report
              rjg5050

              Thanks for the info Zach... yes we are on the newer version of NPM... I guess I did not put together the idea of an interface being unreachable if a node is down but it makes sense... got the SQL working with a little help from a DBA...