Traffic summary using iptables: Difference between revisions

From Skytech
Jump to navigation Jump to search
(comment5, http://www.formspring.me/bottrabite lowest cialis, 196705, http://www.formspring.me/imexades generic cialis canada pharmacy, 402015,)
mNo edit summary
 
(8 intermediate revisions by 4 users not shown)
Line 1: Line 1:
[[Category:Linux]]
comment5, http://www.formspring.me/bottrabite lowest cialis, 196705, http://www.formspring.me/imexades generic cialis canada pharmacy, 402015,
= Overview =
Using iptables and a simple perl script to analyze data traffic.

= Iptables setup =
I've added two new chains; in- and outgoing traffic inside forwarding rules. I've added these to my firewall startup script:

It is pretty self-explanatory.

<pre>
### Static variables
IPT=/sbin/iptables

### Static machines
MIRROR=192.168.0.210
MAIL=192.168.0.220
WEB=192.168.0.240

### Create logging of traffic (assuming eth0 is the wan interface and/or the one doing the forwarding)
$IPT -N TRAFFIC_ACCT_IN
$IPT -N TRAFFIC_ACCT_OUT
$IPT -I FORWARD -i eth0 -j TRAFFIC_ACCT_IN
$IPT -I FORWARD -o eth0 -j TRAFFIC_ACCT_OUT
$IPT -A TRAFFIC_ACCT_IN --dst ${WEB}
$IPT -A TRAFFIC_ACCT_IN --dst ${MAIL}
$IPT -A TRAFFIC_ACCT_IN --dst ${MIRROR}
$IPT -A TRAFFIC_ACCT_OUT --src ${WEB}
$IPT -A TRAFFIC_ACCT_OUT --src ${MAIL}
$IPT -A TRAFFIC_ACCT_OUT --src ${MIRROR}
</pre>

= Perl script to grab data =
I'm putting the data from iptables into a local mysql database. From there I further analyze.

It's a simple two-stage process;
1. Get the data from the newly created chains;
2. Put into db and reset counter.

Mine is based around getting data every hour, so I've made a cron entry for that

Perl script:
<pre>
#!/usr/bin/perl

use strict;
use DBI;

## Reset counter?
my $bReset = 0;
if (defined($ARGV[0]) && $ARGV[0] eq '--reset')
{
$bReset = 1;
}

## Setup database connection
my $dsn = 'dbi:mysql:<databasename>:<hostname or localhost>:3306'; my $user = '<username>'; my $pass = '<password>';
my $dbh = DBI->connect($dsn, $user, $pass) or die "Horrible!!\n$DBI::errstr\n";

my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
$mon++;
$year += 1900;

## Only grab outgoing data
my @aData = `/sbin/iptables -L TRAFFIC_ACCT_OUT -n -v -x | awk '\$1 ~ /^[0-9]+\$/ { printf \"%s, %d \\n\", \$7, \$2 }'`;

foreach (@aData)
{
chomp;
my @aSplitter = split(/, /, $_);
my $sExtraSQL = "ON DUPLICATE KEY UPDATE traffic = ".$dbh->quote($aSplitter[1]);
my $sSQL = "INSERT INTO traffic (source, year, month, day, hour, traffic) VALUES (?, ?, ?, ?, ?, ?) $sExtraSQL\n";
my $sth = $dbh->prepare($sSQL);
$sth->execute($aSplitter[0], $year, $mon, $mday, $hour, $aSplitter[1]);
}

## Reset?
if ($bReset)
{
my $bResetIptableCounter = `/sbin/iptables -Z TRAFFIC_ACCT_OUT`;
}
</pre>

The code is extremely straightforward and no checks really. If I miss out of one hours traffic, it's no biggie, so haven't put much work into that part.

* I added the reset option so that one can update traffic data as wanted as long as the script is not run with --reset (resetting iptable counter). This way the ON UPDATE clause can do it's magic.

= Cron entry =
I've added this to crontab (crontab -e)
<pre>
## Grab data every 5min. This is a relatively lightweight operation taking ~0.3s on old hardware
*/5 * * * * /usr/local/sbin/grabTraffic.pl

## Reset data every hour
59 * * * * /usr/local/sbin/grabTraffic.pl --reset
</pre>

= Database create options =
For those wanting it; I've made a unique key formed by year+month+day+hour+source. It's highly inefficient, but I'm dealing with a relatively low amount of data on my end (checking 3 hosts, so for a year I'll have a max of 3 hosts * 24 hours * 365 days ~= 25000 entries).
<pre>
CREATE TABLE `traffic` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`year` smallint(4) DEFAULT NULL,
`month` smallint(2) DEFAULT NULL,
`day` smallint(2) DEFAULT NULL,
`hour` smallint(2) DEFAULT NULL,
`source` varchar(20) DEFAULT NULL,
`traffic` bigint(20) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `datecheck` (`year`,`month`,`day`,`hour`,`source`)
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=latin1
</pre>

