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

[j_r]

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.

[whrg · Accepted solution]

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     ...

[j_r]

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 .(

[whrg]

Hi @j_r
Try it this way:

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

[j_r]

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?

[j_r]

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

[whrg]

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.

[whrg]

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

[whrg]

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

[j_r]

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)

[whrg]

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

[j_r]

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?

[whrg]

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