cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 14

SQL Query - Match node name to community string

Jump to solution

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?

Untitled.jpg

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

FROM

Nodes

WHERE 

(

  (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

Labels (1)
0 Kudos
1 Solution
Level 12

If you are trying to filter to only ICMP nodes, you could take this route as well.

2013-03-01_0810.png

Zak Kahl

Loop1 Systems

http://www.loop1systems.com

View solution in original post

0 Kudos
5 Replies
Level 12

If you are trying to filter to only ICMP nodes, you could take this route as well.

2013-03-01_0810.png

Zak Kahl

Loop1 Systems

http://www.loop1systems.com

View solution in original post

0 Kudos

That's the ticket!  Between the comment from bsciencefiction.tv and changing to object type, this worked like a champ!

Gold stars, all around.

0 Kudos

_ 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.

Aww man -- my lack of SQL query showing for all to see!  I might have to change that standard...

0 Kudos

you can escape the wildcard by Node.Caption like '[_]%'

0 Kudos