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

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

pcheng — Sat, 01 Apr 2017 02:25:49 GMT

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:

the average duration of events for individual project by day

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

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!

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

Tom1187 — Sat, 01 Apr 2017 13:30:38 GMT

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

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

gouravdashtcs — Sat, 01 Apr 2017 18:53:30 GMT

Try the below code, hope it would work.

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

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

pcheng — Mon, 03 Apr 2017 17:56:25 GMT

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.

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

pcheng — Mon, 03 Apr 2017 18:04:55 GMT

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

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

DalJeanis — Mon, 03 Apr 2017 21:13:50 GMT

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.

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

somesoni2 — Mon, 03 Apr 2017 21:26:37 GMT

Give this a try

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

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

pcheng — Mon, 03 Apr 2017 21:46:29 GMT

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.

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

DalJeanis — Mon, 03 Apr 2017 21:53:43 GMT

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.

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

DalJeanis — Mon, 03 Apr 2017 21:56:14 GMT

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

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

briancronrath — Mon, 03 Apr 2017 21:59:47 GMT

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*

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

pcheng — Mon, 03 Apr 2017 22:01:33 GMT

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

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

somesoni2 — Mon, 03 Apr 2017 22:04:43 GMT

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

...| table _time * Total

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

pcheng — Mon, 03 Apr 2017 22:10:30 GMT

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

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

pcheng — Mon, 03 Apr 2017 22:13:33 GMT

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

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

pcheng — Mon, 03 Apr 2017 22:22:04 GMT

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!

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

JakubJ — Fri, 09 Apr 2021 10:27:51 GMT

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 🙂