Is there something similar to addtotals to sum by a field

[rrustong]

I'm trying to run a calculation that will average all values over a day, then add all values by a field (Building in my example below), average all of the sums and finally sum the averages. Thanks to the help I received here I was able to get everything except the sum by a field. This is a new requirement that I wasn't aware of when I asked the previous question. Here is some sample data:

"2015-03-03 22:00:00" BUILDING="bldgA", LOCATION="locationA", VALUE=2
"2015-03-03 23:00:00" BUILDING="bldgA", LOCATION="locationA", VALUE=3
"2015-03-03 24:00:00" BUILDING="bldgA", LOCATION="locationA", VALUE=4
"2015-03-03 22:00:00" BUILDING="bldgA", LOCATION="locationB", VALUE=2
"2015-03-03 23:00:00" BUILDING="bldgA", LOCATION="locationB", VALUE=2
"2015-03-03 24:00:00" BUILDING="bldgA", LOCATION="locationB", VALUE=3
"2015-03-04 22:00:00" BUILDING="bldgA", LOCATION="locationA", VALUE=5
"2015-03-04 23:00:00" BUILDING="bldgA", LOCATION="locationA", VALUE=4
"2015-03-04 24:00:00" BUILDING="bldgA", LOCATION="locationA", VALUE=10
"2015-03-04 22:00:00" BUILDING="bldgA", LOCATION="locationB", VALUE=2
"2015-03-04 23:00:00" BUILDING="bldgA", LOCATION="locationB", VALUE=4
"2015-03-04 24:00:00" BUILDING="bldgA", LOCATION="locationB", VALUE=6
"2015-03-03 22:00:00" BUILDING="bldgB", LOCATION="locationC", VALUE=2
"2015-03-03 23:00:00" BUILDING="bldgB", LOCATION="locationC", VALUE=3
"2015-03-03 24:00:00" BUILDING="bldgB", LOCATION="locationC", VALUE=4
"2015-03-03 22:00:00" BUILDING="bldgB", LOCATION="locationD", VALUE=2
"2015-03-03 23:00:00" BUILDING="bldgB", LOCATION="locationD", VALUE=2
"2015-03-03 24:00:00" BUILDING="bldgB", LOCATION="locationD", VALUE=3
"2015-03-04 22:00:00" BUILDING="bldgB", LOCATION="locationC", VALUE=5
"2015-03-04 23:00:00" BUILDING="bldgB", LOCATION="locationC", VALUE=4
"2015-03-04 24:00:00" BUILDING="bldgB", LOCATION="locationC", VALUE=10
"2015-03-04 22:00:00" BUILDING="bldgB", LOCATION="locationD", VALUE=2
"2015-03-04 23:00:00" BUILDING="bldgB", LOCATION="locationD", VALUE=4
"2015-03-04 24:00:00" BUILDING="bldgB", LOCATION="locationD", VALUE=6

Here is the search that has gotten me very close:

... | bucket _time span=1d |  chart limit=0 avg(VALUE) over _time BY LOCATION | addtotals fieldname="TOTAL" | stats avg(TOTAL) as Monthly 

What I would really like to do is the following, but obviously it doesn't work this way:

... | bucket _time span=1d |  chart limit=0 avg(VALUE) over _time BY LOCATION | addtotals fieldname="BldgTotal" BY BUILDING | addtotals fieldname="TOTAL" BldgTotal | stats avg(TOTAL) as Monthly

Edit: Looks like I can't post links. I was trying to post a link above to answer-292895 for the question "How to obtain a sum of averages" on splunk answers.

[somesoni2 · Accepted solution]

Try something like this

your base search | bucket span=1d _time | stats avg(Value) as Value by _time LOCATION BUILDING| stats sum(Value) as BldgTotal by _time LOCATION | stats sum(BldgTotal) as TOTAL by _time | stats avg(TOTAL) as Monthly

[rrustong]

Thank you, this got me moving in the right direction so I was able to get exactly what I needed. I realize the math is a bit unorthodox (averaging an average), but I'm trying to replicate some reporting that we are doing today in a different tool, so I need to show that I can average, add and round the data in the same way we are today. After looking back over my question, I may not have described exactly what I need very well, but your answer did get me where I needed to be, so thank you. For reference, this is what I ended up with that seems to be working for me at the moment:

... | bucket _time span=1d | stats avg(Value) as dailyAverage by _time LOCATION BUILDING | stats avg(dailyAverage) as monthlyAverage by LOCATION BUILDING | eval monthlyAverage=round(monthlyAverage,2) | stats sum(monthlyAverage) as BldgTotal by BUILDING | eval BldgTotal=round(BldgTotal) | stats sum(BldgTotal) as Total | eval totalWatts=tostring(totalWatts, "commas")

[gcato]

Hi rrustong,

Have a look at this wiki page which has a good example of rolling averages over different time periods.

http://wiki.splunk.com/Community:Search_Report:_How_To_Create_a_Table_of_Day_of_Week_-_Monthly_Avera...