Multiple Series from Single Query using InfluxDB and Grafana

[ajw (Alan)]

I’ve got Grafana and InfluxDB running with Home Assistant. I’m familiar with MySQL/PostgreSQL but new to InfluxDB and Grafana. Is there a way to use a single query to retrieve data for multiple entities and to plot those entities as separate series in Grafana. I’m wanting to use the Grafana State Timeline widget with something like the following query:

SELECT 
     "entity_id",
     "state" 
FROM 
     "homeassistant"."autogen"."state" 
WHERE 
     "domain" = 'light' 
     AND $timeFilter

But this simply mashes up all the entities as seen here:

I’ve searched the community forums and the broader web, but not finding a solution and feeling like I’m missing a pretty simple/basic design pattern when using InfluxDB and Grafana.

Thanks,
Alan

[ajw (Alan)]

No solution yet, but thinking I may need to use Flux query language and the pivot function.

Here are a few related threads:

1. Getting Flux working with Grafana/InfluxDB add-ons: Flux in Grafana?
2. InfluxDB 2.0: Thoughts on Influxdb (2.0)
3. Bug resulting in Flux not being enabled in recent versions/releases: Error "Flux not enabled" even though it is enabled by environment variable · Issue #117 · hassio-addons/addon-influxdb · GitHub

The bug above is preventing me from trying Flux query language and pivot to plot multiple series from a single query in grafana.

[ajw (Alan)]

With the most recent versions of HA / Grafana / InfluxDB I was able to get this working using FLUX queries. See other threads for info on enabling FLUX queries. For me, when configuring a datasource in Grafana for InfluxDB using Flux, I had to put two double quotes in the Organization field and username:password into the Token field in the Grafana datasource configuration panel. With all that, the following flux query:

from(bucket: "homeassistant/autogen")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r._measurement == "state" and r._field == "state")
  |> filter(fn: (r) => r.domain == "light")

Gives the following visualization result using the grafana State Timeline visualization:

Now I just need to sort out how to get entity friendly name as the series label.

[crzykidd (Matthew Andersen)]

Did you finish this project? Looking for a sample on how you got friendly names for each of those lines

Thanks,

[ajw (Alan)]

I was able to get to reasonably friendly Entity ID labels with a single query:

import "regexp"
domain = "light"

from(bucket: "homeassistant/autogen")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r.domain == domain and r._measurement == "state")
  |> filter(fn: (r) => r._field == "state")
  |> rename(columns: {_value: "state"})
  |> drop(columns: ["_start","_stop","_field","_measurement","domain"])
  |> rename(columns: {state: ""})
  |> map(fn: (r) => ({r with entity_id: regexp.replaceAllString(r: /_access_control_window_door_is_open/, v: r.entity_id, t: "")}))

I was able to get to Friendly Name using a join, but the performance isn’t great with this query:

domain = "device_tracker"

state = from(bucket: "homeassistant/autogen")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r.domain == domain and r._measurement == "state")
  |> filter(fn: (r) => r._field == "state")
  |> rename(columns: {_value: "state"})

name = from(bucket: "homeassistant/autogen")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r.domain == domain and r._measurement == "state")
  |> filter(fn: (r) => r._field == "friendly_name_str")
  |> rename(columns: {_value: "name"})

join(tables: {state, name}, on: ["_time", "_stop", "_start", "entity_id"])
  |> map(fn: (r) => ({ 
      _value: r.state, 
      _time: r._time,
      _field: 
        if exists r.name and r.name != "" then r.name 
        else r.entity_id
    }))
  |> group(columns: ["_field"])

I’m not 100% convinced that the aggregation is showing what I want it to show. It kinda looks right but I’ve not looked into it in detail.

[ajw (Alan)]

Here is another example with Ping sensors for network status:

import "regexp"
domain = "binary_sensor"


from(bucket: "homeassistant/autogen")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r.domain == domain and r._measurement == "state")
  |> filter(fn: (r) => r._field == "state")
  |> filter(fn: (r) => 
      (r.entity_id =~ /ping_/) and
      (r.entity_id !~ /ping_unknown/)
    )
  |> aggregateWindow(
      every: v.windowPeriod, 
      fn: (column, tables=<-) => tables
          |> reduce(
              identity: {state: 1},
              fn: (r, accumulator) => ({
                  state: if r._value == "on" then
                      accumulator.state
                  else
                      0,
              }),
            ),
    )
  |> drop(columns: ["_start","_stop","_field","_measurement","domain"])
  |> rename(columns: {state: ""})
  |> map(fn: (r) => ({r with entity_id: regexp.replaceAllString(r: /ping_/, v: r.entity_id, t: "")}))

[ajw (Alan)]

And another example with quantitative data:

import "regexp"
domain = "sensor"

from(bucket: "homeassistant/autogen")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r.domain == domain and r._measurement == "ms")
  |> filter(fn: (r) => r._field == "value")
  |> filter(fn: (r) => 
      (r.entity_id =~ /max_ping/) and
      (r.entity_id !~ /ping_unknown/)
    )
  |> aggregateWindow(every: v.windowPeriod, fn: median)
  |> rename(columns: {_value: "value"})
  |> drop(columns: ["_start","_stop","_field","_measurement","domain"])
  |> rename(columns: {value: ""})
  |> map(fn: (r) => ({r with entity_id: regexp.replaceAllString(r: /max_ping_/, v: r.entity_id, t: "")}))