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!

Stay Connected: Your Guide to July and August Tech Talks, Office Hours, and Webinars!

Dive into our sizzling summer lineup for July and August Community Office Hours and Tech Talks. Scroll down to ...

Edge Processor Scaling, Energy & Manufacturing Use Cases, and More New Articles on ...

Splunk Lantern is a Splunk customer success center that provides advice from Splunk experts on valuable data ...

Get More Out of Your Security Practice With a SIEM

Get More Out of Your Security Practice With a SIEMWednesday, July 31, 2024  |  11AM PT / 2PM ETREGISTER ...