How to find the number of days between two dates?

[Mrig342]

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.

[isoutamo · Accepted solution]

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]

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?

[Mrig342]

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

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

Thanks @isoutamo for you help..!!

[Mrig342]

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")

[isoutamo]

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?

[ITWhisperer]

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")