Splunk Search

Using lookup table on multivalue field to exclude events

Path Finder

I've found some variations on this issue but nothing exactly the same. Go easy on me...

I'm dealing with events that includes a space delimited field containing column names. Over the years, we've pinpointed columns that we've learned do not include any data we need to be concerned about and have built a lookup table with the specific column names.

As the query is currently built, we are excluding any event that matches a value in the lookup table, regardless of what other column name is present. The problem is we want to see events that include columns not found in the lookup. For example:

bad_column1 bad_column37 (excluded, both bad columns)
bad_column column_to_investigate (should not be excluded)

I've been toying with makemv to try and parse out the columns on an individual basis but I've only been able to get the basic implementation to work where any event with a bad column is excluded (I wrote the below from memory so it may have some syntax errors).

base search NOT [ | inputlookup "bad_columns.csv" | rename bad_columns as 
query ] | table field1, field2, Columns

I'm not married to the multivalue solution but it seems like there's something there. If there's a better suggestion, by all means let me know.

Edit: I have a query that working somewhat but I lose precision during large scans (event counts are lower than the actual number of events). Any assistance is appreciated:

base search | eval UID = _cd | eval singleColumns=split(column_name, " ") | mvexpand 
singleColumns | search NOT [|inputlookup Known_Bad_Columns | rename bad_columns as 
singleColumns ] | dedup UID | stats count by field1, field2 | sort by count desc
1 Solution

Path Finder

My last revision is actually working (really slowly)! I added the tuple as my UID and it returns all the events, which I found here:

https://answers.splunk.com/answers/49/does-each-splunk-event-have-a-unique-identifier.html

base search | eval UID = _cd + index+splunk_server | eval singleColumns=split(column_name, " ") |mvexpand singleColumns | search NOT [|inputlookup Known_Bad_Columns | rename bad_columns as singleColumns ] | dedup UID | stats count by field1, field2 | sort by count desc

View solution in original post

0 Karma

Path Finder

My last revision is actually working (really slowly)! I added the tuple as my UID and it returns all the events, which I found here:

https://answers.splunk.com/answers/49/does-each-splunk-event-have-a-unique-identifier.html

base search | eval UID = _cd + index+splunk_server | eval singleColumns=split(column_name, " ") |mvexpand singleColumns | search NOT [|inputlookup Known_Bad_Columns | rename bad_columns as singleColumns ] | dedup UID | stats count by field1, field2 | sort by count desc

View solution in original post

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!