Convert string field to boolean based on value

Grafana InfluxDB
,
1

Newbie with both InfluxDB and Grafana here. I’ve got a Home Assistant setup which feeds data from my Ecobee into InfluxDB. I am trying to create a grafana graph that overlays the indoor temperature with the state of my furnace, so that I can show runtimes of my furnace overlaid on the temp). I have a field called hvac_action_str which can have a few different values, one of which is ‘heating’. I want to do something like consider heating to be a value of 1 and anything else to be a 0 so I can graph it as a simple boolen on or off type of thing.

I thought this must be a common problem but I’ve been searching and haven’t hit on the answer yet. I saw some suggestions to edit the sql and do a SELECT IF(hvac_action_str = ‘heating’, 1, 0) but either grafana or influxdb doesn’t like that (says that the = operator is expected in the WHERE clause.)

There must be a straightforward way to do this, no? Or am I misunderstanding something fundamental? Thanks!

2

welcome to the :grafana: forum, @flyerguymn

I think there is a way to get this visualized the way you want. But, it also sounds like the ideal solution might be some sort of refactoring on the db / query side.

I think you might want to try regex-based value mapping. Here is an example dashboard. I got this to work, but if you look at how greedy my regex is, it only works because the first value mapping gets evaluated first, and that seems sketchy:

https://play.grafana.org/d/079uaYUnz/regex-mapping?orgId=1

But as others have said, there should be a more performant way to do this…