Iceman
April 19, 2024, 11:17am
1
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
1 Like
Iceman
April 19, 2024, 4:52pm
3
Many Thanks! Oboth solutions works for me!