assassins of ideas. we make things that go.

Remote system logging with syslog-ng

By logging your system messages to a remote MySQL server you can easily write complex database queries to parse through the cruft. The FreeBSD port of syslog-ng makes this almost a no-brainer.

Step 1) Create a MySQL database and table to log into:
On my server I’ve created a MySQL database and user both named syslog. The user must have full privileges over the database. You’ll then need to create a logs table:

CREATE TABLE IF NOT EXISTS `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` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`seq`),
  KEY `host` (`host`),
  KEY `seq` (`seq`),
  KEY `program` (`program`),
  KEY `date` (`datetime`),
  KEY `priority` (`priority`),
  KEY `facility` (`facility`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2827 ;

Step 2) Install syslog-ng:

$ cd /usr/ports/sysutils/syslog-ng2
$ make install clean

Step 3) Configure syslog-ng to start automatically:
Add the following lines to your /etc/rc.conf file. These will ensure that syslog-ng will start on reboot instead of FreeBSD’s default syslog daemon.

syslog_ng_enable="YES"
syslogd_enable="NO"

Step 4) Create syslog-ng.conf and modify it to log to MySQL:

$ cp /usr/local/etc/syslog-ng/syslog-ng.conf.sample /usr/local/etc/syslog-ng.conf

Append the following definitions to the end of your syslog-ng.conf file, replacing HOST, USER, PASSWORD and DATABASE with your MySQL database information:

destination d_mysql
                {
                program("/usr/local/bin/mysql -hHOST -uUSER -pPASSWORD DATABASE < /var/log/mysql.pipe");
                pipe ("/var/log/mysql.pipe"
                template ("INSERT INTO syslog.logs (host, facility, priority, level, tag, datetime, program, msg) VALUES ('$HOST', '$FACILITY', '$PRIORITY', '$LEVEL', '$TAG', '$ISODATE', '$PROGRAM', '$MESSAGE' );\n")
                template_escape(yes));
                };
 
log { source(src); destination(d_mysql); };

If you followed the instructions in Step 1 then USER and DATABASE should both be “syslog”. HOST will be localhost or an IP address, depending on where your database is located.

NOTE: You’ll need the MySQL client installed on this server for this to work properly.

The last line that begins with “log” tells syslog-ng to send any messages received from src to your d_mysql destination (where src is defined near the top of syslog-ng.conf).

If you don’t want to log all system messages to the database, you can apply one of the default syslog-ng filters. So if you only wanted to log security messages then you could do this:

log { source(src); filter(f_security); destination(d_mysql); };

Step 5) Create the mysql.pipe:

$ mkfifo /var/log/mysql.pipe

Step 6) Shut down the default FreeBSD syslogd:

$ kill `cat /var/run/syslog.pid`

Step 7) Start syslog-ng:

$ /usr/local/etc/rc.d/syslog-ng start

If you don’t get any error messages then syslog-ng should now be running and logging messages both locally and to your MySQL database.

You can test your new setup by using the logger command to create an error message:

$ logger -p auth.error "Does it work?"
$ tail /var/log/auth.log

Just check and see if this appears in your new logs database.

Here are some good resources for more information:

• Syslog-NG Installation
• Centralized syslog-ng to Mysql:
• HOWTO setup syslog-ng to log to mysql
• Logging with syslog-ng

Tags: FreeBSD, syslog, Unix