Problem: Can't find a solution for this query in Flux (Migrating from InfluxQL to Flux)

Grafana
,
1

Hi,

so im trying to build a Proxmox monitor with Grafana & InfluxDB and I’ve got this query here:

SELECT (mean("mem")/last(maxmem))*100 FROM "system" WHERE ("object" = 'lxc') AND ("nodename" =~ /^$node$/)

This is what I tried so far, but it keeps saying “No data”:

A:
from(bucket: "my_bucket")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "system")
  |> filter(fn: (r) => r["_field"] == "mem")
  |> filter(fn: (r) => r["object"] == "lxc" and r["nodename"] == "${node}")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)

B:
from(bucket: "my_bucket")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "system")
  |> filter(fn: (r) => r["_field"] == "maxmem")
  |> filter(fn: (r) => r["object"] == "lxc" and r["nodename"] == "${node}")
  |> aggregateWindow(every: v.windowPeriod, fn: last, createEmpty: false)

C:
($A / $B) * 100

I’ve been searching for hours now, but could not quite find a solution on how to divide these two fields. Maybe somebody could help me? :slight_smile:

Thanks in advance!

2

Hi there,

So you want to show a single value that gives the average of all the values on “mem” in the sepected time range (for a particular node) divided by the last value of “maxmem” for that node?

You should be able to do this in a single query, this should get you close but I haven’t tested it.

data = from(bucket: "my_bucket")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r._measurement == "system")
  |> filter(fn: (r) => r._field == "mem" or r._field == "maxmem")
  |> filter(fn: (r) => r["object"] == "lxc" and r["nodename"] == "${node}")
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value") 
  |> sort(columns:["_time"]

mean = data
  |> mean(column:"mem")

last = data
  |> last(column:"maxmem")

union(tables:[mean,last])
  |> map(fn: (r) => ({
      _time: r._time,
      _value: r.mem / r.maxmem * 100.0
    })
  )

Thanks,
Tom

3

Hey,

thanks for your answer.

This almost got me there. As a single value your version works. But I wanted to display it as a graph, so not fully there :smiley:

Is there a way to change this to a graph?

Thanks,

SpreeZ