Whatis Gnuplot:
As defined the Wikipedia. …
Gnuplot is a command-line program that can generate two- and three-dimensional plots of functions, data, and data fits. It is frequently used for publication-quality graphics as well as education. The program runs on all major computers and operating systems (GNU/Linux, Unix, Microsoft Windows, Mac OS X, and others).
I remember when I got in love with the MRTG and I spent many nights in mastering this giant. MRTG is overall a very good graphing too graph about any device but it usually works with snmp (and in some cases with shell scripts too). But what if I have data in a file with simple human readable format and I want to plot different columns in it? MRTG will not help in such cases, Gnuplot will come to rescue 🙂
I used Gnuplot to graph user download for the current month, In this example user data is taken from MYSQL radius DB and then graphed/plotted with Gnuplot.
As always being a duffer , dumber and incompetent, It took me 2-3 Days of continuous efforts to make it as a single script for bundled package.
Requirements for this script:
[You can modify it as per your requirements very easily, I just made it as per my own requirements : D ]
- Linux / UBUNTU / CENTOS
- MySQL with Radius DB
- Gnuplot Application
What this script will do ?
This script will take accounting data for the specified users for the current month by auto detecting the month/year.The file will look something like following
2015-03-01 1688961371 937706875
2015-03-02 2989190965 2974464964
2015-03-04 534479492 31747041
2015-03-05 809968366 170112567
2015-03-06 2189812711 1555484772
First column is DATE
Second column is user DOWNLOADED data in bytes
Third column is user UPLOADED data in bytes
Then it will save this accounting data in /tmp/USERNAME.TXT (Username is what supplied by the user)
Then gnuplot will start its magic and will graph the data based on the supplied data.
To install Gnuplot on Ubuntu , issue following command
apt-get install -y gnuplot
Now create bash script as follows
mkdir /temp touch /temp/usergraph.sh nano /temp/usergraph.sh
and paste following. Make sure to change things according to your network
#!/bin/sh
# Freeradius / Mysql User Graph Generation 'ON THE FLY' using GNUPLOT
# you can use it like ./usergraph.sh 2016 01 (for Jan, 2016)
# By Syed Jahanzaib / aacable [at] hotmail.com
# https://aacable.wordpress.com
# Created: May, 2015
# Last Modified on 29th-JAN-2016
# Defining BASH Variables FOR MYSQL CREDENTIALS and host
SQLUSER="root"
SQLPASS="SQLPASSWORD"
SQLHOST="localhost"
# Date functions to find current date, month year, can be used in auto detect month type script
# you can modify it to auto detect as showed in other examples / zaib
NOW=$(date)
MONTH=$(date +"-%m")
CMONTH=`echo $MONTH | sed -e 's/-//g'`
YEAR=$(date +"-%Y")
CYEAR=`echo $YEAR | sed -e 's/-//g'`
FMONTH=$(date +"%B")
FULLMONTH=`echo $FMONTH # | sed -e 's/-//g'`
# Name of file in which mysql will dump the user accounting data for the current month
TMP="/tmp/$1.txt"
echo "$1 $2 $3"
# Fetch Accounting Data from MYSQL Freeradius radius DB, by using current Year/Month using username provide with the script , and output to file
mysql -u$SQLUSER -p$SQLPASS -h$SQLHOST -e "use radius; SELECT SQL_CALC_FOUND_ROWS date, SUM(allbytesdl) - COALESCE(SUM(specbytesdl), 0), SUM(allbytesul) - COALESCE(SUM(specbytesul), 0), SUM(alltime) - COALESCE(SUM(spectime), 0)
FROM ( SELECT LEFT(radacct.acctstarttime, 10) AS date, acctoutputoctets AS allbytesdl, SUM(dlbytes) AS specbytesdl, acctinputoctets AS allbytesul, SUM(ulbytes) AS specbytesul,
radacct.acctsessiontime AS alltime, SUM(rm_radacct.acctsessiontime) AS spectime FROM radacct LEFT JOIN rm_radacct ON rm_radacct.radacctid = radacct.radacctid
WHERE LEFT(radacct.acctstarttime, 7) LIKE '$2-$3%' AND radacct.username LIKE '$1' AND FramedIPAddress LIKE '%' AND CallingStationId LIKE '%' GROUP BY radacct.radacctid
) AS tmp GROUP BY date LIMIT 0, 50;" |awk '{print $1,$2,$3}' > $TMP
sed '1d' -i $TMP
# Run GNUPLOT SCRIPT on the FLY / by zaib
gnuplot << EOF
reset
set terminal jpeg size 1600,600
# Set output according to your requirement, like you can create file with the username for easier identification
set output "/var/www/$1.jpg"
set xdata time
set timefmt "%Y-%m-%d"
set format x "%d/%m"
set xtics 86400
set xtics rotate by -45
set xlabel "Date (day/month)"
set ylabel "Data Downloaded in GB"
set title "User Account - $1\n Download/Upload Report for Month $3 / Year $2\nTotal Data Used = $TOTAL GB\nThis report was created on $NOW\nPowered by Syed Jahanzaib / aacable@hotmail.com"
set key outside
set grid
set style data histogram
set style histogram cluster gap 1
set style fill solid
set boxwidth 0.9
plot "$TMP" using 1:(\$2/2**30):(sprintf("%.2f", \$2/2**30)) w boxes title "Download" lw 10, \
"$TMP" using 1:(\$3/2**30):(sprintf("%.2f", \$3/2**30)) w boxes lw 6 title "Upload", \
"$TMP" using 1:(\$2/2**30):(sprintf("%.2f", \$2/2**30)) w labels notitle center offset 0,2 tc rgb 'red', \
"$TMP" using 1:(\$3/2**30):(sprintf("%.2f", \$3/2**30)) w labels notitle center offset 0,1 tc rgb 'green'
EOF
# GNUPLOT Script ends here
# Thank you : ) / zaib
Running the SCRIPT
Now execute the script by
/temp/usergraph.sh USERNAME 2016 01
(Like usergraph.sh zaib 2016 01) this will create graph on the fly for use ‘zaib’ for January, 2016 month
If everything goes well and you dont’ see any errors after executing this script, then you can view the output by
Note: the jpg file will be placed in /var/www folder under the username format, for ubuntu.
That’s it …
I showed the very basic usage of Gnuplot. Very Very Basic Level of it. This is only what I have learned so far in few days. But Gnuplot can do things beyond your imagination. Look at this gallery.
Gnuplot is a very good and customizable tool which is used all over the world to create simple OR very complex graphs in a go. Above all good part is that it can take data from local files and all can be done via scripting or terminal.
You should give it a try 🙂
Another version which takes year from your input and then create graph for the whole year usage for the network (overall)
This is another version which input year from you and then create graph for the whole year for overall network usage,
root@radius:/temp# cat year.sh
#!/bin/sh
# MYSQL USER NAME AND PASSWORD Variables
SQLUSER="root"
SQLPASS="SQLPASS"
# Date functions to find current date, month year
NOW=$(date)
MONTH=$(date +"-%m")
CMONTH=`echo $MONTH | sed -e 's/-//g'`
YEAR=$(date +"-%Y")
CYEAR=`echo $YEAR | sed -e 's/-//g'`
FMONTH=$(date +"%B")
FULLMONTH=`echo $FMONTH # | sed -e 's/-//g'`
mysql -u$SQLUSER -p$SQLPASS -e "use radius; SELECT SQL_CALC_FOUND_ROWS
date,
SUM(allbytesdl) - COALESCE(SUM(specbytesdl), 0),
SUM(allbytesul) - COALESCE(SUM(specbytesul), 0),
SUM(alltime) - COALESCE(SUM(spectime), 0)
FROM (
SELECT LEFT(radacct.acctstarttime, 7) AS date,
acctoutputoctets AS allbytesdl, SUM(dlbytes) AS specbytesdl,
acctinputoctets AS allbytesul, SUM(ulbytes) AS specbytesul,
radacct.acctsessiontime AS alltime, SUM(rm_radacct.acctsessiontime) AS spectime
FROM radacct
LEFT JOIN rm_radacct ON rm_radacct.radacctid = radacct.radacctid
WHERE LEFT(radacct.acctstarttime, 4) LIKE '$1%' AND radacct.username LIKE '%' AND
FramedIPAddress LIKE '%' AND CallingStationId LIKE '%'
GROUP BY radacct.radacctid
) AS tmp
GROUP BY date
LIMIT 0, 50;" |awk '{print $1,$2,$3}' > /tmp/raw
sed '1d' -i /tmp/raw
awk '{ print $1, $2 + $3; }' /tmp/raw > /tmp/final
echo DONE
# Name of file in which mysql will dump the user accounting data for the current month
TMP="/tmp/final"
# Run GNUPLOT SCRIPT on the FLY / by zaib
gnuplot << EOF
reset
set terminal jpeg size 1600,600
# Set output according to your requirement, like you can create file with the username for easier identification
set output "/var/www/radius.jpg"
set xdata time
set timefmt "%Y-%m"
set format x "%Y/%m"
#set ytics 1
set xtics rotate by -45
set xlabel "Date (month/year)"
set ylabel "Data Downloaded in GB"
set title "Download/Upload Report for $1\nThis report was created on $NOW\nPowered by Syed Jahanzaib / aacable@hotmail.com"
set key outside
set grid
set style data histogram
set style histogram cluster gap 1
set style fill solid
set boxwidth 0.9
plot "$TMP" using 1:(\$2/2**30):(sprintf("%.0f", \$2/2**30)) w boxes title "Download" lw 10, \
"$TMP" using 1:(\$2/2**30):(sprintf("%.0f", \$2/2**30)) w labels title "Data in GB" center offset 0,1 tc rgb 'red'
EOF
# GNUPLOT Script ends here
# Thank you : )
Now execute script as follows
./year.sh 2015
you ahve to supply year o it will generate overall graph which will look odd as we are graphing details for 1 year only,
Sample of above script will generate graph
as follows …
Regard’s
Syed Jahanzaib
Syed Jahanzaib - Personal Blog to Share Knowledge ! 
