Migrate grafana dashboards from InfluxDB datasource to TimescaleDB

Hi, everybody! Have you tried this migration? Is there some suggestion, how to replace InfluxQL function like “non_negative_derivative”, for example query from Telegraf metric “net”

SELECT non_negative_derivative(mean(“bytes_recv”), 1s) * 8 AS “RX”, non_negative_derivative(mean(“bytes_sent”), 1s) * 8 AS “TX” FROM “default”.“net” WHERE (“host” =~ /^ad2rm1$/ AND “interface” =~ /^bond0$/ AND “dc” =~ /^tower$/) AND time >= now() - 24h and time <= now() GROUP BY time(2m) fill(linear)

how to get the same results from TimescaleDB?

Hi Miroslav, I never did any math like this but I’m interested in know if you made any progress on it.

I was thinking that maybe the toolkit functional pipelines would give you some building blocks to build it. Have you checked it?

Hi, it’s slightly more complicated, but in standard (TimescaleDB) SQL it looks something like this:

WITH subquery AS (
        time_bucket('1m', time) AS time_interval,
        AVG(bytes_recv) *8 AS RX,
        AVG(bytes_sent) *8 AS TX
    FROM "net_default"
    WHERE host = 'ad2rm1'
    AND interface = 'bond0'
    AND time BETWEEN '2024-01-14T08:29:00.702Z' AND '2024-01-15T08:29:00.702Z' 
    GROUP BY time_interval, interface
    ORDER BY time_interval
    GREATEST(0,(RX - lag(RX) OVER (PARTITION BY interface ORDER BY time_interval))) / EXTRACT(EPOCH FROM ('1m'::interval)) AS RX,
    GREATEST(0,(TX - lag(TX) OVER (PARTITION BY interface ORDER BY time_interval))) / EXTRACT(EPOCH FROM ('1m'::interval)) AS TX
FROM subquery
ORDER BY time_interval;