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;
Out of the box this is what users will see when launching pmacct with SQLite.