Questdb datasource query in Grafana alert

Community
1

Hello! I am enjoying my initial experience with Questdb using more natural SQL queries vs InfluxDB. However I have been struggling with Grafana Alerts using a Questdb datasource and hope you can help.

My initial aim is to send an alert from Grafana to Telegram if a device stops sending data to Questdb. A Tank1 datasource includes columns voltage and timestamp. In the QuestDb Web Console I have successfully tested this query to get the most recent 5 minutes of data:

SELECT voltage, timestamp FROM "tank1" WHERE (timestamp > dateadd('m', -5, now())) ORDER BY timestamp DESC;

If I stop the datasource logging to Questdb for greater than 5m, I get a successful response in the console, with no records returned.

If I run the same query as an alert condition in Grafana when there is data, the alert is healthy and does not fire. When the data is stopped, after 5 minutes the alert changes to an error:

[sse.readDataError] [voltage] got error: no float64 value column found in frame voltage
([voltage] is the name of the query)

I have tried many variations, including changing the query to ‘Time series’, (with and without the voltage column), with different errors and alerts not working along the way. For example:

SELECT timestamp FROM "tank1" WHERE $__timeFilter(timestamp) SAMPLE BY $__sampleByInterval ALIGN TO CALENDAR ORDER BY timestamp DESC
using a time range of ‘Now -5m’ to restrict the data. This query produces the error:
[sse.readDataError] [voltage] got error: input data must be a wide series but got type not (input refid)

I want to alert on both the voltage level (<11.5V) and ‘No Data’, but it’s not a problem if I can’t do both in one alert.

Can anyone assist please?

2

If I have not expressed this issue in a way that makes sense, please let me know! I am using Grafana v11.4.0 and Questdb v8.2.2, both in docker.

Fortunately I am logging concurrently to Influxdb and a simple query to return voltage in the last two minutes provides ‘No data’ if the data flow is stopped temporarily.

image
image1153×365 31.5 KB

As detecting a sensor failure is extremely important, how can this be achieved simply with Questdb in Grafana Alerts?

3

Hey, thanks for your patience, will come back to you in a little bit!

4

Ok, for the no data error, you could use count() to ensure you always get data back. Then you can match against count == 0 for when there is no data.

What type is your voltage column? Is it a FLOAT, not a DOUBLE?

Re: wide-series error, I’m guessing this is just a confusing message referring to the fact it can’t find a numeric column to use for the alert - maybe due to a typing issue.

5

Thank you @nwoolmer for the tip. The voltage column is a float.

This rule successfully provides an alert when ‘true’, using a ‘now-2m’ time range:
[voltage_query]

SELECT count(voltage) FROM “tank1” WHERE $__timeFilter(timestamp)
With math expression:
$voltage_query==0

I experimented further and the following also works and provides both No Data and low voltage alerts (with ‘now-2m’ time range):

SELECT last(voltage) FROM “tank2” WHERE $__timeFilter(timestamp)
With math expression (for 12V battery):
${voltage_query} < 11.5

I’m not sure why these queries don’t produce errors like all of the others I tried! I will see what I can discover.

Cheers, Andrew

6

Great news! Let us know if you run into any more issues :slight_smile: