InfluxDB Queries on HomeAssistant Data

I recently added in influx db + grafana + mariadb to one of my HA instances. I was surprised at how easy it was to get HA happy. Essentially nothing more than running the docker instances for each of these components and adding minimal yaml to HA.

When i went to query the data there were a few surprises that I thought I’d note. First, as noted in https://community.home-assistant.io/t/missing-sensors-in-influxdb/144948, HA puts sensors that have a unit name inside a measurement of that name. Thus if i show measurements that match my model_y, for example, all i get are binary (e.g. unittless) sensors:

> show measurements with measurement =~ /sensoricareabout/
name: measurements
name
----
binary_sensor.sensoridontcareabout1
climate.sensoridontcareabout2
device_tracker.sensoridontcareabout3
...

However if i query series, similarly, i see the measurement i care about:

> show series where "entity_id" =~ /sensoricareabout/
key
---
°F,domain=sensor,entity_id=sensoricareabout_foo_1
°F,domain=sensor,entity_id=sensoricareabout_foo_2

Thus, oddly, to query the data i want i form a query something like this:

 select value from "°F" where entity_id = 'sensoricareabout_foo_1'

A couple things. So, it is odd to me to have to query from what I essentially view as a table °F… but that is probably more HA’s fault than influxdb. Second, i think the quoting is a little weird. Note above that the measurement is double quoted, whereas the tag is single quoted. This is important. You can sometimes (maybe?) leave the double quotes off the former but the latter must strictly be single quotes…

The moral of the story is that if you want to find the data you are looking for you need to look under ‘%’ or the actual unit of measurement. For example in grafana i would:

  • click on explore
  • select ‘%’
  • Add a where clause to filter by entity_id
  • Possibly find my series there…
    • But if not remove the where, and switch from % to the unit of measurement, and repeat by adding the where clause

This all works well but is a little unintuitive and might make one think HA has forgotten to put the data into influx.