Dashboards & Visualizations

How to compute an average duration of a group of session in a given time period in a single value with a trendline?

dbcase
Motivator

Hi,

I have the below query the computes an average duration of a group of session in a given time period

index=wholesale_app  CustomAnalytic Properties.index=30 OR Properties.index=21 buildTarget=* product=* |rename Properties.args as properties|stats min(_time) AS earliest max(_time) AS latest by clientSessionId | eval duration=latest-earliest | stats avg(duration) as adur|eval adur=round(adur/60,2)|rename adur as "Average Duration"

It works just fine. What I need to do is get it where there is a single value with a trendline. I realize that you have to do that with the timechart command but no matter how I poke at this I can't seem to get it to work.

Thoughts?

0 Karma
1 Solution

davpx
Communicator

What you don't want to do is calculate the average of averages by slapping timechart on the end of your previously calculated average in stats. Try this instead.

index=wholesale_app CustomAnalytic Properties.index=30 OR Properties.index=21 buildTarget=* product=* |rename Properties.args as properties|stats min(_time) AS earliest max(_time) AS latest by clientSessionId | eval duration=latest-earliest, _time=latest | timechart avg(duration) as adur

View solution in original post

0 Karma

davpx
Communicator

What you don't want to do is calculate the average of averages by slapping timechart on the end of your previously calculated average in stats. Try this instead.

index=wholesale_app CustomAnalytic Properties.index=30 OR Properties.index=21 buildTarget=* product=* |rename Properties.args as properties|stats min(_time) AS earliest max(_time) AS latest by clientSessionId | eval duration=latest-earliest, _time=latest | timechart avg(duration) as adur

0 Karma

dbcase
Motivator

I also tried this and it gave a value (it was a wrong value but it was a value)

index=wholesale_app CustomAnalytic Properties.index=30 OR Properties.index=21 buildTarget=* product=* |rename Properties.args as properties|stats min(_time) AS earliest max(_time) AS latest list(_time) as _time by clientSessionId | eval duration=latest-earliest | timechart span=1d avg(duration) as adur
0 Karma

dbcase
Motivator

Just to confirm here is the updated query

index=wholesale_app CustomAnalytic Properties.index=30 OR Properties.index=21 buildTarget=* product=* |rename Properties.args as properties|stats min(_time) AS earliest max(_time) AS latest list(_time) as _time by clientSessionId | eval duration=latest-earliest,_time=latest |timechart span=1d avg(duration) as adur|
0 Karma

dbcase
Motivator

Hi Davpx,

Tried this (had to clean up a couple of things) but no luck (no results found)

index=wholesale_app CustomAnalytic Properties.index=30 OR Properties.index=21 buildTarget=* product=* |rename Properties.args as properties|stats min(_time) AS earliest max(_time) AS latest by clientSessionId | eval duration=latest-earliest | timechart avg(duration) as adur

I think it is due to the fact that the stats line doesn't pass in the _time field, but I'm not sure how to make that happen.

0 Karma

davpx
Communicator

I think you missed a part. Be sure to pass _time through with this

| eval duration=latest-earliest, _time=latest | timechart avg(duration) as adur

0 Karma

dbcase
Motivator

Whups sorry missed that, fixed and now....

I now get a value, 60 but the original query gave me a value of 135

0 Karma

davpx
Communicator

index=wholesale_app CustomAnalytic Properties.index=30 OR Properties.index=21 buildTarget= product= |rename Properties.args as properties|stats min(_time) AS earliest max(_time) AS latest by clientSessionId | eval duration=latest-earliest, _time=latest | timechart avg(duration) as adur |eval adur=round(adur/60,2)

0 Karma

dbcase
Motivator

I think that one is close. Now that I'm looking at the data I'm thinking my original idea won't work but you gave me one that will, and maybe work even better 🙂

TKS!!!

0 Karma
Get Updates on the Splunk Community!

.conf24 | Day 0

Hello Splunk Community! My name is Chris, and I'm based in Canberra, Australia's capital, and I travelled for ...

Enhance Security Visibility with Splunk Enterprise Security 7.1 through Threat ...

(view in My Videos)Struggling with alert fatigue, lack of context, and prioritization around security ...

Troubleshooting the OpenTelemetry Collector

  In this tech talk, you’ll learn how to troubleshoot the OpenTelemetry collector - from checking the ...