# Syslog-Host with Syslog-NG and MySQL

When administrating a larger farm of servers, not only the security gain gives enough reasons to archive syslog messages at a central location: after a remote server has been compromised those logs may not be altered by the attacker. Additionally, logs may be analyzed over all hosts at the same time.

To achieve this goal, I rely on Syslog-NG. There are alternatives like rsyslog, but those do not offer the same level of comfort.

With Debian Wheezy, which has been released on May 4th (pun intended, I think), the setup and administration of such a central loghost has been simplified.

Syslog-NG now utilizes, amongst other packages, a new configuration layout, which includes all files in the directory /etc/syslog-ng/conf.d/ in the main configuration file. The main config file doesn’t have to be altered any more.

On every system, which has to send its syslog messages to our new central loghost, we install the package “syslog-ng-core” and create the file  /etc/syslog-ng/conf.d/10syslognet.conf with the following contents (change the IP address!):
1.destination d_net { udp("62.146.15.2" port(514) log_fifo_size(1000)); };
2.log { source(s_src); destination(d_net); };

This defines a new “destination” for sending messages to a remote IP address. Instead of an IPv4 address, one may also use “udp6()” and an IPv6 address. Additionally, the command “log()” instructs Syslog-NG to send all messages received by the source “s_src” to the destination “d_net“, which is the target central loghost.

After a restart of the service, Syslog-NG tries to send the messages over the network. Now we have to configure the destination itself.

Beforehand, we install Syslog-NG with the following module:
1.aptitude install syslog-ng-core syslog-ng-mod-sql

Afterwards, we create /etc/syslog-ng/conf.d/05loghost.conf with the specified content:
1.source s_net { udp( ip("62.146.15.2") port(514)); );
2.destination hosts_syslog { file("/var/logs/$HOST/$YEAR/$MONTH/$DAY/syslog" owner(root) group(root) perm(0644) dir_perm(0700) create_dirs(yes)); };
3.log {source(s_net); source(s_src); destination(hosts_syslog); };

Again, to achieve best results, the IP-address has to be changed and possibly the full path to the new log directory as well.

After the service restart, Syslog-NG now accepts syslog messages at the specified IP address and saves them in our predefined folder structure: each source IP / hostname, year, month and day we will have a single folder. This allows us an easy search as well as an easy archive of the data.

As you probably have noticed, Syslog-NG only creates a folder for each remote IP address sending syslog messages. This may get a bit confusing, so we tell Syslog-NG to perform a ReverseDNS query and use the resulting DNS name as the folder name. Edit /etc/syslog-ng/syslog-ng.conf (yes, this is main configuration file, contrary to the initial statement) and change the following paramaters in the section options{} from no to yes:
1.use_dns(no); use_fqdn(no);

To simplify the search, storing messages in the database is an option as well. A new destination is required as well as a filter, which reduces the size of our database:
1.filter f_no_debug { not level(debug); };
2.destination d_mysql {
3.sql(
4.type(mysql)
5.username("logs")
6.password("SECUREPASSWORD")
7.database("logs")
8.host("localhost")
9.table("logs")
10.columns("host", "facility", "priority", "level", "tag", "datetime", "program", "msg")
11.values("$HOST", "$FACILITY", "$PRIORITY", "$LEVEL", "$TAG","$YEAR-$MONTH-$DAY $HOUR:$MIN:$SEC","$PROGRAM", "$MSG")
12.indexes("datetime", "host", "program", "pid", "message")
13.);
14.};
15.log {source(s_net); source(s_src); filter(f_no_debug); destination(d_mysql); };

How to insert and create the appropriate MySQL username and password should be clear – or is at least not part of this tutorial :)

The required MySQL table looks the following:
1.CREATE TABLE `logs` (
2.`host` varchar(32) DEFAULT NULL,
3.`facility` varchar(10) DEFAULT NULL,
4.`priority` varchar(10) DEFAULT NULL,
5.`level` varchar(10) DEFAULT NULL,
6.`tag` varchar(10) DEFAULT NULL,
7.`datetime` datetime DEFAULT NULL,
8.`program` varchar(15) DEFAULT NULL,
9.`msg` text,
10.`seq` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
11.PRIMARY KEY (`seq`),
12.KEY `host` (`host`),
13.KEY `program` (`program`),
14.KEY `datetime` (`datetime`),
15.KEY `priority` (`priority`),
16.KEY `facility` (`facility`)
17.) ENGINE=InnoDB DEFAULT CHARSET=utf8;

After a restart of Syslog-NG, new messages will appear not only in the file system, but the database as well.

If Syslog-NG exits (or logs in /var/log/syslog) an error message like “no such file“, it is missing the DBI database drivers. For Debian Wheezy, we install them with the following command:
1.aptitude install libdbi1 libdbd-mysql

At the end, an additional remark: I found a lot of tutorials or how-tos how to write log messages to a MySQL database using Syslog-NG. None (none I found!) relied on the native solution, but used either a pipe (Syslog-NG writes messages to a FIFO-pipe, where a second daemon reads the contents and performs the actual MySQL insertion) or – worse – use the MySQL client binary. With the MySQL client, Syslog-NG starts the binary for each received syslog message, resulting in a start of the binary, which has to connect to the database, perform the insert query, disconnect and exit the program. This overhead of starting, connecting, disconnecting and exiting is prevented with the native solution, resulting in a better performance and a lower system load :)

Actually, I still miss the “perfect” GUI to display the logs in the database. Currently we are using LogAnalyzer, but this solution does not work that great (e.g., search in 7 days of logs take more than 30 seconds, so the LogAnalyzer cancels the query without any result). Creating a new solution just for accessing the stored logs is achievable.
 

Post navigation

← Nginx für alle! the language of the target audience →
 

2 thoughts on “Syslog-Host with Syslog-NG and MySQL”

@Tacioandrade_  
 
 January 10, 2014 at 3:14 pm  
 

Good day friend, I managed to install syslog-ng to your tutorial and am using LogAnalyzer also for viewing, it is not perfect, more should initially serve my purpose.
 Curious customers in how to proceed in the configuration so they send their logs to the server? I have to install syslog-ng them as well?

Thank you in advance for help.
 
Reply 
 

Anton Dollmaier  
 
 January 10, 2014 at 9:04 pm  
 

Yes, each server sending the logs to your new central loghost has to be configured to forward its local messages.

See the section with “destination d_net” in the tutorial. Instead of syslog-ng, rsyslog works as well with the following directive:
1.*.* @1.2.3.4