Splunk Search

searching a lookup table with multiple values in a field

raysonjoberts
Path Finder

I have a csv file that that I am using for a lookup which has multiple values in a particular field. I am trying to do a lookup which matches any one of the field values.

example:

lookup table file -
room,color
livingroom,purple|green|yellow

(the pipe symbol delineates the different values in the color field)

Then my search -


<base search> 
| lookup paint_colors room OUTPUTNEW color
| search color=purple
| fields room,color
| stats list by room


My desired result would be to see livingroom in the results. Is it possible to search for any one value in a field with multiple values?

Thanks in advance!


 

 

Labels (2)
0 Karma

ITWhisperer
SplunkTrust
SplunkTrust
<base search> 
| lookup paint_colors room OUTPUTNEW color
| eval match=if(match(room_color,color),"matched",null())
| where match="matched"
| fields room,color
| stats list by room
0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@raysonjoberts 

Try where for searching

| where like(color,"%purple%")

 

KV

raysonjoberts
Path Finder

Thanks KV. 
I think I have over simplified my question.
What I am really after is for each value in the lookup table field to be recognized as individual values and not a single string. So for example, a different search might be

<base search>
| lookup paint_colors room OUTPUTNEW color
| stats count by room,color

And my desired outcome would be

livingroom purple 1
livingroom green 1
livingroom yellow 1

 

 

0 Karma

kamlesh_vaghela
SplunkTrust
SplunkTrust

@raysonjoberts 

try this

<base search>
| lookup paint_colors room OUTPUTNEW color
| where like(color,"%purple%") 
| eval color=split(color,"|")
| stats count by room,color

KV

 

0 Karma
Get Updates on the Splunk Community!

See just what you’ve been missing | Observability tracks at Splunk University

Looking to sharpen your observability skills so you can better understand how to collect and analyze data from ...

Weezer at .conf25? Say it ain’t so!

Hello Splunkers, The countdown to .conf25 is on-and we've just turned up the volume! We're thrilled to ...

How SC4S Makes Suricata Logs Ingestion Simple

Network security monitoring has become increasingly critical for organizations of all sizes. Splunk has ...