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

[dbcase]

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?

[davpx · Accepted solution]

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

[dbcase]

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

[dbcase]

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|

[dbcase]

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.

[davpx]

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

[dbcase]

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

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

[davpx]

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)

[dbcase]

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!!!