topic Re: How to edit my search to append a total average column for a chart? in Splunk Search

How to edit my search to append a total average column for a chart?

splunkin11 — Tue, 25 Oct 2016 16:01:50 GMT

I can't seem to find a solution for this. I've created a chart over a given time span. I've been able to add a column for the totals for each row and total averages at the bottom but have not been able to figure out how to add a column for the average of whatever the selected time span would be.
What I have so far ....

index=*
| bucket _time span=1d  
|convert ctime(_time) AS date timeformat="%Y/%m/%d" 
| chart count over host by date 
| addtotals
| appendpipe [stats avg(* ) as *]

Re: How to edit my search to append a total average column for a chart?

somesoni2 — Tue, 25 Oct 2016 16:14:02 GMT

Give this a try

index=*
 | bucket _time span=1d  
 |convert ctime(_time) AS date timeformat="%Y/%m/%d" 
 | stats count by host date 
 | appendpipe [| stats avg(count) as count by host | eval date="Host_Avg" ]
 | xyseries host date count
 | addtotals
 | appendpipe [stats avg(* ) as *]

Final

index=*
  | bucket _time span=1d 
  |convert ctime(_time) AS date timeformat="%Y/%m/%d" 
  | stats count by host date 
  | appendpipe [| stats avg(count) as count by host | eval date="Host Avg" ]
  | xyseries host date count
  | addtotals | eval Total=Total-'Host Avg'
  | appendpipe [stats avg(*) as * | foreach * [eval "<<FIELD>>"=round('<<FIELD>>') ] ]

Re: How to edit my search to append a total average column for a chart?

splunkin11 — Tue, 25 Oct 2016 17:09:54 GMT

hmmm .... returns 0 results

Re: How to edit my search to append a total average column for a chart?

splunkin11 — Tue, 25 Oct 2016 17:15:11 GMT

.. oops .. my fault ... getting results now and the avg. is correct and in a new column but the total column is now showing an incorrect total for the time span for each row.

Re: How to edit my search to append a total average column for a chart?

splunkin11 — Tue, 25 Oct 2016 17:21:14 GMT

I see - it's including the avg(count) # into the total count but how can I exclude the avg count from the total?

Re: How to edit my search to append a total average column for a chart?

splunkin11 — Tue, 29 Sep 2020 11:33:10 GMT

Almost there. I found a way to add the correct total for each column with another appendcols but noticed that the final totals were lost - with the |appendpipe [stats avg(* ) as *]

Here's what I have now but missing the final totals:

Re: How to edit my search to append a total average column for a chart?

somesoni2 — Tue, 25 Oct 2016 18:18:43 GMT

It would be better (efficient) to just add following after addtotals and before last appendpipe. (to avoid querying the host data again.

| eval Total=Total-'Host Avg'

Full search

index=*
| bucket _time span=1d 
|convert ctime(_time) AS date timeformat="%Y/%m/%d" 
| stats count by host date 
| appendpipe [| stats avg(count) as count by host | eval date="Host Avg" ]
| xyseries host date count
| addtotals | eval Total=Total-'Host Avg'
| appendpipe [stats avg(*) as * ]

Re: How to edit my search to append a total average column for a chart?

splunkin11 — Tue, 25 Oct 2016 18:29:45 GMT

Great!! Your final answer fixes everything - thanks!

Re: How to edit my search to append a total average column for a chart?

splunkin11 — Tue, 25 Oct 2016 19:16:11 GMT

Would there be a way to round the final totals from using [stats avg(*) as * ] ?

Re: How to edit my search to append a total average column for a chart?

somesoni2 — Tue, 25 Oct 2016 19:19:52 GMT

Of course 🙂

 index=*
 | bucket _time span=1d 
 |convert ctime(_time) AS date timeformat="%Y/%m/%d" 
 | stats count by host date 
 | appendpipe [| stats avg(count) as count by host | eval date="Host Avg" ]
 | xyseries host date count
 | addtotals | eval Total=Total-'Host Avg'
 | appendpipe [stats avg(*) as * | foreach * [eval "<<FIELD>>"=round('<<FIELD>>') ] ]

Re: How to edit my search to append a total average column for a chart?

splunkin11 — Tue, 25 Oct 2016 19:28:59 GMT

Wowzers! That's something I've never seen or heard of before ... you're awesome!

Re: How to edit my search to append a total average column for a chart?

somesoni2 — Tue, 25 Oct 2016 19:37:15 GMT

Glad to be of help. If there are no follow-up questions, you can close the question by accepting this answer. I'll update my answer to reflect the final version of search.

Re: How to edit my search to append a total average column for a chart?

splunkin11 — Tue, 25 Oct 2016 19:44:11 GMT

.. one small addition if you don't mind .. is there also a way to add a label for the last totals row produced from :
| appendpipe [stats avg(*) as * | foreach * [eval "<>"=round('<>') ] ]

Re: How to edit my search to append a total average column for a chart?

somesoni2 — Tue, 25 Oct 2016 19:47:01 GMT

If by label you mean a value of say "Average by Host" (or something similar) on the 'host' field for very last row, yes. Just add this eval after the foreach command.

....
 | appendpipe [stats avg(*) as * | foreach * [eval "<<FIELD>>"=round('<<FIELD>>') ]  | eval host="Average by Host"]

Re: How to edit my search to append a total average column for a chart?

splunkin11 — Tue, 25 Oct 2016 20:00:03 GMT

cool - thank you sir!