topic Re: Why is my search with "where NOT equals this OR this OR this" not filtering out results as expected? in Splunk Search

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

jsven7 — Tue, 08 Dec 2015 19:01:39 GMT

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

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

jsven7 — Tue, 08 Dec 2015 19:13:54 GMT

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

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

_dave_b — Tue, 08 Dec 2015 19:26:52 GMT

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`

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

Runals — Tue, 08 Dec 2015 19:38:17 GMT

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?

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

MuS — Tue, 08 Dec 2015 19:39:33 GMT

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_AQuickGuideToSearch.pdf

Hope this helps ...

cheers, MuS

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

woodcock — Tue, 08 Dec 2015 20:56:00 GMT

Try this:

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

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

jsven7 — Wed, 09 Dec 2015 14:12:11 GMT

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.

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

dbroggy — Mon, 06 May 2019 18:43:03 GMT

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

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

QQAL2021 — Fri, 11 Mar 2022 06:05:43 GMT

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]

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

anwarmian — Tue, 18 Jun 2024 19:25:35 GMT

Correct. This is also known as the De Morgan's Law of Union Or De Morgan's Law of Intersection