How to correlate field values between an index and a lookup file?

[russell120]

Hi,

I have a CSV ( current_assets.csv) with fields device_name and ip (and tons of values for them). Here is an example:

device_name        ip
  router1     122.145.11.2
  laptop2     11.121.44.55

How do I search my index ( sourcetype="device_assets") for the CSV IPs and return whether or not each IP is found within the index?

An example result would be:

device_name        ip        found
  router1     122.145.11.2    Yes
  laptop2     11.121.44.55    No

Important note: The solution CANNOT use |join command because this is very intensive/slow for my current deployment.

Thanks

[renjith_nair · Accepted solution]

@russell120 ,

Try

|inputlookup current_assets.csv|eval source="lookup" 
| append [search index="your index" sourcetype="device_assets"|stats count by ip|fields ip|eval source="events"]
| stats values(device_name) as device_name , values(source) as source by ip|where mvcount(source) >1 OR source="lookup"
| eval found=if(mvcount(source)>1,"Yes","No")|fields - source

[russell120]

This works, thanks.

[bangalorep]

Hello,
You could usee the inputcsv command. The syntax would be sourcetype="device_assets" | inputcsv current_assets.csv
Documentation on this command https://docs.splunk.com/Documentation/Splunk/latest/SearchReference/Inputcsv

[russell120]

This returns a "Error in 'inputcsv' command: This command must be the first command of a search" error.