Splunk Search
Highlighted

How to use two lookups for comparison

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
Highlighted

Re: How to use two lookups for comparison

Explorer

Any suggestion/idea experts?

0 Karma
Highlighted

Re: How to use two lookups for comparison

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
Highlighted

Re: How to use two lookups for comparison

Explorer

@somesoni2

Thanks Somesh.

0 Karma
Highlighted

Re: How to use two lookups for comparison

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
Highlighted

Re: How to use two lookups for comparison

SplunkTrust
SplunkTrust

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

0 Karma
Highlighted

Re: How to use two lookups for comparison

Communicator

Try this:

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

Re: How to use two lookups for comparison

Explorer

@Raschko

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

0 Karma
Highlighted

Re: How to use two lookups for comparison

Communicator

Have you put the following after the eval?

| fields - BOX1 BOX2
0 Karma