Back

Centralized System Logging with syslog-ng

1. Prerequisites

Server

For this implementation the logging server will use MySQL for storing the log entries, but many other backends are imaginable for this. Just look how the communication is done and then decide for yourself what other solutions you can use this way. Only thing else needed on the server is the mysql commandline client (which should normally come with mysql) as well as an implentation of SSH which supports public key authentification. Syslog-ng is not needed on the server, but it will not do any harm if it is there.

Note: There is no need to have the central logging server available 24/7 - if the server isn't available, logging messages will be queued on the clients, so no messages will be lost.

Client(s)

On the clients there are three things needed: A cron daemon or incrond, (I'll show how to use both) syslog-ng and an ssh client which supports public key authentification.

2. Setting up the centralized logging

Server

First, a database and a database-user for logging are created. The user only gets insert permissions on the database so no information can be manipulated once sent to the server.

Code listing: creating a MySQL User and a corresponding database + table
# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 30
Server version: 5.0.60-log Gentoo Linux mysql-5.0.60-r1

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE USER 'syslog'@'localhost' IDENTIFIED BY 'pass';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE DATABASE syslog;
Query OK, 1 row affected (0.00 sec)

mysql> REVOKE ALL PRIVILEGES ON *.* FROM 'syslog'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> REVOKE GRANT OPTION ON *.* FROM 'syslog'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql> GRANT INSERT ON syslog.* TO 'syslog'@'localhost'
    -> WITH MAX_QUERIES_PER_HOUR 0
    -> MAX_CONNECTIONS_PER_HOUR 0
    -> MAX_UPDATES_PER_HOUR 0
    -> MAX_USER_CONNECTIONS 0;
Query OK, 0 rows affected (0.00 sec)
mysql> USE syslog;
mysql> CREATE TABLE logs (
    -> msg_received TIMESTAMP NOT NULL,
    -> msg_sent TIMESTAMP NOT NULL,
    -> host VARCHAR(1024) NOT NULL,
    -> origin VARCHAR(64) NOT NULL,
    -> priority VARCHAR(64) NOT NULL,
    -> body VARCHAR(4096) NOT NULL,
    -> guid BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
    -> );
Query OK, 0 rows affected (0.04 sec)

Now comes the first tricky part: we create a user account and modify it that way that a login will directly open a mysql shell. (Later we'll see what this is good for)

Code listing: creating a login account for logging
# groupadd syslog
# useradd -md /var/syslog -g syslog -k /dev/null -s /bin/bash syslog
# cd /var/syslog/
# mkdir .ssh
# cat > .bash_profile << "EOF"
> exec mysql -u syslog --password=pass syslog
> EOF

Client

Code listing: changes in /etc/syslog-ng/syslog-ng.conf (for Gentoo, but principle stays the same on all dists)
  options {
+         long_hostnames(on);
+         use_fqdn(yes);
+         create_dirs(yes);
  };
+ destination mysql { 
+         file("/var/spool/syslog-ng/log.$YEAR.$MONTH.$DAY.$HOUR.$MIN.$SEC"
+         template("INSERT INTO logs \( msg_received, msg_sent, host, origin, \
priority, body, guid \) VALUES \( '$R_ISODATE', '$S_ISODATE', '$HOST', \
'$FACILITY', '$PRIORITY', '$MSG', NULL\)\;\n")
+         template_escape(yes)
+         owner(root));
+ };
+
+ log { source(src); destination(mysql); };
Code listing: create /usr/local/sbin/flushlogs
#!/bin/bash

SERVER="phoenix"
USER="syslog"

FILES="$(find /var/spool/syslog-ng -type f)"
sleep 2
cat $FILES | ssh $USER@$SERVER && rm -f $FILES

On each client a private/public key pair without password must be generated now (it is needed for logging in into the central server) Afterwards, all public keys have to be added to /var/syslog/.ssh/authorized_keys on the server.

Code listing: Key generation
# ssh-keygen -t dsa
Generating public/private dsa key pair.
Enter file in which to save the key (/root/.ssh/id_dsa): 
Enter passphrase (empty for no passphrase): 
Enter same passphrase again: 
Your identification has been saved in /root/.ssh/id_dsa.
Your public key has been saved in /root/.ssh/id_dsa.pub.
The key fingerprint is:
00:00:00:00:00:00:00:00:00:00:00:00:00:00:00:00 root@foobar

flushlogs can be either executed by a cron daemon or by incrond.

Code listing: incrontab entry
/var/spool/syslog-ng/ IN_CLOSE_WRITE /usr/local/sbin/flushlogs
Code listing: crontab entry
* * * * * /usr/local/sbin/flushlogs

3. Outlook

This data which is now stored in the MySQL database/table can be used in multiple ways and because of its format, it is relatively easy to filter. Writing a webfrontend for it isn't hard either.

4. Known Bugs

As the execution of flushlogs is written to the system logs itself, there is very much overhead created, as the script logs that it is logging. I will post an update on that here in the near future.

Last modified: 2009-04-19

Summary:
This Document describes how to set up server and clients that way that all logs of the clients running syslog-ng are written on a central logging server which does not need to run syslog-ng itself, this attempt is also secure on intrustable networks like the internet.

nobody-nw@nowhere.ws
Author

Table of Contents:

1. Prerequisites

2. Setting up the centralized logging

3. Outlook

4. Known Bugs