topic Re: Hashtable Functionality OR lookup Tables in Splunk Search

Hashtable Functionality OR lookup Tables

daktapaal — Mon, 28 Sep 2020 15:41:51 GMT

Hi All,
I have a lookup table that looks like:

Key,value

cat1,val1

cat2,val2

cat3,val3

this is in a lookup file called keyvalpairs.csv

i want to query the look up table to return value when a key is passed in.

key is a concat of two field values in a search

i want a

pseudo query

that looks something like,

sourcetype = * | eval keyfield = field1."#'.field2 | lookup keyvalpairs.csv [where Key = **keyfield] OUTPUT value |

so that the concat of field1 and field2 from the events is looked into the CSV and the corresponding value is printed..

is this doable?

Re: Hashtable Functionality OR lookup Tables

nekb1958 — Wed, 22 Jan 2014 10:26:14 GMT

I do not really understand your question. looking at the syntax of the lookup command

http://docs.splunk.com/Documentation/Splunk/6.0.1/SearchReference/Lookup

lookup [local=<bool>] [update=<bool>] <lookup-table-name> (<lookup-field> [AS <local-field>] ) ( OUTPUT | OUTPUTNEW <lookup-destfield> [AS <local-destfield>] )

so for me, your lookup looks like:

lookup keyvaluepairs keyfield OUTPUT value

Re: Hashtable Functionality OR lookup Tables

daktapaal — Wed, 22 Jan 2014 13:15:13 GMT

i did lookup that syntax in docs.. but my query should do the following :
1. search on the sourcetype: *
2.create an interim variable called keyfield which has the concat of the field1 and field2
3.look up into the CSV ( it has two headings: Key and Value.) for the record, where key = keyfield variable we evaluated.
4. Output the value of the record, where Key = key field.
so the pseudo query, or the query i would have thought, which obviously won't work, is :sourcetype=*|eval keyfield = field1."#'.field2 | lookup kevalpairs.csv [Key=keyfield] OUTPUT val.

Re: Hashtable Functionality OR lookup Tables

daktapaal — Wed, 22 Jan 2014 13:20:33 GMT

i can't do your suggestion because for lookup keyvaluepairs keyfield OUTPUT value to work, there should be a header in csv called keyfield. but csv only has key and value as header. so i was hoping , there could be a solution where i can search for a record in the csv. where key = keyfield. and return Value entry for the corresponding record

Re: Hashtable Functionality OR lookup Tables

laserval — Wed, 22 Jan 2014 15:49:50 GMT

~~You can't query the lookup files, unfortunately.~~ Mistunderstood you. As the earlier answer noted, this is exactly what lookup does. Just use key AS keyfield.

I would do

... | eval keyfield = field1."#".field2 | lookup keyvalpairs key AS keyfield OUTPUT value | where isnotnull(value)

Note: if you've set a default value for your lookup, you'll need to check for that instead of null.

You would then filter out any events or rows that do not have keyfield, or where keyfield doesn't match a key in the lookup table.

Re: Hashtable Functionality OR lookup Tables

daktapaal — Thu, 30 Jan 2014 01:57:56 GMT

This is great. Thanks for this.. although I figured that if I keep the variable name same as the name of the key, then it works straight away . Thanks again