Biggest User - Step Three

Year in Review

This is a "run once" script...
. . .

filename: YearInReview.sh

#!/bin/bash
#
#   YearInReview
#
#   Version 8
#   Date: 2021-05-15
#

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

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

    CREATE VIEW
        YearInReview (ip_addr, Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec)  
    AS
    SELECT 
        ip_addr, 
        MAX(CASE theMonth WHEN '01' THEN totalBytes ELSE NULL END) AS Jan,
        MAX(CASE theMonth WHEN '02' THEN totalBytes ELSE NULL END) AS Feb,
        MAX(CASE theMonth WHEN '03' THEN totalBytes ELSE NULL END) AS Mar,
        MAX(CASE theMonth WHEN '04' THEN totalBytes ELSE NULL END) AS Apr,
        MAX(CASE theMonth WHEN '05' THEN totalBytes ELSE NULL END) AS May,
        MAX(CASE theMonth WHEN '06' THEN totalBytes ELSE NULL END) AS Jun,
        MAX(CASE theMonth WHEN '07' THEN totalBytes ELSE NULL END) AS Jul,
        MAX(CASE theMonth WHEN '08' THEN totalBytes ELSE NULL END) AS Aug,
        MAX(CASE theMonth WHEN '09' THEN totalBytes ELSE NULL END) AS Sep,
        MAX(CASE theMonth WHEN '10' THEN totalBytes ELSE NULL END) AS Oct,
        MAX(CASE theMonth WHEN '11' THEN totalBytes ELSE NULL END) AS Nov,
        MAX(CASE theMonth WHEN '12' THEN totalBytes ELSE NULL END) AS Dec
    FROM
        addr_byMonth
    WHERE
        theDate > DATETIME('now','localtime','-1 year', 'start of month', '-1 second')
    GROUP BY
        ip_addr
    ORDER by
        CAST(substr(ip_addr, 12) AS NUMERIC)
    ;
SQL_EOF

To query the above script, first open the database:

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

then enter:

.mode columns
.headers ON

SELECT * FROM YearInReview;

and the results are similar to this:

query for year
query for year

sqlite3 -header -cmd ".mode columns" -cmd ".width 16 12 12 12 12 12 12 12 12 12 12 12 12" pmacct_sqlitev1a.db "SELECT * FROM YearInReview WHERE ip_addr='192.168.60.105';"

query for single IP address
query for single IP address

Next Step - TBD

Edit Page ‐ Yes, you can edit!

Older Revisions • May 17 at 7:28 pm • Jon