LINUX.ORG.RU
Форум — Admin  

ulog + mysql — обработка данных


0

0

Делал по статье:

http://www.opennet.ru/base/net/traf_fprobe.txt.html

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

Вот подскажите. Интересуют сами запросы. Как правильно считать статистику? Пока интересует общая по дням.

Делаю:

SELECT (FROM_UNIXTIME(unix_secs,'%d')),SUM(doctets/1048576) FROM raw WHERE (dstaddr='222.222.222.222' OR dstaddr='192.168.1.1') GROUP BY (FROM_UNIXTIME(unix_secs,'%d')) LIMIT 30;

222.222.222.222 -- адрес на ифейсе смотрящий в нет.
192.168.1.1 -- адрес шлюза впн.

получаю некие данные:

+---------------------------------+----------------------+
| (FROM_UNIXTIME(unix_secs,'%d')) | SUM(doctets/1048576) |
+---------------------------------+----------------------+
| 21 | 292.9583 |
| 22 | 765.3390 |
| 23 | 1023.5575 |
| 24 | 783.2010 |
+---------------------------------+----------------------+
5 rows in set (2.72 sec)

Теперь сверяюсь с провом:

24.01.08 899.2744 M
23.01.08 1,794.2962 Mb
22.01.08 808.5388 Mb
21.01.08 487.2178 Mb

Что за ...? ))

Посмотрел я очередной раз, что вообще в базе:

mysql> SELECT unix_secs,srcaddr,dstaddr,doctets,SRCPORT,DSTport FROM raw WHERE FROM_UNIXTIME(unix_secs) LIKE '2008-01-22 12%' LIMIT 150;

+------------+-----------------+-----------------+---------+---------+--------- +
| unix_secs | srcaddr | dstaddr | doctets | SRCPORT | DSTport |
+------------+-----------------+-----------------+---------+---------+--------- +

| 1200992437 | 81.177.16.160 | 222.222.222.222 | 387 | 80 | 57398 |
| 1200992442 | 192.168.0.34 | 192.168.1.1 | 424 | 4265 | 110 |
| 1200992442 | 192.168.0.63 | 192.168.1.1 | 564 | 3805 | 110 |
| 1200992442 | 192.168.1.1 | 192.168.0.54 | 676 | 110 | 2462 |
| 1200992442 | 192.168.1.21 | 192.168.0.3 | 48 | 139 | 2663 |
| 1200992442 | 192.168.0.44 | 192.168.0.1 | 57 | 32855 | 53 |
| 1200992442 | 192.168.0.95 | 192.168.1.1 | 528 | 56553 | 110 |
| 1200992442 | 192.168.0.55 | 192.168.0.255 | 240 | 138 | 138 |
| 1200992442 | 192.168.0.1 | 192.168.0.44 | 276 | 53 | 32855 |
| 1200992442 | 192.168.0.3 | 192.168.1.37 | 5978 | 2558 | 631 |
| 1200992442 | 192.168.1.37 | 192.168.0.3 | 50126 | 631 | 2558 |
| 1200992442 | 192.168.0.3 | 192.168.1.21 | 88 | 2663 | 139 |
| 1200992442 | 192.168.1.21 | 192.168.0.3 | 581 | 445 | 2662 |
| 1200992442 | 192.168.0.54 | 192.168.1.1 | 632 | 2462 | 110 |
| 1200992442 | 192.168.1.1 | 192.168.0.95 | 676 | 110 | 56553 |
| 1200992442 | 192.168.1.1 | 192.168.0.63 | 744 | 110 | 3805 |
| 1200992442 | 192.168.0.44 | 194.67.23.102 | 627 | 53521 | 110 |
| 1200992442 | 192.168.0.3 | 192.168.1.21 | 1065 | 2662 | 445 |
| 1200992442 | 192.168.1.1 | 192.168.0.34 | 564 | 110 | 4265 |
| 1200992442 | 194.67.23.102 | 192.168.0.44 | 799 | 110 | 53521 |
| 1200992442 | 192.168.0.45 | 192.168.0.255 | 78 | 137 | 137 |
| 1200992447 | 192.168.0.1 | 192.168.0.92 | 421 | 53 | 32776 |
| 1200992447 | 192.168.0.54 | 64.12.24.133 | 552 | 3595 | 5190 |
| 1200992447 | 222.222.222.222 | 85.21.125.66 | 836 | 123 | 123 |
| 1200992447 | 85.21.125.66 | 222.222.222.222 | 836 | 123 | 123 |
| 1200992447 | 192.168.0.92 | 194.67.23.102 | 1375 | 37866 | 110 |
| 1200992447 | 194.67.23.102 | 192.168.0.92 | 6100 | 110 | 37866 |
| 1200992447 | 192.168.0.92 | 192.168.0.1 | 116 | 32776 | 53 |
| 1200992452 | 83.221.211.202 | 192.168.0.40 | 1059 | 110 | 3878 |
| 1200992452 | 194.33.191.69 | 192.168.0.98 | 76 | 123 | 123 |
| 1200992452 | 192.168.0.33 | 217.10.43.117 | 420 | 15149 | 64885 |
| 1200992452 | 192.168.0.36 | 64.12.26.76 | 2920 | 4438 | 443 |
| 1200992452 | 64.12.26.76 | 192.168.0.36 | 7008 | 443 | 4438 |
| 1200992452 | 192.168.0.50 | 194.33.191.69 | 76 | 123 | 123 |
| 1200992452 | 192.168.0.40 | 83.221.211.202 | 504 | 3878 | 110 |
| 1200992452 | 194.33.191.69 | 192.168.0.50 | 76 | 123 | 123 |
| 1200992452 | 213.27.31.136 | 192.168.0.40 | 706 | 110 | 3877 |
| 1200992452 | 193.125.143.140 | 192.168.0.200 | 76 | 123 | 123 |
| 1200992452 | 192.168.0.40 | 213.27.31.136 | 460 | 3877 | 110 |
| 1200992452 | 192.168.0.200 | 193.125.143.140 | 76 | 123 | 123 |
| 1200992452 | 192.168.0.98 | 194.33.191.69 | 76 | 123 | 123 |
| 1200992457 | 192.168.1.21 | 192.168.0.3 | 581 | 445 | 2664 |
| 1200992457 | 217.172.18.235 | 192.168.0.33 | 58 | 13545 | 15149 |
| 1200992457 | 192.168.0.115 | 192.168.0.1 | 104 | 36707 | 3128 |
| 1200992457 | 192.168.0.1 | 192.168.0.115 | 104 | 3128 | 36707 |
+------------+-----------------+-----------------+---------+---------+--------- +

