CODEWITHSUNDEEP
metricsinfluxdbgrafana
bbigras
bbigras

Reputation: 1430

Is there a way to have a moving average in Grafana?

I didn't find a 'moving average' feature and I'm wondering if there's a workaround.

I'm using influxdb as the backend.

Upvotes: 34

Views: 93908

Answers (7)

dloeckx
dloeckx

Reputation: 1200

InfluxDB 2 allows you to calculate the moving average in the query, e.g.:

from(bucket: "iot")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "PoolWeather")
  |> filter(fn: (r) => r["_field"] == "batteryvoltage")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
  |> movingAverage(n: 10)
  |> yield(name: "average")

enter image description here

Upvotes: 4

Ryabchenko Alexander
Ryabchenko Alexander

Reputation: 12470

try avg_over_time(mymetric[5m])

enter image description here

Upvotes: 6

klc
klc

Reputation: 61

Method and capability for this is dependent on your datasource.

You specified InfluxDB, so your query will need to wrap an 'Aggregation function' [ such as mean($field) ] within the moving_average($aggregation_function, $num_of_points) 'Transformation Function'.

In the 'Metrics' tab, you will find both the 'Transformation' functions in the 'select' portion of the menu.

Craft your query with the 'Aggregation function' (mean, min, max, etc.) first -- this way you can make sure the data looks as you expect it.

After this, just click the '+' button next to the 'Aggregation function', and under the menu 'Transformations', select 'moving_average'.

The number in brackets will be the number of points you want the average taken over.

Screenshot:

Screenshot

Upvotes: 6

odedfos
odedfos

Reputation: 4619

Another option is to report the data as "timing" metrics and not counts. This is easy to do especially with Statsd in your stack. Plotting timing data (coming from statsd) as average of the reported data points is already built in.

Upvotes: 1

thaddeusmt
thaddeusmt

Reputation: 15600

I found myself here trying to do a moving average in Grafana with a PostgreSQL database, so I'll just add a way to do with a SQL query:

SELECT
  date as time,
  AVG(daily_average_column) 
    OVER(ORDER BY date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) 
    AS value,
  '5 Day Moving Average' as metric
FROM daily_average_table
ORDER BY time ASC;

This uses a "window" function to average of the last 4 rows (plus the current row).

I'm sure there are ways to do this with MySQL as well.

Upvotes: 8

Sergey Martynov
Sergey Martynov

Reputation: 446

Grafana does no calculations itself, it just queries a backend and draws nice charts. So aggregating abilities depends solely on your backend. While Graphite supports windowing functions such as moving average, InfluxDB currently doesn't support it.

There are quite a lot requests for moving average in influxdb on the web. You can leave your "+1" and track progress in this ticket https://github.com/influxdb/influxdb/issues/77

Possible (yet not so easy) workaround is to create a custom script (cron, daemon, whatever) that will pre-calcuate MA and save it in a separate influxdb series.

Upvotes: 23

user559633
user559633

Reputation:

Grafana supports adding a movingAverage(). I also had a hard time finding it in the docs, but you can (somewhat hilariously) see its usage on the feature intro page:

screenshot showing movingAverage() call

As is normal, click on the graph title, edit, add the metric movingAverage() as per described in the graphite documentation:

movingAverage(seriesList, windowSize)

Graphs the moving average of a metric (or metrics) over a fixed number of past points, or a time interval.

Takes one metric or a wildcard seriesList followed by a number N of datapoints or a quoted string with a length of time like ‘1hour’ or ‘5min’ (See from / until in the render_api_ for examples of time formats). Graphs the average of the preceding datapoints for each point on the graph. All previous datapoints are set to None at the beginning of the graph.

Example:

&target=movingAverage(Server.instance01.threads.busy,10)
&target=movingAverage(Server.instance*.threads.idle,'5min')

Upvotes: 26

Related Questions