Hashtable Functionality OR lookup Tables

[daktapaal]

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?

[laserval · Accepted solution]

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.

[daktapaal]

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

[nekb1958]

Hi

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

[daktapaal]

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

[daktapaal]

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.