[influxdb] Moving averages

[Joel Shprentz]

Last week Zachary Tong posted Staying in Control with Moving Averages -
Part 1
<https://www.elastic.co/blog/staying-in-control-with-moving-averages-part-1>
to the Elastic Co. blog. He described how to compute a moving average with
Elasticsearch and how to combine those with standard deviations to plot
control charts.

I wonder how to accomplish the same thing with InfluxDB and Grafana.

Continuous queries can periodically compute the mean and standard deviation
for some interval, say an hour. It does not seem possible to structure a
continuous query to compute every hour the mean for the past 24 hours. A
time-weighted moving average seems even more challenging.

Once the moving average and standard deviation are written into some new
time series, SELECT column arithmetic could compute the upper and lower
control limits.

I would appreciate any suggestions about how to implement this.

--
Joel Shprentz

[s***@gmail.com]

Once the moving average and standard deviation are written into some new time series, SELECT column arithmetic could compute the upper and lower control limits.

+1 to this ability. Currently using SQL tables I calculate a moving average and std deviations for each hour of the week (e.g. 168 hourly averages and std deviations for each measurement) so that I can chart in Excel the current values vs those thresholds. There is no way to replicate this in InfluxDB today as there are several missing features to do so.

I would need the ability to extract the hour of the week from the timestamp, the ability to join on that extracted hour of the week, the ability to perform inline calculations using conditional logic (e.g. "when avg(cpu_util) - (2 * stddev(cpu_util)) > 0 then round(avg(cpu_util) - (2 * stddev(cpu_util)),2) else 0)". Rounding is a nice to have, not required for this use case but the extra precision isn't really necessary. These values are calculated once a week based on the prior 3 months of data and become the control limits for the next week.

Here is the actual SQLServer Query for context:
SELECT DayOfWeek
,datepart(hh,UM.TimeStamp) AS Hour
,cast(min(UM.timestamp) as date) as WindowBegin
,cast(max(UM.timestamp) as date) as WindowEnd
,UM.Environment
,round(AVG(UM.AvgCPU),2) AS MeanCPU
,round(STDEV(UM.AvgCPU),4) As CPUDeviation
,case
when avg(UM.avgcpu) - (2*STDEV(UM.avgcpu)) > 0 then round(avg(UM.avgcpu) - (2*STDEV(UM.avgcpu)),2)
else 0
end As LowCPULimit
,case
when avg(UM.avgcpu) + (2*STDEV(UM.avgcpu)) <= 100 then round(avg(UM.avgcpu) + (2*STDEV(UM.avgcpu)),2)
else 100
end As HighCPULimit
,round(AVG(UM.AvgMem),2) AS MeanMem
,round(STDEV(UM.AvgMem),4) As MemDeviation
,case
when avg(UM.avgmem) - (2*STDEV(UM.avgmem)) > 0 then round(avg(UM.avgmem) - (2*STDEV(UM.avgmem)),2)
else 0
end As LowMemLimit
,case
when avg(UM.avgmem) + (2*STDEV(UM.avgmem)) <= 100 then round(avg(UM.avgmem) + (2*STDEV(UM.avgmem)),2)
else 100
end As HighMemLimit
,round(AVG(UM.MaxRunQ),2) AS MeanMaxRunQ
,round(STDEV(UM.MaxRunQ),4) As MaxRunQDeviation
,case
when avg(UM.MaxRunQ) - (2*STDEV(UM.MaxRunQ)) > 0 then round(avg(UM.MaxRunQ) - (2*STDEV(UM.MaxRunQ)),2)
else 0
end As LowMaxRunQLimit
,round(avg(UM.MaxRunQ) + (2*STDEV(UM.MaxRunQ)),2) As HighMaxRunQLimit
FROM UtilizationMetrics UM
WHERE UM.TimeStamp >= dateadd(month,-3,(select max(timestamp) from UtilizationMetrics))
GROUP BY DayOfWweek, datepart(hh,UM.timestamp), UM.Environment