Splunk Search

Comparing values between two different fields in ad-hoc search and inputlookup

alexbradley
Explorer

Hello Splunkers,

I am attempting to match values (IP addresses) between FieldA in a search, and FieldB in an inputlookup. I want to come out with a table of only values in FieldB that are also in FieldA. Some pseudocode to explain my logic:

myList = []
for value in FieldB:
     if value in FieldA:
          myList.append(value)

I have attempted to use appendcols, append, if(like(..)), (if(match(..)), and simply search [|inputlookup ...] | where fieldA=fieldB without any luck. Comparing the fields directly with where fieldB=fieldA also does not provide any successful results. I am unable to simply compare in a list of values as strings, as there are potentially hundreds of thousands of distinct values.

It is also noticeable that the name of fieldB (dest_ip) actually does exist in the ad-hoc search result set, but I am not at all concerned with those values, only those in the inputlookup.

Any help to a Splunk newbie is much appreciated, thank you!

edit - I did find partial success combining values(fieldA) and values(fieldB) in mv-fields and then expanding and checking against the values in the other. I got true positive results, but both data sets are so large that it far exceeds my memory limits for just a 60 minute window - let alone 1-3 months that I need.

0 Karma
1 Solution

gcusello
SplunkTrust
SplunkTrust

Hi alexbradley,
you have to use a subsearch using attention that the field name used in main search and subsearch is the same, so in your example:

index=my_index [ | inputlookup my_lookup.csv | rename fieldB AS fieldA | fields fieldA ]
| table _time fieldA 

Bye.
Giuseppe

View solution in original post

gcusello
SplunkTrust
SplunkTrust

Hi alexbradley,
you have to use a subsearch using attention that the field name used in main search and subsearch is the same, so in your example:

index=my_index [ | inputlookup my_lookup.csv | rename fieldB AS fieldA | fields fieldA ]
| table _time fieldA 

Bye.
Giuseppe

alexbradley
Explorer

Thanks for your input Giuseppe; unless I grossly misunderstand, however, this doesn't find matching values between the two fields - rather gives me listings of events with fields FieldA and _time without regard to matching between the ad-hoc search and the inputlookup.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

Have you tried the search? Your comment implies you have not.

Subsearches are like parentheses in math - they come first. The |inputlookup... subsearch expands into a list of fieldA values that, when combined with index=my_index creates a search through my_index for all fieldA values present in the lookup table. That's sounds like what you're looking for.

---
If this reply helps you, Karma would be appreciated.
0 Karma

alexbradley
Explorer

Now with less weekend-brain: I do see what you're getting at and will run this at the first opportunity. Thanks for the clarification, richgalloway.

0 Karma

richgalloway
SplunkTrust
SplunkTrust

Try index=foo [| inputlookup myList.csv | format]

---
If this reply helps you, Karma would be appreciated.
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 ...