Query with 2 measurements for Gauge Visualisation in Grafana

Hi,

I need a query in Grafana which filters the last value from 2 measurements, negate the first value and shows it on a Gauge in Grafana. For a better Explanation:

value1 = from(bucket: “bucket0”)
|> range(start: -1h, stop: now())
|> filter(fn: (r) => (r[“_measurement”] == “measurement1”
|> filter(fn: (r) => r[“_field”] == “value”)
|> last()

value2 = from(bucket: “bucket0”)
|> range(start: -1h, stop: now())
|> filter(fn: (r) => (r[“_measurement”] == “measurement2”
|> filter(fn: (r) => r[“_field”] == “value”)
|> last()

Value = (value1 * -1.0) + value2

The Gauge have the 0 Value in the middle min Value is -100 and max Value is 100.

I tried it with map, but it will not work… Can somebody help me here?

Hello @Iceman,
I would do something like:

result = join(
  tables: {v1: value1, v2: value2},
  on: ["_time"]
)
|> map(fn: (r) => ({
    _value: (r._value_v1 * -1.0) + r._value_v2
  })
)

You could also use findRecord:

value1 = from(bucket: "bucket0")
  |> range(start: -1h)
  |> filter(fn: (r) => r["_measurement"] == "measurement1")
  |> filter(fn: (r) => r["_field"] == "value")
  |> last()
  |> findRecord(fn: (key) => true, idx: 0)  // Assuming there is always at least one record

// Fetch the last value for measurement2
value2 = from(bucket: "bucket0")
  |> range(start: -1h)
  |> filter(fn: (r) => r["_measurement"] == "measurement2")
  |> filter(fn: (r) => r["_field"] == "value")
  |> last()
  |> map(fn: (r) => ({
    _value: (value1._value * -1.0) + r._value_v2
  })
)
|> yield(name:"final")

I hope this helps lmk :slight_smile:

1 Like

Many Thanks! Oboth solutions works for me!