Monitoring Splunk

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

j_r
Path Finder

Hi 🙂

My base search looks like this:
alt text
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)
0 Karma
1 Solution

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

View solution in original post

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

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

0 Karma

whrg
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")
0 Karma

j_r
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?

0 Karma

j_r
Path Finder

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

0 Karma

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

0 Karma

whrg
Motivator

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

whrg
Motivator

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

0 Karma

j_r
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)
alt text

0 Karma

whrg
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

0 Karma

j_r
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?

0 Karma

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