topic Re: How to use a lookup table in a search to match any IPs or domains found in a column, but ignore any values older than 90 days? in Splunk Search

How to use a lookup table in a search to match any IPs or domains found in a column, but ignore any values older than 90 days?

ehaile — Thu, 20 Aug 2015 16:28:02 GMT

I currently have a lookup table that contains 2 columns: date and ioc. The goal is to have Splunk go through the lookup table and match any IPs or domains it finds on the ioc column. However, I would like Splunk to disregard ioc's have been on the lookup table for 90 days or more. Any help would be great. Below is what I have so far not much 😕

index=* sourcetype=* [| inputlookup ioc.csv  | fields + ioc]|

Re: How to use a lookup table in a search to match any IPs or domains found in a column, but ignore any values older than 90 days?

jensonthottian — Thu, 20 Aug 2015 17:11:17 GMT

To your search string add this after removing the last pipe

NOT [ | inputlookup ioc.csv | date>"<90days>"|fields + ioc]

The <90days> should be in the format of how your date is in your CSV.

Re: How to use a lookup table in a search to match any IPs or domains found in a column, but ignore any values older than 90 days?

ehaile — Thu, 20 Aug 2015 18:12:17 GMT

jensonthottian,

The query keeps failing I get the following error message "Error in inputlookup command:Invalid argument: 'date>'.

Below is what I tried:

index=* sourcetype=* [| inputlookup ioc2.csv | fields + ioc] NOT [|inputlookup ioc2.csv date> "08/19/2015" fields +ioc]

Re: How to use a lookup table in a search to match any IPs or domains found in a column, but ignore any values older than 90 days?

somesoni2 — Thu, 20 Aug 2015 18:13:53 GMT

If you just want to do a text search of IP from the lookup into your index, try something like this (assuming your data format of lookup table is %Y-%m-%d)

index=* sourcetype=* [| inputlookup ioc.csv  | where  strptime(date,"%y-%m-%d")>relative_time(now()-"-90d")| eval search=ioc | table search ]

If you just want to a field matching (like value of src_ip) of the IP from lookup table, try something like this

  index=* sourcetype=* [| inputlookup ioc.csv  | where  strptime(date,"%y-%m-%d")>relative_time(now()-"-90d")| eval src_ip=ioc | table src_ip ]

Re: How to use a lookup table in a search to match any IPs or domains found in a column, but ignore any values older than 90 days?

ehaile — Thu, 20 Aug 2015 18:55:40 GMT

Thanks for the response.

I am gettting the following:

"Error in 'where' command:Typechecking failed. "-" only takes numbers."

The data in the lookup is %Y-%m-%d