====== Syslog-ng with mysql logging Ubuntu 12.04 ======

===== Install Required Packages =====
<file>
apt-get install syslog-ng-core syslog-ng-mod-sql mysql-server
apt-get install libdbi1 libdbd-mysql
</file>

===== Install Optional Packages =====
<file>
apt-get install phpmyadmin
</file>

===== Create database =====
<file sql>
create database syslog;
use database 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,
`datetime` datetime DEFAULT NULL,
`program` varchar(15) DEFAULT NULL,
`msg` text,
`seq` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`seq`),
KEY `host` (`host`),
KEY `program` (`program`),
KEY `datetime` (`datetime`),
KEY `priority` (`priority`),
KEY `facility` (`facility`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
</file>

===== Grant Permissions to database =====
<file sql>
CREATE USER 'syslog'@'localhost' IDENTIFIED BY  '***';
GRANT USAGE ON * . * TO  'syslog'@'localhost' IDENTIFIED BY  '***' WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;
GRANT ALL PRIVILEGES ON  `syslog` . * TO  'syslog'@'localhost';
</file>

===== Enable Mysql in syslog-ng =====

<file sql /etc/syslog-ng/conf.d/10mysqlsyslog.conf>
filter f_no_debug { not level(debug); };
destination d_mysql {
sql(
type(mysql)
username("syslog")
password("********")
database("syslog")
host("localhost")
table("logs")
columns("host", "facility", "priority", "level", "tag", "datetime", "program", "msg"
values("$HOST", "$FACILITY", "$PRIORITY", "$LEVEL", "$TAG","$YEAR-$MONTH-$DAY $HOUR:
indexes("datetime", "host", "program", "pid", "message")
);
};
log {source(s_net); source(s_src); filter(f_no_debug); destination(d_mysql); };
</file>