I’ve struggeled some time now with Influx and Home Assistant. There a lots of tutorials explaining how to get Home Assistant data into an Influx database. But vice versa, getting data out of an Influx database into Home Assistant is just explained in the HA integrations page. But unfortunately I could not make it work directly struggeling with the syntax. So I will just share my experiences, hopefully it might help someone.
Why getting InfluxDB data into HA? Pretty easy. I have a lot of data - like e.g. FreeNAS or a power meter - that I already installed before I started with HA. So I have already set up a good way (e.g. FreeNAS with the Graphite option) to push data into a database. Some data - like how much space is left on my storage or how hot are disks running - I can pretty easy watch in Grafana with also historic values. But as HA is for me the “single pane of glass” for all my stuff at home, I wanted to see just the current temperature, current power consumption (coming from my power meter) and not integrate another way to get data in HA to just push it to InfluxDB, what already works.
So first: configuration.yaml needs to be edited.
influxdb:
host: xxx.xxx.xxx.xxx
database: database-name
exclude:
entity_globs: “*”
If your database doesn’t run on the same host, you definitely need to enter the InfluxDB host as target for data that would come from HA. You also need to add a database, when you don’t have a home-assistant database in your InfluxDB. After that, just exclude all data for writing to the Influx.
Then you need to create a sensor for the data you want to query from Influx:
sensor:
platform: influxdb
host: xxx.xxx.xxx.xxx
queries:
- name: PowerConsumption
unit_of_measurement: W
value_template: ‘{{ value | round(1) }}’
where: ‘“meter” = ‘‘Overall Power’’’
measurement: ‘energy’
field: ‘“Power Sum”’
group_function: last
database: db_meter_test
Here you can see what needs to be enter. The query in Influx looks like this:
“SELECT last(“Power Sum”) AS “Power Sum” FROM “energy” WHERE (“meter” = ‘Overall Power’) AND time >= now() - 1h GROUP BY time(2s)”
In fact there are some parts you can’t really translate into HA. Here you can see, what really is needed and can be used for HA:
SELECT last(“Power Sum”) FROM “energy” WHERE (“meter” = ‘Overall Power’) AND time >= now()
I skipped the “AS” clause as well as “1h GROUP BY time(2s)”
The other case, where I struggled, was a query with missing “where” clause:
SELECT last(“value”) FROM “servers.freenas.disktemp-ada0.temperature” WHERE time >= now() - 6h
This also will skip some query parts to look like this:
SELECT last(“value”) FROM “servers.freenas.disktemp-ada0.temperature” WHERE time < now()
As you can see, the “where” clause has another query regarding “>=” what changed to “<”. The reason for that might caused by a different time on InfluxDB and HA, even though a NTP server is running. But I could not find the right reason for this issue. So the sensor looks like this:
- name: ada0_temperature
value_template: '{{ value }}'
where: 'time < now()'
measurement: '"servers.freenas.disktemp-ada0.temperature"'
field: value
group_function: last
database: graphite
As you can see, there needs to be something in the “where” variabel, otherwise it will not work at all.
I hope, this can help some of you to reduce the time to implement.
