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

[fedejko]

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.

[tscroggins]

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.