Kevin Tedesco

In part 1 of the DSL Bandwidth Monitoring adventure we explored the Data Collection techniques used to capture pertinent information related to the DSL connection in my home. This post will build on part 1 by exploring the programmatic Data Analysis used to trend statistics and quasi dynamically publish reports. Let's get started.

Before getting into the code let's take a look at a second MySQL table that was created to store monthly statistics and reduce the need for redundant calculations throughout the year. Four simple columns to track the month, total Bandwidth used, and speed averages. The table name is called Statistics and you will see it utilized throughout the code.

The script begins with some variable declarations of values that will be used frequently later. Notice the logging of the Internal Field Separator. This is captured as the script modifies the default to process the MySQL output at a later point. Don't worry, at the very end we put it back.

#!/bin/bash
MYDB="DBNAME"
MYUSER="DBUSER"
MYPASS='DBUSERPASSWORD'
MYHOST="SERVERHOST"
OLDIFS=$IFS
DSLStatisticsfile="/var/www/subdirectory/filename.php"

The next section defines a function to accomplish number rounding. The function accepts two arguments. The first ($1) is the number to be rounded and the second ($2) is the number of decimal places to round to. A calculation is performed on these arguments utilizing the arbitrary precision calculator language (bc) and then echoed as a result.

round()
{
echo $(printf %.$2f $(echo "scale=$2;(((10^$2)*$1)+0.5)/(10^$2)" | bc))
};

Looking to utilize dates as our key for extracting various data we next define some date variables that will be used as basis for calculations. Fun fact: Math with the date command will fail with a leading zero on 08 and 09 as it believes it is an octal constant instead of base 10. To work around this feature, sed is used to remove the leading 0.

### Identify today
today=$(echo $(date +%F))
echo "Today: $today"
### Identify first day of month
firstofmonth=$(echo $(date -d "-0 month -$(($(date +%d| sed -e 's/^0//')-1)) days" +%F))
    ### Sed command is used to strip leading zero out of day returned by date as it causes octal math instead of base10 which fails on 08 and 09. ###
echo "FirstofThisMonth: $firstofmonth"
### Identify first day of year
firstofyear=$(echo "$(date +%Y)-01-01")
### Identify first day of last month
firstoflastmonth=$(date -d "$firstofmonth - 1 month" +%F)
echo "1st of LM: $firstoflastmonth"
### Identify last day of last month
lastoflastmonth=$(date -d "$firstofmonth - 1 day" +%F)
echo "Last of LM: $lastoflastmonth"
### Identify long name of Last Month
lastMonth=$(date -d "$firstoflastmonth" +%B)

With the date variables calculated we begin to perform analysis on the MySQL data and write the output to variables for reporting later. The IFS is changed here to allow for proper reading of MySQL output into the variables.

### Extract year to date average download speed
IFS=$' \t\n' read -r avgdlspeedytd <<< $(mysql -D$MYDB -u$MYUSER -p$MYPASS -se "SELECT truncate(Avg(DownloadSpeed),2) FROM Statistics WHERE ID >= '$firstofyear' and DownloadSpeed != 0;")
echo $avgdlspeedytd

### Extract year to date average upload speed
IFS=$' \t\n' read -r avgulspeedytd <<< $(mysql -D$MYDB -u$MYUSER -p$MYPASS -se "SELECT truncate(Avg(UploadSpeed),2) FROM Statistics WHERE ID >= '$firstofyear' and UploadSpeed != 0;")
echo $avgulspeedytd

### Extract month to date average download speed
IFS=$' \t\n' read -r avgdlspeedmtd <<< $(mysql -D$MYDB -u$MYUSER -p$MYPASS -se "SELECT truncate(Avg(DownloadSpeed),2) FROM Statistics WHERE ID >= '$firstofmonth' and DownloadSpeed != 0;")
echo $avgdlspeedmtd

### Extract month to date average upload speed
IFS=$' \t\n' read -r avgulspeedmtd <<< $(mysql -D$MYDB -u$MYUSER -p$MYPASS -se "SELECT truncate(Avg(UploadSpeed),2) FROM Statistics WHERE ID >= '$firstofmonth' and UploadSpeed != 0;")
echo $avgulspeedmtd

