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 
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?
