I have an error when i try to return a string value from a query to populate the alert email

Grafana
1

Good morning everyone.
In grafana 10 I set up an alert that, via a simple query, returns a value of type INT and inserts it into the text of the email. so far everything works.
Now I need to insert, in the text of the email, also a value of type VARCHAR which is provided to me by a second query.
The second query is very simple, of the type:

SELECT
operator_name
FROM
table

operator_name is a string in VARCHAR format and if I try to execute the query in the alert build screen I get this error:

Error
Failed to evaluate queries and expressions: input data must be a wide series but got type not (input refid)

If anyone knows why I get this error and how I can solve it I would be infinitely grateful

2

Hello,

why don’t you join the queries and select the operator_name? It will be then available as a label, which you can print it using this:

{{ $labels.operator_name }}

or

{{ $values.<query-name>.Labels.operator_name }}

The queries in the Grafana Alerts should return at least 1 numeric data.

If you choose to keep using the second string, add a column which has 1 or map the string values to numeric values.

SELECT
    operator_name, 1
FROM
    table

or

SELECT
    operator_name, CASE WHEN operator_name == ".." THEN 1 ELSE .. END
FROM
    table

You can then print it in the alert description using:

{{ $labels.operator_name }}

or

{{ $values.<query-name>.Labels.operator_name }}
3

thank you very much!
I tried the suggestions you mentioned and indeed now it no longer gives an error when I run the queries. I decided to keep the two queries split, one responds with a number and the other with a VARCHAR.
However, I can’t seem to get the VARCHAR value into the alerting email. I keep getting [no value] displayed.
I am attaching the code and screenshots so maybe you can see if I am doing something wrong:

in Grafana the name of the query: ordine

SELECT
'cliente: ’ || CAST(RAGSOC as varchar) || ’ codice: ’ || PARTE_CONT as “ordine_attualmente_produzione”, 1
FROM
(
SELECT
MAX(CASE WHEN nome_acquisizione = ‘ordine_continua_anno’ THEN ultimo_valore END) AS ESE_CONT,
MAX(CASE WHEN nome_acquisizione = ‘ordine_continua_numero’ THEN ultimo_valore END) AS NUM_CONT
FROM
dati_processo.configurazione_acquisizione
WHERE nome_acquisizione IN (‘ordine_continua_anno’, ‘ordine_continua_numero’)
) as valori
LEFT JOIN
(
SELECT
cast(ESE_CONT as integer) as ESE_CONT,
NUM_CONT,
RAGSOC,
PARTE_CONT
FROM
quasarx.u_prodcli) as catalogo
ON (valori.ESE_CONT=catalogo.ESE_CONT and valori.NUM_CONT=catalogo.NUM_CONT)

And this is the response I get:

{ordine_attualmente_produzione="cliente: abcdefghij codice: ABC123 "} 1

In the alerting email I now write:

{{ $labels.ordine_attualmente_produzione }}
and
{{ $labels.ordine}} // ordine is the name of my grafana query

i still get [no value] in both

LINK TO SCREENSHOT: screen-query hosted at ImgBB — ImgBB

4

Try:

{{ $values.ordine.Labels.ordine_attualmente_produzione }}

1 Like
5

ok i sent an email with your suggestion but i still receive [no value].

in my alert i have the “working as intended” query with the INT value returned and this query named ordine with the VARCHAR retuned {ordine_attualmente_produzione="cliente: abcdefghij codice: ABC123 "} 1 (it gives me the 1 from your previous suggestion as you can see)

in the alert i do not use grafana “EXPRESSION” panel with this query but i use grafana “EXPRESSION” panel with the INT query to filter the last value (reduce)

in the email i added your suggestion {{ $values.ordine.Labels.ordine_attualmente_produzione }}
and my INT value with {{$values.name_of_the_expression_i_use_to_reduce_query_value}}

any other suggestions you can think of are extremely appreciated! :smiley:

6

Try to print these to see their content

{{ $labels }}
{{ $values }}
{{ $values.ordine }}

1 Like
7

i get this:

alert_rule_namespace_uid=XB1AnFw7k, alert_rule_uid=E_kFV05Vz, alertname=Umidità Fuori Range, grafana_folder=Allarmi, nome_acquisizione=prodotto_continua //this is the label of the expression panel i have for my INT query
map[fermo_check:5180]
[no value]

LINK TO SCREENSHOT OF EXPRESSION expression hosted at ImgBB — ImgBB