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.
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
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
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")
Hi rrustong,
Have a look at this wiki page which has a good example of rolling averages over different time periods.