Простейший подсчёт трафика на сервере ng_ipacct, mysql

Обсуждаем сайт и форум.

Модератор: f0s

Аватара пользователя
E-Wind
сержант
Сообщения: 155
Зарегистрирован: 2008-05-14 16:19:54
Откуда: Санкт-Петербург

Простейший подсчёт трафика на сервере ng_ipacct, mysql

Непрочитанное сообщение E-Wind » 2009-04-21 13:46:06

Как ответ на пост http://forum.lissyara.su/viewtopic.php?f=4&t=16818

Начинал по статье http://www.lissyara.su/?id=1750
Наворотов не требовалось. Только все скидывать в MySQL.

Потом SQL запросами можно выбирать по дням, портам, направлению.
Как вспомню, приложу )

Можно из этого мини-статейку сделать.

Имеется шлюз - интерфейсы em0 - внутрь и em1 - наружу, FreeBSD 7.1

Читаем вышеуказанную статью.

Собираем ядро с доп. опциями:

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

options         NETGRAPH
options         NETGRAPH_ETHER
options         NETGRAPH_SOCKET
options         NETGRAPH_TEE
Далее

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

cd /usr/ports/net-mgmt/ng_ipacct
make install clean
Конфиг /usr/local/etc/ng_ipacct.conf

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

# $FreeBSD: ports/net-mgmt/ng_ipacct/files/ng_ipacct.conf,v 1.6 2008/06/03 10:40:16 skv Exp $
#
# Please read and meditate on netgraph(4), ipacctctl(8) and ngctl(8).

# Enable ng_ipacct (i.e. enable run startup script "ng_ipacct.sh")
#ng_ipacct_enable="YES"

# Enable kernel modules loading.
# On "ng_ipacct.sh start" all kernel modules specified
# in ${ng_ipacct_modules_list} will be loaded.
# Note: on "ng_ipacct.sh stop" only "ng_ipacct" will be unloaded.
ng_ipacct_modules_load="YES"

# Netgraph can load required ng_* modules automatically on the hook creation
# - except for "ng_ether". Generally, modules preloading is recommended.
# Do not add to this list modules which are statically compiled into kernel.
#ng_ipacct_modules_list="netgraph ng_ether ng_cisco ng_socket ng_tee ng_ipacct"
ng_ipacct_modules_list="ng_ipacct"

# List of monitored interfaces. For each interface additional vars must be
# specified in corresponding variables 'ng_ipacct_IFACE_*'.
# See examples below.
#ng_ipacct_interfaces="xl0 cx0 vpn0"
ng_ipacct_interfaces="em0 em1"

# Default start/stop scripts.
#
# Single quotes are required to preserve newlines.
# '%%iface%%' will be automatically expanded with a relevant interface.
# This feature should be applied to use indentical rules
# for similar interfaces.
ng_ipacct_default_ether_start='
        mkpeer %%iface%%: tee lower right
        name %%iface%%:lower %%iface%%_tee
        connect %%iface%%: lower upper left

        mkpeer %%iface%%_tee: ipacct right2left %%iface%%_in
        name %%iface%%_tee:right2left %%iface%%_ip_acct
        connect %%iface%%_tee: %%iface%%_ip_acct: left2right %%iface%%_out
'

ng_ipacct_default_ether_stop='
        shutdown %%iface%%_ip_acct:
        shutdown %%iface%%_tee:
        shutdown %%iface%%:
'

ng_ipacct_bpf_ether_start='
        mkpeer %%iface%%: tee lower right
        name %%iface%%:lower %%iface%%_tee
        connect %%iface%%: lower upper left

        mkpeer %%iface%%_tee: bpf right2left %%iface%%_in
        name %%iface%%_tee:right2left %%iface%%_bpf
        connect %%iface%%_tee: right2left left2right %%iface%%_out

        mkpeer %%iface%%_bpf: ipacct %%iface%%_match_in %%iface%%_in
        name %%iface%%_bpf:%%iface%%_match_in %%iface%%_ip_acct
        connect %%iface%%_bpf: %%iface%%_ip_acct: %%iface%%_match_out %%iface%%_out
'

ng_ipacct_bpf_ether_stop='
        shutdown %%iface%%_ip_acct:
        shutdown %%iface%%_bpf:
        shutdown %%iface%%_tee:
        shutdown %%iface%%:
'

# em0 - internal interface (x10 in demo)

ng_ipacct_em0_dlt="EN10MB"      # required line; see ipacctctl(8)
ng_ipacct_em0_threshold="15000" # '5000' by default
ng_ipacct_em0_verbose="yes"     # 'yes' by default
#ng_ipacct_em0_saveuid="yes"    # 'no' by default
ng_ipacct_em0_savetime="yes"    # 'no' by default
ng_ipacct_em0_start=${ng_ipacct_default_ether_start}
ng_ipacct_em0_stop=${ng_ipacct_default_ether_stop}
ng_ipacct_em0_checkpoint_script="/root/scripts/ng_ipacct.sh em0"


