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

SQL query filter

Jump to solution

I am looking to create a filter for Top XX Nodes by Percent Memory Used and I need to filter out any node with SQL or Node or DB in its name. I have tried a few different ways but it keeps telling me SQL query incorrect.


what i tried:


Node.SysName <> '*sql*' OR  "*node*" OR "*db"


SysName <> '*sql*' OR  "*node*" OR "*db"

NodeID <> '*sql*' OR  "*node*" OR "*db"

Labels (2)
0 Kudos
1 Solution

Replace the OR with AND

I think you want things that do not match any of the three criteria, which needs AND

SysName not like '*sql*' AND SysName not like '*node*' AND SysName not like '*db'

View solution in original post

0 Kudos
12 Replies
Level 7

I know this is an old thread, I just wanted to interject a finding I had with the syntax.  When I was using a star wildcard on the Sysname statement, my results were not as expected (the condition was being triggered when it should not have).  For instance, I was using the SQL condition:

Where 

status in ('1' , '3' )

and SysName not like '*PARTOFSYSTEMNAMEHERE*'

and unmanaged=0 

and DATEDIFF(MINUTE,LastSystemUpTimePollUtc,GETDATE()) >60

What I found was even though the System Name was correctly being parsed, the wildcard portion of it was not working.  On the advice of a much better programmer than I, we changed the condition to use a percent sign for the wildcard.  Worked like a charm.  New SQL condition is this:

Where 

status in ('1' , '3' )

and SysName not like '%PARTOFSYSTEMNAMEHERE%'

and unmanaged=0 

and DATEDIFF(MINUTE,LastSystemUpTimePollUtc,GETDATE()) >60

Hope this helps someone else. 

0 Kudos

Try:

SysName not like '*sql*' OR  not like "*node*" OR not like "*db"

0 Kudos

I think I lied, try this:

SysName not like '*sql*' OR SysName not like "*node*" OR SysName not like "*db"

0 Kudos
Level 9

I just tried that and it came back with

Custom SQL filter is incorrectly formatted.

0 Kudos

OK, sometimes double quotes are evil try:

SysName not like '*sql*' OR SysName not like '*node*' OR SysName not like '*db'

And this did work for me.

0 Kudos
Level 9

I just tried this and it didn't seem to remove any of the items I specified in the query. If i just choose say one item like '*node*' it works but it seems if i add more than one it doesn't like it.

0 Kudos

Replace the OR with AND

I think you want things that do not match any of the three criteria, which needs AND

SysName not like '*sql*' AND SysName not like '*node*' AND SysName not like '*db'

View solution in original post

0 Kudos
Level 9

JM that worked.. The AND did the trick!  Thanks everyone for the help on this.

0 Kudos

Rather than having to build multi-part sql filters all over the place this is a case where I usually just create a single custom property called "HasSQL" with a yes/no value and exclude nodes with a 'yes' from things like Memory utilization alerts, most dashboard views, etc.  It also makes it handy for if you ever want to build a view for your DBA as you can apply an account limitation to their account where they only see devices that have HasSQL=true.  If you are feeling fancy and your server naming conventions are reliable you can even set up a job in the database that automatically sets the custom property based on the caption/sysname containing "%sql%"

For people who aren't as comfortable in the SQL back end there is also an trick to set custom property values using Alert actions.  That can be really handy for building a sort of Custom Property logic to automatically populate related properties.  A simple example being to set a pseudo-alert with a condition where if the custom property City contains "Austin" then the action is to set the State to "Texas" This is nice if you have a whole stack of properties you would want defined consistently for all nodes at the Austin branch you can have the alert set them all automatically.

Anyway, a lot of ways to skin these cats.

  -Marc Netterfield

    Loop1 Systems: SolarWinds Training and Professional Services   

- Marc Netterfield, Github
0 Kudos
Level 13

what version is your NPM, mfoley@homesite.com‌?

0 Kudos
Level 9

11.5.2

0 Kudos
Level 13

OK since there had been some changes in the tables, why not try this one:

NodesData.SysName not like '*sql*'

Also make sure the Type of View is set to Summary just like what's in here:Customize View.JPG

0 Kudos