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]

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