# em1 - internal interface (x10 in demo)

ng_ipacct_em1_dlt="EN10MB"      # required line; see ipacctctl(8)
ng_ipacct_em1_threshold="15000" # '5000' by default
ng_ipacct_em1_verbose="yes"     # 'yes' by default
#ng_ipacct_em1_saveuid="yes"    # 'no' by default
ng_ipacct_em1_savetime="yes"    # 'no' by default
ng_ipacct_em1_start=${ng_ipacct_default_ether_start}
ng_ipacct_em1_stop=${ng_ipacct_default_ether_stop}
ng_ipacct_em1_checkpoint_script="/root/scripts/ng_ipacct.sh em1"
ВНИМАНИЕ НА СТРОКИ В КОНФИГЕ ВЫШЕ (в двух местах)

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

ng_ipacct_em0_checkpoint_script="/root/scripts/ng_ipacct.sh em0"
ng_ipacct_em1_checkpoint_script="/root/scripts/ng_ipacct.sh em1"
В /root/scripts/ng_ipacct.sh - И сделать исполняемым

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

#!/bin/sh
IPACCTCTL="/usr/local/sbin/ipacctctl"
IFACE=$1

$IPACCTCTL ${IFACE}_ip_acct:$IFACE checkpoint
$IPACCTCTL ${IFACE}_ip_acct:$IFACE show | awk '{print "INSERT INTO `dbs_traffic`.`traffic_'$IFACE'` (`src_ip`,`src_port`,`dst_ip`,`dst_port`,`protocol`,`packets`,`bytes`,`unixtime`) VALUES (\""$1"\",\""$2"\",\""$3"\",\""$4"\",\""$5"\",\""$6"\",\""$7"\",\""$8"\"); "}' | /usr/local/bin/mysql -u <ИМЯ ПОЛЬЗОВАТЕЛЯ> -p<ПАРОЛЬ> <dbs_traffic>
$IPACCTCTL ${IFACE}_ip_acct:$IFACE clear
В /etc/rc.conf

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

ng_ipacct_enable="YES"
В MySql

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

-- phpMyAdmin SQL Dump

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- База данных: `dbs_traffic`
--

-- --------------------------------------------------------

--
-- Структура таблицы `traffic_em0`
--

