pmacct using SQLite3

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

pmacct can also 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]: 5m
sql_refresh_time[plugin2]: 300
sql_history_roundoff[plugin2]: m
sql_dont_try_update: true

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

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_sqlitev1.db using this table & schema
pmacct-create-table_v1.sqlite3.
Enter:

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

Since the pmacct.conf file was modified, run the command:

/etc/init.d/pmacct restart

pmacct should be up & running and data will now be added to the database.

Usage - SQLite3 based example

entr:

sqlite3 /var/spool/pmacct/pmacct_sqlitev1.db

see:

[root@ipfirec152 ~]# sqlite3 /var/spool/pmacct/pmacct_sqlitev1.db
SQLite version 3.26.0 2018-12-01 12:34:55
Enter ".help" for usage hints.
sqlite> 

see first 10 lines:

sqlite> select * from acct LIMIT 10;

see last 10 lines:

select rowid,* from acct order by rowid DESC limit 10;

add info...

Edit Page ‐ Yes, you can edit!

Older Revisions • March 8 at 8:20 pm • Jon