Mixing LAST and DERIVATIVE

InfluxDB 2 Dashboards
,
1

Hi,

I’m using telegraf on PFSense to collect stats, I want to know what the “current” bandwidth use is. I can do this over time by using the derivative function, for example, something like this;

SELECT derivative(mean(“bytes_recv”), 10s) FROM “pfsense”.“autogen”.“net” WHERE time > :dashboardTime: AND time < :upperDashboardTime: AND “interface”=‘re0’ GROUP BY time(:interval:) FILL(null)

What I’d like is the last number that returns - so the “current” situation.

Any advice?

J

2

Hello @doowle,
You’ll want to use a nested subquery.

Something like:

SELECT last("myderivative") FROM (SELECT derivative(mean(“bytes_recv”), 10s) AS "myderivative" FROM “pfsense”.“autogen”.“net” ) WHERE time > :dashboardTime: AND time < :upperDashboardTime: AND “interface”=‘re0’ GROUP BY time(:interval:) FILL(null)
1 Like
3

Awesome, I’m off to try this now!

4

The principle worked, thank you :slight_smile:

SELECT LAST("myderivative") FROM (SELECT derivative(mean("bytes_recv"), 10s) AS "myderivative" FROM "net" WHERE ("interface" = 'pppoe0') AND $timeFilter GROUP BY time($__interval) fill(null))