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.
@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.
@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.
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
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]
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!