Install prerequisites:
apt install syslog-ng
apt install syslog-ng-libdbi
apt install libdbi-drivers
apt install libdbi-devel
apt install libdbi-dbd-mysql
apt install syslog-ng-libdbi
Create "syslog" database and a MySQL user which has DELETE, INSERT, SELECT, UPDATE on the syslog database / schema.:
Create
Table
:
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
,
`datetime` datetime
DEFAULT
NULL
,
`program`
varchar
(15)
DEFAULT
NULL
,
`msg` text,
`seq`
bigint
(20) unsigned
NOT
NULL
AUTO_INCREMENT,
PRIMARY
KEY
(`seq`),
KEY
`date_host` (`datetime`,`host`)
) ENGINE=InnoDB AUTO_INCREMENT=14120
DEFAULT
CHARSET=utf8
Edit syslog.conf:
nano /etc/syslog-ng/syslog-ng.conf
@version: 3.13
#options {
#flush_lines (0);
#time_reopen (10);
#log_fifo_size (1000);
#long_hostnames (off);
#use_dns (yes);
#use_fqdn (yes);
#create_dirs (yes);
#keep_hostname (yes);
#};
# syslog-ng log source
source s_net { udp(ip("10.0.0.115") port(514)); };
#perm(0644) dir_perm(0700) create_dirs(yes)); };
source s_sys {
file ("/proc/kmsg" program_override("kernel: "));
unix-stream ("/dev/log");
internal();
};
destination d_file { file("/var/log/messagestest"); };
#perm(0644) dir_perm(0700) create_dirs(yes)); };
destination d_cons { file("/dev/console"); };
destination d_mesg { file("/var/log/messages"); };
destination d_auth { file("/var/log/secure"); };
destination d_mail { file("/var/log/maillog" flush_lines(10)); };
destination d_spol { file("/var/log/spooler"); };
destination d_boot { file("/var/log/boot.log"); };
destination d_cron { file("/var/log/cron"); };
destination d_kern { file("/var/log/kern"); };
destination d_mlal { usertty("*"); };
# MySQL define destination
destination d_mysql {
sql(
type(mysql)
username("root")
password("password")
database("syslog")
host("10.0.0.65")
table("logs")
columns("host", "facility", "priority", "level", "tag", "datetime", "program", "msg")
values("$HOST", "$FACILITY", "$PRIORITY", "$LEVEL", "$TAG","$YEAR-$MONTH-$DAY $HOUR:$MIN:$SEC","$PROGRAM", "$MSG")
indexes("datetime", "host")
);
};
filter f_kernel { facility(kern); };
filter f_default { level(info..emerg) and
not (facility(mail)
or facility(authpriv)
or facility(cron)); };
filter f_auth { facility(authpriv); };
filter f_mail { facility(mail); };
filter f_emergency { level(emerg); };
filter f_news { facility(uucp) or
(facility(news)
and level(crit..emerg)); };
filter f_boot { facility(local7); };
filter f_cron { facility(cron); };
# MySQL log to destination
log {source(s_net); destination(d_file); destination(d_mysql);};
# map source to destination fields.
log { source(s_sys); filter(f_kernel); destination(d_kern); };
log { source(s_sys); filter(f_default); destination(d_mesg); };
log { source(s_sys); filter(f_auth); destination(d_auth); };
log { source(s_sys); filter(f_mail); destination(d_mail); };
log { source(s_sys); filter(f_emergency); destination(d_mlal); };
log { source(s_sys); filter(f_news); destination(d_spol); };
log { source(s_sys); filter(f_boot); destination(d_boot); };
log { source(s_sys); filter(f_cron); destination(d_cron); };