[快速配置]rsyslog+mysql+loganalyzer日志服务器

rsyslog+mysql+loganalyzer

server172.172.178.78
环境需求
MySQL
Rsyslog
PHP
Nginx
yum install rsyslog-mysql -y

添加FromIP字段
vi /usr/share/doc/rsyslog-mysql-5.8.10/createDB.sql
CREATE DATABASE Syslog;
USE Syslog;
CREATE TABLE SystemEvents
(
        ID int unsigned not null auto_increment primary key,
        CustomerID bigint,
        ReceivedAt datetime NULL,
        DeviceReportedTime datetime NULL,
        Facility smallint NULL,
        Priority smallint NULL,
        FromHost varchar(60) NULL,
        FromIP varchar(60) NULL,
        Message text,
        NTSeverity int NULL,
        Importance int NULL,
        EventSource varchar(60),
        EventUser varchar(60) NULL,
        EventCategory int NULL,
        EventID int NULL,
        EventBinaryData text NULL,
        MaxAvailable int NULL,
        CurrUsage int NULL,
        MinUsage int NULL,
        MaxUsage int NULL,
        InfoUnitID int NULL ,
        SysLogTag varchar(60),
        EventLogType varchar(60),
        GenericFileName VarChar(60),
        SystemID int NULL
);


CREATE TABLE SystemEventsProperties
(
        ID int unsigned not null auto_increment primary key,
        SystemEventID int NULL ,
        ParamName varchar(255) NULL ,
        ParamValue text NULL
);

创建数据库用户赋权
mysql -uroot -p </usr/share/doc/rsyslog-mysql-5.8.10/createDB.sql
grant all on Syslog.* to 'syslogdbadmin'@'127.0.0.1' identified by '123456';
grant all on Syslog.* to 'syslogdbadmin'@'172.172.178.78' identified by '123456';
flush privileges;

配置rsyslog
vi /etc/rsyslog.conf
$ModLoad imuxsock
$ModLoad imklog
$ModLoad imudp
$UDPServerRun 514
$ModLoad imtcp
$InputTCPServerRun 514
$template insertpl,"insert into SystemEvents (Message, Facility, FromHost, FromIP, Priority, DeviceReportedTime, ReceivedAt, InfoUnitID, SysLogTag) values ('%msg%', %syslogfacility%, '%HOSTNAME%', '%fromhost-ip%', %syslogpriority%, '%timereported:::date-mysql%', '%timegenerated:::date-mysql%', %iut%, '%syslogtag%')",SQL
$ModLoad ommysql
$ActionFileDefaultTemplate RSYSLOG_TraditionalFileFormat
IncludeConfig /etc/rsyslog.d/*.conf
*.*       :ommysql:172.172.178.78,Syslog,syslogadmin,123456;insertpl
local7.*                                                /var/log/boot.log
$template SpiceTmpl,"%TIMESTAMP%.%TIMESTAMP:::date-subseconds% %syslogtag% %syslogseverity-text%:%msg:::sp-if-no-1st-sp%%msg:::drop-last-lf%\n"
:programname, startswith, "spice-vdagent" /var/log/spice-vdagent.log;SpiceTmpl

/etc/init.d/rsyslog restart
chkconfig rsyslog on

配置loganalyzer
tar zxvf loganalyzer-3.6.6.tar.gz
cd loganalyzer-3.6.6
cp ./src/* /data/web/loganalyzer/
cp ./contrib/* /data/web/loganalyzer/
sh /data/web/loganalyzer/configure.sh

Web页面配置
启web服务
http://172.172.178.78
基础设置setp1-8
AdminCenter->Fields添加FromIP字段->新建view添加需要输出的字段->新建DBMappings->Source修改tabletype

DBMappings对应字段：
uID => id
Date => devicereportedtime
Host => fromhost
IP => fromip
Messagetype => infounitid
Message => message
Facility => facility
Severity => priority
Syslogtag => syslogtag
ProcessID => processid
Event ID => eventid
Eventlog Type => eventlogtype
Event Source => eventsource
Event Category => eventcategory
Event User => eventuser
SystemID => systemid
Checksum => checksum

client172.172.178.79
Rsyslog配置
vi /etc/rsyslog.conf
$ModLoad imuxsock # provides support for local system logging (e.g. via logger command)
$ModLoad imklog   # provides kernel logging support (previously done by rklogd)
$ActionFileDefaultTemplate RSYSLOG_TraditionalFileFormat
$IncludeConfig /etc/rsyslog.d/*.conf
*.info;mail.none;authpriv.none;cron.none                /var/log/messages 
authpriv.*                                              /var/log/secure 
mail.*                                                  -/var/log/maillog 
cron.*                                                  /var/log/cron
*.emerg                                                 *   
uucp,news.crit                                          /var/log/spooler 
$template insertpl,"insert into SystemEvents (Message, Facility, FromHost, FromIP, Priority, DeviceReportedTime, ReceivedAt, InfoUnitID, SysLogTag) values ('%msg%', %syslogfacility%, '%HOSTNAME%', '%fromhost-ip%', %syslogpriority%, '%timereported:::date-mysql%', '%timegenerated:::date-mysql%', %iut%, '%syslogtag%')",SQL
*.* @172.172.178.78
*.* :ommysql:172.172.178.78,Syslog,syslogadmin,123456;insertpl
$template SpiceTmpl,"%TIMESTAMP%.%TIMESTAMP:::date-subseconds% %syslogtag% %syslogseverity-text%:%msg:::sp-if-no-1st-sp%%msg:::drop-last-lf%\n"
:programname, startswith, "spice-vdagent" /var/log/spice-vdagent.log;SpiceTmpl

/etc/init.d/rsyslog restart
chkconfig rsyslog on