in timechart, how do you display average by field, but also show a total average

[pcheng]

Hi,

I have events from various projects, and each event has an eventDuration field.
I'm trying to visualize the followings in the same chart:

1. the average duration of events for individual project by day

... | timechart span=1d avg(eventDuration) by project

1. the total average duration of events for all projects/events by day

... | timechart span=1d avg(eventDuration) as TotalAverage

But I'm having hard time figuring out how to combine these,
so I can have the TotalAverage column from #2 added as the last column to the timechart from #1.

Maybe I'm missing something obvious, but I'm hoping someone can give me some pointers.
Thank you!

[briancronrath]

If you want to avoid searching the results twice, you could just add up the values and manually calc the total average (and remove the fields you don't care about):

 | timechart span=1d avg(eventDuration) sum(eventDuration) as s_ed count(eventDuration) as c_ed by project | addtotals fieldname=s_ed s_ed* | addtotals fieldname=c_ed c_ed* | eval total_avg=s_ed/c_ed | fields - s_ed* - c_ed*

[pcheng]

This is an interesting approach and seems to work too.

In this case, I'll stick with the avg() function since there's a way to get that working. ^^^

But thank you for sharing! 🙂

[somesoni2 · Accepted solution]

Give this a try

your base search
| eval project=split(project."##TotalAverage","##")
| timechart span=1d avg(avgDuration) as avgDuration by project
| table _time * TotalAverage

[DalJeanis]

Interesting approach, and I'll bet it's marginally more efficient than using appendpipe to split the records.

[pcheng]

Interesting! Using split you only increased the amount of values in the project field, but not duplicating the amount of events. 😄

So many ways of doing things. Thank you guys so much!

[DalJeanis]

When doing anything complicated, it's usually easier to use stats, set the name of the series, and then feed the results to timechart. I believe this gets you what you want.

(your search)
| bin _time span=1d
| stats avg(eventDuration) as avgDuration by project _time
| appendpipe [| eval project="Total"]
| timechart span=1d avg(avgDuration) as avgDuration by project
| table _time * Total

Or this, depending on which average you want...

(your search)
| bin _time span=1d
| eval sorttype=0
| appendpipe [| eval project="Total", sorttype=1]
| stats avg(eventDuration) as avgDuration by sorttype project _time
| timechart span=1d avg(avgDuration) as avgDuration by project
| table _time * Total

---

The first will get you the average of the project averages. The second will get you the average of the individual events.

---

Update - Per somesoni2's recommendation, added table command at end of each query to move the total column right.

[JakubJ]

This actually helped me with similar problem. Thank you, you are life savior. My previous solution was using appendcols to combine two timechart searches, this solution is much faster and actually finishes 🙂

[pcheng]

oh~~~ this is cool! Thank you!
I see what you're doing here, and the sorting is really cool.

However, I can't seem to pass the result of stats into timechart (when I do, it returns no result). I had to use eventstats instead, but that nullify the sorting. 😞

And for the eventstats, I actually have to also split by date for the average to calculate properly.

... | bin time span=1d
 | eval sorttype=0
 | appendpipe [| eval project="Total", sorttype=1]
 | eventstats avg(eventDuration) as avgDuration by date sorttype project
 | timechart span=1d avg(avgDuration) by project

Do you know how I might get stats working with timechart? I'd really prefer the "Total" to be the last column, without having to come up with weird names just to satisfy the automatic alphabetical sorting.

[DalJeanis]

It worked fine with my test data.

Ah, sorry, looks like I updated the code after you pulled it.

_time needs an underscore.

Use the current code and try again.

[pcheng]

oh i see. timechart was expecting the default _time field. Thanks that does make stats work with timechart.

The column ordering issue still persists though. Because my project names could be after "T" for "Total".

[somesoni2]

For sorting issues, just add this to the end of the search

...| table _time * Total

[pcheng]

Woo~~ that's it!
Thank you both so much!

[gouravdashtcs]

Try the below code, hope it would work.

..|stats avg(eventDuration) as TotalDuration |timechart TotalDuration over avg(eventDuration) by project

[pcheng]

can't seem to use TotalDuration directly in timechart

Error in 'timechart' command: The specifier 'TotalDuration' is invalid. It must be in form (). For example: max(size).

so I changed it to

| timechart avg(TotalDuration) over avg(eventDuration) by project

but then seems like over is not supported either

Error in 'timechart' command: The specifier 'over' is invalid. It must be in form (). For example: max(size).

[Tom1187]

Try using appendcols:
... | timechart span=1d avg(eventDuration) by project | appendcols [ search ... | timechart span=1d avg(eventDuration) as TotalAverage ]

[pcheng]

This does work for sure (although new column is not necessary the last column), but I was really hoping not to do the whole search again, hitting the index twice while the search is exactly the same.