Simple Alert and Query (Postgresql)

Grafana Alerting
1

Hello all!

I have a simple time series of air temperature that I would like to alert on three possible conditions:

  1. Any value from the last 1 hour is above a value.
  2. Any value from the last 1 hour is below a value.
  3. No data from the last hour.

I have written a very basic query that returns the time series data:

SELECT
  airtemp1,
  ts AS "time"
FROM
  bsp_met_min
WHERE
  $__timeFilter(ts)
ORDER BY
  ts ASC

And then use two reduce for min/max:

image
image1163Ă—405 36.1 KB

And then I use a math expression to check the range. (Yes, they are checking against the same value, 30, as a test for me - in practice these would be actual values.)

Is this the correct way to proceed here? I would have thought threshold might be an answer but I can only use one refid in the threshold check.

I’d really appreciate any insight or pointers for a better way to proceed (this is with just one parameter, airtemp, but I do have multiple other parameters in the table that I’d like to check against other ranges as well - something like pressure and precipitation).

I think I have the nodata sorted out by using the nodata alert state to Alerting - is that correct?

TIA!

2

LGTM. But you have mature SQL, so I would use business logic on SQL level. Idea (not copy&paste solution)

SELECT
  CASE 
    WHEN (MAX(temp) > 30 OR MIN(temp) < 30) THEN 1
    ELSE 0
  END
  AS alerting
....

No reduction/math on the Grafana level. Result can be used directly as alert condition in the grafana alert.

2 Likes
3

Yes, I could move that logic into psql. Thanks for the suggestion!

How would I handle multiple tests in alerts then?

For instance, testing something like airtemp and pressure?

I can write the query to check it, but it would return multiple values (an “alerting” for each parameter)?

4

You need to aggregate all values into one metric alerting:

SELECT
  CASE 
    WHEN (MAX(temp) > 30 OR MIN(temp) < 30 OR pressure > 110 OR waterlevel > 50) THEN 1
    ELSE 0
  END
  AS alerting
....

So if it is alerting, then you know that something is wrong - but you don’t know which metric in this setup.
If you need to know that in the alert, then make separate alert for each metric and then you can mention that in the summary/description annotation (metric X is outside of alerting limits)

1 Like