Splunk Search

Hashtable Functionality OR lookup Tables

daktapaal
Path Finder

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?

Tags (1)
0 Karma
1 Solution

laserval
Communicator

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.

View solution in original post

0 Karma

laserval
Communicator

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.

0 Karma

daktapaal
Path Finder

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

0 Karma

nekb1958
Path Finder

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

0 Karma

daktapaal
Path Finder

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

0 Karma

daktapaal
Path Finder

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.

0 Karma
Get Updates on the Splunk Community!

Introducing the Splunk Community Dashboard Challenge!

Welcome to Splunk Community Dashboard Challenge! This is your chance to showcase your skills in creating ...

Get the T-shirt to Prove You Survived Splunk University Bootcamp

As if Splunk University, in Las Vegas, in-person, with three days of bootcamps and labs weren’t enough, now ...

Wondering How to Build Resiliency in the Cloud?

IT leaders are choosing Splunk Cloud as an ideal cloud transformation platform to drive business resilience,  ...