Import InfluxDB data as sensor in Home Assistant

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.

database: database-name
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:

platform: influxdb
- 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.


Thank you for this example. Helped me a lot on the way. Here is my working code. I had to change the quotes to make it work.

In file sensor.yaml

- platform: influxdb
  host: !secret influxIP
  username: homeassistant
  password: !secret influxpw
    - name: AirConditionPowerConsumption
      unit_of_measurement: W
      value_template: "{{ value | round(1) }}"
      where: '"device" = ''IotaWatt'''
      measurement: iotawatt.autogen.kurs12
      field: "value"
      group_function: last
      database: iotawatt

The query I had in chronograf/Influxdb

SELECT last("value") AS "value" FROM "iotawatt"."autogen"."kurs12" WHERE time > :dashboardTime: AND "device"='IotaWatt'

I cannot make this work - and my query is very simple:

SELECT “Run Time” FROM “runTime” WHERE time >= now() - 7d and time <= now()

I have setup influxdb near the top of the config.yaml file like this:

#Influx connection
verify_ssl: false
entity_globs: “*”

and created a sensor like this:

  • platform: influxdb
    • name: SepticPumpRunTime
      field: Run Time
      where: ‘time > now() - 1d’
      #group_function: last
      database: Pump_Monitor
      measurement: ‘“runTime”’

I realize I tweaked the where function…but I have tried a ton of different combos based on a couple threads I have found in here but with no results. My influx db is on another pi in my house and I would like to populate a chart in lovelace from existing data. I am willing to change approaches if required.

When I look at the logs of my lnfux container I can see it always returns 400 which I assume is the http code for bad request.

Anyone have any ideas?