Splunk Search

how to search for a field containing timedate relative to todays date?

zaynaly
Explorer

I have a field named "Expiry date" that contains future dates. I want to make a search that list will all entries that contain the expiry date +30 days from today date?

field format example of "expiry date"=2/25/2018 9:36 AM

Also How can I sort based on date of this field? Splunk doesn't seem to understand its a date.

Tags (1)
0 Karma
1 Solution

elliotproebstel
Champion

(Edited to fix typo with quotes)

Convert string date to epoch time string
To convert Expiry date from a string value to an epoch time stamp so that it can be compared to other time stamps, you'll use the command strptime like this:

your search that returns a field called "Expiry date"
| eval parsed_date=strptime('Expiry date', "%m/%d/%Y %I:%M %p")

The result will be a field called parsed_date containing an epoch time stamp. Because I am in Eastern Time Zone (US/New York), that converts for me to: 1519847550.000000

Compare converted time stamp to a time stamp 30 days in the future
To create a value 30 days in the future, you'll use the function relative_time():

| future_date=relative_time(now(), "+30d")

and then to compare these two dates:

| where parsed_date<=future_date

All Together

your search that returns a field called "Expiry date"
| eval parsed_date=strptime('Expiry date', "%m/%d/%Y %I:%M %p")
| future_date=relative_time(now(), "+30d")
| where parsed_date<=future_date

View solution in original post

elliotproebstel
Champion

(Edited to fix typo with quotes)

Convert string date to epoch time string
To convert Expiry date from a string value to an epoch time stamp so that it can be compared to other time stamps, you'll use the command strptime like this:

your search that returns a field called "Expiry date"
| eval parsed_date=strptime('Expiry date', "%m/%d/%Y %I:%M %p")

The result will be a field called parsed_date containing an epoch time stamp. Because I am in Eastern Time Zone (US/New York), that converts for me to: 1519847550.000000

Compare converted time stamp to a time stamp 30 days in the future
To create a value 30 days in the future, you'll use the function relative_time():

| future_date=relative_time(now(), "+30d")

and then to compare these two dates:

| where parsed_date<=future_date

All Together

your search that returns a field called "Expiry date"
| eval parsed_date=strptime('Expiry date', "%m/%d/%Y %I:%M %p")
| future_date=relative_time(now(), "+30d")
| where parsed_date<=future_date

zaynaly
Explorer

Thank you for the clear and detailed answer!!

I am having issues though, the search is giving zero results, am I doing this correct? I know for this it should give out 2 results for sure:

 index=test | eval parsed_date=strptime("Expiry Date", "%m/%d/%Y %I:%M %p"), future_date=relative_time(now(), "+30d") | where parsed_date<=future_date
0 Karma

somesoni2
SplunkTrust
SplunkTrust

In the right side of eval, a field name that contain spaces should be enclosed in single quotes, not double quotes. Try this

index=test | eval parsed_date=strptime('Expiry Date', "%m/%d/%Y %I:%M %p"), future_date=relative_time(now(), "+30d") | where parsed_date<=future_date

zaynaly
Explorer

Thanks! That works!!!

0 Karma

elliotproebstel
Champion

Thanks for correcting my typo 🙂

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