Flux Query Quick Reference

1

Long story short, I am running Influxdb 2.0 on my nas, and have the Grafana addon successfully connected on my hassos (pi4). Then I was excited to redo my dashboards when I figured out the obvious downside to it all… no more “gui” to build my charts!!! The dropdown boxes I was used to are all gone now, and it seems I have to learn “flux query” syntax to make my charts. It’s been like slogging through a muddy cotton field so far… GOOD TIMES!

Anyhow, I’ve read all the “heck naw I’m staying with 1.x”… and of course “might as well learn as flux is the future”. For example:

Assuming I’m going forward with the latter and sticking with 2.x, and assuming nobody really going to be interested in writing a gui wrapper to bring back those drop down boxes for us “non-db types”… I need to seriously train myself on this flux syntax!

I already was able to make a basic temperature vs time graph for one of my sensors after reading the first few pages of the influxdb docs… but looking for some shortcuts through those docs to get what I need. I’m looking for some entry level tutorials that would allow my to rebuild my dashboard without missing important details along the way. Any links are appreciated.

I’m actually kinda looking forward to learning this… maybe I can finally get a firm grip on displaying integrated/differential data (like energy cost based on power sensors, etc). I think the gui method before was actually hiding what I needed to know to do this, and learning the underlying syntax should unlock a lot of potential for me.

T.I.A.,
Kev

2

Whether others feel like adding to this or not, I’m going to use this thread to at least document the “cleaner looking and/or more useful queries for HA” as I stumble upon them… feel free to add more queries/screenshots to this!

HA binary_sensors ===============================

from(bucket: "homeassistant")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["domain"] == "binary_sensor")
  |> filter(fn: (r) => r["_measurement"] == "units")
  |> filter(fn: (r) => r["_field"] == "value")
  |> map(fn: (r) => ({
        _value: r._value, 
        _time: r._time, 
        name: "${r["entity_id"]}"
    }))
  |> group(columns: ["name"])

binaries
binaries1600×475 113 KB

HA sensors ===============================
*replace °F with the unit of your sensors that you want displayed

from(bucket: "homeassistant")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["domain"] == "sensor")
  |> filter(fn: (r) => r["_measurement"] == "°F")
  |> filter(fn: (r) => r["_field"] == "value")
  |> map(fn: (r) => ({
        _value: r._value, 
        _time: r._time, 
        name: "${r["entity_id"]}"
    }))
  |> group(columns: ["name"])

sensors
sensors1600×481 155 KB

I’ve seen other versions of this query, but none that are as easy on the eyes as this one. Even better if it used friendly_name instead of entity_id, but I’m too much of an HA/flux newb to derive that at the moment. At least it doesn’t have weird stuff like object brackets or “value” prepended, LOL.

3

@truglodite, Thanks for the post, helped me out a lot.
I even found a way to get the friendly_name into the HA sensors query that you provided.

created an account just to share it with you.

from(bucket: "homeassistant")
  |> range(start: v.timeRangeStart, stop: v.timeRangeStop)
  |> filter(fn: (r) => r["domain"] == "sensor")
  |> filter(fn: (r) => r["_measurement"] == "%")
  |> filter(fn: (r) => r["_field"] == "value" or r["_field"] == "friendly_name_str")
  |> pivot(rowKey:["_time"], columnKey: ["_field"], valueColumn: "_value")
  |> map(fn: (r) => ({
        _value: r.value, 
        _time: r._time, 
        name: "${r["friendly_name_str"]}"
    }))
  |> group(columns: ["name"])
1 Like
4

@masoncrawford1994, awesome and thanks for sharing the query!

I just wanted to add a tip for those just getting in to flux queries. If you log directly in to fluxdb (port 8086 by default), you can use the infuxdb ui to play with flux queries. The workflow then is similar to how it was using influxql queries in the old HA grafana addon. At least this gives a gui to click around on and see what the resulting flux query looks like in text.

5

Watching this old thread. Has anyone figured out how to use the grouping function with the calendar year in flux?

For example how to group by month from a forever increasing sensor? That is pretty much the main reason why I switched to influx 2.x… Influx 1.x didn’t support calendar ranging. Now it is a pain to even get that data out. Not even on the influx forums/faq is it easy to find anything about this. Kind of ironic