Hi everyone, I’m new to Grafana and InfluxDB, but recently I came across a problem with aggregateWindow function and solved the issue by sheer luck. It’d be greatly appreciated if anyone could provide an explanation why it works.
Thank you in advance!
-
What Grafana version and what operating system are you using?
Grafana v10.3.3 on Ubuntu 22.04
InfluxDB v2.7.5 -
What are you trying to achieve?
- To create a Time series graph for CPU utilization of multiple VMs using aggregateWindow function to show max value (fn: max) while dynamically group data in a “readable” manner.
Note: data is polled every 20 seconds, from vCenter to InfluxDB using Telegraf.
- How are you trying to achieve it?
Flux query:
from(bucket: v.defaultBucket)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“_measurement”] == “vsphere_vm_cpu”)
|> filter(fn: (r) => r[“_field”] == “usage_average”)
|> filter(fn: (r) => r[“cpu”] == “instance-total”)
|> filter(fn: (r) => r[“vmname”] =~ /some regex)
|> group(columns: [“vmname”])
|> aggregateWindow(every: v.windowPeriod, fn: max, createEmpty: false)
|> yield(name: “max”)
- What happened? / What’s wrong?
Result:
- fn: max causes field/ column names mixed with start, stop and vmname label tags. E.g. _value {_start=###, _stop=###, vmname=###}.
- All labels/ tags are truncated into individual fields/ columns. If fn: mean, there are only 2 fields: time and value, each series is separated by VM name.
- What did you expect to happen?
- Only time field named “time” and value field named by the VM names.
- Max value of the interval should be displayed, instead of taking the mean of the interval which could result in lower peak value/ lower max value in the graph.
- Even with a larger timeframe, say last 7 days, the data points should be visible and readable. So aggregateWindow is really important in my case, without this function, there will be too many data points.
- What else did I try? (details to be shared upon request)
- Map functions to rename field + select only wanted fields. I tried map using r with r[“vmname”] but no luck. I did not find another way to put r[“vmname”] as series key(?), and column name.
- Data transformation to replace field that matches (.){.} by $1. It reduced the field/ column name to “_value” but data can no longer be distinguished by VM.
- In grafana options, set “Display name” to variable $vm but it will retrieve entire list of VM instead of specific VM.
- No group columns and transform field names into variable, unfortunately, transformation does not allow reference (using variable $vm).
- Solution
Flux query:
from(bucket: v.defaultBucket)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r[“_measurement”] == “vsphere_vm_cpu”)
|> filter(fn: (r) => r[“_field”] == “usage_average”)
|> filter(fn: (r) => r[“cpu”] == “instance-total”)
|> filter(fn: (r) => r[“vmname”] =~ /some regex/)
|> group(columns: [“vmname”])
|> aggregateWindow(every: v.windowPeriod, fn: max, createEmpty: false)
|> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
|> yield(name: “mean”)
I found this solution after a week of trial and error, could anyone shed some light? is the fn: max behavior worked as expected?
I tried both queries on InfluxDB Data Explorer (Query/ Script builder) but got no problems with X/ Y columns. It only happens on Grafana.
I’d really appreciate any clues/ answers. Many thanks!