topic can lookup be used to match default value in .csv table in Splunk Search

can lookup be used to match default value in .csv table

romelrkhan — Sat, 21 Oct 2017 15:32:50 GMT

I have a csv lookup table with 3 columns, eg:
input1,input2,output
240,789,303456
240,330,303457
240,default,303458
246,345,249580
246,330,249589
246,default,230444

The fields to match on input1 and input2 are 3 digit numbers. If input1 matches (240 and 246 in this example) and then input2 matches, then this is a straightforward lookup comand; eg, 240 and 789 will output 303456. But say input1 is 240 and input2 is 389, I need it to match the "240,default,303458" in the table. How can I achieve it in splunk? Thanks.

Re: can lookup be used to match default value in .csv table

lloydknight — Sun, 22 Oct 2017 03:38:30 GMT

Hello @romelrkhan

assuming 389=default, try this

index=indexname |eval input2=if(input2=389,default,input2) | table input1 input2 output

or give a more detailed background on the other fields.

Hope it helps!

Thanks!

Re: can lookup be used to match default value in .csv table

HiroshiSatoh — Sun, 22 Oct 2017 04:28:39 GMT

try this!

(your search ※Including input1,input2)
|join type=outer input1 [|inputlookup XXX.csv where input2=default|table input1,output]
|join type=outer input1,input2 [|inputlookup XXX.csv |table input1,input2,output]

Re: can lookup be used to match default value in .csv table

elliotproebstel — Mon, 23 Oct 2017 00:55:33 GMT

If "default" only appears in the column for input2, then I think this is a more efficient way of matching:
your base search | lookup youlookup.csv input1 OUTPUT input2 AS check_input2 output | where isnotnull(check_input2) | eval match=if(input2=check_input2 OR check_input2="default", 1, 0) | where match=1

This won't work if "default" can appear in the column for input1.

If you are open to changing the content of your lookup table, you might find it easier to convert the instances of default to a wildcard and use wildcard matching. It's not very well documented in the Splunk docs (at least not that I can find), but this other answer provides the structure you'd need:
https://answers.splunk.com/answers/52580/can-we-use-wildcard-characters-in-a-lookup-table.html

Re: can lookup be used to match default value in .csv table

DalJeanis — Mon, 23 Oct 2017 02:29:56 GMT

@romelrkhan - If the accepted version has any performance problems, then convert to using the lookup verb with OUTPUT for the first lookup and OUTPUTNEW for the second lookup.

Try this...

 (your search ※Including input1A,input2A)
| lookup xxx.csv inputname1 as input1A inputname2 as input2A OUTPUT outputname1
| eval defaultname = "default"
| lookup xxx.csv inputname1 as input1A inputname2 as defaultname OUTPUTNEW outputname1

Assumptions:

1) Your lookup table has fields inputname1 inputname2 and outputname1. *(Please don't use keywords like output for a field name, even in examples. It makes puppies cry.) *

2) Your main search returns fields named input1A and input2A.

Explanation:

3) Lookup is significantly more efficient than a join in this kind of situation.

4) The OUTPUTNEW keyword will prevent the second lookup from even running if the first one has succeeded. so it should be significantly more efficient than even a one-outer-join version.