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.
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 ...
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 ...
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 .(
Hi @j_r
Try it this way:
| stats sum(duration) as sum_duration by RobotSubState
| eval sum_duration_in_hhmmss=tostring(sum_duration,"duration")
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?
One more question: what ist the function of | eval _time=min_time
?
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.
I also found this thread which looks similar to your question:
transaction that ends if a certain value changes
Okay so I just learnt about the reset_on_change parameter.
I edited my original answer to make the search much shorter.
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)
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
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?
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")