How to Create a centralized log in server
Configure syslog-ng to log to mysql database using fifo template /etc/syslog-ng/sylog-ng.conf:
destination d_mysql {
pipe("/tmp/mysql.pipe"
template("INSERT INTO logs (host, facility, priority, level, tag, date,
time, program, msg) VALUES ( '$HOST', '$FACILITY', '$PRIORITY', '$LEVEL','$TAG',
'$YEAR-$MONTH-$DAY', '$HOUR:$MIN:$SEC', '$PROGRAM', '$MSG' );\n") template-escape(yes));
};
log { source(net); destination(d_mysql); };
Comment out the following line
#source src { unix-dgram("/dev/log"); internal(); };
Uncomment out the following lines
source src { unix-dgram("/etc/log/log"); internal(); };
source net { udp(); };
Create the fifo pipe for syslog-ng to export out logs
mkfifo /tmp/mysql.pipe
Create syslog database
CREATE DATABASE syslog
USE syslog
CREATE TABLE logs (
host varchar(32) default NULL,
facility varchar(10) default NULL,
priority varchar(10) default NULL,
level varchar(10) default NULL,
tag varchar(10) default NULL,
date date default NULL,
time time default NULL,
program varchar(15) default NULL,
msg text,
seq int(10) unsigned NOT NULL auto_increment,
PRIMARY KEY (seq),
KEY host (host),
KEY seq (seq),
KEY program (program),
KEY time (time),
KEY date (date),
KEY priority (priority),
KEY facility (facility)
) TYPE=MyISAM;
Create the fifo pipe for syslog-ng to export out logs
mkfifo /tmp/mysql.pipe
Restart syslog-ng process
Stop syslog-ng
/etc/init.d/syslog-ng stop
Start syslog-ng
/etc/ini.d/syslog-ng start
Pipe Insert scripts
# Created by Matthias Buch
#
In the syslog-ng.conf we use:
destination d_oracle {
pipe("/dev/ora.pipe"
template("INSERT INTO logs (LL_HOST, LL_facility, LL_priority, LL_level, LL_tag,
LL_DATE, LL_program, LL_msg) VALUES ( '$HOST', '$FACILILITY', '$PRIORITY',
'$LEVEL', '$TAG',
to_date('$YEAR.$MONTH.$DAY $HOUR:$MIN:$SEC', 'yyyy.mm.dd hh24:mi:ss'),
'$PROGRAM', substr('$MSG',1,511));\n COMMIT;\n") template-escape(yes));
This script is used to pipe syslog-ng to mysql
#
# Created by Tadghe Patrick Danu
#
#!/bin/bash
if [ -e /tmp/mysql.pipe ]; then
while [ -e /tmp/mysql.pipe ]
do
mysql -u theuserid --password=thepassword syslogdb < /tmp/mysql.pipe done else mkfifo /tmp/mysql.pipe fi
CronJob
#Rotate logs once a month
0 0 1 * * /var/www/php-syslog-ng/scripts/logrotate.php
@reboot /muysql #the name is right is th script that pipes the local logs to mysql.
No comments:
Post a comment