так вот. Встречается такое:

| 1200992452 | 192.168.0.36 | 64.12.26.76 | 2920 | 4438 | 443 |
| 1200992452 | 64.12.26.76 | 192.168.0.36 | 7008 | 443 |

т.е. dstaddr может быть не только мой или впн шлюз.

Соответственно, почему бы не попробовать запрос: сумма всех пакетов из локальной сети в не локальную сеть. Получилась тоже чепуха :((

mysql> SELECT (FROM_UNIXTIME(unix_secs,'%d')),SUM(doctets/1048576) FROM raw WHERE dstaddr LIKE '192.168.0.%' AND srcaddr NOT LIKE '192.168.0.%' GROUP BY (FROM_UNIXTIME(unix_secs,'%d')) LIMIT 30;
+---------------------------------+----------------------+
| (FROM_UNIXTIME(unix_secs,'%d')) | SUM(doctets/1048576) |
+---------------------------------+----------------------+
| 21 | 140.9408 |
| 22 | 248.0608 |
| 23 | 1014.2998 |
| 24 | 296.1697 |
+---------------------------------+----------------------+
5 rows in set (2.80 sec)

Так как же считать? ((((

★★★★★

Если хочешь с провайдером сверки делать, то в таблице должна быть колонка под интерфейс, т.е. все входящие на интерфейс. По нему и считай по дням.

Plazmid
()

Для начала советую не делить на 1048576, насколько я знаю, большинство провайдеров полагает, что 1 МегаБайт это 1 миллион байт = 1E6.

Потом, советую занести правила в iptables (по аналогии с теми адресами, по которым вы делали запросы к БД, например "iptables -I INPUT -d 222.222.222.222").

Сделать раз в час вызов команды "iptables -L -n -v -x" с перенаправлением в файл для записи счетчиков iptables, потом выполнить запросы к БД и сравнить со счетчиками iptables (это необходимо так как при высокой нагрузке ULOG может терять пакеты) если эти значения будут близки или совпадут, то хорошо.

Ну еще бывает, что провайдер считает пакеты на интерфейсе, учитывая в том числе arp-запросы. Можно попробовать посмотреть счетчики байт на интерфейсе (только учитывать что на i386 они 32х разрядные).

P.S. Надеюсь, что в ULOG пишутся все пакеты, а не после того, как они прошли -j DROP правила

mky ★★★★★
()
Ответ на: комментарий от mky

так а сам подход правильный? Как дожен выглядеть запрос для этого? Мускул я кое-как знаю, мне достаточно алгоритм ))

>> P.S. Надеюсь, что в ULOG пишутся все пакеты, а не после того, как они прошли -j DROP правила

Если честно, то хз. Я с улогом первый раз столкнулся, а иптейблс только начинаю щупать... До этого Фрю и ОпенБСД на шлюзе держал, используя trafd + pf, поэтому смутно представляю... Но для того и статью привёл, по которой делал, чтобы можно было просотреть при желании ))

vovans ★★★★★
() автор топика
Ответ на: комментарий от vovans

Если вы делали по статье, то у вас должно быть всего одно правило:

iptables -I FORWARD -j ULOG

Это правило пишет все пакеты которые идут через цепочку FORWARD, но пакеты, у которых dst-ip пренадлежит серверу идут через INPUT, поэтому непонятно, откуда взялись записи типа:

| 1200992437 | 81.177.16.160 | 222.222.222.222 | 387 | 80 | 57398 |

>так а сам подход правильный?

Вроде правильный, только это "GROUP BY (FROM_UNIXTIME(unix_secs,'%d'))", ИМХО, перебор. Уж лучше дату сначала (до выполнения запроса) преобразовать в секунды (например, "date -d '2008-01-26' '+%s'"), а потом указать условие вхождения unix_secs в нужный интервал.

mky ★★★★★
()
Ответ на: комментарий от mky

>> Это правило пишет все пакеты которые идут через цепочку FORWARD, но пакеты, у которых dst-ip пренадлежит серверу идут через INPUT, поэтому непонятно, откуда взялись записи типа:

сам добавил на все цепочки. Сперва надо определится, что нужно, а потом уже... Вот я и решил что форварда недостаточно... Сервак-то тоже может траф хавать ))

