Biggest User project

Work in progress . . . Work in progress . . . Work in progress . . . Work in progress . . . Work in progress . . .

Disclaimer

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: acct_byDay_table.sh).
Step Two - Add a SQL View to gather data by month (filename: addr_byMonth.sh).
Step Three - Add a SQL View to transpose the data (filename: YearInReview.sh)

Step N+1 - Add another SQL view to display Monthly Totals (filename: MonthlyTotals.sh)

Configuration

The IPFire SQLite3 installation can be used for pmacct data storage. SQLite3 does not need to be re-configured.

Open the /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:

/etc/init.d/pmacct restart

pmacct.conf file:

Note - Learn about the above keys, values and their descriptions at:
https://github.com/pmacct/pmacct/blob/master/CONFIG-KEYS

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.

Run the /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.

Usage - SQLite3 based simple example

To access the database:

sqlite3 /var/spool/pmacct/pmacct_sqlitev1a.db

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