Influx query to transform true or false to 1 or 0 (To use on Grafana Graphic Alert)

Hi,

I have a field in Influxdb called FIXConnected and the values could be True or False.
The query below brings me these values but i need to create an alert and i need to make these true or false as 1 and 0.

SELECT distinct(“FIXConnected”) FROM “koala_application” WHERE (“args1” = ‘fixserver-xxx-yyyyy’) AND $timeFilter GROUP BY time(1m) fill(null)

Is there any other function instead of distinct or a composition of other functions that could be used in this case ?

I am new to influxdb and appreciate your help.

Influxdb 1.7.11
Grafana 7.3

I would suggest moving to 2.x and using Flux for this. This type casting is part of the Flux language.
https://docs.influxdata.com/influxdb/v2.0/reference/flux/flux-vs-influxql/#cast-booleans-to-integers

1 Like

Thank you @tim.hall. I will update soon to 2.x version. Would you mind to let me know how the query would be using Flux ? Thank you once more !!! Have a nice weekend.

From the Grafana side you should be able to map text values to numeric equivalent using Value Mapping.
About the query itself, what are you trying to achieve?

Hi.
I am using the Graph visualization in order to set alerts. If the series comes as 1 nothing to do, but if it brings 0, I will open a ticket in my service desk tool.
As far as I know … the stats vizualization allows me to do the value mapping but the graph I am using did not allow me to use that.
Tks for the attention.

What about filtering the field value itself?
It’s not as performing as filtering tags (as fields are not indexed) but for small amout of data it works fine.

SELECT COUNT("FIXConnected")
WHERE ("args1" = 'fixserver-xxx-yyyyy' AND "FIXConnected" = 'False') AND $timeFilter 
GROUP BY time(1m)
FILL(null)

Hi !!

I am testing now the way to alert and will try to use the answear ‘true’ and when it has no value, it will be false and alert. Testing yet …
image

    SELECT COUNT("FIXConnected") 
FROM "xxxx_application_yyy_server_status" 
WHERE ("args1" = 'aaa-server-xx-zzzzzzz') 
AND ("FIXConnected" = true) 
AND time >=now() - 5m GROUP BY time(2m)

I will let you know the result.

Tks !!

True should be a sting value, so you might need to put it between single quotes.

Hi,

It is bringing values with no quote, doble or single quote do not bring any value.
Still testing and seems to be working.

Thank you very much… Tested for a few days and seems to be working as a charm…

Tks Tks