Date Comparison with current date

[rohankin]

Hi,

I am trying to display results in separate panels based on date fields in my dataset. I want to display results where Date1 is less than 7 days from current date and
in separate panel , I want to display results where Date 2 is less than 7 days from current date.

I tried using eval but it doesn't provide any results

Queries that I tried:
|inputlookup devices_lookup |eval _time=strptime(Date1, "%m/%d/%Y") |search latest=-7d

|inputlookup devices_lookup |eval Test=substr(Date2, 0,10)| eval _time=strptime(Date2, "%m/%d/%Y") |search latest=-7d

Is there any way to perform this using standard date functions as I have NULL values in Date1, Date2 columns too which I want to handle.
I have also attached sample data here.

Thanks !
Rohan K

[woodcock · Accepted solution]

Like this:

|inputlookup devices_lookup
| eval _time=strptime(Date1, "%m/%d/%Y")
| where _time <= relative_time(now(), "-7d")

[koreamit3483]

I have a query on top of this.. 

What if i want to use the token instead of "Date1" ?

means the date which is being selected from drop down.

[rohankin]

Thanks ! That worked. I just noticed my data also has many rows where date is "12/31/1969 07:10 pm" which is UNIX timestamp 0. strptime doesnt work on that. Any suggestion on how I should handle this ?
I am thinking of changing that date to "0" or "missing" to reflect the fact that "Date" field is not being populated for those devices.

Any idea how should I do that ?

Thanks !
Rohan K.

[woodcock]

Fix your data onboarding. DO NOT LET SPLUNK GUESS WHERE/WHAT THE TIMESTAMP IS! Google splunk Magic 8.