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

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?

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.

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:

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

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