So I’ve got some data I’m pulling from my router that’s logged to Influxdb…
According to Influx 1.x documentation you should just be able to add the query to the where or the measurement fields, but my sensor just returns a 0 value…
An example of a query directly in the InfluxDB Shell:
SELECT derivative(first(bytes_recv), 1s) FROM "net" WHERE "interface" = 'pppoe0' AND time > now() - 1h GROUP BY time(1s)
Returns for me how many bytes p/sec are passing through the interface…
name: net
time derivative
---- ----------
1618056720000000000 1073399.6
1618056730000000000 1814794.6
1618056740000000000 1132648
etc ...
Now trying to adapt this to a InfluxDB query has me totally stumped…
Here’s what I’m trying currently, but it’s just returning a 0 value still:
- platform: influxdb
host: localhost
username: pfsense
password: xxx
database: pfsense
queries:
- name: pfSense_wan_throughput_in
measurement: '(SELECT derivative(first(bytes_recv), 1s) FROM "net" WHERE "interface" = ''pppoe0'' AND time > now() - 1h GROUP BY time(1s))'
where: '"time" > now() - 1h'
field: "derivative"
value_template: "{{ (value | int / 131072) | round(2) }}"
unit_of_measurement: MB/s
group_function: last
My understanding is that the above YAML should produce a query that looks something like…
select derivative from (SELECT derivative(first(bytes_recv), 1s) FROM "net" WHERE "interface" = 'pppoe0' AND time > now() - 1h GROUP BY time(1s)) where "time" > now() - 1h limit 1
Which returns the expected results …
name: net
time derivative
---- ----------
1618058740000000000 657227
Yet my HQ sensor is still just reporting a sweet “0”…
Anyone seen this before / have any suggestions?
Thanks,