Рассказ о том, как я начал дружить DLINK dfl-260 и syslog-ng + mysql под ubuntu

Установка syslog

 Ставим syslog  и либу подключения к mysql

# apt-get install syslog-ng libdbi-mysql 

Навсякий случай,  создам резеврную копию 

# cp /etc/syslog-ng/syslog-ng.conf /etc/syslog-ng/syslog-ng.conf.backup 

Открываем наш файлик 

nano /etc/syslog-ng/syslog-ng.conf

 и приводи его к такому виду

@version: 3.3
# настраиваем новый источник — сеть.
# теперь syslog-ng будет слушать 514-й udp-порт  по всем интерфейсам, 

# т.к.меня сервак с одним интерфейсом в локальной сети, я тут ничего не 

# пишу.

source s_udp { udp(); };


# всё что будет направляться в это хранилище будет
# складываться  сразу в базу

destination d_mysql {

sql(type(mysql)
# Стандартный набор параметров доступа к БД - хост, логин, пароль
    host("localhost") username("syslog") password("syslog")
    database("syslog")
    table("logs")
    # Столбцы БД, в которые вы планируете записывать сообщения
    columns("host", "facility", "priority", "level", "tag", "datetime", "program", "msg", "seq")
    # Какую часть сообщения в какой столбец записывать
    values("$HOST_FROM", "$FACILITY", "$PRIORITY", "$LEVEL", "$TAG", "$YEAR-$MONTH-$DAY $HOUR:$MIN:$SEC", "$PROGRAM", "$MSG", "$SEQNUM")
    # Для каких столбцов создавать индексы для быстрого поиска
    indexes("host", "facility", "priority", "datetime", "program", "seq"));
};

log { source(s_udp);  destination(d_mysql); };

В mysql создаем базу таблицу

CREATE DATABASE `syslog` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Я делал через phpmyadmin при создание нового пользователя syslog по этому только создал таблицу

CREATE TABLE logs (

id bigint unsigned NOT NULL AUTO_INCREMENT,

host varchar(128) default NULL,

facility varchar(10) default NULL,

priority varchar(10) default NULL,

level varchar(10) default NULL,

tag varchar(10) default NULL,

datetime datetime default NULL,

program varchar(15) default NULL,

msg text,

seq bigint unsigned NOT NULL default '0',

counter int(11) NOT NULL default '1',

fo datetime default NULL,

lo datetime default NULL,

PRIMARY KEY (id),

KEY datetime (datetime),

KEY sequence (seq),

KEY priority (priority),

KEY facility (facility),

KEY program (program),

KEY host (host)

) ENGINE=MyISAM; 

Рестартим, смотрим на ошибки

root@adm:/var/www# /etc/init.d/syslog-ng restart
 * Stopping system logging syslog-ng  [ OK ]
 * Starting system logging syslog-ng   [ OK ]
root@adm:/var/www#

 Теперь настроим D-link DFL 260e

1.  Настраиваем направление вывода syslog информации

На закладке General задаем

• Имя службы syslog - любое
• IP адрес - компьютера на котором будут орабатываться пакеты ("syslog сервер").
• Facility - local0
• Port - 514

На закладке Severity Filter настраиваем как на иллюстрации, т.е. убариаем всё и оставляем только (6) Info

2. Настраиваем правила, информация о которых должна попадать в лог

Заходим в нужное правило

На закладке Log выставляем галочку и выбираем уровень Info.
Повторяем эту процедуру для всех правил, которые нас интересуют с точки зрения подсчета траффика. Рекомендуется правила назвать со смыслом, чтобы при анализе было понятно о каком траффике идет речь. Например: входящие соединения "NAT_in", исходящие соединения "NAT_out", PPTP соединения - соответственно "PPTP_in" и "PPTP_out"

3. Сохраняем конфигурацию (Configuration - Save&Activate). На этом настройка роутера закончена

Создание скрипта ротации журнала.

 Логи больше месяца хранить бессмысленно для меня, по этому скриптик на очистку и бекап

root@adm:/# touch /home/server/syslog_rotate.sh
root@adm:/# chmod +x /home/server/syslog_rotate.sh
root@adm:/# nano /home/server/syslog_rotate.sh

#!/bin/sh

dbuser=syslog
dbpwd=syslog

last=`date --date='-1 month' +%b`;

cat << SQL | mysql -u $dbuser --password=$dbpwd syslog  || exit 1

DELETE FROM syslog.logs WHERE datetime < ( DATE_SUB(NOW(), INTERVAL 1 MONTH )); 
SQL

Засунем это дело в кронтаб  и будем выполнять раз в месяц

crontab -e

# m h  dom mon dow   command
#* * * * * выполняемая команда
#- - - - -
#| | | | |
#| | | | ----- День недели (0 - 7) (Воскресенье =0 или =7)
#| | | ------- Месяц (1 - 12)
#| | --------- День (1 - 31)
#| ----------- Час (0 - 23)
#------------- Минута (0 - 59)
15 2 1 * * /home/server/syslog_rotate.sh

Таблица для  сбора статистики с dfl

