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

[splunkin11]

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 *]

[somesoni2 · Accepted solution]

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>>') ] ]

[splunkin11]

Great!! Your final answer fixes everything - thanks!

[splunkin11]

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:

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
| appendcols [search index=
| stats count as "Total Count" by host ]
| appendpipe [stats avg(* ) as *]

[somesoni2]

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 * ]

[splunkin11]

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

[somesoni2]

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>>') ] ]

[splunkin11]

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

[somesoni2]

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.

[splunkin11]

.. 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('<>') ] ]

[somesoni2]

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

[splunkin11]

cool - thank you sir!

[splunkin11]

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

[splunkin11]

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

[splunkin11]

hmmm .... returns 0 results