这里还是承接同事的需求，想要将所有网络设备的日志汇总后在一个平台上进行展示。在上一篇syslog-ng日志应用详解中提到了，通过syslog-ng创建日志集中服务器。可以通过syslog-ng+loganalyzer实现将接受采集过来的日志入库并在web页面上统一展示。

一、数据库配置

1、创建数据库及表结构

 1mysql> CREATE DATABASE Syslog character set utf8;
 2mysql> USE Syslog;
 3mysql> CREATE TABLE SystemEvents
 4(
 5        ID int unsigned not null auto_increment primary key,
 6        CustomerID bigint,
 7        ReceivedAt datetime NULL,
 8        DeviceReportedTime datetime NULL,
 9        Facility smallint NULL,
10        Priority smallint NULL,
11        FromHost varchar(60) NULL,
12        Message text,
13        NTSeverity int NULL,
14        Importance int NULL,
15        EventSource varchar(60),
16        EventUser varchar(60) NULL,
17        EventCategory int NULL,
18        EventID int NULL,
19        EventBinaryData text NULL,
20        MaxAvailable int NULL,
21        CurrUsage int NULL,
22        MinUsage int NULL,
23        MaxUsage int NULL,
24        InfoUnitID int NULL ,
25        SysLogTag varchar(60),
26        EventLogType varchar(60),
27        GenericFileName VarChar(60),
28        SystemID int NULL
29);
30mysql> CREATE TABLE SystemEventsProperties
31 (
32         ID int unsigned not null auto_increment primary key,
33         SystemEventID int NULL ,
34         ParamName varchar(255) NULL ,
35         ParamValue text NULL
36 );

创建表结构也有不同的，国外一个站点上看到有人按如下结构创建：

 1CREATE TABLE `logs` (
 2    `host` varchar(32) DEFAULT NULL,
 3    `facility` varchar(10) DEFAULT NULL,
 4    `priority` varchar(10) DEFAULT NULL,
 5    `level` varchar(10) DEFAULT NULL,
 6    `tag` varchar(10) DEFAULT NULL,
 7    `datetime` datetime DEFAULT NULL,
 8    `program` varchar(15) DEFAULT NULL,
 9    `msg` text,
10    `seq` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
11    PRIMARY KEY (`seq`),
12    KEY `host` (`host`),
13    KEY `program` (`program`),
14    KEY `datetime` (`datetime`),
15    KEY `priority` (`priority`),
16    KEY `facility` (`facility`)
17) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2、设置数据库权限

1mysql> GRANT ALL ON Syslog.* TO syslog_ng@localhost IDENTIFIED BY 'syslog_ngpass';
2mysql> FLUSH PRIVILEGES;

二、syslog-ng.conf 配置

 1source s_remote {
 2         tcp(ip(0.0.0.0) port(514));
 3         udp(ip(0.0.0.0) port(514));
 4};
 5destination d_mysql {
 6sql(type(mysql)
 7host("localhost") username("syslog_ng") password("syslog_ngpass")
 8database("Syslog") table("SystemEvents")
 9columns("ID int unsigned not null auto_increment primary key","ReceivedAt datetime NULL", "DeviceReportedTime datetime NULL",
10"Facility smallint NULL","Priority smallint NULL","FromHost varchar(60) NULL",
11"Message text","InfoUnitID int NULL","SysLogTag varchar(60)",
12"CustomerID bigint","NTSeverity int NULL","Importance int NULL","EventSource varchar(60)","EventUser varchar(60) NULL",
13"EventCategory int NULL","EventID int NULL","EventBinaryData text NULL","MaxAvailable int NULL","CurrUsage int NULL","MinUsage int NULL",
14"MaxUsage int NULL","EventLogType varchar(60)","GenericFileName VarChar(60)","SystemID int NULL")
15values("","$R_ISODATE", "$S_ISODATE","$FACILITY_NUM","$LEVEL_NUM","$HOST",
16"$MSGONLY","1","$MSGHDR","","","","","","","","","","","","","","","")
17indexes("ID","ReceivedAt","Facility","Priority","FromHost","SysLogTag",));
18};
19log { source(s_remote); destination(d_mysql); };

三、loganalyzer配置

到 http://loganalyzer.adiscon.com/ 页面下载最新的loganalyzer程序，并放到apache的根目录下 。

1cd  loganalyzer-*
2mkdir /var/www/html/log
3mv ./src/*  /var/www/html/log
4cp contrib/* /var/www/html/log
5cd /var/www/html/loganalyzersh
6sh  configure.sh

配置完成后，在浏览器中输入http://ip/log/install.php 进行安装即可，都是下一步下一步的操作，没有什么可搞性 。这个上个搞完后的效果图：

loganalyzer
loganalyzer

四、日志清理

数据库随着日志的堆积会越来越大，这样会导致在前端页面查询会变慢，所以再搞个清理任务，30天以前的日志进行每天定时清理。

1cat >/etc/cron.daily/syslog-clean.sh <<EOF
2#!/bin/bash
3MYSQL_USER="syslog_ng"
4MYSQL_PASS="syslog_ngpass"
5MYSQL_DB="Syslog"
6mysql  -u\${MYSQL_USER} -p\${MYSQL_PASS} \${MYSQL_DB} -e "DELETE FROM SystemEvents WHERE ReceivedAt < DATE_SUB(CURDATE(),INTERVAL 30 DAY)"
7EOF
8chmod 700 /etc/cron.daily/syslog-clean.sh

五、rsyslog与evtsys

1、rsyslog与loganalyzer的结合

由于syslog-ng语法结构比较灵活，所以这里选的和syslog-ng进行的集成，实际上loganalyzer也可以与rsyslog结合，具体的操作步骤如下：

1安装包：
2yum install rsyslog rsyslog-mysql mysql mysql-devel mysql-server php php-mysql php-pdo php-common php-gd httpd
3导入rsyslog数据库：
4mysql -u root -p < $(rpm -ql rsyslog-mysql | grep sql$)
5数据库用户创建：
6mysql -u root -p
7mysql> grant all privileges on Syslog.* to logger@localhost identified by 'logger';
8mysql> flush privileges;
9mysql> exit;

编辑rsyslog.conf文件，增加如下内容：

1$ModLoad ommysql
2*.* :ommysql:127.0.0.1,Syslog,logger,logger
3$ModLoad imudp.so
4$UDPServerRun 514

其他如服务重启及loganalyzer安装步骤略过或同上。

2、evtsys

网络设备和LINUX对与syslog协议天然具有兼容性，只需要在配置中简单的指定，即可让syslog服务器接收其他主机的日志信息。windows通过evtsys也可以实现将自身的日志发送到日志服务器上去，操作很简单，下载evtsys http://code.google.com/p/eventlog-to-syslog/ 解压缩放到 C:\Windows\System32 ，cmd下执行如下操作：

1evtsys -i -s 10 -h log-server-ip -p 514
2net start evtsys

参考页面：

https://anton.dollmaier.name/syslog-host-mit-syslog-ng-und-mysql

http://gm100861.blog.51cto.com/1930562/1191164