Filtering for specific interface names using the 90/95/99th Percentile Traffic Rate report

I'm making use of the canned alert named "90/95/99th Percentile Traffic Rate - Last Month". This report is a great start to the data that I'd like to pull in, but it runs the report on all interfaces, devices, nodes, etc in SolarWinds. I'd like to build out this report so that it only reports on specific interface names. These interfaces are located on different nodes. Is this possible? Here's the SQL script from the report:

        DECLARE @StartDate DateTime

        DECLARE @EndDate DateTime

        SET @StartDate = ${FromTime}

        SET @EndDate = ${ToTime}

        SELECT Interfaces.InterfaceId,

        Nodes.NodeID,

        City = 'Tipton',

        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 InterfaceID,

        dbo.GetInBpsPercentile(InterfaceID, @StartDate, @EndDate,90) AS Maxbps_In90,

        dbo.GetOutBpsPercentile(InterfaceID, @StartDate, @EndDate,90) AS Maxbps_Out90,

        dbo.GetInBpsPercentile(InterfaceID, @StartDate, @EndDate,95) AS Maxbps_In95,

        dbo.GetOutBpsPercentile(InterfaceID, @StartDate, @EndDate,95) AS Maxbps_Out95,

        dbo.GetInBpsPercentile(InterfaceID, @StartDate, @EndDate,99) AS Maxbps_In99,

        dbo.GetOutBpsPercentile(InterfaceID, @StartDate, @EndDate,99) AS Maxbps_Out99

        FROM InterfaceTraffic

        WHERE InterfaceTraffic.DateTime >= @StartDate AND InterfaceTraffic.DateTime <= @EndDate

        GROUP BY InterfaceID

        ) TrafficStat

        ON Interfaces.InterfaceID = TrafficStat.InterfaceID

Parents
  • At the very bottom of your report, add:

    WHERE (Interface.Caption LIKE 'Internet')

    This will pull any interface that has the word "Internet" in the description. Just change "Internet" to whatever word you are looking for.

    D

  • Thanks for the quick response. When pasting that at the bottom of the SQL query, it's returning "Query not valid". I'm missing something easy due to my lack of SQL knowledge.

    Also, is it possible to use the WHERE statement to add multiple interfaces? For example, if an interface name contains "NameX" OR "NameY", show in the report?

    pastedImage_0.png

  • First the easy answer, yes multiples are allowed. Format:

    WHERE (Interfaces.Caption LIKE 'MPLS') OR (Interfaces.Caption LIKE 'Internet')

    Second, check your syntax, it's "Interfaces." not "Interface.". Gotta love those simple typo's, especially when you are just starting out and learning something new. emoticons_happy.png

    D

  • Thanks again for the guidance. Everything is working as expected. Here's a snippet of what I'm doing in case it might help someone in the future.

    WHERE (Interfaces.Caption LIKE '%InterfaceX%') OR (Interfaces.Caption LIKE '%InterfaceY%')

    I'm using the "%%" modifiers so that SQL will grab any interface caption that contains "InterfaceX" in the interface caption. Without the % symbols, the exact name of the interface is needed. Both syntaxes might be helpful depending on the scenario.

    The column widths need some tweaking, but the report is giving the information needed:

    Report.PNG

    Here's the SQL script itself modified with the WHERE statement at the bottom. It would be more efficient to use a custom property instead of stacking multiple OR statements for the interface caption, but I'm only monitoring a total of 5 interfaces.

            DECLARE @StartDate DateTime

            DECLARE @EndDate DateTime

            SET @StartDate = ${FromTime}

            SET @EndDate = ${ToTime}

            SELECT Interfaces.InterfaceId,

            Nodes.NodeID,

            City = 'Tipton',

            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 InterfaceID,

            dbo.GetInBpsPercentile(InterfaceID, @StartDate, @EndDate,90) AS Maxbps_In90,

            dbo.GetOutBpsPercentile(InterfaceID, @StartDate, @EndDate,90) AS Maxbps_Out90,

            dbo.GetInBpsPercentile(InterfaceID, @StartDate, @EndDate,95) AS Maxbps_In95,

            dbo.GetOutBpsPercentile(InterfaceID, @StartDate, @EndDate,95) AS Maxbps_Out95,

            dbo.GetInBpsPercentile(InterfaceID, @StartDate, @EndDate,99) AS Maxbps_In99,

            dbo.GetOutBpsPercentile(InterfaceID, @StartDate, @EndDate,99) AS Maxbps_Out99

            FROM InterfaceTraffic

            WHERE InterfaceTraffic.DateTime >= @StartDate AND InterfaceTraffic.DateTime <= @EndDate

            GROUP BY InterfaceID

            ) TrafficStat

            ON Interfaces.InterfaceID = TrafficStat.InterfaceID

            WHERE (Interfaces.Caption LIKE '%InterfaceX%') OR (Interfaces.Caption LIKE '%InterfaceY%') OR (Interfaces.Caption LIKE '%InterfaceZ%')

  • I am using this same query but now getting an invalid query on a brand new Solarwinds NPM installation.  This worked on our older Solarwinds box, but we had to rebuild.  Saving the query reports this error.  Any ideas?

    Unexpected Website Error
    Error executing SQL: Cannot find either column "dbo" or the user-defined function or aggregate "dbo.GetInBpsPercentile", or the name is ambiguous.
    Query:
    
    DECLARE @StartDate DateTime
    DECLARE @EndDate DateTime
    
    SET @StartDate = ${FromTime}
    SET @EndDate = ${ToTime}
    
    SELECT Interfaces.InterfaceId,
    Nodes.NodeID,
    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 InterfaceID,
    dbo.GetInBpsPercentile(InterfaceID, @StartDate, @EndDate,90) AS Maxbps_In90,
    dbo.GetOutBpsPercentile(InterfaceID, @StartDate, @EndDate,90) AS Maxbps_Out90,
    dbo.GetInBpsPercentile(InterfaceID, @StartDate, @EndDate,95) AS Maxbps_In95,
    dbo.GetOutBpsPercentile(InterfaceID, @StartDate, @EndDate,95) AS Maxbps_Out95,
    dbo.GetInBpsPercentile(InterfaceID, @StartDate, @EndDate,99) AS Maxbps_In99,
    dbo.GetOutBpsPercentile(InterfaceID, @StartDate, @EndDate,99) AS Maxbps_Out99
    FROM InterfaceTraffic
    WHERE InterfaceTraffic.DateTime >= @StartDate AND InterfaceTraffic.DateTime

  • Hi Robertsig,

      You are right, even me to facing the same problem.

      The Query is working for old solarwinds (2017.3.5) not working on 2020.2.5 we are migrated  very recently.

      Is it any kind of bug in 2020.2.5.

      Have your found any solution on this issue?

      Note : 2020.2.5 90/95/99 predefined report also there in the format of SWQL, but it takes too much time and failed most of the time while pulling report.

    Regards

    Premnaseer A

Reply
  • Hi Robertsig,

      You are right, even me to facing the same problem.

      The Query is working for old solarwinds (2017.3.5) not working on 2020.2.5 we are migrated  very recently.

      Is it any kind of bug in 2020.2.5.

      Have your found any solution on this issue?

      Note : 2020.2.5 90/95/99 predefined report also there in the format of SWQL, but it takes too much time and failed most of the time while pulling report.

    Regards

    Premnaseer A

Children
No Data