Grafana Temperature data with InfluxDB v2 and Flux

[wrightsonm]

I have just upgraded to the latest version of InfluxDB meaning I am now needing to update my queries in Grafana from InfluxQL to Flux.

I have now managed to correctly plot my various temperature nodes and alias each line to the entity friendly name string.

I was hoping someone may be able to advise whether there is an easier way to achieve this. It took quite a bit of trial and error to convince it to render all of the sensors individually…

This is my influx query:

sensorName = from(bucket: "home_assistant/two_hours")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "°C")
  |> filter(fn: (r) => r["domain"] == "sensor")  
  |> filter(fn: (r) => r._field == "friendly_name_str")
|> keep(columns: ["_time","entity_id", "_field","_value"])

sensorValue = from(bucket: "home_assistant/two_hours")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "°C")
  |> filter(fn: (r) => r["domain"] == "sensor")
  |> filter(fn: (r) => r._field == "value")
  |> keep(columns: ["_time","entity_id", "_field","_value"])

Raw = join(tables: {name: sensorName, temperature: sensorValue}, on: ["_time","entity_id"])  
  |> rename(columns: {_value_temperature:"_value"})
  |> map(fn: (r) => ({ r with entity_id: r._value_name }))
  |> drop(columns: ["_value_name"])
  |> yield()

Regards
Mark

[wrightsonm]

The answer is to use pivot.

Here is an improvement to the above query:

from(bucket: "home_assistant/two_hours")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["_measurement"] == "°C")
  |> filter(fn: (r) => r["domain"] == "sensor")  
  |> filter(fn: (r) => r["_field"] == "value" or r["_field"] == "friendly_name_str")
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
  |> group(columns: ["friendly_name_str"])
  |> keep(columns: ["_time","friendly_name_str","value"])
  |> yield()

[njbair (Nick Bair)]

I’ve tried adapting this code to my use case (tracking on/off state of multiple entities over time), and it’s working great. But the row labels on my state timeline all have the word “value” prepended to the friendly name (see screenshot):

Any way to change this? Here’s my query code, for reference:

from(bucket: "homeassistant/autogen")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r._measurement == "state" and r._field == "value")
  |> filter(fn: (r) => r._field == "value" or r._field == "friendly_name_str")
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
  |> group(columns: ["friendly_name_str"])
  |> keep(columns: ["_time","friendly_name_str","value"])
  |> yield()

[wrightsonm]

Ah there is a simple solution to this. You can use a grafana transform the rewrite the names using a regex.

Something like

value\s(.*)
$1

[Thyraz]

Thanks for this hint.
Added an example here:

Flux in Grafana?

A little hint for getting the friendly names shown in the legend of Grafana without setting name aliases all the time: You can use a rename-by-regex transform to catch the name using a capture group and use the result ($1) as the replacement text. [Bildschirm­foto 2023-01-20 um 10.10.10] [Bildschirm­foto 2023-01-20 um 10.10.42]

[DIYMax (Max)]

Hi @Thyraz @wrightsonm @njbair

…pinging you because you seem to get Flux working in Grafana and hoping that you may have an idea, what needs to be done in my case. See this thread: How to use Flux in Grafana 2023