Biggest User - Step One

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).

Step One - Reduce

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.)

filename: acct_byDay_table.sh

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

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

then enter:

.mode columns
.headers ON

select * from acct_byDay order by rowid DESC limit 10;

and the results are similar to this:

Why is this needed?

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