Getting hourly traffic data by device

[puckpuck]

I have the cstats and rstats files getting written hourly, but they only contain daily/monthly data per device. I know about the /var/spool/rstats-speed.js file which contains per router interface data taken at 30 second intervals for the past 24 hours.

What I really want is traffic data by device, by hour (or shorter). I don't care if the file is binary, json, or whatever. I'm a software engineer and can write code to parse the files, in fact I did just that for the cstats and rstats file. From what I can tell playing with the GUI traffic analyzer, it seems the router has this data somewhere. Does anyone know how I can get to it?

I'm using an RT-AC68U


PS: If anyone is interested, I wrote a proper python script, and more robust Java app that parses the cstats file properly. The only python script I found online didn't work since it looks for SPEED_SUPPORT data, and doesn't account for the storage of 2 additional values written for each record.

PPS: I seen in assuswrt-merlin source that the SPEED_SUPPORT are specifically omitted from the cstats file. Is this solely for drive space consumption reasons. Any hope of getting this to become and GUI option instead for people who don't mind the larger files?
https://github.com/RMerl/asuswrt-me...8cc6c0/release/src/router/cstats/cstats.h#L26

 

[ColinTaylor]

PS: If anyone is interested, I wrote a proper python script, and more robust Java app that parses the cstats file properly.

That could be very useful as it's something that's asked for quite frequently. Perhaps you could put it on github or dropbox or something. Thanks.

I don't know anything about Traffic Analyzer (traffic.db) as I don't use it, but...

https://www.snbforums.com/threads/traffic-analyzer-statistic-problem.26112/#post-194954

3) Traffic Analyzer: This monitors the traffic per IP and per protocol. This uses the Trend Micro DPI engine. Traffic is saved in /jffs. That functionality is closed source.

 

[RMerlin]

traffic.db is an sqlite database. If you can figure out its format, maybe you can leverage its content.

 

[puckpuck]

Here's a github I created with my cstats script. I'll add a few more router scripts as well in the future. https://github.com/puckpuck/asuswrt-merlin-extensions

About Traffic Analyzer and the traffic.db file. This is exactly what I was looking for in the first place, not sure why it didn't dawn on me to just try and open this thing with sqlite. I even remember looking at it, and thinking that when I was initially getting asuswrt-merlin configure and setup. For those not aware, sqlite is an open source database system, and there are plenty of tools for it out there, including this easy to use graphical browser (supports windows + mac) http://sqlitebrowser.org/ If you want to make something automated, you can use sqlite though Entware on the router (I think you need libsqlite3 and sqlite3-cli), or if you have a linux/mac host handy that can work as well, and just copy the file to that linux host and use the sqlite3 command line package there.

I did a quick check, and the database is super simple. 1 table called traffic with 6 columns: mac, app_name, cat_name, timestamp, tx, rx. The fact that this uses mac instead of ip makes me feel much better since ip (what cstats uses) is unreliable for traffic monitoring given it could change at any time. timestamp is seconds since epoch, or seconds since Jan 1, 1970 00:00:00 GMT. Looking at the data it takes a snapshot every hour, on the hour. The tx/rx columns show bytes transferred for that past hour, not counters that keep going up.

Kicking myself for not finding this earlier, would of saved 2 evenings of trying to figure out why I couldn't parse that cstats file properly, efforts that I finished but will likely never use, and now instead I'm just going to query this database and get everything I need in a much cleaner way.

If anyone cares, here's a few handy queries you can use to get data from this file:
Get data per mac interface for each hour ->
select mac, datetime(timestamp, 'unixepoch'), sum(tx) as tx, sum(rx) as rx
from traffic
group by mac, timestamp
order by mac, timestamp

Get data per mac, per app, for each hour ->
select mac, app_name, datetime(timestamp, 'unixepoch'), sum(tx) as tx, sum(rx) as rx
from traffic
group by mac, app_name, timestamp
order by mac, timestamp

 

[Jack Yaz]

Here's a github I created with my cstats script. I'll add a few more router scripts as well in the future. https://github.com/puckpuck/asuswrt-merlin-extensions

