Biggest User - Step Two

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.

Step Two - View IP addresses by Month

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.

filename: addr_byMonth

#!/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>

then enter:

.mode column
.headers ON

SELECT * FROM "addr_byMonth" order BY theDate DESC LIMIT 10;

and the results are similar to this:

Why is this needed?

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 192.168.60.105:

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