Splunk Search

Searching result using two inputlookup collections

ssamant007
Explorer

I need to find the rows from the first inputlookup collection that has matching field values from the second inputlookup collection.

For example:

collection A :      field1, field2, field3

                                  X             1          3

                                   X            2         4

                                    Y            4         1

                                    Z             1        2

                                     B            3         3

                                     B            1           1

CollectionB:    fieldX

                               X       

                               Y         

                               B          

The expected result is: (exclude row containing 'Z' as it does not entry in collectionB)

                                 field1, field2, field3

                                  X             1          3

                                   X            2         4

                                   Y            4         1

                                    B            1           1

the query like:

| inputlookup collectionA | search field1 IN ('X','Y','Z'....).

How can I set  values 'X','Y','Z'....  to search for field1 from collectionB as this list can be of any length. I tried the following but didn't work:

| inputlookup collectionA | search field1 IN (| inputlookup collectionB  |fields fieldX). (as in reality the collectionB can have more than one columns but I want to match values only with fieldX)

Labels (2)
0 Karma
1 Solution

ITWhisperer
SplunkTrust
SplunkTrust

Try something like this

| inputlookup collectionA | search [| inputlookup collectionB | fields fieldX | rename fieldX as field1 | format]

View solution in original post

ITWhisperer
SplunkTrust
SplunkTrust

Try something like this

| inputlookup collectionA | search [| inputlookup collectionB | fields fieldX | rename fieldX as field1 | format]

View solution in original post

ssamant007
Explorer

Thanks @ITWhisperer .

0 Karma

ssamant007
Explorer

What if I need to match with only subset of fieldX values from collectionB into the field1 values from collectionA assuming the collectionB has other fields .

Like 

collectionB:

  fieldX,   fieldY

 Y              ss

A               zz

B               yy

i.e. first I need to extract results from collectionB based on column 'fieldY' say fieldY='zz' 

| inputlookup collectionA | search [| inputlookup collectionB | search fieldY ='zz' | fields fieldX | rename..... but it didnt serve my required results. It is returning all the rows from collection A.

 

0 Karma
Did you miss .conf21 Virtual?

Good news! The event's keynotes and many of its breakout sessions are now available online, and still totally FREE!