Alert on no results

Get Help
1

Hi I have a query I run to determine if a table is being populated recently. Ie

SELECT to_char(ts, 'DD-MM-YYYY HH24:MI:SS') FROM sensors, sensor_values
    WHERE sensors.name  ILIKE 'bi1%' AND sensors.id=sensor_values.sensor_id
    AND ts >= (NOW() + INTERVAL '-4 minute')::timestamptz ORDER BY ts DESC LIMIT 1

I want to setup an alert when this returns no results ie data is older than 4 minutes.
What is the best way to achieve that ? At the moment I can seem to only setup an alert when rows are returned.

Thanks

2

This makes total sense. I’ve opened an issue for it here:

1 Like
3

You could so something in SQL that returns no data when there is a match, but a single row when there is no match.
This was done with a sample SQL Server database, but easy enough to modify for your case.

I’m just looking for when there are no customers in a certain country.
First, when I have data:

SELECT * FROM (SELECT case 
WHEN (select count(*) from Customer where country = 'USA') = 0 THEN 'Not Found' 
END as find
) a
INNER JOIN
( SELECT 'Not Found' as find2 ) b on a.find = b.find2

Returns no rows

Then when there is no match (just the country name changed):

SELECT * FROM (SELECT case 
WHEN (select count(*) from Customer where country = 'Nonsense') = 0 THEN 'Not Found' 
END as find
) a
INNER JOIN
( SELECT 'Not Found' as find2 ) b on a.find = b.find2

Returns a single row, so would trigger the alert.

It’s a bit fiddle with the inner join, but otherwise I just ended up with a single row with a value of ‘NULL’

1 Like
4

Thanks for the work around. This gets me out of a spot until the zero row alert is implemented.