Splunk Search

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

Explorer

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!

0 Karma
1 Solution

SplunkTrust
SplunkTrust

Give this a try

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

View solution in original post

Contributor

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*
0 Karma

Explorer

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

0 Karma

SplunkTrust
SplunkTrust

Give this a try

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

View solution in original post

SplunkTrust
SplunkTrust

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

0 Karma

Explorer

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!

0 Karma

SplunkTrust
SplunkTrust

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.

Explorer

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.

0 Karma

SplunkTrust
SplunkTrust

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.

0 Karma

Explorer

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

0 Karma

SplunkTrust
SplunkTrust

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

...| table _time * Total

Explorer

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

0 Karma

New Member

Try the below code, hope it would work.

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

0 Karma

Explorer

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

0 Karma

Path Finder

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

0 Karma

Explorer

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.

0 Karma