This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

SQL Code for Node and Interface Count per Site

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!





  • 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

  • The SWQL seems pretty straight forward. Thanks for adding to this thread. Could you post a screen pic of the end result/output for your SWQL? That may be helpful too.

  • Sure, below are the 2 queries in SWQL Studio, and the 3rd is the Custom table widget config. 4th is the widget on the page.

    pastedImage_1.png

    pastedImage_0.png

    pastedImage_3.png

    pastedImage_4.png