== Sample data would look like ==
<pre>
mysql> SELECT * FROM traffic;
+----+------+-------+------+------+---------------+-----------+
| id | year | month | day | hour | source | traffic |
+----+------+-------+------+------+---------------+-----------+
| 1 | 2011 | 3 | 12 | 14 | 192.168.0.210 | 273143717 |
| 2 | 2011 | 3 | 12 | 14 | 192.168.0.220 | 2920 |
| 3 | 2011 | 3 | 12 | 14 | 192.168.0.240 | 30071 |
| 4 | 2011 | 3 | 12 | 15 | 192.168.0.210 | 3111394 |
| 5 | 2011 | 3 | 12 | 15 | 192.168.0.220 | 0 |
| 6 | 2011 | 3 | 12 | 15 | 192.168.0.240 | 1379200 |
| 7 | 2011 | 3 | 12 | 16 | 192.168.0.210 | 376536344 |
| 8 | 2011 | 3 | 12 | 16 | 192.168.0.220 | 1572 |
| 9 | 2011 | 3 | 12 | 16 | 192.168.0.240 | 42356 |
| 10 | 2011 | 3 | 12 | 17 | 192.168.0.210 | 665197917 |
| 11 | 2011 | 3 | 12 | 17 | 192.168.0.220 | 1440 |
| 12 | 2011 | 3 | 12 | 17 | 192.168.0.240 | 60937 |
[ ... ]
</pre>

= Example iptables output to test if it is working =
For ingoing traffic, issue:
<pre>
root@gateway:~# iptables -L TRAFFIC_ACCT_IN -n -v -x
Chain TRAFFIC_ACCT_IN (1 references)
pkts bytes target prot opt in out source destination
968985 56959759 all -- * * 0.0.0.0/0 192.168.0.210
78 4328 all -- * * 0.0.0.0/0 192.168.0.220
55144 80428099 all -- * * 0.0.0.0/0 192.168.0.240
</pre>

For outgoing, do:
<pre>
root@gateway:~# iptables -L TRAFFIC_ACCT_OUT -n -v -x
Chain TRAFFIC_ACCT_OUT (1 references)
pkts bytes target prot opt in out source destination
12713 4252586 all -- * * 192.168.0.210 0.0.0.0/0
26 1440 all -- * * 192.168.0.220 0.0.0.0/0
928 53851 all -- * * 192.168.0.240 0.0.0.0/0
</pre>

If you need to flush the counter for any of those, just use the -Z option followed by the chain-name:
<pre>
iptables -Z TRAFFIC_ACCT_OUT
</pre>

Latest revision as of 22:42, 2 April 2012

Overview

Using iptables and a simple perl script to analyze data traffic.

Iptables setup

I've added two new chains; in- and outgoing traffic inside forwarding rules. I've added these to my firewall startup script:

It is pretty self-explanatory.

### Static variables
IPT=/sbin/iptables

### Static machines
MIRROR=192.168.0.210
MAIL=192.168.0.220
WEB=192.168.0.240

### Create logging of traffic (assuming eth0 is the wan interface and/or the one doing the forwarding)
$IPT -N TRAFFIC_ACCT_IN
$IPT -N TRAFFIC_ACCT_OUT
$IPT -I FORWARD -i eth0 -j TRAFFIC_ACCT_IN
$IPT -I FORWARD -o eth0 -j TRAFFIC_ACCT_OUT
$IPT -A TRAFFIC_ACCT_IN --dst ${WEB}
$IPT -A TRAFFIC_ACCT_IN --dst ${MAIL}
$IPT -A TRAFFIC_ACCT_IN --dst ${MIRROR}
$IPT -A TRAFFIC_ACCT_OUT --src ${WEB}
$IPT -A TRAFFIC_ACCT_OUT --src ${MAIL}
$IPT -A TRAFFIC_ACCT_OUT --src ${MIRROR}

Perl script to grab data

I'm putting the data from iptables into a local mysql database. From there I further analyze.

It's a simple two-stage process; 1. Get the data from the newly created chains; 2. Put into db and reset counter.

Mine is based around getting data every hour, so I've made a cron entry for that

Perl script:

#!/usr/bin/perl

use strict;
use DBI;

## Reset counter?
my $bReset = 0;
if (defined($ARGV[0]) && $ARGV[0] eq '--reset')
{
        $bReset = 1;
}

## Setup database connection
my $dsn = 'dbi:mysql:<databasename>:<hostname or localhost>:3306'; my $user = '<username>'; my $pass = '<password>';
my $dbh = DBI->connect($dsn, $user, $pass) or die "Horrible!!\n$DBI::errstr\n";

my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
$mon++;
$year += 1900;

