Work in progress . . . Work in progress . . . Work in progress . . . Work in progress . . . Work in progress . . .
I wrote a few queries to get the pmacct information out of SQLite3. Eventually these queries will be used to populate a web-page as part of the IPFire WebGUI (Sorry, not anytime soon. This is beyond my skill set).
The first query is to reduce the data for the other queries. Data is collect by pmacct and placed into SQL. Depending on how pmacct is configured the data may be posted once per minute, or once per five minute, or once per hour. This is a user choice.
Note - For me the pmacct is configured to post data once per hour. Eventually I'll get brave and move to posting pmacct data once per day.
The smallest unit of data currently needed is "one day". The first query will reduce the data gathered to one day and to remove unneeded data. The data kept is LAN to WAN (local to internet) and from WAN to LAN (internet to local). Everything else is set aside (e.g., LAN-to-LAN, multicast, broadcast, etc.)
#!/bin/bash # # acct_byDay_table # # Version 9 # Date: 2021-05-17 # # This script runs once per day via fcron # [ ! -f "$1" ] && echo -e "\nError: Database missing\nUsage: bash acct_byDay_table.sh <database_name>\n" && exit 1 sqlite3 "$1" <<-"SQL_EOF" DROP TABLE IF EXISTS acct_byDay; CREATE TABLE IF NOT EXISTS acct_byDay ( ip_addr CHAR(45) NOT NULL DEFAULT '0.0.0.0', mac_addr CHAR(17) NOT NULL DEFAULT '0:0:0:0:0:0', totalBytes BIGINT NOT NULL, theDate DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (ip_addr, mac_addr, theDate) ); INSERT OR IGNORE INTO acct_byDay (ip_addr, mac_addr, totalBytes, theDate) SELECT ip_addr, mac_addr, (SUM(bytes_src) + SUM(bytes_dst)) AS totalBytes, theDate FROM ( SELECT -- grab destination in LAN only ip_dst AS ip_addr, mac_dst AS mac_addr, '0' AS bytes_src, SUM(bytes) AS bytes_dst, strftime('%Y-%m-%d',stamp_inserted) as theDate FROM main.acct WHERE ip_addr LIKE "192.168.%" -- or "10.%.%.%" GROUP by theDate, ip_addr, mac_addr UNION SELECT -- grab source in LAN only ip_src AS ip_addr, mac_src AS mac_addr, SUM(bytes) AS bytes_src, '0' AS bytes_dst, strftime('%Y-%m-%d',stamp_inserted) AS theDate FROM main.acct WHERE ip_addr LIKE "192.168.%" -- or "10.%.%.%" GROUP by theDate, ip_addr, mac_addr ) GROUP by theDate, ip_addr, mac_addr ORDER by theDate, CAST(substr(ip_addr, 12) AS NUMERIC), mac_addr ; SQL_EOF
To run the above script once per day, add this line to
# for pmacct: reduce hourly acct table to daily acct table (acct_byDay) 7 0 * * * bash /root/pmacct/acct_byDay_table.sh /var/spool/pmacct/pmacct_sqlitev1a.db > /dev/null 2>&1
To query the above SQL Table, first open the database:
[root@ipfire ~]# sqlite3 /var/spool/pmacct/pmacct_sqlitev1a.db sqlite>
.mode columns .headers ON select * from acct_byDay order by rowid DESC limit 10;
and the results are similar to this:
The above script reduces the amount of data significantly (about 100 to 1) making future queries MUCH faster. Notice all external IP address are ignored as well as port numbers and protocols. The script also aggregates the data used by each device / client. And it displays a summary of each device's daily data usage (Bytes per Day).
Next step - Biggest User - Step Two