Alert for actual time is bigger than a given timestamp

HI there,

I started using grafana some weeks ago and was very impressed of how much you can do with that. Now I want to get a bit deeper and trying to use alerts.

This is the use case
I have a whole in my garden where the water from the drainage is collected. Every few hours a pump is running for some minutes to pump the water into the main drain under the street. I want to proof that the pump is running and be informed when there is an inconsistency. If the pump is not running the water will be too much and than go back into the drainage and that the drainage is not a drainage but more a water tunnel to the house, which I am trying to avoid :slight_smile:

This is my environment
A homematic sensor is measuring the consumed power. If the power is over a defined value this is taken as “pump is running” = 1, otherwise this value is 0. Iobroker is collecting this data in a mysql database. This data is visualized by grafana.

What I already did

  • I calculated the time between the runnings (mysql statement)
  • I calculated when the pump should run again by taking the average of the last three intervals.
  • I added a time by taking the 10th of the avg interval and adding that to the calculated next running, so that I have a time threshold
  • I manually looked at the data and if the pump is running into the definied time frame and that fits for the moment

This is the blonging mysql statement for getting the intervals:

SELECT
a.ts AS "time",
(a.ts-(SELECT c.ts from ts_bool as c where c.ts < a.ts and a.id=c.id and c.val =1 order by c.ts 
desc limit 1)) as "Zeitabstand"
FROM ts_bool as a
WHERE
Id = 93 and val=1
ORDER BY ts desc

This is the calculation for the time frame:

SELECT
max(a.ts) AS "time",
sum((a.ts-(SELECT c.ts from ts_bool as c where c.ts < a.ts and a.id=c.id and c.val =1 order by c.ts desc limit 1)))/count(*)+max(a.ts) as nextrun,
max((a.ts-(SELECT c.ts from ts_bool as c where c.ts < a.ts and a.id=c.id and c.val =1 order by c.ts desc limit 1)))/60/1000/10 as korridorminuten,
(sum((a.ts-(SELECT c.ts from ts_bool as c where c.ts < a.ts and a.id=c.id and c.val =1 order by c.ts desc limit 1)))/count(*)+max(a.ts))+(max((a.ts-(SELECT c.ts from ts_bool as c where c.ts < a.ts and a.id=c.id and c.val =1 order by c.ts desc limit 1)))/10) as maxtime,
(sum((a.ts-(SELECT c.ts from ts_bool as c where c.ts < a.ts and a.id=c.id and c.val =1 order by c.ts desc limit 1)))/count(*)+max(a.ts))-(max((a.ts-(SELECT c.ts from ts_bool as c where c.ts < a.ts and a.id=c.id and c.val =1 order by c.ts desc limit 1)))/10) as mintime
FROM (
select id, ts, val
from ts_bool 
WHERE Id = 93 and val=1 
ORDER BY ts desc
limit 3) as a

This is my visualisation at the moment:

All time data is available as timestamp and only shown as date/time by giving it the right unit in grafana.

This is my problem
Now I want to be informed when the actual timestamp is bigger than the timestamp for maxtime, which is the calculated next run plus the buffer time. Therefore I already set up a alert channel (telegram) whoose test message was sent correctly.
Actually I am stuck by how to set up this alert. Can you help out? Do you have any idea?

Hi @lomtas,

First of all congrats on making so much progress with grafana so quickly! A few questions:

  • what grafana version are you running and where?
  • what kind of alert channel do you want to set up?

Also, just a note that Grafana 8 will release in the coming weeks, and it will ship with much greater support for alerting :+1: