Подсчет трафика

В статье про настройку маршрутизации и организацию доступа подсети в internet есть несколько правил, пишущих логи о пролетающих в маршрутизаторе пакетах. Настало время их художественно причесать, для удобного составления отчетов.

Предварительный парсинг логов

Данный скрипт читает /var/log/debug, парсит из него логи iptables в файлик в /var/log/traflogs/, после чего очищает/var/log/debug.

#!/usr/bin/perl -w

my $LOG_FILENAME = "/var/log/debug";
my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime(time);
$mon = $mon +1;
$year = $year + 1900;
my $TAB_OUTPUT = "/var/log/traflogs/$year-$mon-$mday-$hour-$min-$sec.csv";

my %MONS = (
    "Jan"=>"01",
    "Feb"=>"02",
    "Mar"=>"03",
    "Apr"=>"04",
    "May"=>"05",
    "Jun"=>"06",
    "Jul"=>"07",
    "Aug"=>"08",
    "Sep"=>"09",
    "Oct"=>"10",
    "Nov"=>"11",
    "Dec"=>"12"
);

open (A,$LOG_FILENAME);
open (B,">".$TAB_OUTPUT);
$count = 0;
print B "datetime\t\ti_in\ti_out\tsource\t\tdestination\tletgth\tproto\tsport\tdport\tresult\n";
print B "-----------------------------------------------------------------------------------------------------------------\n";
while(<A>){
    if(~/(\w+)\s+(\d+)\s+([\d:]+)\s+.*?\]\s+.*?(\w+)\s+
        IN=([\d\w]*)\s+OUT=([\d\w]*)\s+.*?SRC=([\d\.]+)\s+DST=([\d\.]+)\s+LEN=(\d+).*?
        PROTO=(\w+)\s+SPT=(\d+)\s+DPT=(\d+)/xig){
        $count = $count + $9;
        print B "$year-$MONS{$1}-".($2<10?"0$2":$2)." $3\t$5\t$6\t$7\t$8\t$9\t$10\t$11\t$12\t$4\n";
    }

}
close(A);
close(B);
`cat /dev/null > /var/log/debug`

Разумно повесить его на cron, чтобы каждый час формировал такую табличку

# head /var/log/traflogs/2011-1-5-17-55-1.csv
datetime                i_in    i_out   source          destination     letgth  proto   sport   dport   result
-----------------------------------------------------------------------------------------------------------------
2011-01-05 16:55:07     eth0    ppp0    192.168.0.1     77.88.57.177    274     TCP     50488   5222    ACCEPT
2011-01-05 16:55:08     ppp0    eth0    77.88.57.177    192.168.0.1     40      TCP     5222    50488   ACCEPT
2011-01-05 16:55:09     eth0    ppp0    192.168.0.1     77.88.57.177    226     TCP     50488   5222    ACCEPT
2011-01-05 16:55:09     ppp0    eth0    77.88.57.177    192.168.0.1     40      TCP     5222    50488   ACCEPT
2011-01-05 16:55:12     eth0    ppp0    192.168.0.1     77.88.57.177    114     TCP     50488   5222    ACCEPT
2011-01-05 16:55:12     ppp0    eth0    77.88.57.177    192.168.0.1     40      TCP     5222    50488   ACCEPT
2011-01-05 16:55:16     eth0    ppp0    192.168.0.1     77.88.57.177    290     TCP     50488   5222    ACCEPT
2011-01-05 16:55:17     ppp0    eth0    77.88.57.177    192.168.0.1     40      TCP     5222    50488   ACCEPT

Размещение логов в БД

Создадим 3 таблички в БД MySQL

CREATE TABLE `devs` (
  `id` tinyint(2) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(8) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=latin1

CREATE TABLE `protos` (
  `id` tinyint(2) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(8) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 |

CREATE TABLE `packs` (
  `dat` datetime DEFAULT NULL,
  `i_dev_id` tinyint(2) UNSIGNED NOT NULL DEFAULT '0',
  `o_dev_id` tinyint(2) UNSIGNED NOT NULL DEFAULT '0',
  `i_ip` int(11) UNSIGNED DEFAULT NULL,
  `o_ip` int(11) UNSIGNED DEFAULT NULL,
  `size` smallint(5) DEFAULT NULL,
  `proto_id` tinyint(2) UNSIGNED DEFAULT NULL,
  `i_port` smallint(5) DEFAULT NULL,
  `o_port` smallint(5) DEFAULT NULL,
  `accept` enum('0','1') NOT NULL DEFAULT '0',
  KEY `i_dev_id` (`i_dev_id`),
  KEY `o_dev_id` (`o_dev_id`),
  KEY `proto_id` (`proto_id`),
  KEY `i_port` (`i_port`),
  KEY `o_port` (`o_port`),
  KEY `dat` (`dat`),
  KEY `accept` (`accept`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Следующий скрипт читает содержимое каталога /var/log/traflogs/, все файлы *.csv перегоняет в *.csv.sql, после чего загружает их в БД с последующим удалением исходников *.csv

#!/usr/bin/perl -w

my $CSV_DIR = "/var/log/traflogs";
my $counter = 0;
my ($datetime,$i_dev,$o_dev,$i_ip,$o_ip,$size,$proto,$i_port,$o_port);

my %DEVS = (
""=>'0',
"eth0"=>'1',
"eth1"=>'2',
"eth2"=>'3',
"ppp0"=>'4',
"lo"=>"5"
);
my %PROTOS = (
'TCP'=>'1',
'UDP'=>'2'
);

opendir($dd, $CSV_DIR);
while($filename = readdir($dd)){
    if($filename ne '.' && $filename ne '..' && $filename=~/^.*\.csv$/g){
        chomp($filename);
        print $filename."\n";
        open (A,"$CSV_DIR/$filename");
        open (B,">$CSV_DIR/$filename.sql");
        $counter = 0;
        while(<A>){
            $counter = $counter+1;
            if($counter>2){
                ($datetime,$i_dev,$o_dev,$i_ip,$o_ip,$size,$proto,$i_port,$o_port,$result) = split /\t/;
                chomp($result);
                $i_dev = $DEVS{$i_dev};
                $o_dev = $DEVS{$o_dev};
                $proto = $PROTOS{$proto};
                $i_ip = ip_to_int($i_ip);
                $o_ip = ip_to_int($o_ip);
                print B "insert into packs values('$datetime','$i_dev','$o_dev','$i_ip','$o_ip','$size','$proto','$i_port','$o_port','".
                ($result eq 'ACCEPT'?"1":"0")
                ."');\n";
            }
        }
        close(A);
        close(B);
        `mysql -u root -ptoor trafd < $CSV_DIR/$filename.sql`;
        unlink("$CSV_DIR/$filename");
        unlink("$CSV_DIR/$filename.sql");
    }
}
closedir($dd);

sub ip_to_int{
    my ($ip) = @_;
    my ($a3,$a2,$a1,$a0) = split(/[^\d]/,$ip);
    return $a3*256*256*256 + $a2*256*256 + $a1*256 +$a0;
}

В результате имеем таблицу packs набитую информацией о пакетах.

Простые запросы для отчетов по трафику

Размер входящего трафика по портам

SELECT 
    i_port,
    round(sum(size)/1024/1024,3) AS MBytes 
FROM 
    packs 
WHERE 
    (
        (i_dev_id=4 AND o_dev_id=0) OR (i_dev_id=4 AND o_dev_id!=0)
    ) AND 
    accept='1' 
GROUP BY 
    i_port 
ORDER BY 
    MBytes DESC 
LIMIT 
    32

Получаем вот такой результат

+--------+--------+
| i_port | MBytes |
+--------+--------+
|     80 | 27.688 |
|     53 |  0.997 |
|    443 |  0.262 |
|   5222 |  0.205 |
|    123 |  0.052 |
|  11350 |  0.014 |
|  32767 |  0.004 |
|     25 |  0.001 |
|    110 |  0.001 |
|  12350 |  0.001 |
|   5277 |  0.001 |
|   7777 |  0.001 |
|  20253 |  0.000 |
|  24332 |  0.000 |
|  29605 |  0.000 |
|  32285 |  0.000 |
|  31902 |  0.000 |
|  21705 |  0.000 |
|  22306 |  0.000 |
|   4779 |  0.000 |
|  17944 |  0.000 |
|  22068 |  0.000 |
|  25343 |  0.000 |
|   4762 |  0.000 |
|  10176 |  0.000 |
|   1600 |  0.000 |
|   1720 |  0.000 |
|   1659 |  0.000 |
|   8625 |  0.000 |
|  27076 |  0.000 |
|  27858 |  0.000 |
|  27755 |  0.000 |
+--------+--------+

Размер исходящего трафика по портам

SELECT 
    o_port,
    round(sum(size)/1024/1024,3) AS MBytes 
FROM 
    packs 
WHERE 
    (
        (o_dev_id=4 AND i_dev_id=0) OR (o_dev_id=4 AND i_dev_id!=0)
    ) AND 
    accept='1' 
GROUP BY 
    o_port 
ORDER BY 
    MBytes DESC 
LIMIT 
    32

Даёт следующий результат

+--------+--------+
| o_port | MBytes |
+--------+--------+
|     80 |  7.067 |
|     53 |  0.418 |
|   5222 |  0.133 |
|    443 |  0.094 |
|    123 |  0.056 |
|  32767 |  0.031 |
|  11350 |  0.003 |
|    110 |  0.002 |
|   7777 |  0.002 |
|  32285 |  0.001 |
|  14439 |  0.001 |
|  17418 |  0.001 |
|   8776 |  0.001 |
|   5975 |  0.001 |
|   1899 |  0.001 |
|  13645 |  0.001 |
|  12350 |  0.001 |
|   3990 |  0.001 |
|   6615 |  0.001 |
|   8342 |  0.001 |
|   8625 |  0.001 |
|   9466 |  0.001 |
|   1636 |  0.001 |
|  19776 |  0.001 |
|   1090 |  0.001 |
|  21879 |  0.001 |
|  16385 |  0.001 |
|  27858 |  0.001 |
|  21520 |  0.001 |
|     25 |  0.001 |
|  28555 |  0.001 |
|  28222 |  0.001 |
+--------+--------+

Отчет о последних отброшенных пакетах

SELECT
    dat AS `date`,
    i.name AS `IN`,
    o.name AS `OUT`,
    o_port AS dport, 
    i_port AS sport, 
    INET_NTOA(i_ip) AS `from`,
    INET_NTOA(o_ip) AS `to`,
    IF(proto_id=1,'TCP','UDP') AS `proto`  
FROM 
    devs AS i 
        RIGHT JOIN 
    packs 
        ON i.id=packs.i_dev_id 
        LEFT JOIN 
    devs AS o 
        ON o.id=packs.o_dev_id 
WHERE 
    accept='0'
ORDER BY
    `date` DESC 
LIMIT 
    32;

Получаем что-то вроде

+---------------------+------+------+-------+-------+-------------+---------------+-------+
| date                | IN   | OUT  | dport | sport | from        | to            | proto |
+---------------------+------+------+-------+-------+-------------+---------------+-------+
| 2011-01-05 15:38:53 | eth0 | NULL |   138 |   138 | 192.168.0.1 | 192.168.0.255 | UDP   |
| 2011-01-05 15:38:53 | eth0 | NULL |   138 |   138 | 192.168.0.1 | 192.168.0.255 | UDP   |
| 2011-01-05 15:28:09 | NULL | lo   |   953 | 32767 | 127.0.0.1   | 127.0.0.1     | TCP   |
| 2011-01-05 15:28:03 | NULL | lo   |   953 | 32767 | 127.0.0.1   | 127.0.0.1     | TCP   |
| 2011-01-05 15:28:00 | NULL | lo   |   953 | 32767 | 127.0.0.1   | 127.0.0.1     | TCP   |
| 2011-01-05 15:26:52 | eth0 | NULL |   138 |   138 | 192.168.0.1 | 192.168.0.255 | UDP   |
| 2011-01-05 15:26:52 | eth0 | NULL |   138 |   138 | 192.168.0.1 | 192.168.0.255 | UDP   |
| 2011-01-05 15:14:52 | eth0 | NULL |   138 |   138 | 192.168.0.1 | 192.168.0.255 | UDP   |
| 2011-01-05 15:14:52 | eth0 | NULL |   138 |   138 | 192.168.0.1 | 192.168.0.255 | UDP   |
| 2011-01-05 15:02:51 | eth0 | NULL |   138 |   138 | 192.168.0.1 | 192.168.0.255 | UDP   |
| 2011-01-05 15:02:51 | eth0 | NULL |   138 |   138 | 192.168.0.1 | 192.168.0.255 | UDP   |
| 2011-01-05 14:50:51 | eth0 | NULL |   138 |   138 | 192.168.0.1 | 192.168.0.255 | UDP   |
| 2011-01-05 14:50:51 | eth0 | NULL |   138 |   138 | 192.168.0.1 | 192.168.0.255 | UDP   |
| 2011-01-05 14:38:50 | eth0 | NULL |   138 |   138 | 192.168.0.1 | 192.168.0.255 | UDP   |
| 2011-01-05 14:38:50 | eth0 | NULL |   138 |   138 | 192.168.0.1 | 192.168.0.255 | UDP   |
| 2011-01-05 14:26:49 | eth0 | NULL |   138 |   138 | 192.168.0.1 | 192.168.0.255 | UDP   |
| 2011-01-05 14:26:49 | eth0 | NULL |   138 |   138 | 192.168.0.1 | 192.168.0.255 | UDP   |
| 2011-01-05 14:14:49 | eth0 | NULL |   138 |   138 | 192.168.0.1 | 192.168.0.255 | UDP   |
| 2011-01-05 14:14:49 | eth0 | NULL |   138 |   138 | 192.168.0.1 | 192.168.0.255 | UDP   |
| 2011-01-05 14:02:48 | eth0 | NULL |   138 |   138 | 192.168.0.1 | 192.168.0.255 | UDP   |
| 2011-01-05 14:02:48 | eth0 | NULL |   138 |   138 | 192.168.0.1 | 192.168.0.255 | UDP   |
| 2011-01-05 13:50:48 | eth0 | NULL |   138 |   138 | 192.168.0.1 | 192.168.0.255 | UDP   |
| 2011-01-05 13:50:48 | eth0 | NULL |   138 |   138 | 192.168.0.1 | 192.168.0.255 | UDP   |
| 2011-01-05 13:38:47 | eth0 | NULL |   138 |   138 | 192.168.0.1 | 192.168.0.255 | UDP   |
| 2011-01-05 13:38:47 | eth0 | NULL |   138 |   138 | 192.168.0.1 | 192.168.0.255 | UDP   |
| 2011-01-05 13:26:46 | eth0 | NULL |   138 |   138 | 192.168.0.1 | 192.168.0.255 | UDP   |
+---------------------+------+------+-------+-------+-------------+---------------+-------+

При желании запросы можно разнообразить


Добавить комментарий