How to calculate bandwidth and throghtput

samimego

Hi,

I have been asked to calculate the KPI bandwidth and throghtput monthly for some network devices. Does anyone have any idea how to do this?

Here are some ideas I have:

SELECT 'Bandwidth_Throughput' AS Bandwidth_Throughput--n.NodeCaption, i.Caption
,CONCAT ((i.InBandwidth/1000000000),' Gbps') AS Bandwidth
,CONCAT(ROUND(MAX(t.OutPercentUtil),2),'%') AS [TransmitPercentUtil]
,CONCAT((100-ROUND(MAX(t.OutPercentUtil),2)),'%') AS [AvailablePercent]
,CONCAT(MAX(i.Traffic.OutMaxbps)/1000000, ' Mbps') AS [Max_Transmit(Mbps)]
,CONCAT(AVG(i.Traffic.OutAveragebps)/1000000, ' Mbps') AS [Throughput(Mbps)]
--, DateTime
FROM Orion.NPM.InterfaceTraffic t
INNER JOIN NCM.Nodes n on t.NodeID = n.CoreNodeID
INNER JOIN Orion.NPM.Interfaces i on i.InterfaceID = t.InterfaceID

mesverrum

Superficially the SWQL you are showing looks like it would work, but i think its weird that you are joining to the NCM nodes table instead of the orion.nodes.  It's probably not going to make a functional difference, just a less intuitive way to do this query.

I'll just give you a heads up, if you have a good sized environment then it is a huge load on the database to do 30 days of interface utilization like that, big enough that it might lock up your information service while it tries to crunch this query.  In the past for larger customers the only way were able to do this kind of report is to do it in pure SQL outside of the Orion report engine because we needed to be able to let it run much longer than the Orion app settings would allow.

And on a more philosophical level, why would average bandwidth usage be chosen as a KPI?  I'm struggling to imagine a scenario where the average utilization number is by itself going to be much use in driving some kind of business outcomes.  If you are trying to measure for something like congestion I've found what usually makes the most sense is to do something like a report that show the percent of polled samples above the warning threshold, during business hours.  Doing that math can be a real load for the system if you just brute force the samples directly in SQL, but the way I keep it performant is to have an alert that doesn't send out any messages or raise any INC, but just marks the times when our uplinks were above their warning thresholds.  Then I can just sum up the amount of time all those alerts were active to get a pretty good estimate on how much time our uplinks spent being bandwidth constrained.  Doing it that way is the kind of query that will run in under 2 minutes, where actually checking against all the historical interfacetraffic records can take hours to complete.