Timescale db and materialized view

[keith6014]

Please direct all support questsions to slack or the forums. Thank you.

Using telegraf 1.27.2. I am trying to setup a materialized view to down sample network traffic (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.

[telegraf-tiger[bot]]

Hello! I recommend posting this question in our Community Slack or Community Forums, we have a lot of talented community members there who could help answer your question more quickly. You can also learn more about Telegraf by enrolling at InfluxDB University for free!

Heads up, this issue will be automatically closed after 7 days of inactivity. Thank you!

[keith6014]

Also opened, https://community.influxdata.com/t/telegraf-with-timescaledb-and-materialized-views/34815

[telegraf-tiger[bot]]

Hello! I am closing this issue due to inactivity. I hope you were able to resolve your problem, if not please try posting this question in our Community Slack or Community Forums or provide additional details in this issue and reqeust that it be re-opened. Thank you!