0 Replies Latest reply on Feb 21, 2018 2:14 PM by ebradford

    SQL Code for Node and Interface Count per Site

    ebradford

      Hi All.

      Our business has multiple sites, with many nodes and interfaces monitored by NPM at larger sites. We have a custom field on Nodes, Airport_Code, which is the airport code of the nearest major Airport. I was wanting to do some planning for APEs, and wanted to see how many nodes and interfaces are in the database on a per-site basis. I share here what I found, in the hopes that this may be of value to you.

       

      The keys here are to do a Left Join between Nodes and Interfaces tables, and include the word Distinct in the NodeID count.

       

      When just using Distinct, with just an Inner Join, I didn’t see all records – just records which also had interfaces. We happen to have several nodes with no interfaces in management in some locations. Those weren’t being counted using Inner Join.  Using Left Join, all Node records were selected, not just the ones which had interfaces under management.

       

      When not using the word Distinct in the NodeID count, you will get the same number for NodeID count as Interface count. That's because each interface has a node associated with it, and the SQL query will count every NodeID listed (ie. a NodeID which has 3 managed Interfaces will be counted 3 times under Node ID). So, use Distinct to count only the unique occurrences of the NodeID.

       

      Here is the SQL Code:

      SELECT
      Nodes.Airport_Code AS Airport_Code, COUNT(DISTINCT Nodes.NodeID) AS Node_Cnt,COUNT(Interfaces.InterfaceID) AS Interface_Cnt
      FROM
      Nodes LEFT JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID)

      GROUP BY Nodes.Airport_Code
      ORDER BY 2 DESC

       

      Here is our output (numbers and codes have been changed slightly):

      Airport_Code     Node_Cnt     Interface_Cnt
      SEA                    662              2800
      PDX                    623              1656
      SFO                    467              2507
      LAX                    343               887
      ...

      I hope this if of help to someone out there. Cheers!