Splunk Search

user!=xxx user!=yyy VS. NOT user IN (xxx yyy)

Explorer

Hi,

when building queries I'm all for their clean look and readability - of course performance always matters more.

But I have this case in which I want to exclude lots of usernames from my search:

username!=Johndoe username!=Mikesomeone username!=Jennifersomeoneelse

but this looks much shorter and cleaner:

NOT username IN (Johndoe Mikesomeone Jennifersomeoneelse)

I've been using a lot the "field IN (value1 value2 value3)" but only today I've started using it with the NOT operator. Unfortunately, after I press CTRL+SHIFT+E in the query editor, I see that this shorter "NOT value IN ()" is transformed to

NOT user=xxx NOT user=yyy ...etc

As far as I know this is not the same as user!=xxx AND user!=yyy and returns more results which I need.

Is there a way of a shorter query to do the same as explicit exclusion of multiple values from one field?

I've looked through similar topic: https://answers.splunk.com/answers/48398/how-to-exclude-a-list-of-values-for-a-field.html but my case is not covered there. I'll appreciate all help.

0 Karma

Path Finder

I've been under the impression that both the = and IN operators require that a field be defined. If you want to include events where username is not defined, i.e. null is valid but not in your value set, add the following to your search:

NOT username IN (Johndoe Mikesomeone Jennifersomeoneelse) OR NOT username=*

The search optimizer itself may treat =, IN and !=, NOT IN as equivalent and expand IN to = internally irrespective of the search bar formatter. Perhaps a Splunk employee can confirm.

0 Karma
State of Splunk Careers

Access the Splunk Careers Report to see real data that shows how Splunk mastery increases your value and job satisfaction.

Find out what your skills are worth!