Splunk Search

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

romelrkhan
New Member

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.

0 Karma
1 Solution

DalJeanis
SplunkTrust
SplunkTrust

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

View solution in original post

0 Karma

DalJeanis
SplunkTrust
SplunkTrust

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

0 Karma

elliotproebstel
Champion

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

0 Karma

HiroshiSatoh
Champion

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]
0 Karma

lloydknight
Builder

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!

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...