Match substring in list to event

[jq06]

I am using lookup to "house" this long list of keywords. Now, I want to run a query against field A (eg. ABC-DEF-ZYL) of my events, to see if there is a substring match against the list of keywords (eg. DEF) How do I: 1) Run the search through so that an additional column comes up to show what has been positively matched. 2) Sort keywords by highest number of occurrence

[David · Accepted solution]

This may not be the best approach, but I think it should get you there.

Assuming the keywords (DEF) and field A (ABC-DEF-ZYL) are going to be exactly that format (e.g., Field A will have a series of keywords separated by hyphens), I would probably take the following approach:

YourSearch | makemv delim="-" FieldA | lookup MyTable FieldA OUTPUT Description | search Description=*

Where your lookup table is:

FieldA,Description
ABC,ABC
DEF,DEF
ZYL,ZYL

You could then do stats based off that, such as:

YourSearch | makemv delim="-" FieldA | Lookup MyTable FieldA OUTPUT Description 
           | search Description=* | stats count by Description

http://www.splunk.com/base/Documentation/latest/SearchReference/Makemv

http://www.splunk.com/base/Documentation/latest/SearchReference/Lookup

[David]

I'm glad it works. I should improve my point by saying that a "better" approach would probably be to have the Description be something actually useful. For example, in your Lookup table, you could have:
ABC,"ABC - Transaction successful"
DEF,"DEF - Database Deadlock, Contact Support Immediately!"
And then the rest would work just fine. You might change the last query to
...| search Description=* | stats count by FieldA
to maintain statistics about FieldA, from from a methodology perspective, if we're adding data we should add useful data. Anyway -- Food for Thought.

[jq06]

Great stuff! Thanks.