{"id":433,"date":"2020-12-10T12:01:32","date_gmt":"2020-12-10T12:01:32","guid":{"rendered":"https:\/\/carson.fenimorefamily.com\/?p=433"},"modified":"2020-12-10T12:01:34","modified_gmt":"2020-12-10T12:01:34","slug":"influxdb-queries-on-homeassistant-data","status":"publish","type":"post","link":"https:\/\/carson.fenimorefamily.com\/?p=433","title":{"rendered":"InfluxDB Queries on HomeAssistant Data"},"content":{"rendered":"\n

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.<\/p>\n\n\n\n

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<\/a>, HA puts sensors that have a unit name inside a measurement<\/em> of that name. Thus if i show measurements that match my model_y, for example, all i get are binary (e.g. unittless) sensors:<\/p>\n\n\n

\n> show measurements with measurement =~ \/sensoricareabout\/\nname: measurements\nname\n----\nbinary_sensor.sensoridontcareabout1\nclimate.sensoridontcareabout2\ndevice_tracker.sensoridontcareabout3\n...\n<\/pre>\n\n\n
However if i query series, similarly, i see the measurement i care about:<\/p>\n\n\n
\n> show series where "entity_id" =~ \/sensoricareabout\/\nkey\n---\n\u00b0F,domain=sensor,entity_id=sensoricareabout_foo_1\n\u00b0F,domain=sensor,entity_id=sensoricareabout_foo_2\n<\/pre>\n\n\n
Thus, oddly, to query the data i want i form a query something like this:<\/p>\n\n\n
\n select value from "\u00b0F" where entity_id = 'sensoricareabout_foo_1'\n<\/pre>\n\n\n
A couple things.  So, it is odd to me to have to query from what I essentially view as a table \u00b0F… 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<\/span><\/em> be single quotes…<\/span><\/p>\n\n\n\n
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:<\/p>\n\n\n\n