Using Inputlookup to find a string contains a value and more

[73mustang]

Hi guys, So heres  what im trying to do. I have a lookup csv with 3 columns. I have data with string values that might contain a value in my lookup. I have the basic setup working but i want to populate additional fields in my data set. Here is a very stripped down version of what i am doing.  First I have a basic lookup csv. It has  3 columns:

active flagtype colorkey
yes sticker blue
yes tape red
no tape pink

then my search which creates a couple test records looks like this:

| makeresults count=4
| streamstats count
| eval number = case(count=1, 25, count=2, 39, count=3, 31, count=4, null())
| eval string1 = case(count=1, "I like blue berries", count=3, "The sea is blue", count=2, "black is all colors", count=4, "Theredsunisredhot")
| table flagtype, flag, string1, ck
|search [ inputlookup templookup.csv
| eval string1 = "string1=" + "\"" + "*" + colorkey + "*" + "\""
| return 500 $string1
]
| eval flag = "KEYWORD FLAG"
| table flagtype, flag, string1, colorkey

my 4 column output results are:

flagtype flag string1 colorkey
empty   KEYWORD FLAG   I like blue berries     empty
empty   KEYWORD FLAG   The sea is blue          empty
empty   KEYWORD FLAG   Theredsunisredhot empty

How do  I populate the two empty columns using other columns in the lookup table.

Thanks in advance for any help I can get.

[yuanliu]

You cannot do this with simple event search as you attempted.  To add fields (sometimes called "enrichment"), you need to use lookup command. (Or join with inputlookup and sacrifice performance.  But this doesn't apply in your case.)  Your question is really about wanting to match a wildcard at the beginning of a key, which lookup does not support.  Given your sample data, you don't seem to have a real choice.  So, you will have to take some performance penalty and perform string matches yourself.

People (including myself) used to work around similar limitations in lookup with awkward mvzip-mvexpand-split sequences and the code is difficult to maintain.  Since 8.2, Splunk introduced a set of JSON functions that can represent data structure more expressively.  Here is one method:

| makeresults count=4
| streamstats count
| eval number = case(count=1, 25, count=2, 39, count=3, 31, count=4, null())
| eval string1 = case(count=1, "I like blue berries", count=3, "The sea is blue", count=2, "black is all colors", count=4, "Theredsunisredhot")
| table string1
| append
    [| inputlookup wildlookup.csv
| tojson output_field=wildlookup
| stats values(wildlookup) as wildlookup
| eval wild = json_object()
| foreach wildlookup mode=multivalue
    [ eval wild = json_set(wild, json_extract(<<ITEM>>, "colorkey"), <<ITEM>>)]
| fields wild]
| eventstats values(wild) as wild
| where isnotnull(string1)
| eval colors = json_keys(wild)
| foreach colors mode=json_array
    [eval colorkey = mvappend(colorkey, if(match(string1, <<ITEM>>), <<ITEM>>, null()))]
| mvexpand colorkey ``` in case of multiple matches ```
| foreach flagtype active
    [eval <<FIELD>> = json_extract(json_extract(wild, colorkey), "<<FIELD>>")]
| eval flag = "KEYWORD FLAG"
| table flagtype, flag, string1, colorkey

Note I stripped fields that are irrelevant to the resultant table.  I also made provisions to protect possible multiple color matches.  The output is

flagtype	flag	string1	colorkey
sticker	KEYWORD FLAG	I like blue berries	blue
	KEYWORD FLAG	black is all colors
sticker	KEYWORD FLAG	The sea is blue	blue
tape	KEYWORD FLAG	Theredsunisredhot	red

Hope this helps.

[73mustang]

Thanks for the sample. I opted to add a column "key" to my csv file, with wild card before and after the colorkey, (*blue*  for example) then add a lookup to the search after the inputlookup section.      | lookup keywords.csv key as "String1" output Key .  I'm not sure of the performance ramifications, I don't see any difference in run times.

[yuanliu]

If you don't observe performance degradation, you needn't worry about it.