VnstatSystrayIcon Files

https://sourceforge.net/projects/vnstatsystray/

VnstatSystrayIcon is a bunch of things working together that creates an icon in your systray showing how much GiB you've used this month.
Currently used with an Ubuntu 10.04 gateway and win7 client on the lan.

***** install vnstat on linux gateway *****
sudo apt-get install vnstat
vnstat monitors traffic on the eth0 interface by default
if your internet interface is different than eth0, change it with 
vnstat -i newinterface

***** install mysql on linux gateway *****
do steps 1 and 2 from this tutorial
http://www.codelain.com/wiki/index.php?title=How_to_set_up_a_MySQL_Server_on_Linux_(Ubuntu)
cd /etc/mysql
sudo vi my.cnf
set the bind-address to be equal to your lan facing interface instead of localhost
allow port 3306 tcp through your firewall from the lan facing interface as mysql server listens on this port
	
configure database
We will login to the database locally as root, create a new database, a new table, a new column, add a row, and create two users with permissions to access it
mysql -u root -p<password>

CREATE DATABASE vnstatDb;
use vnstatDb;
CREATE TABLE vnstatTable(monthlyBandwidthUsed varchar (20));
INSERT INTO vnstatTable (monthlyBandwidthUsed) VALUES(10);
SELECT monthlyBandwidthUsed from vnstatTable; (should return 10)
UPDATE vnstatTable SET monthlyBandwidthUsed = 5;
SELECT monthlyBandwidthUsed from vnstatTable; (sould return 5)
CREATE USER 'vnstatUser'@'localhost';
GRANT SELECT, UPDATE ON vnstatDb.* TO 'vnstatUser'@'localhost';
CREATE USER 'vnstatClient'@'10.11.12.5'; (the local ip of the windows machine)
GRANT SELECT ON vnstatDb.* TO 'vnstatClient'@'10.11.12.5';
exit

now try logging in as vnstatUser
mysql -u vnstatUser
use vnstatDb;
SELECT monthlyBandwidthUsed from vnstatTable;
exit

***** configure perl script/cron job on linux gateway *****
copy vnstatScriptPerl from VnstatSystrayIcon.zip to /etc
try running vnstatScriptPerl, then selecting the monthlyBandwidthUsed from vnstatTable value from the mysql server to see if it changed
the value should be updated with the GiB value from the total column of the current month when doing a vnstat -s
then edit the crontab to have vnstatScriptPerl execute every 5 minutes
vi /etc/crontab
add another entry,
*/5 *    * * *   root    /etc/vnstatScriptPerl
to check the job is running, after 5 minutes do a vi /var/log/syslog and look for an entry like
Nov 20 09:30:01 GlaDOS CRON[2612]: (root) CMD (   /etc/vnstatScriptPerl)

***** install VnstatSystrayIcon on win7 *****
install the 32-bit MySQL ODBC 5.1 Driver from http://dev.mysql.com/downloads/connector/odbc/
verify it was installed by running C:\Windows\SysWOW64\odbcad32.exe, click add, scroll down to bottom, it should be there
add it and do a configure just to test you can make the connection
remove it if you added it after you are done testing with the configure
unzip VnstatSystrayIcon.zip, edit config.txt file and enter the ip of the server where mysql is running
start VnstatSystrayIcon and it should update with the GiB in red letters
if something isn't working ER appears
will poll db once every 10 minutes, sleeps when not polling

***** versions *****
vnStat 1.10
mysqld 5.1.41-3ubuntu12.10
ubuntu 10.04lts
win7 x64
32-bit MySQL ODBC 5.1 Driver
visual studio 2010
32-bit VnstatSystrayIcon.exe