Use of a Continuous Query with non_negative_difference and non_negative_derivative functions

Hi I am looking at using Influxdb to store network interface traffic metrics.

I query the network interface via SNMP to retrieve the raw counter value (InOctets, OutOctets) - I am storing these values in a measurement with all needed tags etc.

I need to transform these counter values into additional derived time series of ‘bytes transferred’ and ‘bits per second’ values, I then plan on using standard continuous queries and retention policies to create additional series of min, max, average, percentile etc of the bytes and bps derived values.

I have created a continuous query that utilises the non_negative_difference and non_negative_derivative functions to populate the additional series in a RP called “rp_5m”: e.g.:

SELECT non_negative_difference(first(in_octets)) AS in_bytes,
non_negative_derivative(first(in_octets), 1s) AS in_bps,
non_negative_difference(first(out_octets)) AS out_bytes,
non_negative_derivative(first(out_octets), 1s) AS out_bps
INTO “database”.rp_5m.traffic FROM “database”.“default”.traffic WHERE time >= ‘2018-02-15T16:30:00Z’ AND time < ‘2018-02-15T16:35:00Z’ GROUP BY time(5m, -5m)

My primary data is being added to the “database”.“default”.traffic measurement every minute, so each 5m group should contain 5 samples, the CQ runs on a 5m lag.

This runs but does not capture any data.

I suspect this is because the aggregate function inside the call to non_negative_derivative/non_negative_difference is only returning 1 value, so there is no difference/derivative.

Is there a way to use a custom WHERE clause in the CQ, e.g. select 10mins of data and then group by 5mins to generate 2 data points? It seems at present the WHERE and GROUP BY are fundamentally linked.

Or should I just use an external mechanism rather then a CQ to populate the secondary series?