topic Re: How to know which value from lookup table matched the log in Splunk Search

How to know which value from lookup table matched the log

hFHUT2 — Wed, 07 Apr 2021 21:06:16 GMT

I have a lookup table that has a list of values in it similar to:

id	value
1	test_value1
2	test_value2

I can search for all logs that have any of these values in them by doing:

index=blah sourcetype=blah [|inputlookup test_values | rename value AS search | fields search | format]

This creates a search such as:

index=blah sourcetype=blah ( ( "test_value1" ) OR ( "test_value2" ) )

What I'm trying to figure out is how to know which value from the lookup table was responsible for matching each log.

However, because I'm not searching for the values in any particular field in the logs, and the fact that the lookup table values might be substrings of a larger value in the logs, I don't think I can use "| lookup" in order to match back to the lookup table.

Put another way that doesn't even necessarily need to involve a lookup table, if you had this search:

index=blah sourcetype=blah *this* OR *that*

Is there any way to create a new field within the matching logs that would contain either the string "this" or "that" depending on which one was the cause of the match?

Re: How to know which value from lookup table matched the log

bowesmana — Wed, 07 Apr 2021 21:59:05 GMT

@hFHUT2

Couple of suggestions:

1. You can always use rex to extract the fields from your data, e.g.

<your search> | rex field=_raw ".*(?<event_has_this>this).*" | rex field=_raw ".*(?<event_has_that>that).*" | rex field=_raw ".*(?<event_has_other>other).*" | foreach event_* [ eval matches_found=mvappend(<<FIELD>>, matches_found) ]

this will create a new field event_has_X for each match found and then the last foreach line will collect all the matches found. (This assumes it's possible to have more than one match in the data)

If you just want a single (first) match, you could use

| eval match=coalesce(event_has_this, event_has_that, event_has_other)

2. An alternative is to use a lookup definition with wildcard, so configure the lookup definition with

WILDCARD(value)

and then surround your lookup values with *, i.e. *test_value1*

then do

| lookup definition_name value as _raw OUTPUT id value as found_value

Hope this helps

Re: How to know which value from lookup table matched the log

hFHUT2 — Thu, 08 Apr 2021 12:49:16 GMT

I really like your wildcard lookup table suggestion. It seems like the cleanest fit for what I'm trying to do. I need to have someone enable that wildcard option on the KV Store I'm using so that I can give it a shot.

However, for your first suggestion, I'm unclear as to how I would actually make that work. In your example, you have three separate "rex" commands, but I'm not sure how to make that work with the lookup table... I would need to somehow automatically add those into the search for every value in the lookup table, which is a lot of them.

I'll report back when I have that set up so that I can potentially mark your answer as accepted. Thanks!

Edit: I got the WILDCARD setting enabled, and it works beautifully! Thank you so much! I didn't realize that was an option.

Re: How to know which value from lookup table matched the log

bowesmana — Sun, 11 Apr 2021 10:41:06 GMT

On the rex part of my suggestion, as you suggested, it's not a practical solution for many matches. Glad the lookup worked though.