CREATE TABLE IF NOT EXISTS `traffic_em0` (
  `src_ip` varchar(15) NOT NULL,
  `src_port` int(11) NOT NULL,
  `dst_ip` varchar(15) NOT NULL,
  `dst_port` int(11) NOT NULL,
  `protocol` int(11) NOT NULL,
  `packets` int(11) NOT NULL,
  `bytes` int(11) NOT NULL,
  `unixtime` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=cp1251;

-- --------------------------------------------------------

--
-- Структура таблицы `traffic_em1`
--

CREATE TABLE IF NOT EXISTS `traffic_em1` (
  `src_ip` varchar(15) NOT NULL,
  `src_port` int(11) NOT NULL,
  `dst_ip` varchar(15) NOT NULL,
  `dst_port` int(11) NOT NULL,
  `protocol` int(11) NOT NULL,
  `packets` int(11) NOT NULL,
  `bytes` int(11) NOT NULL,
  `unixtime` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=cp1251;
В крон

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

*/5     *       *       *       *       /usr/local/etc/rc.d/ng_ipacct checkpoint
Примеры SQL (интерфейсы em0 - внутрь и em1 - наружу)

Весь входящий трафик по портам за февраль

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

SELECT FROM_UNIXTIME(unixtime,'%M') as Mounth, (SUM(bytes)/(1024*1024)) as Megabytes, dst_ip as IP, src_port as Port 
FROM traffic_em1 
WHERE 
dst_ip='<Внешний IP Шлюза>' 
AND unixtime > 1233435600 
AND unixtime < 1235854800 
GROUP BY src_port
Order By Megabytes DESC
Весь входящий трафик по месяцам

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

SELECT FROM_UNIXTIME(unixtime,'%M') as Mounth, (SUM(bytes)/(1024*1024)) as Megabytes, dst_ip as IP 
FROM traffic_em1 
WHERE 
dst_ip='<Внешний IP Шлюза>' 
GROUP BY Mounth
Order By Mounth
Сколько машинки скачали по 80-му порту за февраль (для машин с ip, начинающихся на "192.168.0.")

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

SELECT 
  FROM_UNIXTIME(unixtime,'%M') as Mounth, 
  (SUM(bytes)/(1024*1024)) as Megabytes, 
  dst_ip as IP, src_port as Port 
FROM traffic_em0 
WHERE 
    src_port = 80
AND unixtime > 1233435600   
AND unixtime < 1235854800 
AND dst_ip LIKE '192.168.0.%'

Group By dst_ip 
ORDER BY Megabytes Desc
И сколько кем всего скачано из интернета за февраль по всем портам (за вычетом скачанного со шлюза (samba))

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

SELECT 
  FROM_UNIXTIME(unixtime,'%M') as Mounth, 
  (SUM(bytes)/(1024*1024)) as Megabytes, 
  dst_ip as IP FROM traffic_em0 
WHERE 
       unixtime > 1233435600
AND unixtime < 1235854800 
AND dst_ip LIKE '192.168.0.%'
AND src_ip <> '<Внутренний IP шлюза>'
AND dst_ip <> '<Внутренний IP шлюза>'

Group By dst_ip 
ORDER BY Megabytes Desc

Хостинговая компания Host-Food.ru
Хостинг HostFood.ru
 

Услуги хостинговой компании Host-Food.ru

Хостинг HostFood.ru

Тарифы на хостинг в России, от 12 рублей: https://www.host-food.ru/tariffs/hosting/
Тарифы на виртуальные сервера (VPS/VDS/KVM) в РФ, от 189 руб.: https://www.host-food.ru/tariffs/virtualny-server-vps/
Выделенные сервера, Россия, Москва, от 2000 рублей (HP Proliant G5, Intel Xeon E5430 (2.66GHz, Quad-Core, 12Mb), 8Gb RAM, 2x300Gb SAS HDD, P400i, 512Mb, BBU):
https://www.host-food.ru/tariffs/vydelennyi-server-ds/
Недорогие домены в популярных зонах: https://www.host-food.ru/domains/

papenkin
проходил мимо
Сообщения: 7
Зарегистрирован: 2008-07-22 18:41:29

Re: Простейший подсчёт трафика на сервере ng_ipacct, mysql

Непрочитанное сообщение papenkin » 2009-04-23 13:33:04

Тоже сейчас занимаюсь сиим чудом, сразу стоит отметить:
1. Складывать в базу лучше перлом - сильно быстрее получается
2. Не стоит использовать VARCHAR для хранения ip - долго искать потом.
3. Стоит использовать индексы.

Откуда взялись такие мысли: на двух 100 мбитных каналах в базу попадает очень много записей, за полчаса "набегает" несколько десятков тысяч записей - поэтому вопрос быстродействия стоит довольно остро. Так и не решил пока что и как можно оптимизировать, но пока думаю насчет ежеминутного сброса данных в базу с неким суммированием.

Аватара пользователя
hizel
дядя поня
Сообщения: 9031
Зарегистрирован: 2007-06-29 10:05:02
Откуда: Выборг

Re: Простейший подсчёт трафика на сервере ng_ipacct, mysql

Непрочитанное сообщение hizel » 2009-04-24 12:43:01

такую статистику не агрегированную на чуть более толстых каналах никто в sql не хранит :-)
В дурацкие игры он не играет. Он просто жуткий, чу-чу, паровозик, и зовут его Блейн. Блейн --- это Боль.

Аватара пользователя
ADRE
майор
Сообщения: 2641
Зарегистрирован: 2007-07-26 8:53:49
Контактная информация:

Re: Простейший подсчёт трафика на сервере ng_ipacct, mysql

Непрочитанное сообщение ADRE » 2009-05-14 9:45:02

papenkin писал(а):Тоже сейчас занимаюсь сиим чудом, сразу стоит отметить:
1. Складывать в базу лучше перлом - сильно быстрее получается
2. Не стоит использовать VARCHAR для хранения ip - долго искать потом.
3. Стоит использовать индексы.

Откуда взялись такие мысли: на двух 100 мбитных каналах в базу попадает очень много записей, за полчаса "набегает" несколько десятков тысяч записей - поэтому вопрос быстродействия стоит довольно остро. Так и не решил пока что и как можно оптимизировать, но пока думаю насчет ежеминутного сброса данных в базу с неким суммированием.
я немного туп: полчаса*2*8 = 480 минут * 10 000 000/30 = 333334 *480 = 160000000 записей в рабочее время, соответственно в БД за месяц = 4800 000 000 строк ну и соответственно сорт будет где-то около 30 минут ) если не больше....
советую в какой-то период времени, например часа в 4 утра пересохранить полученные запросы, оставляя порт и IP адрес юзера, если более детальная информация, то сортить еще по отправителю и порту... иначе просто не как статистику не выковырять, выковырять, конечно, но долго.....
//del

paradox
проходил мимо
Сообщения: 11620
Зарегистрирован: 2008-02-21 18:15:41

Re: Простейший подсчёт трафика на сервере ng_ipacct, mysql

Непрочитанное сообщение paradox » 2009-05-14 10:12:21

