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

[zaynaly]

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.

[elliotproebstel · Accepted solution]

(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]

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

[somesoni2]

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]

Thanks! That works!!!

[elliotproebstel]

Thanks for correcting my typo 🙂