I am trying to create an alert to be fired if a overflow sensor has had overflow (overflow=1) for more than five minutes. My main problem is that if I check very often (every 1-5 min) I get a lot of repeated alerts as the level has not changed. What I want is one alert when it has been in overflow for 5min or more, and then be alerted if it goes from overflow=0 to overflow=1 again. This means that I only want to alerted when the state changes and has kept it’s value for 5 minutes or more. But I do not want alerts when the state has not changed since the last alert or when the state changes back to 0.
As a data source we use timescale and have currently used the following query to get the warning (same 0/1 response, sensor located lower for warning pre overflow) value over time for each object (sensor):
SELECT
time,
warning,
obj.metadata -> 'sid' as name
FROM ts_water_overflow as wof
JOIN iot_objects as obj ON wof.object_fk = obj.id
WHERE
$__timeFilter(time)
AND
obj.metadata -> 'sid' != '0'
GROUP BY 1,2,3
ORDER BY 1,2
Any tips on how to create such alerts?
1 Like
Can someone please help us with the above requirement?
hi naresh 
could you share more details of the configuration of your alert? what’s your evaluation interval (the every and for part of the rule) ?
For more details see step 6 in this guide:
Hi @antonio
I appreciate the response. I have selected all rows in the database where the status is “FAILED”; an email notice should then be sent.
MySQL is my data source. When a failed status is received, an email alert should be sent, and if a success is received, ignore it.
@antonio Can you please help me on the above?
Hi,
If I understand you correctly, I think you should make an alert with a condition on the FAILED state. The alert should be firing when the condition is met and Grafana will send you a firing notification. When the Firing state changes to OK (SUCCESS), by default Grafana will send you a resolved notification.
To disable this behavior, go to Alerts → Contact points → Edit → Notification Settings → Check Disable resolved message.
@clevernessisamyth
It’s true, but I’m encountering the following issue while attempting to create a new alert with MySQL as the database source. Please let me know how to fix the problem.
Failed to evaluate queries and expressions: failed to execute conditions: input data must be a wide series but got type not (input refid)
This error is not clear unless you know how server-side expressions (SSE) work in Grafana. Basically, it says that the response format is expected to be a time series but received not a time series (or “type not”). However, SSE has a special case for non-timeseries but it needs a number field. So, I would recommend you changing your SELECT clause the following way
SELECT
bkp_status as Status,
1 as value
....
This should work,
@yuriy.tseretyan Let me try the above, and I will let you know the same.
