Non_positive_derivative or SELECT CASE? (trying to query for 'days of disk space remaining')

, ,
1

I am trying to construct an influxdb query for disk data (from telegraf) that monitors how many days of storage are remaining at the current data storage rates.

I’ve had success with this for my data zfs pool:

SELECT -MEAN("free")/DERIVATIVE(MEAN("free"), 1d)
FROM "disk"
WHERE "device" = 'data' AND $timeFilter
GROUP BY time(12h), "host"

This gives me the math I want, since:

days_remaining = -(slope_in_bytes_per_day / bytes_remaining)

and I’m calculating the slope (in bytes/day) over a 12 hr window to avoid bursty data usage causing devops heart attacks.

However… when the measured usage slope is positive (ie: space was recovered on the disk in the 12 hr period) the result is a negative intercept value for “days_remaining”, which is confusing. The real interpretation there is that, when the intercept is negative, “at current rates you will never run out of space”.

The negative value is bad in two ways:

  1. It messes up the charting, since “days remaining” takes a scary/bogus nose dive (to below zero)
  2. It defeats simple alerts like “Alert me when storage space has < 60 days of space remaining”.

non_negative_derivative gets around this type of problem… but it is in the wrong direction here! I need non_positive_derivative (which does not exist), or I need to invert the source data. I also tried derivative(-mean("free")), but that results in this error:

ERR: error parsing query: aggregate function required inside the call to derivative

I also dug around the docs to see if there was some SELECT CASE way of saying “if the value is < 0, let’s just call it 10000 days”, but influxdb does not seem to support this (yet?).

I also hacked around with some nested queries to try and flip the sign on “free” so I could use non_negative_derivative. This seems to work somewhat in influxdb (with a real filter in place of $timeFlter), but does not work at all in grafana:

SELECT -MEAN("free")/NON_NEGATIVE_DERIVATIVE(MEAN("free"), 1d)
FROM (
    SELECT -free
    FROM "disk"
    WHERE "device" = 'data' AND $timeFilter
)
WHERE $timeFilter
GROUP BY time(12h), "host"

After all that background, my actual questions are:

  1. Is there some way to get influxdb to reject those negative values (like my nested query example does) in a way that grafana will support?
  2. Is there some way to get influxdb to replace negative and/or null values with another value (similar to SQL stuff like CASE logic, COALESCE, etc)?

The preferred solution would be to have something like the CASE WHEN or COALESCE options so I could avoid null values in the monitoring.