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