topic Re: Use each row of a csv as an individual search and return the results in a table in Splunk Search

Use each row of a csv as an individual search and return the results in a table

tnegun — Tue, 14 May 2024 22:03:39 GMT

Hi all,

I've a csv file with 3 columns ip, earliest, latest and over 400 rows. I'm trying to return all evens associated with the IP for an hour before and after the interesting request time. The search below works for a single row but I can't figure out how treat each row as a unique search and compile the results at the end.

What appears to happen when I upload multiple rows in the csv is the search will run for all interesting IPs from the earliest earliest value to the latest latest value. It kind of meets the intent but is very wasteful as the index is huge and the times span several years with days/months between them.

Is what I'm trying to achieve possible?

index=myindex client_ip_address earliest latest [| inputlookup ip_list_2.csv | eval ip = "*" . 'Extracted IP' . "*" | eval earliest=strptime('REQUEST_TIME', "%m/%d/%y %H:%M")-(60*60) | eval latest=strptime('REQUEST_TIME', "%m/%d/%y %H:%M")+(60*60) | fields ip earliest latest ]

Re: Use each row of a csv as an individual search and return the results in a table

ITWhisperer — Tue, 14 May 2024 22:56:13 GMT

I am not sure what the problem is if it works! Having said that, I am not sure what the earliest and latest are doing on the index line. Try something like this

index=myindex client_ip_address [| inputlookup ip_list_2.csv | eval ip = "*" . 'Extracted IP' . "*" | eval earliest=strptime('REQUEST_TIME', "%m/%d/%y %H:%M")-(60*60) | eval latest=strptime('REQUEST_TIME', "%m/%d/%y %H:%M")+(60*60) | fields ip earliest latest ]

The subsearch becomes a series of (ip=value AND earliest=value AND latest=value) joined by ORs which is what you appear to want. Or am I missing something?

Re: Use each row of a csv as an individual search and return the results in a table

bowesmana — Tue, 14 May 2024 23:55:36 GMT

What @ITWhisperer , but I suspect your problem is that you have

client_ip_address earliest latest

in your initial search term, which I am guessing corresponds to ip earliest latest in your lookup. If your data contains a field called ip and that is what you are calling client_ip_address, then remove client_ip_address also from your search.

If your data contains a field called client_ip_address and that is supposed to be a match for the ip in the lookup, then in your subsearch rename ip as client_ip_address.

Re: Use each row of a csv as an individual search and return the results in a table

tnegun — Wed, 15 May 2024 10:37:24 GMT

Apologies there was a typo as I renamed fields to try generalize this is the search I'm trying

index=myindex ip earliest latest [| inputlookup ip_list_2.csv | eval ip = "*" . 'Extracted IP' . "*" | eval earliest=strptime('REQUEST_TIME', "%m/%d/%y %H:%M")-(60*60) | eval latest=strptime('REQUEST_TIME', "%m/%d/%y %H:%M")+(60*60) | fields ip earliest latest ]

This is an sample of the csv

REQUEST_TIME	Extracted IP
3/29/24 16:13	1.1.1.1
3/14/24 8:51	2.2.2.2
1/26/24 13:24	3.3.3.3

I had though the search was running like this and stopped it

index=myindex (ip=1.1.1.1 OR ip =2.2.2.2 OR ip=3.3.3.3) earliest=1/26/24 13:24 latest =3/29/24 16:13 .

I only want to report on IP 1.1.1.1's activity at 3/29/24 16:13 and not any other time.

Thanks I'll try the suggestions and report back.