Has anybody set up fetching download/upload speed from influxdb (unifipoller)?

I’ve set up unifipoller here in a LXC container, it works fine after finding a bug and working around it.
It was @AlohaDan that suggested it, and also told me that I can make a sensor in HA pull the data from influxdb into HA.
I’ve tried stealing the query from the unifipoller dashboards, and reuse that, but I can’t make it work.
So I was hoping somebody else had done it already?

Best regards

I’m spying on this query from unifpoller dashboards:

SELECT last("rx_bytes-r") AS "Rx Rate", last("tx_bytes-r") AS "Tx Rate", last("num_new_alarms") AS "Alarms" FROM "subsystems" WHERE ("site_name" =~ /^$site$/ AND "subsystem" = 'wan') AND $timeFilter GROUP BY "status", "gw_name", "wan_ip"

Which gives this output:

So far I’ve gotten this made that doesn’t work:

- platform: influxdb
    host: localhost
    username: !secret ha-influx-user
    password: !secret ha-influx-pw
      - name: Unifi Download Speed
        where: '"site_name" = ''default'' AND "subsystem" = ''wan'''
        field: rx_bytes-r
        measurement: 'Bs'
        group_function: last
        database: unifi
        unit_of_measurement: 'Bs'

The error looks like this:

2020-05-11 17:52:48 ERROR (MainThread) [homeassistant.components.sensor] influxdb: Error on device update!
Traceback (most recent call last):
  File "/usr/src/homeassistant/homeassistant/helpers/entity_platform.py", line 322, in _async_add_entity
    await entity.async_device_update(warning=False)
  File "/usr/src/homeassistant/homeassistant/helpers/entity.py", line 472, in async_device_update
    await self.hass.async_add_executor_job(self.update)
  File "/usr/local/lib/python3.7/concurrent/futures/thread.py", line 57, in run
    result = self.fn(*self.args, **self.kwargs)
  File "/usr/src/homeassistant/homeassistant/components/influxdb/sensor.py", line 164, in update
  File "/usr/src/homeassistant/homeassistant/util/__init__.py", line 240, in wrapper
    result = method(*args, **kwargs)
  File "/usr/src/homeassistant/homeassistant/components/influxdb/sensor.py", line 203, in update
    points = list(self.influx.query(self.query).get_points())
  File "/usr/local/lib/python3.7/site-packages/influxdb/client.py", line 461, in query
    in data.get('results', [])
  File "/usr/local/lib/python3.7/site-packages/influxdb/client.py", line 460, in <listcomp>
    for result
  File "/usr/local/lib/python3.7/site-packages/influxdb/resultset.py", line 25, in __init__
    raise InfluxDBClientError(self.error)
influxdb.exceptions.InfluxDBClientError: expected field argument in last()

So it says I should put an argument in last (), but didn’t I do that with the field ?

I played around a bit and this seems to work:

      - name: Unifi_Download_Speed
        where: '"subsystem" = ''wan'''
        field: '"rx_bytes-r"'
        measurement: '"subsystems"'
        group_function: last
        database: unifi
        unit_of_measurement: 'Bs'

The measurement needs to be the table in the database, not Bs. Also, the site name for me is “Default (default)”. If you have multiple sites you could add something more specific, but this seems to work for me.

Currently, I’m seeing 0 as the measurement and haven’t played around with it enough to know if it really works, but hopefully this at least puts you in the right direction.

1 Like

Hey that works, and no need to give the site apparently, if there is only 1.
Final step was to convert it to Mbit/s

        unit_of_measurement: 'Mb/s'
        value_template: '{{ (value | int / 131072) | round(2) }}'

Weehee, thankyou very much for your invaluable help @AlohaDan

I got my WAN IP out as well with this:

      - name: WAN IP
        where: true
        field: '"wan_ip"'
        measurement: '"subsystems"'
        group_function: last
        database: unifi