Splunk Search

How to use two lookups for comparison

mprreddy51
Explorer

Hi All,

Here is my requirement:

I have 100 values (abc1,def1,....etc) in lookup1 and 100 values in lookup2 (ABC1,DEF1....etc) and I want to do this with LOOKUPS only, not case or if because of 100's of values in lookup1 and lookup2.

Events:

20140122T100513 EMP MESSAGE=RES COUNTRY=USA ACCNO=1234 TYPE=SET COUNT=0 STATUS=P REASON=ABC1
20140122T100515 EMP MESSAGE=RES COUNTRY=USA ACCNO=1235 TYPE=SET COUNT=1 STATUS=P REASON=abc1
20140122T100516 EMP MESSAGE=RES COUNTRY=USA ACCNO=1236 TYPE=SET COUNT=0 STATUS=P REASON=def1
20140122T100517 EMP MESSAGE=RES COUNTRY=USA ACCNO=1237 TYPE=SET COUNT=1 STATUS=P REASON=DEF1
20140122T100513 EMP MESSAGE=RES COUNTRY=USA ACCNO=1238 TYPE=SET COUNT=0 STATUS=P REASON=ghi1
20140122T100515 EMP MESSAGE=RES COUNTRY=USA ACCNO=1239 TYPE=SET COUNT=1 STATUS=P REASON=KLM1
20140122T100516 EMP MESSAGE=RES COUNTRY=USA ACCNO=1240 TYPE=SET COUNT=0 STATUS=P REASON=OPQ1
20140122T100517 EMP MESSAGE=RES COUNTRY=USA ACCNO=1241 TYPE=SET COUNT=1 STATUS=P REASON=xyz1

lookup1.csv

abc1
def1
ghi1
xyz1

lookup2.csv

ABC1
DEF1
KLM1
OPQ1

Expected output in a table:

ACCNO    TYPE    STATUS    REASON    BOX
1234     SET     P         ABC       BOX2
1235     SET     P         abc       BOX1
1236     SET     P         def       BOX1
1237     SET     P         DEF       BOX2
1238     SET     P         ghi       BOX1
1239     SET     P         KLM       BOX2
1240     SET     P         OPQ       BOX2
1241     SET     P         xyz       BOX1

Thanks in advance.

-P

0 Karma
1 Solution

somesoni2
SplunkTrust
SplunkTrust

Try like this

your base search  | lookup lookup1.csv REASON OUTPUTNEW REASON as BOX1 | lookup lookup2.csv REASON OUTPUTNEW REASON as BOX2 | eval BOX=if(isnotnull(BOX1),"BOX1","BOX2") | fields - BOX1 BOX2

View solution in original post

0 Karma

somesoni2
SplunkTrust
SplunkTrust

Try like this

your base search  | lookup lookup1.csv REASON OUTPUTNEW REASON as BOX1 | lookup lookup2.csv REASON OUTPUTNEW REASON as BOX2 | eval BOX=if(isnotnull(BOX1),"BOX1","BOX2") | fields - BOX1 BOX2
0 Karma

mprreddy51
Explorer

@somesoni2

Thanks Somesh.

0 Karma

mprreddy51
Explorer

Hi @somesoni2

If i use this query if any REASON comes apart from lookup1 and lookup2 by default it is taking BOX1.In this case it should show "N/A"

for example: If i get this new event with different REASON as shown below.

20150126T100519 EMP MESSAGE=RES COUNTRY=USA ACCNO=1290 TYPE=SET COUNT=1 STATUS=P REASON= ASIA
By defalut it is going to BOX1. It should display N/A

0 Karma

Raschko
Communicator

Try this:

| eval BOX=if(isnull(BOX1),if(isnull(BOX2),"N/A","BOX2"),"BOX1")
0 Karma

mprreddy51
Explorer

@Raschko

It is showing all "N/A" only no luck

0 Karma

Raschko
Communicator

Have you put the following after the eval?

| fields - BOX1 BOX2
0 Karma

somesoni2
SplunkTrust
SplunkTrust

Replace | eval BOX=if(isnotnull(BOX1),"BOX1","BOX2") with | eval BOX=coalesce(BOX1,BOX2,"N/A")

0 Karma

mprreddy51
Explorer

Any suggestion/idea experts?

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