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

#   addr_byMonth
#   Version 3
#   Date: 2021-05-15

[ ! -f "$1"  ] && echo -e "\nError: Database missing\nUsage: bash <database_name>\n" && exit 1

sqlite3 "$1" <<-"SQL_EOF"
    DROP VIEW IF EXISTS addr_byMonth;
    .timer ON

        addr_byMonth (ip_addr, mac_addr, totalBytes, totalGB, theDate, theMonth)
        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
    GROUP by
        theDate1, ip_addr, mac_addr
    ORDER by
        theDate1, CAST(substr(ip_addr, 12) AS NUMERIC), mac_addr

To query the above script, first open the database:

[root@ipfire ~]# sqlite3 /var/spool/pmacct/pmacct_sqlitev1a.db

then enter:

.mode column
.headers ON

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

and the results are similar to this:

Example query results (10 rows only)
Example query results (10 rows only)

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

sqlite3 -header -cmd ".mode columns" -cmd ".width 15 18 14" pmacct_sqlitev1a.db "SELECT * FROM addr_byMonth WHERE ip_addr = '';"

Example query results
Example query results

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='';"

Example query results
Example query results

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

Edit Page ‐ Yes, you can edit!

Older Revisions • May 20, 2021 at 12:37 am • Jon