List active alerts

I have kapacitor setup with this script:

dbrp “metrics”.“raw”

var data = stream

// raise an alert when loss is too high
.id(‘{{ .TaskName }}/{{index .Tags “res_name” }}’)


the alerts are correctly stored in influxdb but I have some troubles to write a query returning only active ones, here is what I tried but fails:

select “time”, “duration”, “id”, “level”, “value”, LAST(“level”) AS last_level from raw.alerts WHERE “last_level” != “OK” GROUP BY “res_name”, “host”, “id_tag” ORDER BY time DESC LIMIT 1

My idea was to query all alerts where the last level is not OK but it returns some lines with OK as level and I am not sure why.

I played a bit more with the query but still cannot find a solution, part of the problem is the number of errors you can do with influxdb happily taking your wrong query and just ignore the part it does not like, I really hate that, why not just return a plain error in my face ?

For example why all these works (but return nothing obviously):

select “I_dont_exists” from raw.alerts;
select * from raw.alerts where “dingo” = ‘32’;

(I don’t have any fields or tags by those names)

This makes writing more advanced queries in influxdb a nightmare sometimes, I just do not get why it was designed that way :frowning:

In this specific case “last_level” does not seems to be valid in my where clause, if I write “last_dingo” the result is the same…

this looks more and more like a bug, I opened an issue on github: aggregation not working in where clause · Issue #9394 · influxdata/influxdb · GitHub