topic Re: How to correlate field values between an index and a lookup file? in Getting Data In

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

russell120 — Tue, 22 Jan 2019 20:28:08 GMT

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

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

bangalorep — Wed, 23 Jan 2019 03:46:20 GMT

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

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

renjith_nair — Wed, 23 Jan 2019 03:47:24 GMT

@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

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

russell120 — Wed, 23 Jan 2019 13:48:21 GMT

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

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

russell120 — Wed, 23 Jan 2019 16:38:45 GMT

This works, thanks.