90/95/99th Percentile Traffic Rate - Last 7 Days

Hi Team,

 This highlighted subject report working fine in the old version of 2017.3.5, but now not working in the latest version of 2020.2.5 (we migrated recently) if we copied the same SQL command pasted over here.

 Report Name : 90/95/99th Percentile Traffic Rate - Last 7 Days ( working based on SQL query).

 Working - 2017.3.5 

 Not working - 2020.2.5

 the same command we are using, but not working.

 Issue : Cannot find either column "dbo" or the user-defined function or aggregate "dbo.GetInBpsPercentile", or the name is ambiguous.

Note : In 2020.2.5 customized report available in the format of SWQL, it takes more time and most of the times failed while pulling report.

  

  • The main reason is in 2017 DB table structure is different from the 2020.2.5. Hence there is no database table called 'GetInBpsPercentile' OOB report is in SWQL and doesn't contain such table. here dbo stands for Database table in SQL.

    Possibly what you can do

    1.convert the SWQL based OOB report to SQL based report. Tables involved( Orion.Nodes, Orion.NPM.Interfaces, Interfaces)

    2.Move on to the SWQL based OOB report.

    If possible put the entire code here to check what could be the table it's referring to.

  • Hi daham_bm,

       Thanks a lot !!!...

       We tried with the 1st approach. From standard template of SWQL ( week report) into SQL report.

       Changed commands like below format...But we are facing syntax error mentioned below notes, also seems some error in From Commands

    SELECT
    Nodes.Caption AS NodeName,
    Interfaces.Caption AS Interface_Caption,
    Maxbps_In90,
    Maxbps_Out90,
    Maxbps_In95,
    Maxbps_Out95,
    Maxbps_In99,
    Maxbps_Out99
    FROM Nodes
    INNER JOIN Interfaces ON Nodes.NodeID = Interfaces.NodeID
    INNER JOIN (
    SELECT InterfaceTraffic.InterfaceID,
    (SELECT MAX(In_Averagebps) as maxInAverageBps FROM (
    SELECT TOP 90 PERCENT In_Averagebps
    FROM InterfaceTraffic WHERE InterfaceID = InterfaceTraffic.InterfaceID AND DateTime >= '25/10/2021 00:01'
    ORDER BY In_Averagebps ASC )) AS Maxbps_In90,
    (SELECT MAX(Out_Averagebps) as maxOutAverageBps FROM (
    SELECT TOP 90 PERCENT Out_Averagebps
    FROM InterfaceTraffic WHERE InterfaceID = InterfaceTraffic.InterfaceID AND DateTime >= '25/10/2021 00:01'
    ORDER BY Out_Averagebps ASC )) AS Maxbps_Out90,
    (SELECT MAX(In_Averagebps) as maxInMaxbps FROM (
    SELECT TOP 95 PERCENT In_Averagebps
    FROM InterfaceTraffic WHERE InterfaceID = InterfaceTraffic.InterfaceID AND DateTime >= '25/10/2021 00:01'
    ORDER BY In_Averagebps ASC )) AS Maxbps_In95,
    (SELECT MAX(Out_Averagebps) as maxOutMaxbps FROM (
    SELECT TOP 95 PERCENT Out_Averagebps
    FROM InterfaceTraffic WHERE InterfaceID = InterfaceTraffic.InterfaceID AND DateTime >= '25/10/2021 00:01'
    ORDER BY Out_Averagebps ASC )) AS Maxbps_Out95,
    (SELECT MAX(In_Averagebps) as maxInAverageBps FROM (
    SELECT TOP 99 PERCENT InAveragebps
    FROM InterfaceTraffic WHERE InterfaceID = InterfaceTraffic.InterfaceID AND DateTime >= '25/10/2021 00:01'
    ORDER BY In_Averagebps ASC )) AS Maxbps_In99,
    (SELECT MAX(Out_Averagebps) as maxOutAverageBps FROM (
    SELECT TOP 99 PERCENT Out_Averagebps
    FROM InterfaceTraffic WHERE InterfaceID = InterfaceTraffic.InterfaceID AND DateTime >= '25/10/2021 00:01'
    ORDER BY Out_Averagebps ASC )) AS Maxbps_Out99
    FROM InterfaceTraffic
    WHERE DateTime >= '25/10/2021 00:01'
    GROUP BY InterfaceID
    ) TrafficStat ON Interfaces.InterfaceID = TrafficStat.InterfaceID

    Error:

    Msg 102, Level 15, State 1, Line 17
    Incorrect syntax near ')'.
    Msg 102, Level 15, State 1, Line 21
    Incorrect syntax near ')'.
    Msg 102, Level 15, State 1, Line 25
    Incorrect syntax near ')'.
    Msg 102, Level 15, State 1, Line 29
    Incorrect syntax near ')'.
    Msg 102, Level 15, State 1, Line 33
    Incorrect syntax near ')'.
    Msg 102, Level 15, State 1, Line 37
    Incorrect syntax near ')'.

  • Both GetInBpsPercentile and GetOutBpsPercentile SQL functions have been deprecated in the recent versions of the Orion platform.

    There are out of the box replacements for these native SQL reports using SWQL, but I think they didn't give enough details on how the things actually worked, so I shared some of my own.  Like all of my queries for reports, I normally test these in SWQL Studio before putting into a report.

    Here is a sample of them.  At the top of the query, I've got details on how to handle the date range filtering.

  • Thanks for your response.

    We are usually looking for the option on SQL not in SWQL ( as it is taking too much of time to generate report and most of the time failed to pull the report.

    That's why modified the report from SWQL to SQL along with it's parameter.

    So , is there any option to correct above report ( which i shared) for SQL , means possible to filter out the issue.

  • Since SWQL is mostly based on SQL, you can use the same logic to calculate the Percentile.

    -- Snippet from report
    SELECT MAX(InAverageBps) AS [maxInMaxBps]
                   FROM (
                        SELECT TOP 90 PERCENT InAverageBps
                        FROM Orion.NPM.InterfaceTraffic AS [In90th]
                        WHERE [In90th].InterfaceID = [Traffic].InterfaceID
                             -- Filter for the last X days (requried for all filters)
                             AND [In90th].[DateTime] >= AddDay(- 7, GETUTCDATE())
                        ORDER BY InMaxBps ASC
                        )
                   ) AS [MaxBps_In90]

    But I'm seriously going to caution you from using SQL directly since the database structure can change when moving between versions, but the SWQL will continue to work.