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.
|inputlookup devices_lookup | eval _time=strptime(Date1, "%m/%d/%Y") | where _time <= relative_time(now(), "-7d")
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 ?