все елементанрно
стаститику нужно разбивать по разным таблицам

каждый месяц к примеру создавать новую таблицу
тогда для поиска будет проще

Аватара пользователя
ADRE
майор
Сообщения: 2641
Зарегистрирован: 2007-07-26 8:53:49
Контактная информация:

Re: Простейший подсчёт трафика на сервере ng_ipacct, mysql

Непрочитанное сообщение ADRE » 2009-05-15 5:19:43

paradox писал(а):все елементанрно
стаститику нужно разбивать по разным таблицам

каждый месяц к примеру создавать новую таблицу
тогда для поиска будет проще
а когда это еще отсортировано, то вообще сказка )
//del

Larin
лейтенант
Сообщения: 975
Зарегистрирован: 2008-01-29 20:08:41
Откуда: РБ, Минск

Re: Простейший подсчёт трафика на сервере ng_ipacct, mysql

Непрочитанное сообщение Larin » 2009-05-27 14:42:38

кстати, решил я попробовать как оно работает. работает. пишет. только вот вопрос.
в последней колонке оно должно писать unix время так? т.е. кол-во секунд с 80-го года.
а у меня оно пишет либо -1 любо 100. что бы это значило?

paradox
проходил мимо
Сообщения: 11620
Зарегистрирован: 2008-02-21 18:15:41

Re: Простейший подсчёт трафика на сервере ng_ipacct, mysql

Непрочитанное сообщение paradox » 2009-05-27 15:12:20

зачем угадывать
вывод давай сюда

Larin
лейтенант
Сообщения: 975
Зарегистрирован: 2008-01-29 20:08:41
Откуда: РБ, Минск

Re: Простейший подсчёт трафика на сервере ng_ipacct, mysql

Непрочитанное сообщение Larin » 2009-05-27 15:26:08

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

217.21.50.123	51332	93.84.114.70	80	6	9	2239	100
217.21.50.123	51332	93.84.114.70	80	6	8	320	-1
217.21.50.123	25	93.139.85.182	12781	6	4	530	0
217.21.50.123	51275	91.149.189.42	80	6	18	1398	100
217.21.50.123	65355	91.149.189.42	80	6	11	1033	100
217.21.50.123	62993	93.186.225.211	80	6	11	1408	100
217.21.50.123	51326	81.222.128.113	80	6	5	1077	100
217.21.50.123	50227	88.212.196.66	80	6	5	786	100
217.21.50.123	65474	74.125.39.102	80	6	4	795	100
217.21.50.123	65474	74.125.39.102	80	6	1	52	-1
что-то аля такого

paradox
проходил мимо
Сообщения: 11620
Зарегистрирован: 2008-02-21 18:15:41

Re: Простейший подсчёт трафика на сервере ng_ipacct, mysql

Непрочитанное сообщение paradox » 2009-05-27 15:42:56

бардак какойто
то время
но почему они его выводят знаковым число непонятно
там беззнаковое число должно быть

Larin
лейтенант
Сообщения: 975
Зарегистрирован: 2008-01-29 20:08:41
Откуда: РБ, Минск

Re: Простейший подсчёт трафика на сервере ng_ipacct, mysql

Непрочитанное сообщение Larin » 2009-05-27 15:52:56

paradox писал(а):бардак какойто
то время
но почему они его выводят знаковым число непонятно
там беззнаковое число должно быть
угу. оно у мну модулями подгружено. попробую завтра в ядро вкомпилить.

paradox
проходил мимо
Сообщения: 11620
Зарегистрирован: 2008-02-21 18:15:41

Re: Простейший подсчёт трафика на сервере ng_ipacct, mysql

Непрочитанное сообщение paradox » 2009-05-27 15:57:40

а смысл?
оно глючит полюбому

Larin
лейтенант
Сообщения: 975
Зарегистрирован: 2008-01-29 20:08:41
Откуда: РБ, Минск

Re: Простейший подсчёт трафика на сервере ng_ipacct, mysql

Непрочитанное сообщение Larin » 2009-05-27 16:10:17

paradox писал(а):а смысл?
оно глючит полюбому
только почему?:)
у других вроде работает. правда у меня там

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

proxy# uname -r
6.2-RELEASE
proxy# 

infrasty
проходил мимо
Сообщения: 1
Зарегистрирован: 2009-06-01 9:23:22

Re: Простейший подсчёт трафика на сервере ng_ipacct, mysql

Непрочитанное сообщение infrasty » 2009-06-01 9:26:55

Видимо никто уже не читает конфиги...

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

/usr/local/etc/ng_ipacct.conf
ng_ipacct_xl0_saveuid="yes"     # 'no' by default
ng_ipacct_xl0_savetime="no"     # 'no' by default
-1 - это то, что работает под root-ом, 100-ка это скорее всего Ваш squid, а столбец со временем просто отсутствует.