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:
Nodes.Airport_Code AS Airport_Code, COUNT(DISTINCT Nodes.NodeID) AS Node_Cnt,COUNT(Interfaces.InterfaceID) AS Interface_Cnt
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!