Ежедневная статистика

Форум для размещения материалов по реализации различных схем использования ПО, решению частых проблем и предупреждению частых ошибок
Закрыто
J.C.
Сообщения: 2
Зарегистрирован: Пн авг 08, 2011 09:34
Откуда: Chelyabinsk

Ежедневная статистика

Сообщение J.C. »

Доброго времени суток.

Скорее всего я изобретаю велосипед, но тем не менее, хочу поделиться своим опытом по созданию сабжа.

Предыстория
Дело в том что возникла необходимость в написании собственного веб-интерфейса к UTM5, именно личного кабинета.
Соответственно, пользователям нужно нужно её как-то отображать. Вариант использовать URFA в нашем случае не подошёл по причине ОЧЕНЬ долгого формирования статистики. Ну и, опять же, некоторым пользователям нужен не сводный отчёт по классам трафика за период, а "обычную" статистику по каждому дню.

Как это решить средствами UTM я не нашёл (возможно, плохо искал?), и мной было принято решение о написании собственной системы подсчёта.

История
Для сбора информации о трафике мы используем данные NetFlow. Предварительно, была изучена эта статья и было принято решение обрабатывать данные непосредственно из "сырых" логов UTM'а, и писать их уже в свою отдельную таблицу в БД.

Спешу поделиться своим решением с вами - вдруг, кому-то пригодится.

Итак, за основу был взят Perl-скрипт raw_fd_script от пользователя Magnum72 (за что ему огромное спасибо).
Думаю, на просторах этого форума можно найти эти скрипты.
В оригинале этот скрипт осуществлял бинарное чтение из лога UTM, группировал данные по месяцам и лиц.счетам и и записывал уже в сжатом (gzip) на файловую систему - один лиц.счёт=один файл.
На основании этих файлов, мы выдавали пользователям (по заказу) детализированную статистику по трафику.

После некоторого "рефакторинга", я научил этот скрипт ещё и писать обработанные (разбитые на даты) данные в БД, для последующего использования их в личном кабинете.

Системные требования
Для корректной работы мне потребовалось установить следующие perl-модули: Также, Вам потребуется добавить в конфигурационный файл UTM (обычно, /netup/utm5/utm5.cfg) строку, указывающую на Вашу дополнительную БД - в моём случае это UTM5_opt

Код: Выделить всё

database_optional=UTM5_opt
Установка и настройка
Для начала Вам потребуется создать таблицу под статистические данные в вашей БД (той, что Вы указали в database_optional):

Код: Выделить всё

