pmacct using SQLite3

pmacct can be used with SQLite. SQLite3 is part of the core IPFire setup - no additional SQLite installation is needed.

Below is a simple example for SQLite usage with pmacct.

Config - SQLite3 based example

The IPFire sqlite3 installation can be used for pmacct data storage.

Open the /etc/pmacct/pmacct.conf and configure pmacct similar to this:

!
! "plugin2" plugin configuration
!
plugins: sqlite3[plugin2]

sql_db[plugin2]: /var/spool/pmacct/pmacct_sqlitev1.db
sql_table_version[plugin2]: 1
sql_history[plugin2]: 60m
sql_refresh_time[plugin2]: 900
sql_history_roundoff[plugin2]: m

aggregate[plugin2]: src_host, src_port, src_mac, dst_host, dst_port, dst_mac, proto
aggregate_filter[plugin2]: ip

Note - Whenever the pmacct.conf file is modified, run the command:

/etc/init.d/pmacct restart

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

Now create the database pmacct_sqlitev1.db using this command:

sqlite3 /var/spool/pmacct/pmacct_sqlitev1.db < /usr/share/pmacct/sql/pmacct-create-table_v1.sqlite3

The above command is only needed once. It can be re-run to start over.

Now 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 up to take 15 minutes to add data the first time.

Usage - SQLite3 based simple example

To access the database:

sqlite3 /var/spool/pmacct/pmacct_sqlitev1.db

and then you will see:

[root@ipfire ~]# sqlite3 /var/spool/pmacct/pmacct_sqlitev1.db

SQLite version 3.34.1 2021-01-20 14:10:07
Enter ".help" for usage hints.
sqlite> 

To view 10 rows as pretty columns:

.mode columns
.headers ON
.width 18 18 15 15 8 8 8 8 8 20 20

select * from acct LIMIT 10;

Example query results (10 rows only)
Example query results (10 rows only)

Out of the box this is what users will see when launching pmacct with SQLite.

Edit Page ‐ Yes, you can edit!

Older Revisions • May 16 at 4:57 pm • Jon