Showing Traffic Stats on the last 30 Days on Grafana

Gamienator 0

Hey folks,
I'm brainstorming and can't get any idea. Maybe you can help me out

I'm monitoring my pfsense with CheckMK and telegraf. Now I'm setting up a Grafana Board and asked myself: How can I show my trafficstats of the last 30 days? Does anybody do this? It's not possible to get it from influx sadly:

> SELECT * FROM "net" WHERE interface='igb0';
name: net
time                bytes_recv  bytes_sent  drop_in drop_out err_in err_out host             interface packets_recv packets_sent
----                ----------  ----------  ------- -------- ------ ------- ----             --------- ------------ ------------
1644479080000000000 38898829321 41959100527 0       0        0      0       pfSense.i.3fu.de igb0      72286847     37520397
1644479090000000000 38899008142 41959134792 0       0        0      0       pfSense.i.3fu.de igb0      72287955     37520644
1644479100000000000 38899208374 41959173347 0       0        0      0       pfSense.i.3fu.de igb0      72289096     37520940
1644479110000000000 38899314673 41959233503 0       0        0      0       pfSense.i.3fu.de igb0      72290192     37521242
1644479120000000000 38899405688 41959277760 0       0        0      0       pfSense.i.3fu.de igb0      72291089     37521435
1644479130000000000 38900026325 41959413891 0       0        0      0       pfSense.i.3fu.de igb0      72292619     37521901
1644479140000000000 38900383952 41959448241 0       0        0      0       pfSense.i.3fu.de igb0      72293802     37522197
1644479150000000000 38907453519 41959524955 0       0        0      0       pfSense.i.3fu.de igb0      72300266     37522965
1644479160000000000 38913524436 41959584356 0       0        0      0       pfSense.i.3fu.de igb0      72305844     37523576

As you can see, the stats are only rising. Does anybody got a hint for me how I could achieve it? :)

Cheers,
Gamie

stephenw10

You mean like 30 days on a rolling basis?

I have no idea but just to clarify.

Gamienator 0

@stephenw10

Yep, rolling bases. But having stats for month January for example would be great as well

bmeeks

Maybe I am not understanding exactly what you want, but it seems you have the basis of the data needed to calculate stats -- at least by looking at the data you provided.

You have a UNIX timestamp and bytes sent and received (also packets, if you want that). You simply need to "do the math" across any interval of timestamps by subtracting the total bytes received value from the most recent timestamp from the total bytes received value from the earlier timestamp. This will give you an aggregrate throughput in bytes per that timestamp interval.

So for example, if you calculate the delta received bytes between two stamps that are one second apart, that will give you the throughput in bytes/second on a rough averaged basis. The smaller the timestamp intervals you calculate across, the better the more accurate the number will be.

I don't use Grafana nor telegraf, so I have no idea if they have this feature built in. But if you can run SQL queries against the database as you provided an example of, you can certainly calculate throughput stats.

Gamienator 0

Thanks for all your help,
I wrote my own solution now: https://github.com/Hornochs/pfsense_trafficstats_into_influxdb

I parse in a python script the data of vnstat and push it into a database. I still have to figure out, why vnstats thinks on my pppoe interfac I have a transfer of 4 GB when I'm reconnecting.