Some of the data does not require repeat analysis as it does not change or only requires to be calculated once. To accomplish this within one script we can either repetitively calculate then overwrite or use an if statement to reduce the number of calculations performed. I chose the latter. If the date conditions are met then we read the MySQL data into an array and calculate.

### test for first day of month, if true calculate and archive statistics for previous month. Else query data needed to complete php file.
if [ "$today" == "$firstofmonth" ]; then
    echo "today is the first day of the month"
    ### Check for existence of entry in table as script runs multiple times in a day
    if [ "test$(mysql -D$MYDB -u$MYUSER -p$MYPASS -se "select Month from DataUsage where Month = '$firstoflastmonth'")" = "test" ]; then
        ### entry does not exist add it
        ### extract all rxbytes and txbytes from previous month
        read -ra RXBytes <<< $(mysql -D$MYDB -u$MYUSER -p$MYPASS -se "SELECT RXbytes from Statistics where ID >= '$firstoflastmonth' and ID <= '$lastoflastmonth';")
        read -ra TXBytes <<< $(mysql -D$MYDB -u$MYUSER -p$MYPASS -se "SELECT TXBytes from Statistics where ID >= '$firstoflastmonth' and ID <= '$lastoflastmonth';")
        ### loop through RXbytes, if current count is less than previous count add max int value (4294967295) to current count and add to running total
        previousvalue=0
        counter=0
        totalRXbytes=0
        for i in "${RXBytes[@]}"; do
            counter=$(echo "$counter + 1" | bc -l)
            echo $counter
            if [ $counter -eq 1 ]; then
                totalRXbytes=$(echo "$totalRXbytes - ${i}" | bc -l)
                echo $totalRXbytes
            fi
            echo "Current Value: ${i}"
            echo "Previous Value: $previousvalue"

            if [ ${i} -lt $previousvalue ]; then
                echo "true, add 4294967295 to running total"
                totalRXbytes=$(echo "$totalRXbytes + 4294967295" | bc -l)
            fi
            previousvalue=${i}
        done
        ### add final value to totalRXBytes
        totalRXbytes=$(echo "$totalRXbytes + ${RXBytes[-1]}" | bc -l)

        ### loop through TXbytes, if current count is less than previous count add max int value (4294967295) to current count and add to running total
        previousvalue=0
        counter=0
        totalTXbytes=0
        for i in "${TXBytes[@]}"; do
            counter=$(echo "$counter + 1" | bc -l)
            echo $counter
            if [ $counter -eq 1 ]; then
                totalTXbytes=$(echo "$totalTXbytes - ${i}" | bc -l)
                echo $totalTXbytes
            fi
            echo "Current Value: ${i}"
            echo "Previous Value: $previousvalue"

            if [ ${i} -lt $previousvalue ]; then
                echo "true, add 4294967295 to running total"
                totalTXbytes=$(echo "$totalTXbytes + 4294967295" | bc -l)
            fi
            previousvalue=${i}
        done
        ### add final value to totalTXBytes
        totalTXbytes=$(echo "$totalTXbytes + ${TXBytes[-1]}" | bc -l)

        ### add RX and TX bytes together for total
        totalBytes=$(echo "$totalRXbytes + $totalTXbytes" | bc -l)
        ### Calculate GB from bytes
        totalGB=$(echo "$totalBytes / 1073741824" | bc -l)
        totalGB=$(round $totalGB 2)
        ### Extract last month's average download speed
        IFS=$' \t\n' read -r avgdlspeedlm <<< $(mysql -D$MYDB -u$MYUSER -p$MYPASS -se "SELECT truncate(Avg(DownloadSpeed),2) FROM Statistics WHERE ID >= '$firstoflastmonth' and ID <= '$lastoflastmonth' and DownloadSpeed != 0;")

        ### Extract last month's average upload speed
        IFS=$' \t\n' read -r avgulspeedlm <<< $(mysql -D$MYDB -u$MYUSER -p$MYPASS -se "SELECT truncate(Avg(UploadSpeed),2) FROM Statistics WHERE ID >= '$firstoflastmonth' and ID <= '$lastoflastmonth' and UploadSpeed != 0;")

        ### Display results
        echo "$lastMonth"
        echo "Total RXBytes: $totalRXbytes"
        echo "Total TXBytes: $totalTXbytes"
        echo "Total Bytes: $totalBytes"
        echo "Total GB: $totalGB"
        echo "Average DL Speed: $avgdlspeedlm"
        echo "Average UL Speed: $avgulspeedlm"

        ### Archive Total GB used to DataUsage table
        mysql -D$MYDB -u$MYUSER -p$MYPASS -se "insert into DataUsage (Month, TotalGBUsed, AvgDLSpeed, AvgULSpeed) values ('$firstoflastmonth', '$totalGB', '$avgdlspeedlm', '$avgulspeedlm')"
        echo "data archived"
    else
        ### Today is the first day of the month but the entry does exist so use previous data
        echo "entry exists, grabbing previous data"
        IFS=$' \t\n' read -r totalGB avgdlspeedlm avgulspeedlm <<< $(mysql -D$MYDB -u$MYUSER -p$MYPASS -se "SELECT TotalGBUsed, AvgDLSpeed, AvgULSpeed from DataUsage where Month = '$firstoflastmonth';")
    fi
