Query/Alert for Difference in Value

InfluxDB 2
1

I have sensor data going into InfluxDB 2.0 and being visualized via Grafana. One of the queries that I use to visualize my data is here:

from(bucket: “bucket1”)
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == “Veggies”)
|> filter(fn: (r) => r[“model”] == “TR310W”)
|> filter(fn: (r) => r[“addr”] == “1”)
|> filter(fn: (r) => r["_field"] == “VWC_percentage”)
|> aggregateWindow(every: v.windowPeriod, fn: last, createEmpty: false)
|> yield(name: “last”)

I use this to view the water content level of my plants. I need help writing the query that I can use to alert me whenever the value for the water content decreases by a defined value, and when it increases by a defined value. This will help automate the triggering of watering and serve as a watering confirmation.

I did my research and saw that I could use derivative to calculate the rate of change, but I don’t need that; I need to know when the value has changed by a certain amount. The difference function seems appropriate, but I cannot for the life of me figure out the syntax to make it work.

If this is a function or calculation better done in Grafana, or if I am asking in the wrong section, please forgive me!

Thank you in advance for your time and assistance!

2

Hello @SBNV,
Welcome!
Are you using OSS or Cloud?
Do you want to create an alert task through the UI or your own custom one?
You’re right you can use difference as you suggested. The following worked for me:

from(bucket: "noaa")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "average_temperature")
  |> filter(fn: (r) => r["_field"] == "degrees")
  |> filter(fn: (r) => r["location"] == "coyote_creek")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
  |> difference(nonNegative: false, columns: ["_value"])

A complete alert task would look something like:

option task = {
name: "water alert",
every: 10s,
offset: 2s,
}

import "slack"

alert = (level, type, eventValue)  => {
slack.message(
       url: "https://hooks.slack.com/services/xxx/xxx",
       text: "An alert \"${string(v: type)}\" event has occurred! The number of field values= \"${string(v: eventValue)}\".",
       color: "warning", 
       channel: "#Anais"
       )
       return level 
       }

from(bucket: "noaa")
  |> range(start: -task.every)
  |> filter(fn: (r) => r["_measurement"] == "average_temperature")
  |> filter(fn: (r) => r["_field"] == "degrees")
  |> filter(fn: (r) => r["location"] == "coyote_creek")
  |> aggregateWindow(every: v.windowPeriod, fn: mean, createEmpty: false)
  |> difference(nonNegative: false, columns: ["_value"])
// I include a limit here for testing only to make sure that I don't spam my slack and that I only have a couple of values that exceed my thresholds defined in the filter() function below. 
  |> limit(n: 10)
  |> filter(fn: (r) => r._value < -3.0 or r._value > 3.0) 
  |> map(
        fn: (r) => ({r with level:  alert(level: 1, type: "warn", eventValue: r._value) }))

You could also take out the filter function, and perform conditional mapping and assign a level for okay and bad saturation levels. Then you could use the to() function to write that level data back into influxdb so you have more information about your system and the check you created. Let me know if you want an example of that as well.

You can test out this task by executing it in the data explorer as well. And also including multiple yield() functions in between lines. I think of yield() functions like print statements.

3

@SBNV,
one note:
Since you’re looking at differences, you want to make sure that you’re querying for data from the last time the task ran plus the last data point from the last task every parameter. So you don’t miss a difference between task runs.
Like if you’re running the task every 30s and collecting data 5s I’d make my range(start: -35) to include the last point of the previous task run.