Splunk Search

Why is my search with "where NOT equals this OR this OR this" not filtering out results as expected?

jsven7
Communicator
| dedup _raw
| where NOT MsgId=="AUT22673" OR MsgId=="AUT23574" OR MsgId=="AUT20915" OR MsgId=="AUT22886"

What am I doing wrong here? I expect it to disregard events with that criteria. Its bringing up events with those MsgIds.

1 Solution

Runals
Motivator

If you are wanting to include multiple NOTs you have to use ANDs not ORs so that it becomes an inclusive statement = and not this and not this and not this. At a high level let's say you want not include something with "foo". If you say NOT foo OR bar, "foo" is evaluated against "foo" but then also evaluated against "bar". Because foo!=bar the event with foo makes it through the filter.

Try the following

| where NOT MsgId=="AUT22673" AND NOT MsgId=="AUT23574" AND NOT MsgId=="AUT20915" AND NOT MsgId=="AUT22886"

Note that in Splunk when you are including multiple evaluations in a where or eval statement you have to include the boolean AND.

Any particular reason why you are searching for those events and then looking for events that don't meet your search criteria?

View solution in original post

dbroggy
Path Finder

or use regex: | regex All_Traffic.app !="not-applicable|bob|blah"

0 Karma

woodcock
Esteemed Legend

Try this:

| dedup _raw
| where NOT (MsgId=="AUT22673" OR MsgId=="AUT23574" OR MsgId=="AUT20915" OR MsgId=="AUT22886")
0 Karma

MuS
SplunkTrust
SplunkTrust

Hi jsven,

I don't know why you do it this way, because your base search is searching for the multiple MsgId but further down the pipe you discard them again....could it be those are multivalve fields and/or your events are not properly line broken? Anyway, probably you have a reason to do so; so let me help you....

try this search:

mysearch... (MsgId=AUT22670 OR MsgId=AUT24414 OR  MsgId=AUT22673 OR MsgId=AUT23574 OR MsgId=AUT20915 OR MsgId=AUT22886)
 | dedup User 
 | search NOT MsgId="AUT22673" OR NOT MsgId="AUT23574" OR NOT MsgId="AUT20915" OR NOT MsgId="AUT22886"
 | eval Cluster="C"+substr(Node,10,1)  
 | table MsgId

Keep in mind, try to avoid NOT search, instead search for what you want and need.

Also keep in mind if you have multi value fields, it will still match events which for example holds a value of MsgId="AUT11111, AUT20915". To remove those events as well use the NOT MsgId="*AUT20915*", but this will be a bad performer on large searches.

Here is a link to a .conf slide about Search Efficiency Optimisation http://conf.splunk.com/session/2015/conf2015_JHarty_DuncanTurnbull_Splunk_UsingSplunkSearchLanguage_...

Hope this helps ...

cheers, MuS

0 Karma

Runals
Motivator

If you are wanting to include multiple NOTs you have to use ANDs not ORs so that it becomes an inclusive statement = and not this and not this and not this. At a high level let's say you want not include something with "foo". If you say NOT foo OR bar, "foo" is evaluated against "foo" but then also evaluated against "bar". Because foo!=bar the event with foo makes it through the filter.

Try the following

| where NOT MsgId=="AUT22673" AND NOT MsgId=="AUT23574" AND NOT MsgId=="AUT20915" AND NOT MsgId=="AUT22886"

Note that in Splunk when you are including multiple evaluations in a where or eval statement you have to include the boolean AND.

Any particular reason why you are searching for those events and then looking for events that don't meet your search criteria?

QQAL2021
Engager

instead of explicitly specify the values, can we get result of subsearch

 

 

| where NOT [how to write another search here to return a list of values of MsgId]

E.G.,

| where NOT [search anotherField=value | fields MsgId]

 

0 Karma

jsven7
Communicator

Thanks Runals! I'm trying to identify open sessions. AUT24414 and AUT22673 represent a login. AUT22673, AUT23574, AUT20915, AUT22886 represent logouts. So if I dedup per User ID and then disregard the logouts I can report on open sessions.

Courtesy of @jplumsdaine22.

0 Karma

jsven7
Communicator

Full code

mysearch... (MsgId=AUT22670 OR MsgId=AUT24414 OR  MsgId=AUT22673 OR MsgId=AUT23574 OR MsgId=AUT20915 OR MsgId=AUT22886)
| dedup User 
| where NOT MsgId=="AUT22673" OR MsgId=="AUT23574" OR MsgId=="AUT20915" OR MsgId=="AUT22886"
| eval Cluster="C"+substr(Node,10,1)  
| table MsgId
0 Karma

_dave_b
Communicator

Have you tried to exclude those values in the search portion, so it would go like

`mysearch... where NOT MsgId=="AUT22673" OR MsgId=="AUT23574" OR MsgId=="AUT20915" OR MsgId=="AUT22886"

| dedup User
| eval Cluster="C"+substr(Node,10,1)
| table MsgId`

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...