Splunk Search

Using Inputlookup to find a string contains a value and more

73mustang
Engager

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.

Labels (1)
0 Karma

yuanliu
SplunkTrust
SplunkTrust

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

flagtypeflagstring1colorkey
stickerKEYWORD FLAGI like blue berriesblue
 KEYWORD FLAGblack is all colors 
stickerKEYWORD FLAGThe sea is blueblue
tapeKEYWORD FLAGTheredsunisredhotred

Hope this helps.

73mustang
Engager

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.

0 Karma

yuanliu
SplunkTrust
SplunkTrust

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

0 Karma
Get Updates on the Splunk Community!

Splunk Custom Visualizations App End of Life

The Splunk Custom Visualizations apps End of Life for SimpleXML will reach end of support on Dec 21, 2024, ...

Introducing Splunk Enterprise 9.2

WATCH HERE! Watch this Tech Talk to learn about the latest features and enhancements shipped in the new Splunk ...

Adoption of RUM and APM at Splunk

    Unleash the power of Splunk Observability   Watch Now In this can't miss Tech Talk! The Splunk Growth ...