Getting Data In

Calculating hours since event

geoffmoraes
Path Finder

I am attempting to calculate hours since an event occurred, however, the calculated time shows decimals including .6 to .9 between hour values.

index=abc 
| eval time_difference=(now() - _time) 
| eval time_in_hours=(time_difference/60) 
| eval Hours_elapsed=round(time_in_hours/60,2) 
| eval Time=strftime(_time, "%Y-%m-%d %H:%M:%S") 
| table Time Hours_elapsed 
| sort - Hours_elapsed

Here's an example of the output

Time                   Hours_elapsed
2019-10-16 05:39:02 22.96
2019-10-16 05:39:19 22.96
2019-10-16 05:39:14 22.96
2019-10-16 05:48:48 22.80
2019-10-16 05:48:47 22.80

I've attempted to calculate the difference of now() and _time in epoch and then do an eval to calculate the hours, but have had no luck.

1 Solution

adonio
Ultra Champion

looks like you are doing fine ...
epoch is in seconded and you are calculating hours when dividing in 60 ...

try this:

| gentimes start=-1 increment=1h 
| eval _time = starttime 
| eval  now_time = now()
| table now_time _time
| eval difference_in_seconds = now_time - _time
| eval difference_in_hours = round(difference_in_seconds / 3600, 2)

hope it helps

View solution in original post

wmyersas
Builder

It's not showing "minutes greater than 59"

The "difference in hours" column is showing you decimal "time"

30.95 hours is 30 hours, 57 minutes (.95 hours = 57 minutes)

There's nothing wrong with the displayed values - unless you want them in hh:mm format

Then you should do this:

| makeresults
| eval seconds=107836
| eval intermediate=round(seconds/3600,2)
| rex field=intermediate "(?<hh>\d+)\.(?<mm>\d+)"
| eval mm=round((mm*60)/100)
| eval hhmm=hh+":"+mm
| table *

Which will yield:

hh | hhmm  | intermediate | mm | seconds
29 | 29:57 | 29.95        | 57 | 107836
0 Karma

geoffmoraes
Path Finder

Thanks! I didn't think about decimal "time". It makes sense now.

0 Karma

adonio
Ultra Champion

looks like you are doing fine ...
epoch is in seconded and you are calculating hours when dividing in 60 ...

try this:

| gentimes start=-1 increment=1h 
| eval _time = starttime 
| eval  now_time = now()
| table now_time _time
| eval difference_in_seconds = now_time - _time
| eval difference_in_hours = round(difference_in_seconds / 3600, 2)

hope it helps

geoffmoraes
Path Finder

Thanks, but it still shows hours with minutes greater than 59. The expected output is that the hour would increment after the minute crosses 59.

now_time    _time   difference_in_hours difference_in_seconds
1571313436  2019-10-16 00:00    30.95   111436
1571313436  2019-10-16 01:00    29.95   107836
1571313436  2019-10-16 02:00    28.95   10423
0 Karma

wmyersas
Builder

You're seeing decimal time, instead of "traditional" time - 30.95 hours is 30 hours, 57 minutes

0 Karma

wmyersas
Builder

You're not getting minute values greater than 59 - you're seeing "decimal time"

0 Karma

adonio
Ultra Champion

yup ...
i guess this is what you want:

| gentimes start=-1 increment=1h 
| eval _time = starttime 
| eval  now_time = now()
| table now_time _time
| eval difference_in_seconds = now_time - _time
| eval difference_in_hours_min_sec = tostring(difference_in_seconds, "duration")

geoffmoraes
Path Finder

Yes! this is perfect 🙂
Thanks adonio!

0 Karma
Get Updates on the Splunk Community!

Improve Your Security Posture

Watch NowImprove Your Security PostureCustomers are at the center of everything we do at Splunk and security ...

Maximize the Value from Microsoft Defender with Splunk

 Watch NowJoin Splunk and Sens Consulting for this Security Edition Tech TalkWho should attend:  Security ...

This Week's Community Digest - Splunk Community Happenings [6.27.22]

Get the latest news and updates from the Splunk Community here! News From Splunk Answers ✍️ Splunk Answers is ...