Syslog-ng+rsyslog Collection log: Write database mysq, Ms-sql,sqlite, MSQL (vi)

For statistical convenience, we want to select some messages from the log and put them in the database. Read and write support for the database to add parameters at compile time, but also to open the corresponding module in the configuration file. Module if a lot of monitoring needs to the database module, can be placed in the/etc/rsyslog.conf global configuration file, if only a certain monitoring collection used to put in the corresponding local configuration file/etc/rsyslog.d/.

1. Compile.

./configure--enable-mysql

2. module, generated template.

Ommysql # mysql output module
Ompgsql # PostgreSQL output module
Omlibdbi # Universal Database Output Module (Firebird/Interbase, MS-SQL, Sybase, SQLite, Ingres, Oracle, mSQL)

$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

3. Use

Usage: local4.*: ommysql:server:port,yourdb,yourname,yourpass;

Example: local4.*: Ommysql:127.0.0.1:3306,yourdb,yourname,yourpass;

4. Global configuration file example.

vi /etc/rsyslog.conf    //edit rsyslog Global file 

# rsyslog v5 configuration file
# For more information see /usr/share/doc/rsyslog-*/rsyslog_conf.html
# If you experience problems, see http://www.rsyslog.com/doc/troubleshoot.html

#### MODULES ####

$ModLoad imuxsock #provides support for local system
                    #logging (e.g. via logger command)#Unix sockets, including system log sockets

$ModLoad imklog # provides kernel logging support(previously done by rklogd)
#module(load"immark") # provides --MARK-- message capability

#mysqloutput module
$ModLoad ommysql

#PostgreSQL output module
$ModLoad ompgsql

#通用databaseoutput module (Firebird/Interbase, MS SQL, Sybase, SQLite, Ingres, Oracle, mSQL)
$ModLoad omlibdbi

# Provides UDP syslog reception
# UDP transmission, only used when rsyslog is used as a server
#$ModLoad imudp
#$UDPServerRun 514

#Provides TCP syslog reception
# TCP transmission, only used when rsyslog is used as a server
#$ModLoad imtcp
# tcpPort for receiving information
#$InputTCPServerRun 514

#增database insert statement
$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

#### GLOBAL DIRECTIVES ####
# Use default timestamp format
$ActionFileDefaultTemplate RSYSLOG_TraditionalFileFormat

# File syncing capability is disabled by default. This feature is usually not required,
# not useful and an extreme performance hit
#$ActionFileEnableSync on

# Include all config files in /etc/rsyslog.d/
#Modular local configuration file storage, default loading all .conf suffix files
$IncludeConfig /etc/rsyslog.d/*.conf

#### RULES ####

# Log all kernel messages to the console.
# Logging much else clutters up the screen.
#kern.* /dev/console

# Log anything (except mail) of level info or higher.
# Don‘t log private authentication messages!
*.info;mail.none;authpriv.none;cron.none /var/log/messages

# The authpriv file has restricted access.
Authpriv.* /var/log/secure

# Log all the mail messages in one place.
Mail.* /var/log/maillog

# Log cron stuff
Cron.* /var/log/cron

# Everybody gets emergency messages
*.emerg :omusrmsg:*

# Save news errors of level crit and higher in a special file.
Uucp,news.crit /var/log/spooler

# Save boot messages also to boot.log
Local7.* /var/log/boot.log

# ### begin forwarding rule ###
# The statement between the begin ... end define a SINGLE forwarding
# rule. They belong together, do NOT split them. If you create multiple
# forwarding rules, duplicate the whole block!
# Remote Logging (we use TCP for reliable delivery)
#
# An on-disk queue is created for this action. If the remote host is
# down, messages are spooled to disk and sent when it is up again.
#$WorkDirectory /var/lib/rsyslog # where to place spool files
#$ActionQueueFileName fwdRule1 # unique name prefix for spool files
#$ActionQueueMaxDiskSpace 1g # 1gb space limit (use as much as possible)
#$ActionQueueSaveOnShutdown on # save messages to disk on shutdown
#$ActionQueueType LinkedList # run asynchronously
#$ActionResumeRetryCount -1 # infinite retries if host is down
# remote host is: name/ip:port, e.g. 192.168.0.1:514, port optional
#*.* @@remote-host:514
# ### end of the forwarding rule ###

#日志 If non-local storage, you need to specify the remote collection log server IP: Port
*.* :ommysql:192.168.1.251:514 # indicates that all types of logs are submitted

Restart the log service, modify the configuration file must restart the service to take effect

# Service Rsyslog Restart

Reference article:

http://www.oschina.net/question/12_4191

http://zhaomengsen.iteye.com/blog/2059236

http://www.ituring.com.cn/article/128536

This article is from the "Enlightened Grocery store" blog, please be sure to keep this source http://wutou.blog.51cto.com/615096/1766272

Syslog-ng+rsyslog Collection log: Write database mysq, Ms-sql,sqlite, MSQL (vi)