I have a csv lookup table with 3 columns, eg:
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.
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!
(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]
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
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:
@romelrkhan - If the accepted version has any performance problems, then convert to using the
lookup verb with
OUTPUT for the first
OUTPUTNEW for the second
(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
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.
Lookup is significantly more efficient than a
join in this kind of situation.
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.