6 Replies Latest reply on Apr 11, 2016 1:52 PM by Steven Klassen

    Filtering a report by "Caption" (interface description) containing a value?

    cmccurry

      We're wanting to filter the 90/95/99th Percentile report to only those interfaces having a certain value in their description (eg, like putting in '-95th' in the description).   Out of the thousands of interfaces, only a few need to be included in that report for us.   We need to go from a 100+ page report, to like a 2 page report of just the interfaces we care about for 95th analysis.

       

      We've gone round-and-round guessing on the proper "WHERE" syntax to add to the existing (default) SQL database query.   Each time we just get "Query is not Valid" from the report tool (web).

       

      We think we need a "WHERE Interface_Caption CONTAINS '-95th'" -- somewhere in the below.   But for the life of us, we can't figure out the right "variable" -- eg, Caption, Interfaces.Caption, etc to use -- no "where" would it go

       

      And we're not sure "CONTAINS" is a valid keyword in the SQL editor either..

       

      The SQL below is right out of the 90/95/99th built-in report (with 90th, and 99th removed as we don't use those)

       

      ...Chuck

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

              Maxbps_Out95

              FROM Nodes

              INNER JOIN Interfaces ON Nodes.NodeID = Interfaces.NodeID

              INNER JOIN (

              SELECT InterfaceID,

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

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

              FROM InterfaceTraffic

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

              GROUP BY InterfaceID

              ) TrafficStat

              ON Interfaces.InterfaceID = TrafficStat.InterfaceID