Syslog-NG to MySql

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); };