About Traffic Analyzer and the traffic.db file. This is exactly what I was looking for in the first place, not sure why it didn't dawn on me to just try and open this thing with sqlite. I even remember looking at it, and thinking that when I was initially getting asuswrt-merlin configure and setup. For those not aware, sqlite is an open source database system, and there are plenty of tools for it out there, including this easy to use graphical browser (supports windows + mac) http://sqlitebrowser.org/ If you want to make something automated, you can use sqlite though Entware on the router (I think you need libsqlite3 and sqlite3-cli), or if you have a linux/mac host handy that can work as well, and just copy the file to that linux host and use the sqlite3 command line package there.

I did a quick check, and the database is super simple. 1 table called traffic with 6 columns: mac, app_name, cat_name, timestamp, tx, rx. The fact that this uses mac instead of ip makes me feel much better since ip (what cstats uses) is unreliable for traffic monitoring given it could change at any time. timestamp is seconds since epoch, or seconds since Jan 1, 1970 00:00:00 GMT. Looking at the data it takes a snapshot every hour, on the hour. The tx/rx columns show bytes transferred for that past hour, not counters that keep going up.

Kicking myself for not finding this earlier, would of saved 2 evenings of trying to figure out why I couldn't parse that cstats file properly, efforts that I finished but will likely never use, and now instead I'm just going to query this database and get everything I need in a much cleaner way.

If anyone cares, here's a few handy queries you can use to get data from this file:
Get data per mac interface for each hour ->
select mac, datetime(timestamp, 'unixepoch'), sum(tx) as tx, sum(rx) as rx
from traffic
group by mac, timestamp
order by mac, timestamp

Get data per mac, per app, for each hour ->
select mac, app_name, datetime(timestamp, 'unixepoch'), sum(tx) as tx, sum(rx) as rx
from traffic
group by mac, app_name, timestamp
order by mac, timestamp

I wonder if many people would want something like this to run hourly (just after the snapshot), to email the stats to them. Shoudn't be too hard, script called by cru running the select query. I may look into this over the weekend

 

[puckpuck]

I wonder if many people would want something like this to run hourly (just after the snapshot), to email the stats to them. Shoudn't be too hard, script called by cru running the select query. I may look into this over the weekend

My goal here is to actually take the data and send it to an actual monitoring platform on the hour. This is more of a pet project for me, since I work for a company that provides a monitoring platform service.

I'm actually having issues getting a cron job to run. Tried using cru as well as manually creating the /var/spool/cron/crontabs/admin file. Not sure why it's not working . crond process is running

 

[ColinTaylor]

I'm actually having issues getting a cron job to run. Tried using cru as well as manually creating the /var/spool/cron/crontabs/admin file. Not sure why it's not working . crond process is running

Use cru to add/remove/list jobs, don't directly edit the files. It should work. Remember to make the scripts executable.

Use "cru l" to verify the entries.

admin@RT-AC68U:/# cru

Cron Utility
add:    cru a <unique id> <"min hour day month week command">
delete: cru d <unique id>
list:   cru l

admin@RT-AC68U:/# cru a just-a-comment "00 * * * * /jffs/scripts/myscript.sh"

admin@RT-AC68U:/# cru l
00 * * * * /jffs/scripts/myscript.sh #just-a-comment#

 

[puckpuck]

yeah I got it working... silly me didn't have #!/bin/sh in my script

 

[puckpuck]

So I got this all done and working now. Here's a screenshot of the data loaded into my company's monitoring software. I have a script that will copy the traffic.db file to the USB drive connected to my router at 03 past the hour, then at 05 past the hour I have a cron job running on a Raspeberry Pi that picks up that file (mounted NFS) and runs it through a Java app which also gets the router's client list from a custom user page I made, mashes it all together, figures out data that has yet to be sent to the monitoring platform, and sends all the new stuff. All in all it works great, and I can now tell if my son is watching videos on his iPad at night without going through the clunky interface on my router.

https://drive.google.com/file/d/0BzP1B64eMMd6SnhLNG9NQk9QZE0/view?usp=sharing