else
    ### extract data from archive to populate php file
    echo "today is not the first day of the month."
    IFS=$' \t\n' read -r totalGB avgdlspeedlm avgulspeedlm <<< $(mysql -D$MYDB -u$MYUSER -p$MYPASS -se "SELECT TotalGBUsed, AvgDLSpeed, AvgULSpeed from DataUsage where Month = '$firstoflastmonth';")
fi

### Extract average GB per month used
IFS=$' \t\n' read -r avgGBpermonth <<< $(mysql -D$MYDB -u$MYUSER -p$MYPASS -se "SELECT truncate(avg(TotalGBUsed),2) from DataUsage;")

Why an array and not just sum the data you ask? Fun fact number two: my router utilizes an unsigned integer to store byte information. Once it hits the maximum value it resets. You can see the pattern we are programmatically checking with nested loops in the table below:

The data has been calculated to BASH variables and ready for reporting. For simplicity sake, I chose to transfer the BASH variables to a PHP file containing nothing but PHP variables. This is the quasi dynamic reporting I mentioned earlier. I didn't feel the data changed enough to compile a complete PHP solution that would query the database each time a webpage was served. Seemed excessive so this PHP variable file is updated via the script and referenced by a more static PHP page.

### Write statistics to file
printf "<?php\n" > $DSLStatisticsfile
printf "\$avgdlspeedytd=\"$avgdlspeedytd\";\n" >> $DSLStatisticsfile
printf "\$avgulspeedytd=\"$avgulspeedytd\";\n" >> $DSLStatisticsfile
printf "\$avgdlspeedmtd=\"$avgdlspeedmtd\";\n" >> $DSLStatisticsfile
printf "\$avgulspeedmtd=\"$avgulspeedmtd\";\n" >> $DSLStatisticsfile
printf "\$avgGBpermonth=\"$avgGBpermonth\";\n" >> $DSLStatisticsfile
printf "\$lastMonth=\"$lastMonth\";\n" >>$DSLStatisticsfile
printf "\$totalGB=\"$totalGB\";\n" >> $DSLStatisticsfile
printf "\$avgdlspeedlm=\"$avgdlspeedlm\";\n" >> $DSLStatisticsfile
printf "\$avgulspeedlm=\"$avgulspeedlm\";\n" >> $DSLStatisticsfile
printf "?>" >> $DSLStatisticsfile

As promised, we perform some housekeeping and reset the Internal Field Separator back to it's original state.

IFS=$OLDIFS

Lastly, we pull it all together in the cron for consistent processing at 31 minutes after midnight:

31 00 * * * /home/user/Scripts/evaluateDSLInfo.sh

So there it is. An end to end solution for collecting, analyzing, and reporting on Bandwidth usage. Follow along for more great adventures in technology.....

Kevin Tedesco

Tuesday, May 17, 2016

Dynamic DNS

Monday, April 25, 2016

DSL Bandwidth Monitoring Tool Part 2