Note - 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.
The first script (last page) was to reduce the data for the the following query and for future queries. The second script gathers data by IP address / MAC address and by month.
This is a "run once" script and it creates a View in the SQL database and only needs to be launched once.
#!/bin/bash # # addr_byMonth # # Version 3 # Date: 2021-05-15 # [ ! -f "$1" ] && echo -e "\nError: Database missing\nUsage: bash addr_byMonth.sh <database_name>\n" && exit 1 sqlite3 "$1" <<-"SQL_EOF" DROP VIEW IF EXISTS addr_byMonth; .timer ON CREATE VIEW addr_byMonth (ip_addr, mac_addr, totalBytes, totalGB, theDate, theMonth) AS SELECT ip_addr, mac_addr, SUM(totalBytes) AS totalBytes, printf ("%.2f", CAST( SUM(totalBytes) AS FLOAT ) /1000 /1000 /1000 ) AS totalGB, strftime('%Y-%m',theDate) as theDate1, strftime('%m',theDate) as theMonth FROM acct_byDay GROUP by theDate1, ip_addr, mac_addr ORDER by theDate1, CAST(substr(ip_addr, 12) AS NUMERIC), mac_addr ; SQL_EOF
To query the above script, first open the database:
[root@ipfire ~]# sqlite3 /var/spool/pmacct/pmacct_sqlitev1a.db sqlite>
.mode column .headers ON SELECT * FROM "addr_byMonth" order BY theDate DESC LIMIT 10;
and the results are similar to this:
The above script aggregates data by IP address and by month. Its main purpose is to provide data to the Year in Review SQL View (next step). It can also query a specific IP address to determine how many bytes per month were used.
To send a SQL query from a command line and search for IP address
sqlite3 -header -cmd ".mode columns" -cmd ".width 15 18 14" pmacct_sqlitev1a.db "SELECT * FROM addr_byMonth WHERE ip_addr = '192.168.60.105';"
Or just request the date and the bytes consumed for a single IP address:
sqlite3 -header -cmd ".mode columns" pmacct_sqlitev1a.db "SELECT theDate, totalGB FROM addr_byMonth WHERE ip_addr='192.168.60.105';"
Side Note: There is an error in my query and the totalGB column can not be properly sorted. Currently the totalGB column sorts by ASCII instead of numeric.
Next step - Biggest User - Step Three