CREATE TABLE `syslog`.`dfl` (

`ID` int( 11 ) NOT NULL AUTO_INCREMENT ,
`DATATIME` datetime NOT NULL ,
`rule` text NOT NULL ,
`connipproto` varchar( 4 ) NOT NULL ,
`connrecvif` varchar( 10 ) NOT NULL ,
`connsrcip` varchar( 15 ) NOT NULL ,
`connsrcport` int( 11 ) NOT NULL ,
`conndestif` varchar( 4 ) NOT NULL ,
`conndestip` varchar( 15 ) NOT NULL ,
`conndestport` int( 11 ) NOT NULL ,
`origsent` int( 11 ) NOT NULL ,
`termsent` int( 11 ) NOT NULL ,
`conntime` int( 11 ) NOT NULL ,
PRIMARY KEY ( `ID` )

) 

Триггер поля message. 

use syslog;
delimiter |

DROP TRIGGER IF EXISTS `parse_msg` |

CREATE TRIGGER `parse_msg` BEFORE INSERT ON `syslog`.`logs`
FOR EACH ROW
begin
    DECLARE i INTEGER;
    DECLARE var1 text;
    set i:=(SELECT LOCATE('id=' ,  NEW.msg ))+(SELECT LENGTH('id=' ));
    set var1:=(SELECT SUBSTRING(NEW.msg,i,(CONVERT((SELECT LOCATE(SPACE(1),NEW.msg,i)),SIGNED)-i+1)));
    IF var1='00600005' THEN
        BEGIN
        DECLARE rule VARCHAR(50);
        DECLARE connipproto VARCHAR(4);
        DECLARE connrecvif VARCHAR(10);
        DECLARE connsrcip VARCHAR(15);
        DECLARE connsrcport INTEGER;
        DECLARE conndestif VARCHAR(4) ;
        DECLARE conndestip VARCHAR(15);
        DECLARE conndestport INTEGER;
        DECLARE origsent INTEGER;
        DECLARE termsent INTEGER;
        DECLARE conntime INTEGER;
        set i := (SELECT LOCATE('rule=' ,  NEW.msg ))+(SELECT LENGTH('rule=' ));
        set rule :=(SELECT SUBSTRING(NEW.msg,i,((SELECT LOCATE(SPACE(1),NEW.msg,i))-i+1)));
        set i := (SELECT LOCATE( 'connipproto='  ,  NEW.msg ))+(SELECT LENGTH('connipproto=' ));
        set connipproto :=(SELECT SUBSTRING(NEW.msg,i,((SELECT LOCATE(SPACE(1),NEW.msg,i))-i+1)));
        set i := (SELECT LOCATE( 'connrecvif='  ,  NEW.msg ))+(SELECT LENGTH('connrecvif=' ));
        set connrecvif :=(SELECT SUBSTRING(NEW.msg,i,((SELECT LOCATE(SPACE(1),NEW.msg,i))-i+1)));
        set i := (SELECT LOCATE( 'connsrcip='  ,  NEW.msg ))+(SELECT LENGTH('connsrcip=' ));
        set connsrcip :=(SELECT SUBSTRING(NEW.msg,i,((SELECT LOCATE(SPACE(1),NEW.msg,i))-i+1)));
        set i := (SELECT LOCATE( 'connsrcport='  ,  NEW.msg ))+(SELECT LENGTH('connsrcport=' ));
        set connsrcport :=(SELECT SUBSTRING(NEW.msg,i,((SELECT LOCATE(SPACE(1),NEW.msg,i))-i+1)));
        set i := (SELECT LOCATE( 'conndestif='  ,  NEW.msg ))+(SELECT LENGTH('conndestif=' ));
        set conndestif :=(SELECT SUBSTRING(NEW.msg,i,((SELECT LOCATE(SPACE(1),NEW.msg,i))-i+1)));
        set i := (SELECT LOCATE( 'conndestip='  ,  NEW.msg ))+(SELECT LENGTH('conndestip=' ));
        set conndestip :=(SELECT SUBSTRING(NEW.msg,i,((SELECT LOCATE(SPACE(1),NEW.msg,i))-i+1)));
        set i := (SELECT LOCATE( 'conndestport='  ,  NEW.msg ))+(SELECT LENGTH('conndestport=' ));
        set conndestport :=(SELECT SUBSTRING(NEW.msg,i,((SELECT LOCATE(SPACE(1),NEW.msg,i))-i+1)));
        set i := (SELECT LOCATE( 'origsent='  ,  NEW.msg ))+(SELECT LENGTH('origsent=' ));
        set origsent :=(SELECT SUBSTRING(NEW.msg,i,((SELECT LOCATE(SPACE(1),NEW.msg,i))-i+1)));
        set i := (SELECT LOCATE( 'termsent='  ,  NEW.msg ))+(SELECT LENGTH('termsent=' ));
        set termsent :=(SELECT SUBSTRING(NEW.msg,i,((SELECT LOCATE(SPACE(1),NEW.msg,i))-i+1)));
        set i := (SELECT LOCATE( 'conntime='  ,  NEW.msg ))+(SELECT LENGTH('conntime=' ));
        set conntime :=(SELECT SUBSTRING(NEW.msg,i,(SELECT LENGTH(NEW.msg)-i+1)));
        INSERT INTO `syslog`.`dfl`( `DATATIME`, `rule`, `connipproto`,  `connrecvif`, `connsrcip`,`connsrcport`, `conndestif`,`conndestip` ,`conndestport` ,`origsent` ,`termsent` ,  `conntime` )
               VALUES( NEW.datetime, rule , connipproto, connrecvif, connsrcip, connsrcport , conndestif ,  conndestip , conndestport , origsent,termsent, conntime  ) ;
    end;
    END IF;
END |
DELIMITER ;


Теперь надо найти/написать станичку для группировке записей,  возможно рисования графиков и т.д.