Aggregate bytes_recv with lag for host with multiple interfaces

TimescaleDB and PostgreSQL
,
1

Using timescaledb with telegraf. I have a host with multiple interfaces. The query I run is,

select time,interface,(bytes_recv-lag(bytes_recv,1) OVER (partition by interface order by time)) as bytes_recv from net where host in ('hosta','hostb') AND time between "2023-01-29" AND "2023-02-01" group by time,bytes_recv,interface

This works but I am trying to get
time,host,total_bytes_recv (delta) from all interfaces

2

Hi @Rita_Morgan ,

You’ll need to create a custom table expression (CTE) for your query then you can sum it as sub query:

WITH delta AS (
  select time,interface,(bytes_recv-lag(bytes_recv,1) OVER (partition by interface order by time)) as bytes_recv from net where host in ('hosta','hostb') AND time between "2023-01-29" AND "2023-02-01" group by time,bytes_recv,interface
)
SELECT time, sum(bytes_recv) as total from delta group by 1 order by 1;

Not sure if it will work exactly as I shared but the syntax is something around it.

1 Like
3

Thankyou for this.
For some reason when I chart this on Grafana I see very large values. I see data in the petabytes per second. I am not sure why thats happening.

4

Hi Rita, can you isolate a POC of your data with the query and maybe we can help you.

Just try to create a table with the minimal data and share with your query here, so I can try to run on my side.

5

Thankyou. This was very helpful