5 Replies Latest reply on Mar 1, 2013 10:25 AM by bsciencefiction.tv

    SQL Query - Match node name to community string

    jbiggley

      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