2 Replies Latest reply on Sep 10, 2018 1:49 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!


       

       




        • Re: SQL Code for Node and Interface Count per Site
          jm_sysadmin

          Because I did a search on interface counts and this came up, this was a great time saver. But I needed SWQL not SQL in the end so here are a few things that might help people that need SWQL for Orion widgets, reports, or whatever.

          This has all devices not monitored with ICMP (which means that devices with 0 interfaces show up_

          SELECT N.NodeID, N.Caption, Count(N.Interfaces.InterfaceID) as [Device Interface Count]

          FROM Orion.Nodes N

          Where N.ObjectSubType not like 'ICMP'

          Group By N.NodeID, N.Caption

          Order by [Device Interface Count] Desc

           

          This has all interfaces grouped be device (so no 0 count interfaces)

          SELECT I.Node.Caption, I.NodeID, Count(I.InterfaceID) as [Device Interface Count]

          FROM Orion.NPM.Interfaces I

          Group by I.Node.Caption, I.NodeID

          Order by [Device Interface Count] Desc