Grafana Alerting based on value of a string?

[mbentley]

Is there any way that one can use data, in my case from InfluxDB, where one of the values I would like to alert on is a string match? I am using the HAProxy Telegraf plugin and as far as I can tell, the only way to get the up/down status is from a status field that is a string. Is there any method to query the data and convert it to a simple 0 or 1 or any way to natively alert based on the string value?

I have some sample values from doing a telegraf test if that helps: Example Telegraf Output for HAProxy Input · GitHub

I don’t see anything that will return an integer that shows if an individual object that is type=server is up or not. I can only see the number of active servers are in a backend but then the backend also doesn’t have a total count of servers either so I can’t determine it from that either.

[ldrascic]

Hi @mbentley,
In my example I am checking if port 8282 is open. If port is open I get result_type=success and if not then result_type=connection_failed. Then I used InfluxDB function map to iterate over each row and inside map I called strings.replaceAll function to replace connection_failed with 0 and success with 1 (those are still strings). After that I needed to convert string to float (or integer value) with toFloat(). After that I used Reduce expression in order to get one alert check per time series and finally set threshold expression to alert

Query:

import "strings"
from(bucket: "test")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "net_response")
  |> filter(fn: (r) => r["_field"] == "result_type")
  |> keep(columns: ["_time", "_value", "host", "port"])
  |> aggregateWindow(every: v.windowPeriod, fn: last, createEmpty: false)
  |> map(fn: (r) => ({ r with _value: strings.replaceAll(v: r._value, t: "connection_failed", u: "1") }))
  |> map(fn: (r) => ({ r with _value: strings.replaceAll(v: r._value, t: "success", u: "0") }))
  |> toFloat()  

Tested with:
Grafana 9.3.2
InfluxDB 2.6.1

Best regards,
ldrascic

[mbentley]

Thanks for that! I have something that’s functional based on your Flux query and I’ll have to spend a bit of time optimizing the data as the HAProxy input can provide a wide range of values for my status field and it doesn’t like it when there is an undefined string that isn’t mapped when converting to float. It’s my first time digging into Flux as I’ve been using InfluxQL due to the lack of wizard type interface so this is certainly an experiment for me.

[grant2]

Just to give a potentially helpful query example, in this case I am using a map() function and if/else statement to create a new column (AtOperatingTemp, which contains either true or false) based on the value of the field.

from(bucket: "sample-data")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "sample-measurement")
  |> filter(fn: (r) => r["Tank_ID"] == "D5")
  |> filter(fn: (r) => r["MeasType"] == "actual")
  |> filter(fn: (r) => r["_field"] == "temperature")
  |> map(fn: (r) => ({ r with AtOperatingTemp: if r._value >= 450 then 
                "true"
            else
              "false",
        }),
    )
  |> aggregateWindow(every: 5m, fn: last, createEmpty: false)

Also, since there were so few examples of Flux and Grafana Alerting, I wrote up this tutorial which may also help you transition to Flux.

[infin80y]

@ldrascic
Thanks alot! I was trying to do this for quite some time, but your example solved it.
I noticed that the email received from this alert only contains the mapped float value. How can we include the original string in the email message? i.e. in this case: “connection_failed” or “success”

[ldrascic]

Hi @infin80y,

@infin80y

How can we include the original string in the email message?

You can achieve this by using duplicate() function. All newly added lines have comments in next query.

 

import "strings"
from(bucket: "test")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "net_response")
  |> filter(fn: (r) => r["_field"] == "result_type")
  |> group(columns: ["host", "port"], mode:"by") // group data only by host and port (don't group by connection status i.e connection_failed or success)
  |> last()  // get last value
  |> keep(columns: ["_time", "_value", "host", "port"])
  |> duplicate(column: "_value", as: "original_value")  // duplicate _value column and save to original_value
  |> map(fn: (r) => ({ r with _value: strings.replaceAll(v: r._value, t: "connection_failed", u: "1") }))
  |> map(fn: (r) => ({ r with _value: strings.replaceAll(v: r._value, t: "success", u: "0") }))
  |> group(columns: ["host", "original_value", "port"], mode:"by") // group data because grafana alerts requires that only _time and _value columns can be ungrouped (all other columns must be gruoped)
  |> drop(columns: ["_start", "_stop"]) // drop columns that we don't need and are also ungrouped (which grafana alerts doesn't allow)
  |> toFloat()
  |> yield(name: "result")  

 

I used group() and last() functions in order to get only last value regardless of connection status. If I don’t add this then I would get one entry per host, port and connection status (i.e connection_failed or success). I don’t want data to be grouped by connection status because this is same as value (it isn’t proper tag like host or port).

duplicate() function is used in order to copy all values from _value (i.e connection_falied or success) to original_value column. That way we can have can have float values in _value (which we get from map functions) and string values in original_value columns.

Last group() and drop() functions are used as a workaround for current bug in Grafana Alerts “no float64 value column found in frame”. As far as I understood Grafana Alerts require that only one column can be ungrouped (for example _value) with _time column. All other columns must be grouped (e.g. host, port, original_value and all other columns that are used as tags). That way Grafana interpretes _value and _time as values and all other columns as tags. By “grouped” and “ungrouped” I mean on this:

 

Result in Grafana:

 

Best regards,
ldrascic

[infin80y]

@ldrascic
All I can say is Wow! It worked. I have managed to get the alarm to trigger based on the integer _value and the email includes the original_value as a string. Thank you so much for your time as I would have never worked this out on my own. Much appreciated.