Colin Bodor

I needed some way to keep track of how much data (in GB) per month any given IP address is using in one of our data centers. So after googling a bit, and writing some hairy PHP code I came up with a working example that we (my company) can build on for bandwidth accounting and billing. Below is a overview of what I did, and my sample code and config files.

1) Collecting the data
For this I used a Mikrotik RB250GS (http://routerboard.com/RB250GS) I had just laying around. If you don’t know anything about these, or never heard of them before, they are GREAT little managed switches, very cheap, and have a number of features usually only found on your higher end expensive switches. They are perfect for your lab use to replicate production configurations, or for small SOHO deployments where you need some VLAN functionality. In this case I wanted to use the port mirroring functionality. I setup port 1 and port 5 to mirror both ingress and egress traffic to port 3, and then wired it in between my internal firewall and my internal switch with port 1 connecting to the firewall LAN side and port 5 connecting to my LAN switch (although it does not really matter which one is plugged into which). You could put this in between anything really that you want to capture traffic on, this setup is basically a passive network tap that mirrors all data to port 3.

Here is a screenshot:

2) Data processing
For this part I fired up a CentOS VM and connected it directly to the ethernet port on my laptop, which was then plugged into the mirrored port 3 on the Microtik. In a production enviroment I would probably not use a VM, just a small server in the rack with an extra NIC on it for the mirroring data. It should be noted that this setup would not scale to multi GB/sec traffic, but if you are dealing with that kinda of data throughput you should already have enterprise level switches with the proper functionality and probably a more robust solution than I am doing. That being said, this should work well for anywhere up to 100MB/sec depending on the hardware you are using to process the data.

Then I installed pmacct (http://www.pmacct.net/) which is a nifty little peice of open source software that can collect all data on your listening NIC and do something with it, in my case I had it insert all the data into MySQL via its MySQL plugin. Below is the config I used:

interface: eth1
daemonize: true
aggregate: sum_host

plugins: mysql
sql_db: pmacct
sql_table: acct
sql_table_version: 1
sql_passwd: *****
sql_user: *****
sql_refresh_time: 90
!sql_history: 10m
!sql_history_roundoff: mh

networks_file: /root/networks.lst

The contents if networks.1st is just my internal IP range, you would change this to whatever range(s) you want to be collecting the usage data on:

192.168.69.0/24

3) Viewing the data
Now that everything I want is in MySQL tables, I created a real quick PHP script to view it. Please forgive my horrible PHP, I am not a developer, eventually this will be passed onto someone who actually knows what they are doing with PHP to extend the functionality. Below is the code:

<?php
$user="*****";
$password="******";
$database="pmacct";

mysql_connect(localhost,$user,$password);
@mysql_select_db($database) or die( "Unable to select database");

if(isset($_GET['clear'])) {
$table="acct";
mysql_query("TRUNCATE $table");
   echo "Cleared - <a href=\"index.php\">Go Back.</a><br /><br />";
exit();
}

$query="SELECT * FROM `acct` WHERE `bytes` >100 ORDER BY `bytes` DESC";
$result=mysql_query($query);

$num=mysql_numrows($result);

echo "<strong>Known Hosts: $num</strong>&nbsp <a href=\"index.php?clear\">Clear Data</a>&nbsp <a href=\"index.php\">Refresh</a><br /><br />";

echo "<table width=500>
      <tr>
        <td><strong>IP</strong></td>
        <td><strong>Packets</strong></td>
        <td><strong>Usage</strong></td>
      </tr>";

$i=0;
while ($i < $num) {

$ip=mysql_result($result,$i,"ip_src");
$packets=mysql_result($result,$i,"packets");
$bytes=mysql_result($result,$i,"bytes");
$transfer = $bytes / 1000 / 1000;

echo "<tr>
        <td>$ip</td>
        <td>$packets</td>
        <td>$transfer MB</td>
      </tr>";

$i++;
}

echo "</table>";

mysql_close();

?>

4) The result:

As you can see this gives a nice little breakdown by IP of bandwidth usage, the idea being that each month you would reset the data, after using it for whatever you need, in most cases billing someone. Ideally I would like to extend this to make calls back to a billing system for automated invoicing, but that’s above my code skill set so I will leave that bit of magic to others.

I hope someone finds this at least helpful, if you have any questions, concerns or suggestions feel free to comment.