topic Date Comparison with current date in Splunk Search

Date Comparison with current date

rohankin — Fri, 25 Oct 2019 18:36:43 GMT

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

Re: Date Comparison with current date

woodcock — Fri, 25 Oct 2019 19:50:50 GMT

Like this:

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

Re: Date Comparison with current date

rohankin — Wed, 30 Oct 2019 12:43:51 GMT

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.

Re: Date Comparison with current date

woodcock — Thu, 31 Oct 2019 19:38:06 GMT

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

Re: Date Comparison with current date

koreamit3483 — Thu, 09 Dec 2021 14:08:35 GMT

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.