I’m using Influxdb and am trying to create an alert that will tell me if the number of data points from the previous hour is below a certain threshold. (In other words, I want to look at the dataset for a completed hour, not the current hour since all data point will not yet be collected.) Looking at the debug info, I see that the following query is generated:
SELECT count("value") FROM "rf_status" WHERE time > now() - 2h and time < now()-1h group by time(1h)
This results in 2 rows of output as shown most easily via testing via the influx cli:
name: rf_status
time count
---- -----
2020-12-19T18:00:00Z 15
2020-12-19T19:00:00Z 3
Removing the group by clause produces the correct results which in this case is a single row with a value of 18.
The problem with the group by is that when running inside grafana the alert only seems to look at the last row returned resulting in a false positive alert.
How can I tell grafana to only return a single row?
Here’s the rule test output:
{
"firing": true,
"state": "alerting",
"conditionEvals": "true = true",
"timeMs": "3.393ms",
"matches": [
{
"metric": "rf_status.count",
"value": 2
}
],
"logs": [
{
"message": "Condition[0]: Query",
"data": {
"from": 1608403625734,
"queries": [
{
"refId": "A",
"model": {
"groupBy": [
{
"params": [
"1h"
],
"type": "time"
}
],
"measurement": "rf_status",
"orderByTime": "ASC",
"policy": "default",
"query": "SELECT count(\"value\") FROM \"rf_status\" WHERE $timeFilter GROUP BY time(1h)",
"rawQuery": false,
"refId": "A",
"resultFormat": "time_series",
"select": [
[
{
"params": [
"value"
],
"type": "field"
},
{
"params": [],
"type": "count"
}
]
],
"tags": []
},
"datasource": {
"id": 7,
"name": "InfluxDB-weather"
},
"maxDataPoints": 0,
"intervalMs": 0
}
],
"to": 1608407225734
}
},
{
"message": "Condition[0]: Query Result",
"data": {
"series": [
{
"name": "rf_status.count",
"points": [
[
3,
1608400800
],
[
15,
1608404400
]
]
}
]
}
},
{
"message": "Condition[0]: Eval: true, Metric: rf_status.count, Value: 2.000",
"data": null
}
]
}