How to exclude a list of values for a field?

[jundai]

Is there a shorthand for:

host=SOMEENV* Type=Error NOT EventCode=1234 NOT EventCode=2345 NOT EventCode=3456 NOT EventCode=4567 NOT EventCode=5678 NOT EventCode=6789 NOT EventCode=7890

Basically, I'm looking for something like:

host=SOMEENV* Type=Error NOT EventCode IN (1234, 2345, 3456, 4567, 5678, 6789, 7890)

but of course that doesn't work 🙂

[ckp123]

You can try 

<your search> | search NOT EventCode IN (1234, 2345, 3456, 4567, 5678, 6789, 7890)

Hope this help. @jundai  

[xavierashe]

Wow, look at all the options! This required some testing! So I have Qualys data and was sent a list of 43 QIDs they want filtered out. So I built a query for all the options above and ran them over a 24 hour period using Fast Mode. Then I did a 3 day query. I'm running v6.6.3 on a stand alone search head with 3 indexers. Tests were done in the evening with no other users on the SH.

First a control.

eventtype=qualys_vm_detection_event

This search has completed and has returned 135,534 results by scanning 135,534 events in 7.27 seconds
This search has completed and has returned 343,584 results by scanning 343,584 events in 13.817 seconds

The answer provided by @Damien Dallimore:

eventtype=qualys_vm_detection_event NOT (QID=38606 OR QID=86477 OR QID=86175 OR QID=86771 OR QID=38606 OR QID=38601 OR QID=38603 OR QID=42366 OR QID=82052 OR QID=38169 OR QID=38173 OR QID=38170 OR QID=82024 OR QID=38293 OR QID=42395 OR QID=86476 OR QID=82054 OR QID=82058 OR QID=38174 OR QID=82052 OR QID=82003 OR QID=82005 OR QID=38172 OR QID=82048 OR QID=70000 OR QID=34000 OR QID=86247 OR QID=38628 OR QID=45242 OR QID=34002 OR QID=15034 OR QID=15020 OR QID=15068 OR QID=34002 OR QID=86728 OR QID=86729 OR QID=38003 OR QID=105651 OR QID=38657 OR QID=38655 OR QID=11827 OR QID=86729 OR QID=105728)

This search has completed and has returned 124,758 results by scanning 135,534 events in 6.986 seconds
This search has completed and has returned 311,256 results by scanning 343,584 events in 13.116 seconds

When I tried @samkidman's regex trick, it didn't filter anything out. Here's the regex that did work. (I put in the carriage returns for readability)

eventtype=qualys_vm_detection_event | regex QID="^(?!(38606$|86477$|86175$|86771$|38606$|38601$|
38603$|42366$|82052$|38169$|38173$|38170$|82024$|38293$|42395$|86476$|82054$|82058$|
38174$|82052$|82003$|82005$|38172$|82048$|70000$|34000$|86247$|38628$|45242$|34002$|
15034$|15020$|15068$|34002$|86728$|86729$|38003$|105651$|38657$|38655$|11827$|86729$|105728$)).*"

This search has completed and has returned 124,758 results by scanning 135,534 events in 10.319 seconds
This search has completed and has returned 311,256 results by scanning 343,584 events in 18.323 seconds

Next up is @gkanapathy. I really like the elegance of this solution. However, this didn't work right either. I had to add some parentheses around the subsearch.

eventtype=qualys_vm_detection_event NOT ([ inputlookup bad_qids.csv | return 100 QID ])

This search has completed and has returned 124,758 results by scanning 135,534 events in 6.974 seconds
This search has completed and has returned 311,256 results by scanning 343,584 events in 13.057 seconds

Then @xxing brings it IN.

eventtype=qualys_vm_detection_event NOT QID IN (38606, 86477, 86175, 86771, 38606, 38601, 38603, 42366, 82052, 38169, 38173, 38170, 82024, 38293, 42395, 86476, 82054, 82058, 38174, 82052, 82003, 82005, 38172, 82048, 70000, 34000, 86247, 38628, 45242, 34002, 15034, 15020, 15068, 34002, 86728, 86729, 38003, 105651, 38657, 38655, 11827, 86729, 105728)

This search has completed and has returned 124,758 results by scanning 135,534 events in 6.858 seconds
This search has completed and has returned 311,256 results by scanning 343,584 events in 13.126 seconds

Personally, I'm a big fan of using !=.

eventtype=qualys_vm_detection_event QID!=38606 QID!=86477 QID!=86175 QID!=86771 QID!=38606 QID!=38601 QID!=38603 QID!=42366 QID!=82052 QID!=38169 QID!=38173 QID!=38170 QID!=82024 QID!=38293 QID!=42395 QID!=86476 QID!=82054 QID!=82058 QID!=38174 QID!=82052 QID!=82003 QID!=82005 QID!=38172 QID!=82048 QID!=70000 QID!=34000 QID!=86247 QID!=38628 QID!=45242 QID!=34002 QID!=15034 QID!=15020 QID!=15068 QID!=34002 QID!=86728 QID!=86729 QID!=38003 QID!=105651 QID!=38657 QID!=38655 QID!=11827 QID!=86729 QID!=105728

This search has completed and has returned 124,758 results by scanning 135,534 events in 7.001 seconds
This search has completed and has returned 311,256 results by scanning 343,584 events in 13.064 seconds

So there you have it. There isn't a clear winner, but there a loser in the bunch. Sorry regex, you just can't keep up. (Now if Splunk was written in Perl that would be a different story!) Since my use case is all about filtering out the same set of values out of different reports, I'm going with @gkanapathy's lookup solution.

[xxing]

Apparently after years development, splunk supports your first idea now:

host=SOMEENV* Type=Error NOT EventCode IN (1234, 2345, 3456, 4567, 5678, 6789, 7890)

should work at in 6.5.

Correction: it works from 6.6.

[AndyMarr]

Hooray - I've been looking for this exclusion list

[fedesg]

Tested in Splunk Version 6.6.0 and work for mi.

[gjanders]

Tested in 6.5.2 and it did not work as expected, are you sure that works? I do not see syntax highlighting for the "IN" keyword.

[tonymorin]

I thought "IN" is only in 6.6...

[gjanders]

Correct it is, thanks!

[joxley]

I do this quite a lot on dash boards where you have a multi-select populating a list of options. Start off with running this search to see what'll happen

| stats count | fields - count | eval EventCode="1234,2345,3456,4567" | makemv delim="," EventCode | format

Which gives the output ( ( ( EventCode="1234" OR EventCode="2345" OR EventCode="3456" OR EventCode="4567" ) ) ). This can be fed into your main search ala:

host=SOMEENV* Type=Error NOT [ | stats count | fields - count | eval EventCode="1234,2345,3456,4567" | makemv delim="," EventCode | format ]

For bonus points, let's pretend that there is a ParentEvent field and you want to exclude all events that have one of those parent events as well. You need to add the ParentEvent field to the subsearch and change the params to the format command so it has OR between the commands instead of AND

| stats count | fields - count | eval EventCode="1234,2345,3456,4567" | makemv delim="," EventCode | eval ParentCode=EventCode | format  "(" "(" "OR" ")" "OR" ")"

This outputs ( ( ( EventCode="1234" OR EventCode="2345" OR EventCode="3456" OR EventCode="4567" ) OR ( ParentCode="1234" OR ParentCode="2345" OR ParentCode="3456" OR ParentCode="4567" ) ) )

[samkidman]

or

host=SOMEENV* Type=Error | regex "EventCode=(?!1234|2345|3456|4567|5678|6789|7890)"

The first answer would propably perform better though. I find this a little easier to type.

[lakromani]

This gives slow performance on my big server. Minutes compare to 5 seconds on my search.

[e82than]

I think there is no field called Type=Error I looked up www.ultimatewindowssecurity.com. It doesn't seems to have a field by that name. Unless it is just a metaphor.

[e82than]

Ok, a more human way would be to hold ALT then click on the eventcode you do not want. Then save your searches.

Alternatively, if you're trying to lower the data indexed. You can perform selective indexing. e.g send events to nullqueue.

I did this before.

[sbsbb]

Splunk seems to be missing some "fields in(value1,value2...)" function....

[cttorres]

Hi!
If you see one of the comments above, Splunk now supports that since version 6.6.

[gkanapathy]

Or:

host=SOMEENV* Type=Error NOT [ inputlookup safecodes.csv | return 10000 EventCode ]

after you create a CSV lookup named safecodes.csv and populate it:

EventCode
1234
2345
3456
...

[xavierashe]

In 6.6.3, I had to enclose the subsearch in parenthesis for this to work.

host=SOMEENV* Type=Error NOT ([ inputlookup safecodes.csv | return 10000 EventCode ])

[jundai]

How do I create a CSV lookup? I can't seem to find that in the documentation. Keep in mind I'm not an admin and don't have access to the machine it's running on.

[Damien_Dallimor · Accepted solution]

host=SOMEENV* Type=Error NOT (EventCode=1234 OR EventCode=2345 OR EventCode=3456 OR EventCode=4567 OR EventCode=5678 OR EventCode=6789 OR EventCode=7890 )

[jundai]

This is definitely an improvement, thank you. But is more verbose than I was hoping for.