Splunk Search

Date Comparison with current date

rohankin
New Member

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. alt text

Thanks !
Rohan K

0 Karma
1 Solution

woodcock
Esteemed Legend

Like this:

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

View solution in original post

0 Karma

woodcock
Esteemed Legend

Like this:

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

koreamit3483
Explorer

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.

0 Karma

rohankin
New Member

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.

0 Karma

woodcock
Esteemed Legend

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

0 Karma
Get Updates on the Splunk Community!

Index This | I am a number, but when you add ‘G’ to me, I go away. What number am I?

March 2024 Edition Hayyy Splunk Education Enthusiasts and the Eternally Curious!  We’re back with another ...

What’s New in Splunk App for PCI Compliance 5.3.1?

The Splunk App for PCI Compliance allows customers to extend the power of their existing Splunk solution with ...

Extending Observability Content to Splunk Cloud

Register to join us !   In this Extending Observability Content to Splunk Cloud Tech Talk, you'll see how to ...