topic Re: How can I calculate the average duration between changes of two fields / events? in Monitoring Splunk

How can I calculate the average duration between changes of two fields / events?

j_r — Mon, 10 Dec 2018 14:24:15 GMT

Hi 🙂

My base search looks like this:

I used | dedup RobotSubState for this screenshot. In reality, every 1 second, an event is output as a result.

How can I calculate the average duration for a state ?

For example:
time 1: RobotSubState = Parked
time 2: RobotSubState = Parked
time 3: RobotSubState = Parked
time 4: RobotSubState = Driving
time 5: RobotSubState = Driving
time 6: RobotSubState = Parked

Here i would need the time 4 + time 5 as duration or time 1 + time 2 + time 3.

Re: How can I calculate the average duration between changes of two fields / events?

whrg — Tue, 11 Dec 2018 09:53:10 GMT

Hello @j_r,

Check out this search. It consists of two parts.

Here is the first part of the search:

your base search
| streamstats count as sequence
| streamstats first(sequence) as group reset_on_change=true by RobotSubState
| ...

This will give you:

_time    RobotSubState   sequence    group
time 1   Parked          1           1
time 2   Parked          2           1
time 3   Parked          3           1
time 4   Driving         4           4
time 5   Driving         5           4
time 6   Parked          6           6

Now here is the second part:

...
| stats min(_time) as min_time max(_time) as max_time first(RobotSubState) as RobotSubState by group
| eval duration=max_time-min_time | eval _time=min_time

This will get you:

group    min_time    max_time    RobotSubState    _time      duration
1        time 3      time 1      Parked           time 1     ...
4        time 5      time 4      Driving          time 4     ...
6        time 6      time 6      Parked           time 6     ...

Re: How can I calculate the average duration between changes of two fields / events?

j_r — Tue, 11 Dec 2018 11:55:33 GMT

This one works.
I would like to display the avg duration in a chart by RobotSubstate.
I tried with | stats avg(duration) by RobotSubState but the time is displayed as epoch time.
tostring(X) didnt work :/.
Any suggestions?

Re: How can I calculate the average duration between changes of two fields / events?

whrg — Tue, 11 Dec 2018 12:13:08 GMT

In my search above, duration returns the time difference in seconds. (Not as epoch time.)
So your question should be how to convert seconds to HH:MM:SS.
I found this question here: How to convert second to HH:MM:SS format in the exported search result?
So try this:

... | eval duration_in_hhmmss=tostring(duration, "duration")

Re: How can I calculate the average duration between changes of two fields / events?

whrg — Tue, 11 Dec 2018 15:30:59 GMT

I also found this thread which looks similar to your question:
transaction that ends if a certain value changes

Re: How can I calculate the average duration between changes of two fields / events?

whrg — Tue, 29 Sep 2020 22:20:14 GMT

Okay so I just learnt about the reset_on_change parameter.
I edited my original answer to make the search much shorter.

Re: How can I calculate the average duration between changes of two fields / events?

j_r — Wed, 12 Dec 2018 08:30:43 GMT

One more question: what ist the function of | eval _time=min_time ?

Re: How can I calculate the average duration between changes of two fields / events?

whrg — Wed, 12 Dec 2018 09:12:10 GMT

It copies the value of min_time to _time.
The thing about _time is that it displays time objects (epoch times) in a human readable format.

Re: How can I calculate the average duration between changes of two fields / events?

j_r — Wed, 12 Dec 2018 09:22:45 GMT

The result looks like this and duration is not calculated. Actually the duraction can also be calculated as the difference betweeen two rows. For example: _time(group10) - _time(group1)

Re: How can I calculate the average duration between changes of two fields / events?

whrg — Wed, 12 Dec 2018 10:16:00 GMT

Run the search step by step and see where it goes wrong.
It is working for me:
https://ibb.co/0FsJxTG
https://ibb.co/rpGhkVC

Re: How can I calculate the average duration between changes of two fields / events?

j_r — Wed, 12 Dec 2018 10:20:35 GMT

i added | delta _time as Diff | eval Dauer= tostring(Diff, "duration") to calculate the difference between 2 rows. But the results are displayed in the "wrong" row. How can i shift them to previous row?

Re: How can I calculate the average duration between changes of two fields / events?

j_r — Thu, 13 Dec 2018 17:34:41 GMT

Hi, its me again 🙂
how can i sum the duration and format it to a readable format?

| streamstats count as sequence
| streamstats first(sequence) as group reset_on_change=true by RobotSubState
| stats min(_time) as min_time max(_time) as max_time first(RobotSubState) as RobotSubState by group
| eval duration=max_time-min_time
|stats sum(duration) by RobotSubState

the result is something like this sum(duration): 5989.18

i already tried tostring, strptime or strftime. but it didnt work .(

Re: How can I calculate the average duration between changes of two fields / events?

whrg — Fri, 14 Dec 2018 07:50:44 GMT

Hi @j_r
Try it this way:

| stats sum(duration) as sum_duration by RobotSubState
| eval sum_duration_in_hhmmss=tostring(sum_duration,"duration")