Китаев А.Е. (Kitaev_A_E сбчк mail.ru)

Наверх

Пред.

Счет траффика с помощью MySql.

Как вы помните (см. 2 предыдущие статьи), я считал траффик, проходящий через Linux-маршрутизатор с помощью разнородных программ, запускаемых и в Linux, и в Windows. Поразмышляв, я пришел к выводу, что лучше запускать все необходимые приложения в одной операционной системе, в Linux (напомню, что это Red Hat 7.3). Хотя честно говоря, в моменту написания этих программ (лето 2007) вопрос несколько потерял для меня актуальность, так как по настоянию начальства я переподключил пользователей к Интернету через squid и стал использовать sarg для сбора статистики. Но нужно отдавать себе отчет, что через squid и sarg мы отловим не весь траффик, ведь изначально squid предназначен для «посредничества» при просмотре именно Web-страниц (основная функция – кэширование, как я прочитал в одной из статей в сети). Но возможна потребность для выхода в Интернет не только для подключения к 80 порту, но и к множеству других портов. Здесь squid не поможет, нужен маскарад. Так что проект по перехвату траффика я все-таки решил завершить.

Итак, опишу вкратце свои действия для подготовки mysql-ной базы данных.

service mysqld start

это чтобы запустить сервер базы данных (считаем, что он установлен).

Кроме того можно поставить «галку» в запускаемых сервисах в программе setup.

Далее:

mysqladmin create ip2

это создание базы данных для подсчета траффика.

            mysql mysql

Ø      update user set Password = PASSWORD(‘111') where User='root';

Ø      exit;

mysqladmin –u root reload

Это мы, войдя в клиент mysql,  назначили mysql-ный пароль для пользователя root (пароль для входа в операционную систему при этом останется прежним). Обратите внимание, что после каждого оператора в клиенте mysql  – обязательно точка с запятой.

mysql mysql  -u root –p111

> select * from mysql.db

Базы данных ip2 в выданной по этому запросу информации мы не находим.

Ø      grant all privileges on ip2.* to root@”5”;

Ø      flush privileges;

Теперь уже по «select»-у мы видим ip2.

Ø      update mysql.db set Host=”%” where Db=”ip2”;

Ø      flush privileges;

Ø      exit;

Это – чтоб нормально обращаться к таблицам базы данных mysql из программы на языке Си (аналогично – php). Здесь изложена просто последовательность действий, теорию же можно найти в источниках в Интернете. Примечание: возможно также придется еще запустить запрос (Update mysql.db set User=”” where Db=”ip2”;), а потом – (flush privileges;).

Далее:

mysql ip2  -u root –p111

Ø      create table Pack1(D1 DATE,

Ø      N1 INT,

Ø      IPS CHAR(15),

Ø      IPD CHAR(15),

Ø      N2  INT,

Ø      S1 CHAR(10),

Ø      PS CHAR(6),

Ø      PD CHAR(6));

Это создается таблица для запоминания данных из заголовков перехваченных пакетов.

D1 – дата

N1 – длина IP-пакета

IPS – адрес источника

IPD – адрес получателя

N2 – номер протокола

S1 – символьное обозначение протокола

PS – порт источника (если протокол – TCP)

PD – порт получателя (если протокол – TCP)

Далее, когда мы хотим начать сбор (то-есть перехват) информации о траффике, мы запускаем программу:

./sniff95 eth1 ip2

Первый параметр – это прослушиваемый интерфейс, второй – это база данных, где будет сохраняться информация (перед этим созданная нами).

Маршрутизатор очень сильно загружен выполняемой работой по фильтрованию траффика (iptables, а также redir) для почти что сотни рабочих станций. Но подключение вышеописанного перехвата незаметно (в смысле не замедляет работу для пользователей Интернета). База данных увеличивается в объеме примерно на 1 мегабайт в минуту. 5 часов работы (почти полный рабочий день) – около 300 Мб.

Интересующие нас отчеты можно получить, выполняя в программе mysql (это клиент mysql) следующие запросы. Для вывода IP-адресов, откуда скачивали информацию пользователи (и количества скачанных байт соответственно):

Select IPD,IPS,SUM(N1) FROM Pack1 GROUP BY IPD,IPS;

Просто для счета траффика по пользователям:

Select IPD, SUM(N1) FROM Pack1 GROUP BY IPD;

Можно прибегнуть к пакетному запуску mysql, кроме того, отчеты выводить в текстовый файл. Для этого создаем файл SQLuserssites.txt. В нем:

Select IPD,IPS,SUM(N1) FROM Pack1 GROUP BY IPD,IPS

INTO OUTFILE ‘/Work/Report.txt' FIELDS TERMINATED BY ‘ ‘;

Потом:

mysql ip2 –u root –p123 </Work/SQLuserssites.txt

Это все для первого запроса, для второго (статистика только по пользователям) – аналогично.

Возможно, придется задать права для записи сервером mysql в каталог /Work

chmod a+x /Work

Итак, файл, в который только-что мы выгрузили данные, будет служить исходным файлом для следующих двух программ – finduser и finduserDN. Нам ведь неплохо распознать IP-адреса получателей пакетов в имена пользователей, а IP-адреса источников пакетов – в доменные имена хостов.

Создаем таблицу User1 с полями

N1 – просто номер записи

IP – адрес машины пользователя в локальной сети

NAME – имя пользователя.

Далее создаем текстовый файл load.txt с адресами и именами пользователей (лучше латинскими буквами) вида:

1 192.168.0.5 Petrov

2 192.168.0.7 Ivanov

Потом выполняем в mysql оператор

Ø      load data infile ‘/Work/load.txt' into table User1 fields terminated by ‘ ‘;

Лучше, если в конце текстового файла не будет пустых строк (возможно появление «мусора» в таблице, который придется удалять.

Далее, чтоб преобразовать адреса пользователей в имена, запускаем

./finduser ip2 Report.txt Users.txt

Здесь первый параметр – это имя базы данных, второй – исходный файл с данными (экспортированный после выполнения запроса в клиенте mysql – смотри выше), а третий – файл с результатом.

Если нам нужны и доменные имена, то запускаем

./finduserDN ip2 Report.txt Sites.txt /Work/0.txt

Здесь все аналогично, только последний параметр – имя (и путь) временного файла (туда пишется результат выполнения программы host x.x.x.x, которая вызывается через функцию system).

Эти программы выполняются за вполне разумное время (вторая, которая обращается в Интернет через команду host, работает дольше).

Дополнение:

1)Компилируются программы так:

gcc -I/usr/include/mysql -L/usr/lib/mysql /Work/Pcap/sniff95.c -o /Work/Pcap/sniff95 -lm -lmysqlclient -lpcap -L. libKA

libKA – это моя библиотека, кроме того, так как для библиотеки pcap в этих строках не указано ее местонахождение, пришлось перекопировать одну из папок в текущий каталог и подправить заголовочный файл.

2) Очистить таблицу Pack1 можно, выполнив в клиенте mysql оператор

DELETE FROM Pack1;

3) Как уже упоминалось в одной из предыдущих статей, не стоит ожидать, что программа finduserDN (использующая host) выдаст все доменные имена в том виде, в каком они набирались в адресной строке броузера. В случае, если обратное преобразование выдает несколько адресов, строка в выходном файле будет помечена знаком вопроса. Можете сами поэкспериментировать с прямым и обратным преобразованием через команды host или nslookup.

Скачать исходники