Get difference since 30 days ago in InfluxQL/InfluxDB

Question

I have a single stat in my grafana dashboard showing the current usage of a disk. To get that info I use the following query:

SELECT last("used") FROM "disk" WHERE "host" = 'server.mycompany.com' 
AND "path" = '/dev/sda1' AND $timeFilter

I want to add another stat showing the increase/decrease in usage over the last 30 days. I assume for this I want to get the last measurement and the measurement from 30 days ago and subtract them.

How can I do this in InfluxQL?

Answer 1

It wont be perfect, but something to the effect of

SELECT last("used") - first("used") FROM "disk" WHERE ... AND time > now() - 30d

should be sufficient.

Answer 2

For future people that might stumble upon this answer.

The the last("used") - first("used") approach when used with grouping by time will not result in correct results, because the difference will be computed between the values inside a single time interval (10s for example), and not for the whole specified period.

The proper solution is described in one of the last comments at the previously mentioned issue at https://github.com/influxdata/influxdb/issues/7076, specifically adapted for OP's case:

SELECT cumulative_sum(difference) 
  FROM (SELECT difference(last("used")) 
    FROM "disk") WHERE "host" = 'server.mycompany.com'
                 AND "path" = '/dev/sda1' AND time >= now() - 30d GROUP BY time(5m))

What this will do is choose the last values of "used" in 5 minute intervals (buckets), and then compute the differences between those "last" values.

This will result in a time series of numbers representing increases / decreases of HDD space usage.

Those values are then summed up into a running total via cumulative_sum, which returns a series of values like (1GB, 1+5GB, 1+5-3GB, etc) for each time interval.