Splunk Search

How to find the number of days between two dates?

Mrig342
Contributor

Hi All,

I have logs as below to check certificate validity:
Valid from: Tue Jul 13 02:51:21 EDT 2021 until: Thu Jul 13 02:51:21 EDT 2023

I have extracted the from_date and until_date by using the below query:

..... | rex field=_raw "from\:\s(?P<Valid_From>\w+\s\w+\s(\s{0,1})\d+\s\d+\:\d+\:\d+\s\w+\s\d+)\s"
| rex field=_raw "until\:\s(?P<Valid_Until>\w+\s\w+\s(\s{0,1})\d+\s\d+\:\d+\:\d+\s\w+\s\d+)"

Now I want to get the no. the days between these two dates to get the certificate validity.

Please help me to create a query to get the desired output.

Labels (1)
Tags (1)
0 Karma
1 Solution

isoutamo
SplunkTrust
SplunkTrust

Hi

you could try something like

use strptime to get epoch time for both days, then subtract to get difference between times. In last use tostring (duration_as_sec, “duration”) to get duration as days, hours, etc.

r. Ismo 

| makeresults
| eval _raw="Valid from: Tue Jul 13 02:51:21 EDT 2021 until: Thu Jul 13 02:51:21 EDT 2023"
| rex "from\:\s(?P<Valid_From>\w+\s\w+\s(\s{0,1})\d+\s\d+\:\d+\:\d+\s\w+\s\d+)\s+until\:\s(?P<Valid_Until>\w+\s\w+\s(\s{0,1})\d+\s\d+\:\d+\:\d+\s\w+\s\d+)"
| eval vf_epoch = strptime(Valid_From, "%a %b %d %H:%M:%S %Z %Y"), vu_epoch = strptime(Valid_Until, "%a %b %d %H:%M:%S %Z %Y")
| eval dur=vu_epoch-vf_epoch
| eval duration = tostring(dur, "duration")
| table Valid_From Valid_Until duration

this gives 730d 

View solution in original post

isoutamo
SplunkTrust
SplunkTrust

Hi

you could try something like

use strptime to get epoch time for both days, then subtract to get difference between times. In last use tostring (duration_as_sec, “duration”) to get duration as days, hours, etc.

r. Ismo 

| makeresults
| eval _raw="Valid from: Tue Jul 13 02:51:21 EDT 2021 until: Thu Jul 13 02:51:21 EDT 2023"
| rex "from\:\s(?P<Valid_From>\w+\s\w+\s(\s{0,1})\d+\s\d+\:\d+\:\d+\s\w+\s\d+)\s+until\:\s(?P<Valid_Until>\w+\s\w+\s(\s{0,1})\d+\s\d+\:\d+\:\d+\s\w+\s\d+)"
| eval vf_epoch = strptime(Valid_From, "%a %b %d %H:%M:%S %Z %Y"), vu_epoch = strptime(Valid_Until, "%a %b %d %H:%M:%S %Z %Y")
| eval dur=vu_epoch-vf_epoch
| eval duration = tostring(dur, "duration")
| table Valid_From Valid_Until duration

this gives 730d 

Mrig342
Contributor

Hi @isoutamo 

This works..!! But the value comes up as 730+00:00:00.000000 ; 9131+00:09:52.000000 etc.

How can I round it to the number of days only?

0 Karma

Mrig342
Contributor

Nevermind..!!  I got it how to round it to number of days.

| eval duration = round(dur/(60*60*24))

 

Thanks @isoutamo for you help..!!

0 Karma

Mrig342
Contributor

Hi @isoutamo 

I tried below queries, but still unable to get the desired out put. Can you please look and help.

....... | rex field=_raw "from\:\s(?P<Valid_From>\w+\s\w+\s(\s{0,1})\d+\s\d+\:\d+\:\d+\s\w+\s\d+)\s"
| rex field=_raw "until\:\s(?P<Valid_Until>\w+\s\w+\s(\s{0,1})\d+\s\d+\:\d+\:\d+\s\w+\s\d+)"
| eval From = strptime("Valid_From", "%d/%m/%Y %H:%M:%S")
| eval Until = strptime("Valid_Until", "%d/%m/%Y %H:%M:%S")
| eval Validity = tostring((Until - From), "duration")

0 Karma

isoutamo
SplunkTrust
SplunkTrust

It seems that your time string is not same what you have in your examples. Can you try my example, which I just add there?

0 Karma

ITWhisperer
SplunkTrust
SplunkTrust

It looks like you need to use the correct parsing date time format to match the string you have extracted. Try something like this

....... | rex field=_raw "from\:\s(?P<Valid_From>\w+\s\w+\s(\s{0,1})\d+\s\d+\:\d+\:\d+\s\w+\s\d+)\s"
| rex field=_raw "until\:\s(?P<Valid_Until>\w+\s\w+\s(\s{0,1})\d+\s\d+\:\d+\:\d+\s\w+\s\d+)"
| eval From = strptime("Valid_From", "%+")
| eval Until = strptime("Valid_Until", "%+")
| eval Validity = tostring((Until - From), "duration")
0 Karma
Get Updates on the Splunk Community!

Splunk Observability Cloud’s AI Assistant in Action Series: Analyzing and ...

This is the second post in our Splunk Observability Cloud’s AI Assistant in Action series, in which we look at ...

Elevate Your Organization with Splunk’s Next Platform Evolution

 Thursday, July 10, 2025  |  11AM PDT / 2PM EDT Whether you're managing complex deployments or looking to ...

Splunk Answers Content Calendar, June Edition

Get ready for this week’s post dedicated to Splunk Dashboards! We're celebrating the power of community by ...