So I thought I was building a pretty straight-forward report using the report writer. In the environments that I design, I use an '_' to preface the name name (_ProdFW01) to quickly indicate that I am only collecting stats via ICMP. I know there are other ways to do it, but the standard is to use SNMP, so I want a quick visual when a node alerts whether there are SNMP performance stats available or not.
I built a report that looks like the screenshot and SQL below. The idea was to check for node names that contain _ (I tried starts with too, and that failed), AND does not have one of the 4 SNMP strings. However, every time I run the report I get a mishmash of nodes, some with the _ and no SNMP string, other without the _ and no SNMP. There doesn't appear to be a method that I can see -- thought it does return a couple of hundred nodes, so it's harder to see the pattern.
Any ideas where I went wrong?
Select NodeID, NodeName, IP_Address, Community, Cast(N_Network As nvarchar(250)) as N_Network From ( SELECT
Nodes.NodeID AS NodeID, Nodes.Caption AS NodeName, Nodes.IP_Address AS IP_Address, Nodes.Community AS Community, Nodes.N_Network AS N_Network
(Nodes.Caption LIKE '%_%') AND
NOT (Nodes.Community LIKE 'string1%') OR
NOT (Nodes.Community LIKE 'string2%') OR
NOT (Nodes.Community LIKE 'string3%') OR
NOT (Nodes.Community LIKE 'string4%'))
) As r ORDER BY 2 ASC, 3 ASC, 5 ASC
Solved! Go to Solution.
_ is a SQL wild card and therefore is causing your problem.
It is a wildcard for exactly on space so begins with _ is telling SQL to return items that begin with anything.
SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community. More than 150,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process. Learn more today by joining now.