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

The field you want is InterfaceAlias (specifically the description on the interface without its name) in the Interfaces table and instead of CONTAINS you want LIKE with the % wildcards:

DECLARE @StartDate DateTimeDECLARE @EndDate DateTimeSET @StartDate = ${FromTime}SET @EndDate = ${ToTime}SELECT Interfaces.InterfaceId,Nodes.NodeID,Nodes.Caption AS NodeName,Interfaces.Caption AS Interface_Caption,Maxbps_In95,Maxbps_Out95FROM NodesINNER JOIN Interfaces ON Nodes.NodeID = Interfaces.NodeIDINNER JOIN (SELECT InterfaceID,dbo.GetInBpsPercentile(InterfaceID, @StartDate, @EndDate,95) AS Maxbps_In95,dbo.GetOutBpsPercentile(InterfaceID, @StartDate, @EndDate,95) AS Maxbps_Out95FROM InterfaceTrafficWHERE InterfaceTraffic.DateTime >= @StartDate AND InterfaceTraffic.DateTime <= @EndDateGROUP BY InterfaceID) TrafficStatON Interfaces.InterfaceID = TrafficStat.InterfaceIDWHERE Interfaces.InterfaceAlias LIKE '%-95th%'

Edit: Fixed the field name to be the interface description instead of the name.

Steven W. Klassen

Programmer Analyst @ Loop1 Systems

All comments

The field you want is InterfaceAlias (specifically the description on the interface without its name) in the Interfaces table and instead of CONTAINS you want LIKE with the % wildcards:

DECLARE @StartDate DateTimeDECLARE @EndDate DateTimeSET @StartDate = ${FromTime}SET @EndDate = ${ToTime}SELECT Interfaces.InterfaceId,Nodes.NodeID,Nodes.Caption AS NodeName,Interfaces.Caption AS Interface_Caption,Maxbps_In95,Maxbps_Out95FROM NodesINNER JOIN Interfaces ON Nodes.NodeID = Interfaces.NodeIDINNER JOIN (SELECT InterfaceID,dbo.GetInBpsPercentile(InterfaceID, @StartDate, @EndDate,95) AS Maxbps_In95,dbo.GetOutBpsPercentile(InterfaceID, @StartDate, @EndDate,95) AS Maxbps_Out95FROM InterfaceTrafficWHERE InterfaceTraffic.DateTime >= @StartDate AND InterfaceTraffic.DateTime <= @EndDateGROUP BY InterfaceID) TrafficStatON Interfaces.InterfaceID = TrafficStat.InterfaceIDWHERE Interfaces.InterfaceAlias LIKE '%-95th%'

Edit: Fixed the field name to be the interface description instead of the name.

Steven W. Klassen

Programmer Analyst @ Loop1 Systems

cmccurry

Stephen, that worked perfectly !! My sincere thanks.

Now, in the 'teach a man to fish' category - two quick followup questions:

1) How could I have found out the name of the field I wanted was 'Interfaces.InterfaceAlias' -- on my own.

2) I had googled about SQL syntax and thought I could use CONTAINS -- is that not a valid NPM verb? Or was I just wrong in choosing that and should have used LIKE all along (which I figured out the % matching zero or any)

...Chuck

cmccurry - you're very welcome!

Without knowing how you were going about creating your report it's hard to tell how you could have known the correct column. If you were in the Manage Report interface creating a new SQL report from scratch you're not going to have any visibility into what's possible - you need to bring that information with you when you create the report. If you were in SQL Management Studio testing your SQL queries as you go, there's an interface on the left side that can be expanded to show you what's available like the one below:

Hope that helps!

Steven W. Klassen

Programmer Analyst @ Loop1 Systems

Oh and on the CONTAINS thing - that's a term that you're going to find in user interfaces (because it makes sense that string A can be contained in larger string and sometimes also in programming languages like PowerShell ("foobar".Contains('foo')). In SQL the equivalent is LIKE. So it wasn't necessarily wrong - you just had the wrong syntax for the language.

Steven W. Klassen

Programmer Analyst @ Loop1 Systems

http://stackoverflow.com/questions/14290857/sql-select-where-field-contains-words

cmccurry

Ah, I had googled various SQL constructs and had come across CONTAINS -- I suspect it must be in some SQL implementations and not in others. LIKE works -- now that I know the syntax.

https://blog.udemy.com/sql-contains/

** For those looking at this discussion -- please use LIKE as provided in the example by Steven -- as CONTAINS is not correct **

Oh wow, I stand corrected. It looks like we're both correct! There's a new predicate for the WHERE clause called CONTAINS that's available in SQL Server 2014 and later:

The important bit is probably the warning at the top of the blog post you linked above:

Before we begin, you have to understand that the CONTAINS SQL function is not a standard SQL function. While every database system has the function, they all deal with it differently. The function also requires different arguments for different databases as well. Therefore, before you use it, you must know and understand the database system you use.

So if you want to stay backward-compatible use the standardized LIKE format and you'll stay between the ditches.

Steven W. Klassen

Programmer Analyst @ Loop1 Systems