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

[alexbradley]

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.

[gcusello · Accepted solution]

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]

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.

[richgalloway]

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.

[alexbradley]

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.

[richgalloway]

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