last part - not like '%Tunnel%'
you have to have the wild cards i believe. if that doesn't work, try using *.
This didn't work.
All I want to do is exclude any node that has an INTERFACE showing down that has the word Tunnel in the interface description. I am using the canned "Nodes with problems" report.
Ah yes, sorry...didn't read closely enough. you are trying to run a report against the nodes table that needs to include criteria from the interfaces table so you must do a custom SQL report that combines both tables in the query.
If you are not the SQL person, I recommend getting with them to assist you with tweaking the code below.
if you ARE the SQL person, you will have to add columns you want to see and play with the report to get the criteria you want, but the base of the query would look like this:
from Nodes left join Interfaces on nodes.NodeID=interfaces.NodeID
where Interfaces.interfacename like '%Tunnel%' and interfaces.status = 2
Personally, I use the SQL Server mgmt studio to tweak my queries for desired data, then put the finished query into report writer. I also use a subset of nodes to verify my data (i.e. add to where clause Nodes.NodeID = 111 or nodes.NodeID in (35,111), etc...
so add the columns you want to see in your report (you can "hide" the columns you don't want to see like nodeid or you can leave them out of the select statement after you've verified your data set/query is correct.
as I'm digging deeper, you are actually using a "canned resource" and not a "report". so let me clarify a little further...you will not be able to tweak the resource as it is coded to only hit the nodes table...that would require coding the asp.
the way to accomplish this would be to add a new view and have the source of that view be the custom sql report that you create based on the above. then go into the view that currently displays the "nodes with problems" resources that you want to replace, edit it and add a resource -> "report writer" -> "report from orion report writer"
then when you edit the view, you assign the report to use. I do this on my top 10 page to show nodes that are unmanaged.
Add this to your filter:
statusdescription not like '%Tunnel%'
Winner! This worked. This is exactly what I wanted. THANKS!!!!!
Very cool, and MUCH easier..I guess it makes sense that it talks to interface table since it has the interface description...
This thread helped me today, but what worked for us is:
InterfaceName not like '%tunnel%' and InterfaceName not like '%vlan%'
I tried using InterfaceType(not like encapsulation and not like proprietary virtual) and a few others to no avail. Sometimes it takes some good ole' trial and error until you get it to display what you need, especially if you aren't a SQL whiz