Work in progress . . . Work in progress . . . Work in progress . . . Work in progress . . . Work in progress . . .
Please keep in mind I am a beginner SQLite user (and pmacct user). So my SELECT queries may not be well written and are not elegant. Fell free to offer suggestions or make changes.
The "project" will help monitor data usage (IP-based or MAC-based data accounting) down to the client level. Net-Traffic or
vnstat will monitor traffic for the entire network but it will not pinpoint which client is using lots of data. Connections or
conntrack will take a snapshot and only show day-by-day sums. pmacct can help admins keep tabs on users that use too much data.
Why do I use it? My ISP implemented data caps and I occasionally over-run my limit. When that limit is exceeded I pay $10 for every additional 50GB of data use ($100 max). Ouch!
There are a few steps to get Biggest User project setup within the SQL database. There is one additional SQL table and two SQL views. The SQL views provide quick information to users and for future web-pages.
Step One - Add a second SQL Table to reduce DB size (filename:
Step Two - Add a SQL View to gather data by month (filename:
Step Three - Add a SQL View to transpose the data (filename:
Step N+1 - Add another SQL view to display Monthly Totals (filename:
The IPFire SQLite3 installation can be used for pmacct data storage. SQLite3 does not need to be re-configured.
/etc/pmacct/pmacct.conf and configure pmacct similar to this:
! ! "plugin3" plugin configuration ! plugins: sqlite3[plugin3] sql_db[plugin3]: /var/spool/pmacct/pmacct_sqlitev1a.db sql_table_version[plugin3]: 1 sql_history[plugin3]: 60m sql_refresh_time[plugin3]: 900 sql_history_roundoff[plugin3]: m aggregate[plugin3]: src_host, src_mac, dst_host, dst_mac aggregate_filter[plugin3]: ip and not (dst and src net 192.168.0.0/16)
Note - Whenever the
pmacct.conf file is modified, run the command:
|sql_history||60m||aggregate data after 60 minutes|
|sql_refresh_time||900||save in database every 900 seconds (15 min)|
|aggregate||src_host, src_mac, dst_host, dst_mac||collect these (ignore port and protocol)|
|aggregate_filter||ip||collect IPv4 only (ignore IPv6)|
|not (dst and src net 192.168.0.0/16)||collect LAN-to-WAN (ignore LAN-to-LAN traffic)|
Note - Learn about the above keys, values and their descriptions at:
Now create the database
pmacct_sqlitev1a.db using this command:
sqlite3 /var/spool/pmacct/pmacct_sqlitev1a.db < /usr/share/pmacct/sql/pmacct-create-table_v1.sqlite3
The above command is only run once. It can be re-run to start over.
/etc/init.d/pmacct restart command. pmacct should be up & running and data will now be added to the SQL database. It may take 15 minutes to add data the first time.
To access the database:
and then you will see:
[root@ipfire ~]# sqlite3 /var/spool/pmacct/pmacct_sqlitev1a.db SQLite version 3.34.1 2021-01-20 14:10:07 Enter ".help" for usage hints. sqlite>
To view the last 10 rows as pretty columns:
.mode columns .headers ON .width 18 18 15 15 8 8 8 8 8 20 20 select * from acct order by rowid DESC limit 10;
Note - the src_port, the dst_port, and the ip_proto columns are all zero. That data is ignored in the above configuration.
Next step - Biggest User - Step One