## Only grab outgoing data
my @aData = `/sbin/iptables -L TRAFFIC_ACCT_OUT -n -v -x | awk '\$1 ~ /^[0-9]+\$/ { printf \"%s, %d \\n\", \$7, \$2 }'`;

foreach (@aData)
{
        chomp;
        my @aSplitter = split(/, /, $_);
        my $sExtraSQL = "ON DUPLICATE KEY UPDATE traffic = ".$dbh->quote($aSplitter[1]);
        my $sSQL = "INSERT INTO traffic (source, year, month, day, hour, traffic) VALUES (?, ?, ?, ?, ?, ?) $sExtraSQL\n";
        my $sth = $dbh->prepare($sSQL);
        $sth->execute($aSplitter[0], $year, $mon, $mday, $hour, $aSplitter[1]);
}

## Reset?
if ($bReset)
{
        my $bResetIptableCounter = `/sbin/iptables -Z TRAFFIC_ACCT_OUT`;
}

The code is extremely straightforward and no checks really. If I miss out of one hours traffic, it's no biggie, so haven't put much work into that part.

  • I added the reset option so that one can update traffic data as wanted as long as the script is not run with --reset (resetting iptable counter). This way the ON UPDATE clause can do it's magic.

Cron entry

I've added this to crontab (crontab -e)

## Grab data every 5min. This is a relatively lightweight operation taking ~0.3s on old hardware
*/5 * * * * /usr/local/sbin/grabTraffic.pl

## Reset data every hour
59 * * * * /usr/local/sbin/grabTraffic.pl --reset

Database create options

For those wanting it; I've made a unique key formed by year+month+day+hour+source. It's highly inefficient, but I'm dealing with a relatively low amount of data on my end (checking 3 hosts, so for a year I'll have a max of 3 hosts * 24 hours * 365 days ~= 25000 entries).

CREATE TABLE `traffic` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `year` smallint(4) DEFAULT NULL,
  `month` smallint(2) DEFAULT NULL,
  `day` smallint(2) DEFAULT NULL,
  `hour` smallint(2) DEFAULT NULL,
  `source` varchar(20) DEFAULT NULL,
  `traffic` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `datecheck` (`year`,`month`,`day`,`hour`,`source`)
) ENGINE=MyISAM AUTO_INCREMENT=13 DEFAULT CHARSET=latin1

Sample data would look like

mysql> SELECT * FROM traffic;
+----+------+-------+------+------+---------------+-----------+
| id | year | month | day  | hour | source        | traffic   |
+----+------+-------+------+------+---------------+-----------+
|  1 | 2011 |     3 |   12 |   14 | 192.168.0.210 | 273143717 |
|  2 | 2011 |     3 |   12 |   14 | 192.168.0.220 |      2920 |
|  3 | 2011 |     3 |   12 |   14 | 192.168.0.240 |     30071 |
|  4 | 2011 |     3 |   12 |   15 | 192.168.0.210 |   3111394 |
|  5 | 2011 |     3 |   12 |   15 | 192.168.0.220 |         0 |
|  6 | 2011 |     3 |   12 |   15 | 192.168.0.240 |   1379200 |
|  7 | 2011 |     3 |   12 |   16 | 192.168.0.210 | 376536344 |
|  8 | 2011 |     3 |   12 |   16 | 192.168.0.220 |      1572 |
|  9 | 2011 |     3 |   12 |   16 | 192.168.0.240 |     42356 |
| 10 | 2011 |     3 |   12 |   17 | 192.168.0.210 | 665197917 |
| 11 | 2011 |     3 |   12 |   17 | 192.168.0.220 |      1440 |
| 12 | 2011 |     3 |   12 |   17 | 192.168.0.240 |     60937 |
[ ... ]

Example iptables output to test if it is working

For ingoing traffic, issue:

root@gateway:~# iptables -L TRAFFIC_ACCT_IN -n -v -x
Chain TRAFFIC_ACCT_IN (1 references)
    pkts      bytes target     prot opt in     out     source               destination         
  968985 56959759            all  --  *      *       0.0.0.0/0            192.168.0.210       
      78     4328            all  --  *      *       0.0.0.0/0            192.168.0.220       
   55144 80428099            all  --  *      *       0.0.0.0/0            192.168.0.240

For outgoing, do:

root@gateway:~# iptables -L TRAFFIC_ACCT_OUT -n -v -x
Chain TRAFFIC_ACCT_OUT (1 references)
    pkts      bytes target     prot opt in     out     source               destination         
   12713  4252586            all  --  *      *       192.168.0.210        0.0.0.0/0           
      26     1440            all  --  *      *       192.168.0.220        0.0.0.0/0           
     928    53851            all  --  *      *       192.168.0.240        0.0.0.0/0

If you need to flush the counter for any of those, just use the -Z option followed by the chain-name:

iptables -Z TRAFFIC_ACCT_OUT