Influxdb Sensor Help

Configuration
1

Hello,

Can someone please me setup an Influxdb sensor?

I am trying to convert this influxql into a sensor. I

SELECT sum("value") AS "sum_value" FROM "iotawattdb"."autogen"."HouseUsage" WHERE time > now() - 1h GROUP BY time(1h) FILL(null)

Here is what I have in sensor.yaml

# InfluxDB sensor
  - platform: influxdb
    #api_version: 1
    #organization:
    #token: 
    #queries_flux:
    #   - range_start: "-1h"
    #     name: "HouseUsageFlux"
    #     query:  >
    #       filter(fn: (r) => r._measurement == "HouseUsage" and (r._field == "value"))
    #       |> window(every: 1h)
    #       |> sum()
    #       |> group(columns: ["_time", "_start", "_stop", "_value"], mode: "except")

    queries:
    - name: HouseUsage
      database: iotawattdb
      where: 'time > now() - 1h GROUP BY time(1h)'
      measurement: '"iotawattdb"."autogen"."HouseUsage"'
      field: value
      group_function: sum

    - name: HouseUsage2
      unit_of_measurement: 'kWh'
      #value_template: '{{ value | round(1) }}'
      group_function: sum
      where: 'time > now() - 1h GROUP BY time(1h)'
      measurement: "HouseUsage"
      field: value
      database: iotawattdb

I tried flux but couldn’t get hassio to understand the flux query.

I have an iotawatt home energy monitoring system. It is dumping kWh usage into Influxdb. I need to pull the last hours total usage. However, it does need to be limited to the current hour. In other words - if it is 5:30, it should only pull from 5:00 to 5:30. Not 4:30 to 5:30. I believe the group by section is correctly pulling this.

Any help is greatly appreciated!

2

Any help would be greatly appreciated!!

3

I am also fighting this implementation. It seems this is not well implemented at all.

4

Any success? Quite complicated, why not just passing the SQL request in the .yaml… unnecessary complicated, really.

5

@e-raser

No, I was never able to get it to work. It needs better documentation. Frankly I gave up. I think it would be better to just use Grafana instead. That next on my list.

6

Thanks for confirmation. Same experience for me, such a pain.

7

Trying same thing with target value for automation:

SELECT last("value") AS "last_value" FROM "homeassistant"."autogen"."settings" WHERE "name"='obyv_target_temp'

Jan

8

So funny when you are searching for solutions and find your own (quite) old posts amongst others also struggling with the very same difficulties.

In case someone ever manages to successfully create a sensor for the influxdb platform according to InfluxDB - Home Assistant, please enlighten all the other remaining InfluxDB users. THANK YOU! :smile:

I started to do so today once again, and once again gave up after a considerable amount of wasted time.

I only need this as sensor in HA (just noting in case someone will post a solution, I come back here and can remind myself to finally implement this):

  • number of measurements
    SELECT numMeasurements FROM "_internal"."monitor"."database" WHERE "database"='homeassistant' ORDER BY time DESC LIMIT 1
  • number of series
    SELECT numSeries FROM "_internal"."monitor"."database" WHERE "database"='homeassistant' ORDER BY time DESC LIMIT 1
9

I’m a newbie and have been struggling with this today as well. In my case I have an IotaWatt power sensor that I would like to be able to use some power measurements in some automations. I followed the suggestion of mfenniak and had iotawatt send power data to influxdb, where I could then pull it into home assistant. I finally got a sensor created from data pulled from the database and displayed in a card on the main panel. Each query will create a sensor, so I created one based on your use case for “number of measurements”. After saving the YAML file I restarted HA and went to Developer Tools → States, and saw an entity named sensor.numberofmeasurements to verify that it was created. There should be a card called Sensors on the dashboard with NumberOfMeasurements shown.

YAML is:

influxdb:
  exclude:
    entity_globs: "*"

sensor:
  - platform: influxdb
    api_version: 1
    host: a0d7b954-influxdb
    port: 8086
    ssl: false
    verify_ssl: false
    username: homeassistant
    password: homeassistant
    queries:
      - name: maxDryerPower
        unit_of_measurement: W
        value_template: "{{ value | round(1) }}"
        group_function: max
        measurement: '"iotawatt"."autogen"."iotawatt"'
        where: '"input" = ''Dryer'' and "time" > now() - 1h'
        field: '"Watts"'
        database: iotawatt

      - name: NumberOfMeasurements
        value_template: "{{ value | round(0) }}"
        measurement: '"_internal"."monitor"."database"'
        where: '"database" = ''home_assistant'' and "time" > now() - 12h'
        field: '"numMeasurements"'
        database: home_assistant

Like I said, I’m new at this, so if anyone has suggestions I’m eager to learn.