Monitoring Splunk

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

Path Finder

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.

Tags (1)
1 Solution
Motivator

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

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     ...
``````
Path Finder

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

Motivator

Hi @j_r
Try it this way:

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

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?

Path Finder

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

Motivator

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.

Motivator

transaction that ends if a certain value changes

Motivator

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

Path Finder

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)

Motivator

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

Path Finder

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?

Motivator

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")
``````
Get Updates on the Splunk Community!

#### Introducing the 2024 SplunkTrust!

Hello, Splunk Community! We are beyond thrilled to announce our newest group of SplunkTrust members!  The ...

#### Introducing the 2024 Splunk MVPs!

We are excited to announce the 2024 cohort of the Splunk MVP program. Splunk MVPs are passionate members of ...

#### Splunk Custom Visualizations App End of Life

The Splunk Custom Visualizations apps End of Life for SimpleXML will reach end of support on Dec 21, 2024, ...