topic Re: ,number of days between two dates in same event in Splunk Search

,number of days between two dates in same event

Mike6960 — Tue, 29 Sep 2020 16:08:07 GMT

In an event i have two dates.

G_S="2017-10-07 23:21:19.0" and A_Z="2017-10-07 00:00:00.0"

I have mutiple examples but somehow i cannot get it working. IK would like to know how to extract the number of days between these dates

,

Re: ,number of days between two dates in same event

richgalloway — Tue, 10 Oct 2017 11:38:34 GMT

Here's one way.

... | eval eG_S=strptime(G_S,"%Y-%m-%d %H:%M:%S.%N"), eA_Z=strptime(A_Z, "%Y-%m-%d %H:%M:%S.%N") | eval days=(eG_S-eA_Z)/86400 | ...

Re: ,number of days between two dates in same event

Mike6960 — Tue, 10 Oct 2017 11:55:13 GMT

Thanks, but I have tried something like this before. I would expect a field as output with the number of days but i does not seem to work

Re: ,number of days between two dates in same event

cmerriman — Tue, 10 Oct 2017 12:11:09 GMT

if you used this instead:

| eval days=floor((eG_S-eA_Z)/86400)

for the days you gave, it would give you 0 days, because it wasn't a full 24 hours. it'll automatically round down.

Re: ,number of days between two dates in same event

Mike6960 — Tue, 10 Oct 2017 12:21:26 GMT

I don't see an putputfield with a number of days, do I have to include this in my search?

Re: ,number of days between two dates in same event

cmerriman — Tue, 29 Sep 2020 16:13:00 GMT

you'll need this whole string:

| eval eG_S=strptime(G_S,"%Y-%m-%d %H:%M:%S.%N"), eA_Z=strptime(A_Z, "%Y-%m-%d %H:%M:%S.%N") | eval days=floor((eG_S-eA_Z)/86400)

days should be a field as long as G_S and A_Z are the field names in your data. otherwise, replace them with the actual field names.

Re: ,number of days between two dates in same event

Mike6960 — Tue, 29 Sep 2020 16:08:12 GMT

I just made a change and now i see fields like eG_S and eA_Z. But the contents of these are like: 1507240800.000000 .

Re: ,number of days between two dates in same event

cmerriman — Tue, 29 Sep 2020 16:13:03 GMT

right. eG_S and eA_Z are in epoch (that is what strptime does), so that they can be used in calculations for the number of days. did you add the eval for days and does it work?
https://docs.splunk.com/Documentation/SplunkCloud/6.6.3/SearchReference/DateandTimeFunctions#strptime.28X.2CY.29

Re: ,number of days between two dates in same event

Mike6960 — Tue, 29 Sep 2020 16:08:15 GMT

I have used this whole string
| eval eG_S=strptime(G_S,"%Y-%m-%d %H:%M:%S.%N"), eA_Z=strptime(A_Z, "%Y-%m-%d %H:%M:%S.%N")
| eval days=floor((eG_S-eA_Z)/86400)
, the two fields eG_S and eA_Z are generated. But i dont see a result of number of days anywhere

Re: ,number of days between two dates in same event

Mike6960 — Tue, 10 Oct 2017 12:40:59 GMT

sorry!!!, i did something wrong, i see the field 'days' now. Sorry wasting your time....
Thans a lot for the help

Re: ,number of days between two dates in same event

cmerriman — Tue, 10 Oct 2017 12:53:52 GMT

No worries, glad you figured it out!!
Please accept the answer if your problem is solved to award points and close the question 🙂

Re: ,number of days between two dates in same event

Mike6960 — Wed, 11 Oct 2017 06:39:22 GMT

If I want to use the current time in Splunk, can ik then just replace G_S in NOW() ?

Re: ,number of days between two dates in same event

cmerriman — Wed, 11 Oct 2017 11:36:52 GMT

yes, that'll give you the current timestamp, and it's already in epoch, so there is no need to convert it.