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.
(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
(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
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
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
Thanks! That works!!!
Thanks for correcting my typo 🙂