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.

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

FormerMember
FormerMember

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,

  • Just at a glance i see you are joining on the datetimes of the polls for two different interfaces.  That's probably never going to happen.  The timestamps are down to millisecond scale and will not match. 

    Not entirely sure where you plan to go with this SQL but I'd most likely be doing something like this instead of all the complexity you had.

    SELECT DateTime, In_Averagebps AS In_AveragebpsNNI1, InterfaceID
    	FROM InterfaceTraffic
    	WHERE InterfaceTraffic.InterfaceID in (1265,1266) AND DateTime >= @StartDate AND DateTime <= @EndDate
    	ORDER BY DateTime ASC
  • FormerMember
    0 FormerMember in reply to mesverrum

    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:

    1. Get the average bps from each interface.
    2. Combine them as a total.
    3. Keep the bottom 95% from that.
    4. Get the max from the previous step.
    5. 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. 

  • FormerMember
    0 FormerMember in reply to mesverrum

    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
    0 FormerMember

    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,