CREATE TABLE `stat__daily` (
  `date` date NOT NULL,
  `ip` int(11) NOT NULL,
  `utm_account_id` int(11) default NULL,
  `packets` int(11) default NULL,
  `bytes` bigint(20) NOT NULL default '0',
  UNIQUE KEY `ip` (`ip`,`date`),
  KEY `account` (`utm_account_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COMMENT='Ежедневная статистика';
Далее, представляю Вашему вниманию сам скрипт:

Код: Выделить всё

# cat raw_fd_script
#!/usr/bin/perl

use DBI;
use IO::File;
use IO::Compress::Gzip;
use Config::IniFiles;
use Class::Date;
use strict;

my $datadir = '/var/flowstat';

my $log = IO::File->new('/var/log/fd.log','a') or die('Unable to open/create log-file!');

my $cfg = Config::IniFiles->new(-file=>'/netup/utm5/utm5.cfg',-fallback=>'main') or do{ print $log "Unable to load UTM config\n"; die(); };
my $dbh = DBI->connect(sprintf('dbi:%s:%s',$cfg->val('main','database_type'),$cfg->val('main','database_optional')),$cfg->val('main','database_login'),$cfg->val('main','database_password')) or do{ print $log "Unable to connect DB!\n"; die(); };
my %traf;

printf $log "[%s] [!] PID %d started...\n", (Class::Date->new(time())), $$;

if ( !-d($datadir) )
{
    printf $log "[%s] [>] Creating data-dir \'%s\'...\n", (Class::Date->new(time())), $datadir;
    mkdir($datadir,0755) or do{ printf $log "Failed! Error %d, exiting...\n", $!; die($!); };
}

# 1. Processing UTM RAW file(s)

foreach my $utmLogFile ( @ARGV )
{
    if ( -s($utmLogFile)%76 == 0 )
    {
        my $raw = IO::File->new($utmLogFile) or printf $log 'Unable to fopen() UTM data-file \'%s\'', $utmLogFile;
        my $i;

        my $start = time();

        printf $log "&#91;%s&#93; &#91;<&#93; Processing %s...\n", &#40;Class&#58;&#58;Date->new&#40;time&#40;&#41;&#41;&#41;, $utmLogFile;

        until &#40; $raw->eof&#40;&#41; &#41;
        &#123;
            $raw->read&#40; undef, 4 &#41;;             # 1-4
            $raw->read&#40; my $src_ip, 4 &#41;;        # 5-8
            $raw->read&#40; my $dst_ip, 4 &#41;;        # 9-12
            $raw->read&#40; undef, 8 &#41;;             # 13-20
            $raw->read&#40; my $packets,4 &#41;;        # 21-24
            $raw->read&#40; my $bytes,  4 &#41;;        # 25-28
            $raw->read&#40; undef, 4 &#41;;             # 29-32
            $raw->read&#40; undef, 4 &#41;;             # 33-36
            $raw->read&#40; my $src_p,  2 &#41;;        # 37-38
            $raw->read&#40; my $dst_p,  2 &#41;;        # 39-40
            $raw->read&#40; undef, 16&#41;;             # 41-56
            $raw->read&#40; my $acc_id, 4 &#41;;        # 57-60
            $raw->read&#40; undef, 4 &#41;;             # 61-64
            $raw->read&#40; my $tclass, 4 &#41;;        # 65-68
            $raw->read&#40; my $tstamp, 4 &#41;;        # 69-72
            $raw->read&#40; undef, 4 &#41;;             # 73-76

            my $ts    = Class&#58;&#58;Date->new&#40;unpack&#40;'%32I',$tstamp&#41;&#41;;
            my $month = $ts->strftime&#40;'%Y%m'&#41;;
            my $date = $ts->strftime&#40;'%Y-%m-%d'&#41;;

            $acc_id   = unpack&#40;'%32I',$acc_id&#41;;
            $tclass   = unpack&#40;'%32I',$tclass&#41;;

            if &#40; $tclass == 0 &#41;
            &#123;
                $acc_id = 'NULL';
            &#125;

#                     26 =                   4        4         4       4       2       2                  2        4
            if &#40; my $rec = join&#40;undef, $src_ip, $dst_ip, $packets, $bytes, $src_p, $dst_p, pack&#40;'S',$tclass&#41;, $tstamp&#41; &#41;
            &#123;
                push @&#123;$traf&#123;'monthly'&#125;->&#123;$month&#125;->&#123;$acc_id&#125;&#125;, $rec if &#40; length&#40;$rec&#41; == 26 &#41;;
            &#125;

            $i++;

            $src_ip = unpack&#40;"%32l" ,$src_ip&#41;;
            $dst_ip = unpack&#40;"%32l" ,$dst_ip&#41;;

            $traf&#123;'daily'&#125;->&#123;$date&#125;->&#123;$acc_id&#125;->&#123;$dst_ip&#125;->&#123;packets&#125; += unpack&#40;'%32I',$packets&#41;;
            $traf&#123;'daily'&#125;->&#123;$date&#125;->&#123;$acc_id&#125;->&#123;$dst_ip&#125;->&#123;bytes&#125;   += unpack&#40;'%32I',$bytes&#41;;
        &#125;

        $raw->close&#40;&#41;;

        printf $log "&#91;%s&#93; &#91;!&#93; Records completed&#58; %d in %d seconds\n", &#40;Class&#58;&#58;Date->new&#40;time&#40;&#41;&#41;&#41;, $i, &#40;time&#40;&#41;-$start&#41;;
    &#125;
    else
    &#123;
        printf $log "&#91;%s&#93; &#91;E&#93; Log-file '%s' has incorrect format!\n", &#40;Class&#58;&#58;Date->new&#40;time&#40;&#41;&#41;&#41;, $utmLogFile;
    &#125;
&#125;

# 2. Writing optimized GZipped DS-files into data-dir
printf $log "&#91;%s&#93; &#91;>&#93; Saving traffic log into gzipped binary DS-files...\n", &#40;Class&#58;&#58;Date->new&#40;time&#40;&#41;&#41;&#41;;

foreach my $period &#40; sort keys %&#123;$traf&#123;'monthly'&#125;&#125; &#41;
&#123;
    my $path = sprintf '%s/%s', $datadir, $period;

    if &#40; !-d&#40;$path&#41; &#41;
    &#123;
        printf $log "&#91;%s&#93; &#91;>&#93; Creating data-dir \'%s\'...\n", &#40;Class&#58;&#58;Date->new&#40;time&#40;&#41;&#41;&#41;, $path;
        mkdir&#40;$path,0755&#41; or do&#123; printf $log "Failed! Error %d, exiting...\n", $!; die&#40;$!&#41;; &#125;;
    &#125;

    foreach my $acc_id &#40; sort keys %&#123;$traf&#123;'monthly'&#125;->&#123;$period&#125;&#125; &#41;
    &#123;
        my $out = IO&#58;&#58;Compress&#58;&#58;Gzip->new&#40;sprintf&#40;'%s/%s.ds.gz',$path,$acc_id&#41;,Append=>1&#41;;

        foreach my $rec &#40; @&#123;$traf&#123;'monthly'&#125;->&#123;$period&#125;->&#123;$acc_id&#125;&#125; &#41;
        &#123;
            $out->write&#40;$rec&#41;;
        &#125;

        $out->close&#40;&#41;;
    &#125;
&#125;

# 3. Writing daily user statistics into database
printf $log "&#91;%s&#93; &#91;>&#93; Saving traffic into DB......\n", &#40;Class&#58;&#58;Date->new&#40;time&#40;&#41;&#41;&#41;;

my $start = time&#40;&#41;;
my $i;

foreach my $d &#40; sort keys %&#123;$traf&#123;'daily'&#125;&#125; &#41;
&#123;
    foreach my $a &#40; sort keys %&#123;$traf&#123;'daily'&#125;->&#123;$d&#125;&#125; &#41;
    &#123;
        foreach my $ip &#40; sort keys %&#123;$traf&#123;'daily'&#125;->&#123;$d&#125;->&#123;$a&#125;&#125; &#41;
        &#123;
            $i++;
            my $query = sprintf 'INSERT INTO `stat__daily` &#40;`date`,`ip`,`utm_account_id`,`packets`,`bytes`&#41;'
                                .' VALUES &#40;\'%s\',%d,%s,%d,%d&#41;'
                                .' ON DUPLICATE KEY'
                                .' UPDATE `utm_account_id`=%3$s,'
                                    .'`packets`=`packets`+%4$d,'
                                    .'`bytes`=`bytes`+%5$d;',
                                $d,
                                $ip,
                                $a,
                                $traf&#123;'daily'&#125;->&#123;$d&#125;->&#123;$a&#125;->&#123;$ip&#125;->&#123;packets&#125;,
                                $traf&#123;'daily'&#125;->&#123;$d&#125;->&#123;$a&#125;->&#123;$ip&#125;->&#123;bytes&#125;;

            $dbh->do&#40;$query&#41;;
            $dbh->do&#40;sprintf&#40;'UPDATE `stat__daily` SET `utm_account_id`=%d WHERE `ip`=%d `utm_account_id` IS NULL AND `date`>=\'2011-01-01\'',$a,$ip&#41;&#41; if &#40;$a ne 'NULL'&#41;;
        &#125;
    &#125;
&#125;

printf $log "&#91;%s&#93; &#91;!&#93; DB queries completed&#58; %d in %d seconds\n", &#40;Class&#58;&#58;Date->new&#40;time&#40;&#41;&#41;&#41;, $i, &#40;time&#40;&#41;-$start&#41;;
printf $log "&#91;%s&#93; &#91;!&#93; PID %d completed...\n--\n", &#40;Class&#58;&#58;Date->new&#40;time&#40;&#41;&#41;&#41;, $$;

$log->close&#40;&#41;;
Сохраните его, как /netup/utm5/bin/raw_fd_script (например), далее в админке биллинга переходим в Настройки -> Список параметров, и убеждаемся в том что значение параметра raw_fd_script соответствует тому пути, где у вас действительно лежит скрипт.

Использование
Итак, если Вы всё сделали правильно, Вам остаётся только ждать - наполнения таблицы - статистическими данными.

Далее, чтобы получить статистику по какому-то пользователю Вы можете использовать SQL-запросы

Получение статистики по IP за период:

Код: Выделить всё

SELECT `date`,`bytes` FROM `stat__daily` WHERE `ip`=inet_aton&#40;'127.0.0.1'&#41; AND `date` BETWEEN '2011-07-01' AND '2011-07-31' ORDER BY `date`
Вообщем, далее возможности и гибкость отчётов, которые Вы можете сгенерировать самостоятельно (и быстро!) ограничивается только Вашим знанием SQL!

Если интересует, в последующих постах могу рассказать об анализе и подготовки детализированной статистики из полученных в результате обработки скриптом данных.

Если у Вас возникли вопросы/пожелания/предложения - пишите. Сюда или в личку.

Проект на GitHub, присоединяйтесь ;)

Закрыто