Per, Timescale db and materialized view · Issue #15590 · influxdata/telegraf · GitHub
Using telegraf 1.27.2. I am trying to setup a materialized view to down sample network tra
ffic (bytes_sent/bytes_received). I am using grafana for visualization. For some reason, I am getting very large values when I query the materialized view. My view looks like this
CREATE MATERIALIZED VIEW public.net_bytes_5min_recv WITH (timescaledb.continuous) AS
SELECT time_bucket('5 mins',t."time") as time,tt.host,tt.interface,AVG(bytes_recv) as avg_bytes_recv
FROM telegraf.net t,telegraf_net_tag tt WHERE (t.tag_id=tt.tag_id) group by 1,2,3 WITH NO DATA;
This creates the view.
My grafana query looks like this
WITH delta as (SELECT time,host,interface,avg_bytes_recv-lag(avg_bytes_recv,1) OVER (partition by host,interface order by time)) as bytes_recv FROM
net_bytes_5_min_recv WHERE host in ($server) AND interface in ($interface) AND $__timeFilter("time) group by time,host,avg_bytes_recv,interface)
SELECT time,host,interface,bytes_recv from delta group by time,host,interface,bytes_recv order by 1
For some reason, I am seeing very large values for my interface. I suspect its doing a SUM() somewhere…
I was wondering if there is a good practice to do materialized views with telegraf schema.
