TechInfoDepot:DD-WRT/Using RFlow Collector and MySQL To Gather Traffic Information
You can have your DD-WRT router send traffic information to a PC on your network and then store the information in a MySQL database that you can search. This can tell you a lot about the traffic on your network. This process involves several steps.
Part 1 (setting your router to send the traffic information to a local PC):
The first thing you need to do is go to the "Administration" tab of DD-WRT and then the "Services" sub-tab. Look for the "RFlow / MACupd" section. Enable RFlow and MACupd . Set the "Server IP" to the IP address of the computer on your network that will be gathering the data. There also will be options for a "Port", "Interface", and "Interval". Just leave those at the defaults. Click "Save Settings" at the bottom of the screen. Now your router should be sending packets to the computer with the IP address you specified.
Part 2 (setting up RFlowCollector):
Now you have to download RFlowCollector to a Windows PC. There is information about the program at http://www.dd-wrt.com/dd-wrtv2/rflow.php. You download it at http://www.dd-wrt.com/dd-wrtv2/down.php?path=downloads%2Fothers%2Frflow%20collector%2F&download=RFlowCollectorV3.rar. Unrar the program to whatever directory you want it to be in. (If you are not familiar with RAR files, they are compressed archives like zip files - 7-Zip [1] is one of several free programs that can extract files from a RAR archive) C:\Program Files\RFlowCollectorV3 is one logical choice for a place to store the files that you extract from the RFlowCollectorV3.rar archive.
Now run RFlowCollector and click the "Option" button. That should bring up a screen like the one shown below.
In the bottom left of the dialog box that appears click "mySQL Export support". For "Server Host or IP" enter "localhost", without the quotation marks, if the MySQL server will be running on the same PC as RFlowCollector. If the MySQL server will not be running on the same PC as RFlowCollector, then you would enter the host name or IP for the PC that will run MySQL. For "mySQL User" you want to pick a user name that you will set up in MySQL. You are going to have to set up a user called "rflow" or something else (since giving anyone with access to the server root access to the mySQL database is probably a bad idea). Pick a password for the "mySQL Pwd" field. Type the name that you want for your database in the "mySQL Database" field. In the examples below I will assume your user name is "root", your password is "password" (which is a user/password combination you should never use ...), and your database name is "rflow". After you have set this up, click "Save". You will see an error at the top of the RFlow Collector screen that says "no mySQL Connection!" Let's set up SQL and fix that.
Part 3 (setting up MySQL):
Let's save a little trouble and use version 4 of MySQL, which is available at http://dev.mysql.com/get/Downloads/MySQL-4.1/mysql-4.1.22-win32.zip/from/pick#mirrors. If you want to use a later version of MySQL, you will have to make the password for the user name that you set up, be compatible with RFlow Collector. The following URLs should help you with later versions of MySQL http://dev.mysql.com/doc/refman/5.0/en/old-client.html and http://linksysinfo.org/forums/showthread.php?p=294207. The method discussed in the dev.mysql.com web page will be shown below.
Extract and set up MySQL. Now you have to create the database / schema that the data will be saved in, and you have to create the tables for it too. To do that, go to a DOS prompt, change to the directory that has "mysql.exe", and type in:
mysql -h localhost -u root -p
Of course, if the database is somewhere other than the local computer then you need to change localhost to the host name, and if you are not using the root login then you have to change that. This will result in a password prompt and you have to put in the correct password. Now at the mysql prompt type:
CREATE DATABASE rflow;
If you used another database name besides rflow, then you would use that name here instead of rflow. Then at the mysql prompt type:
USE rflow;
Again, if your database is not named rflow then use the database name you just created. Now create the user -- this information (database name, user, password) will need to be the same as was entered into RflowCollector. Use the actual database name in the "rflow.*" part of the GRANT command and the username you put in RFlow Collector in the "'rflow'@'localhost'" part:
GRANT ALL ON rflow.* TO 'rflow'@'localhost' IDENTIFIED BY 'password';
SET PASSWORD FOR 'rflow'@'localhost' = OLD_PASSWORD('password');
The GRANT command will create the new user (note: this user can only log in from localhost, so if RflowCollector is on a different host, specify that host). SET PASSWORD will change the password to the older style used by pre-5.0 versions of MySQL.
There is a file that comes with RFlow Collector called "mySQL.txt". Copy and paste each CREATE TABLE command from the "mySQL.txt" file to the mySQL prompt. Copy and paste them one at a time and do not include the DROP TABLE commands. Each CREATE TABLE must be modified slightly, or you will get an error telling that 'TYPE' is deprecated and to use 'ENGINE' instead.
Replace the ') TYPE=MyISAM;' line at the end of each set by ') ENGINE=MyISAM;'
So, for example, your first set of commands (query) should look like:
CREATE TABLE akteth (
ID int(11) NOT NULL auto_increment,
ip varchar(17) NOT NULL default ,
mac varchar(17) NOT NULL default ,
status char(1) NOT NULL default ,
lasttraffic int(11) NOT NULL default '0',
name varchar(100) NOT NULL default ,
device varchar(10) NOT NULL default ,
PRIMARY KEY (ID)
) ENGINE=MyISAM;
then:
DROP TABLE IF EXISTS aktrouter;
CREATE TABLE aktrouter (
ID int(11) NOT NULL auto_increment,
ip varchar(17) NOT NULL default ,
flowsequenz int(11) NOT NULL default '0',
lastflow varchar(7) NOT NULL default '0',
ploss int(11) NOT NULL default '0',
PRIMARY KEY (ID)
) ENGINE=MyISAM;
Etc. After you have pasted each command and hit Enter so that you have created all the tables, type:
EXIT;
Now, if DD-WRT is sending out RFlow and MACupd packets, RFlowCollector will receive them and will write them to your MySQL database. Now you can use graphical tools like the MySQL Query Browser (available at http://dev.mysql.com/downloads/gui-tools/5.0.html) to query your network data.
