Combine average bps on two interfaces to later get the combined 95th percentile

Question

Hello, I am trying to combine the data from two interfaces before keeping the lowest 95%, then getting the max out of that. This would be a mathematically sound way of getting the combined 95th percentile. I am at the step where I want to display the data in the same table before combining it. These are two interfaces on the same node. The polling interval is the same, so the polling DateTime do match. Here's the code I have produced. It does not work. What am I missing? DECLARE @StartDate DateTime DECLARE @EndDate DateTime SET @StartDate = ${FromTime} SET @EndDate = ${ToTime} SELECT a.DateTime as DateTime, a.In_AveragebpsNNI1 AS In_AveragebpsNNI1, b.In_AveragebpsNNI2 AS In_AveragebpsNNI2 FROM (SELECT DateTime, In_Averagebps AS In_AveragebpsNNI1 FROM InterfaceTraffic WHERE InterfaceTraffic.InterfaceID = '1265' AND DateTime >= @StartDate AND DateTime <= @EndDate ORDER BY DateTime ASC) AS a INNER JOIN (SELECT DateTime, In_Averagebps AS In_AveragebpsNNI2 FROM InterfaceTraffic WHERE InterfaceTraffic.InterfaceID = '1266' AND DateTime >= @StartDate AND DateTime <= @EndDate ORDER BY DateTime ASC) AS b ON a.DateTime = b.DateTime Regards,

FormerMember · Answer

Hi, I'm still looking into combining the maximum bps in and out of two interfaces before extracting the 95th percentile of that. Here's the current code. It is erronerous. DECLARE @StartDate DateTime DECLARE @EndDate DateTime SET @StartDate = ${FromTime} SET @EndDate = ${ToTime} SELECT TOP 1 @StartDate AS Start_Date, @EndDate AS End_Date, Maxbps_In95, Maxbps_Out95 FROM Nodes INNER JOIN Interfaces ON Nodes.NodeID = Interfaces.NodeID INNER JOIN ( SELECT InterfaceID, (SELECT MAX(In_Averagebps) AS Maxbps_In95 from (SELECT TOP 95 PERCENT DateTime, SUM(In_Averagebps) AS In_Averagebps FROM InterfaceTraffic WHERE InterfaceTraffic.InterfaceID IN ('1265', '1266') AND DateTime >= @StartDate AND DateTime <= @EndDate GROUP BY DateTime ORDER BY DateTime ASC ) AS x ) AS Maxbps_In95, (SELECT MAX(Out_Averagebps) AS Maxbps_Out95 from (SELECT TOP 95 PERCENT DateTime, SUM(Out_Averagebps) AS Out_Averagebps FROM InterfaceTraffic WHERE InterfaceTraffic.InterfaceID IN ('1265', '1266') AND DateTime >= @StartDate AND DateTime <= @EndDate GROUP BY DateTime ORDER BY DateTime ASC ) AS x ) AS Maxbps_Out95 FROM InterfaceTraffic AS IFT GROUP BY InterfaceID ) TrafficStat ON Interfaces.InterfaceID = TrafficStat.InterfaceID WHERE Interfaces.InterfaceID IN ('1265','1266')If somebody's willing to code this, I am willing to pay.Regards,

FormerMember · Answer

Here's another take where I round to the minute. The sampling is at 5 minutes interval, this could be acceptable. To my dismay, both sub SELECT routines do work, but not the joined one. DECLARE @StartDate DateTime DECLARE @EndDate DateTime SET @StartDate = ${FromTime} SET @EndDate = ${ToTime} SELECT a.DateTimeNNI1 AS DateTimeNNI1, a.In_AveragebpsNNI1 AS In_AveragebpsNNI1, b.In_AveragebpsNNI2 AS In_AveragebpsNNI2 FROM (SELECT dateadd(mi, datediff(mi, 0, DateTime), 0) AS DateTimeNNI1, In_Averagebps AS In_AveragebpsNNI1 FROM InterfaceTraffic WHERE InterfaceTraffic.InterfaceID = '1265' AND DateTime >= @StartDate AND DateTime <= @EndDate ORDER BY DateTimeNNI1 ASC) AS a INNER JOIN (SELECT dateadd(mi, datediff(mi, 0, DateTime), 0) AS DateTimeNNI2, In_Averagebps AS In_AveragebpsNNI2 FROM InterfaceTraffic WHERE InterfaceTraffic.InterfaceID = '1266' AND DateTime >= @StartDate AND DateTime <= @EndDate ORDER BY DateTimeNNI2 ASC) AS b ON a.DateTimeNNI1 = b.DateTimeNNI2

FormerMember · Answer

Hi,

Thank you for your quick answer. The problem I have with the solution you propose is that it doesn't easily let me combine the average bps from both interfaces as it were a single virtual interface. I've tried that and the data I get is erronerous.

I want the 95th percentile from both interfaces combined. The builtin 95th percentile function would also be erronerous as it is possible it would not pick the same sample date and time for the 95th percentile.

Here's the diabolical plan I devised, based on what I can do in Excel:

* Get the average bps from each interface.
* Combine them as a total.
* Keep the bottom 95% from that.
* Get the max from the previous step.
* Display in a simple table with a single row containing the beginning date, end date, 95th percentile in and out.

The previous code I submitted is the step where I want to get the data side by side from each interface. It would then be very easy to add them in another or even the same SELECT.