Hello,
I have various fields in a measurement, which are only added to the DB on change. Now I would like to see if a certain signal at any time is greater than another signal at that time/period.
e.g. temperature1 greater than temperature2
When I have found the time points, I would like to visualize the time period of the exceedance by an annotation in Grafana.
I found this post Find timerange based on the change of a value but this work with an constant and not with an other field.
There is also the challenge that not all fields are available at all times, as the values are only added when the DB is changed.
Hello @Nick135,
How are you writing this data to InfluxDB? If you’re using Telegraf, I might encourage you to use a processor plugin like execd and make that comparison and write a new data point when that condition is met.
Otherwise you could use a flux task to perform that evaluation and then write new data point with the to() function at that time that indicates a change.
And plot those change points.
from(bucket: "your_bucket")
|> range(start: -1h) // Adjust the time range as needed
|> filter(fn: (r) => r["_measurement"] == "your_measurement")
|> filter(fn: (r) => r["_field"] == "temperature1" or r["_field"] == "temperature2")
|> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
|> map(fn: (r) => ({
r with
temp1_greater_than_temp2: if r.temperature1 > r.temperature2 then 1 else 0
}))
|> filter(fn: (r) => r.temp1_greater_than_temp2 == 1)
Something like that. Then use the to() funciton to write that data back.
Or use the execd processor plugin for wahtever logic you need.
Hello @Anaisdg,
I write the data with the .Net Api to the InfluxDB, but the comparison and e.g. an offset should be flexible. That’s why I prefer flux.
I have already tried pivot() and map() but it doesn’t work as expected. Just like your example.
I have tried a bit and the problem is probably with the pivot function. Could it be that I still need the fill() or a similar function so that the result of the map() function is correct?
from(bucket: "your_bucket")
|> range(start: -1h) // Adjust the time range as needed
|> filter(fn: (r) => r["_measurement"] == "your_measurement")
|> filter(fn: (r) => r["_field"] == "temperature1" or r["_field"] == "temperature2")
|> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
|> map(fn: (r) => ({
r with
temp1_greater_than_temp2: if r.temperature1 > 0.0 then 100 else -100
}))
It looks like the pivot() function creates a lot of rows in which either one or the other variable has a value. Is there a function to fill the gaps in the pivot table? Either with the last available value or better with an interpolated value between the last and next available value.
