I have a data set that looks similar to the sample lines below and I'm having a difficult time finding a good way to get averages of some events, and then sum the averages together. I'm hoping that someone can help me work out a search to get the data I need.
Here are a few sample events:
"2015-03-03 22:00:00" CLIENT_NAME="ClientA", LOCATION="locationA", VALUE=2
"2015-03-03 23:00:00" CLIENT_NAME="ClientA", LOCATION="locationA", VALUE=3
"2015-03-03 24:00:00" CLIENT_NAME="ClientA", LOCATION="locationA", VALUE=4
"2015-03-03 22:00:00" CLIENT_NAME="ClientA", LOCATION="locationB", VALUE=2
"2015-03-03 23:00:00" CLIENT_NAME="ClientA", LOCATION="locationB", VALUE=2
"2015-03-03 24:00:00" CLIENT_NAME="ClientA", LOCATION="locationB", VALUE=3
"2015-03-04 22:00:00" CLIENT_NAME="ClientA", LOCATION="locationA", VALUE=5
"2015-03-04 23:00:00" CLIENT_NAME="ClientA", LOCATION="locationA", VALUE=4
"2015-03-04 24:00:00" CLIENT_NAME="ClientA", LOCATION="locationA", VALUE=10
"2015-03-04 22:00:00" CLIENT_NAME="ClientA", LOCATION="locationB", VALUE=2
"2015-03-04 23:00:00" CLIENT_NAME="ClientA", LOCATION="locationB", VALUE=4
"2015-03-04 24:00:00" CLIENT_NAME="ClientA", LOCATION="locationB", VALUE=6
I have worked out a pivot table that gives me the following fields, but the last field does not contain the data that I want (this is what I'm asking for help with):
Time (per day), locationA, locationB, ALL
2015-03-03, 3, 2.33, 2.65
2015-03-04, 6.33, 4, 5.165
The values under "locationA" and "locationB" are an average of the values for that day, which is exactly what I want. However, the values under the "ALL" column are an average of what is under "locationA" and "locationB". What I want in the "ALL" column is a sum of the locationA + locationB values. So the ALL column should contain 5.33 and 10.33 for 2015-03-03 and 2015-03-04 respectively.
This is the command generated by my pivot table:
| pivot myDataModel myDataModelObject avg(VALUE) AS "Average of VALUE" SPLITROW _time AS _time PERIOD day SPLITCOL LOCATION FILTER CLIENT_NAME is ClientA SORT 100 _time ROWSUMMARY 0 COLSUMMARY 1 NUMCOLS 100 SHOWOTHER 0
I do not need to do this within a pivot table, but doing so has gotten me closer than any other search that I've tried to write. I really just need to be able to get the values and will likely need to apply them to a bar graph, but I'm sure I can work out a visualization once I have the data that I'm after.
Just to make sure I'm communicating my requirements clearly, this is what I need to get from the data set:
(side note - I can't seem to figure out how to make a nested list...)
Like this:
... | bucket _time span=1d | chart avg(VALUE) over _time BY LOCATION | fieldformat _time=strftime(_time, "%Y/%m/%d") | addtotals fieldname="ALL"
You could try something like this.
... | eval locationB=if(LOCATION=="locationB",value, isnull())|locationA=if(LOCATION=="locationA",value, isnull()) | bucket _time span =1d | stats avg(locationA) as locationA, avg(locationB) as locationB, avg(value) as ALL by _time
Like this:
... | bucket _time span=1d | chart avg(VALUE) over _time BY LOCATION | fieldformat _time=strftime(_time, "%Y/%m/%d") | addtotals fieldname="ALL"
This appears to do exactly what I need. I figured I would need to use buckets, but couldn't wrap my head around what to do with it after that. Thank you.