>> только это "GROUP BY (FROM_UNIXTIME(unix_secs,'%d'))", ИМХО, перебор

это всё детали. В Пёрле есть для этого штатные средства. И если недо, ими воспользуюсь. Но пока неясно что надо )) Пока хочу получить реальную картину, а потом уже все эти тонкости реализации... А на этапи тестирования именно этот подход показался самым удобным. Разве нет? ))

vovans ★★★★★
() автор топика
Ответ на: комментарий от vovans

>Но пока неясно что надо ))

С этого и надо было начинать :) ИМХО, надо в iptables, в -t mangle или еще раньше запретить весь трафик из локалки с не локальным (! 192.168.1.0/24) ip-адресами (или включить rp-фильтр на локальный интерфейс). Тогда можно счиать все пакеты, у которых src-адрес не 192.168.1.0/24 и не 222.222.222.222 входящими из интернета. Сделате такой запрос, не переводите сумму в двоичные МегаБайты и сравните с данными провайдера...

mky ★★★★★
()
Ответ на: комментарий от mky

Попробуем в понедельник.

Что ты пристал к делению на 1024/1024? ))) Ну, это ж единицы! Копейки. Я бы по такой ерунде не стал бы и в форум писать ))) Просто расхождение у меня больно огромно :((

vovans ★★★★★
() автор топика
Ответ на: комментарий от mky

Может такой запрос ближе к телу будет? ;))

mysql> SELECT (FROM_UNIXTIME(unix_secs,'%d')),SUM(doctets/1048576) FROM raw WHERE (dstaddr='87.117.29.122' OR dstaddr='192.168.1.1' OR (srcaddr LIKE '192.168.0.%' AND dstaddr NOT LIKE '192.168.0.%')) GROUP BY (FROM_UNIXTIME(unix_secs,'%d')) LIMIT 30;
+---------------------------------+----------------------+
| (FROM_UNIXTIME(unix_secs,'%d')) | SUM(doctets/1048576) |
+---------------------------------+----------------------+
| 11 | 0.0087 |
| 21 | 370.6479 |
| 22 | 911.1647 |
| 23 | 1292.5208 |
| 24 | 936.1202 |
| 25 | 654.0140 |
| 26 | 19.0727 |
| 27 | 10.9003 |
+---------------------------------+----------------------+

Почти совпадает! Ну, за 21-е число я статистику не с самого начала включил. А за 23-е она вообще какая-то аномальная... Завтра сделаю тетализацию трафика по всем и подумаю. могло ли такое быть или это пров грибов наелся...

vovans ★★★★★
() автор топика
Вы не можете добавлять комментарии в эту тему. Тема перемещена в архив.
Admin