Detect constant streak of values

InfluxDB 2
1

Hi.

We use InfluxDB + Grafana to store and plot sensor values. We would like to detect when a given sensor returns a constant value, as this is typical of a faulty sensor.

I’m setting a daily alert in Grafana using an InfluxQL query to check the last day of data.

My first intent is to use difference or derivative function, then either sum the absolute values or set a threshold to detect if it is a constant zero.

Looks like those functions don’t work as I expect because they require an aggregation method, while I want to operate on the raw values.

Should I be using mean anyway? Or last? I performed a few tests in Grafana explore mode, and it is a bit unclear to me what the aggreg method does when combined with difference.

The only doc I could find is the Flux doc about difference.

Am I using the wrong tool?

How would you advise to achieve what I’m trying to?

Thanks.

2

Hello @Jerome,
They don’t require an aggregation method!

import "sampledata"

sampledata.int()
    |> difference()

import "sampledata"

sampledata.int()
    |> derivative(nonNegative: true)

I think both of your proposed solutions sound great.

import "array"
import "slack"

option task = { name: "Event Alert", every: 1h0m0s, offset: 5m0s }

alert = (eventValue, threshold) =>
   (if eventValue >= threshold then slack.message(
       url: "https://hooks.slack.com/services/####/####/####",
       text: "An alert event has occurred! The number of field values= \"${string(v: eventValue)}\".",
       color: "warning",
   ) else 0)
data = from(bucket: "bucket1")  
   |> range(start: -task.every, stop: now())
   |> filter(fn: (r) =>
       (r._measurement == "measurement1" and r._field == "field1" and exists r._value))
   |> increase() 
data_0 = array.from(rows: [{_value: 0}])
events = union(tables: [data_0, data])
   |> group()
   |> sum()
   |> findRecord(fn: (key) =>
       (true), idx: 0)
eventTotal = events._value

data_0
   |> yield(name: "ignore")
alert(eventValue: eventTotal, threshold: 1)
3

Thanks @Anaisdg for answering.

I’m using InfluxQL in Grafana plugin.

More specifically, my question is why this works

SELECT difference(mean("value")) FROM "device_frmpayload_data_temperature" WHERE $timeFilter GROUP BY time($__interval)

and this

SELECT difference("value") FROM "device_frmpayload_data_temperature" WHERE $timeFilter GROUP BY time($__interval)

fails with

aggregate function required inside the call to difference

I want to operate on raw data, not mean, and I don’t understand the need for the aggregation, and I couldn’t find the docs for the InfluxQL functions, only the Flux ones.

4

@Jerome,
I’m not sure as you should be able to do that with influxdb:

can you try querying influxdb directly with curl or the api or the cli or the ui?
I wonder if this is a grafana issue.