An easy dedicated syslog server
posted | about 5 minutes to read
tags: syslog-ng mysql system administration
Recently, I had the need to put together a centralized internal logging server. While the growth of external monitoring services like Loggly or Sumo Logic is awesome (and trust me, I’m not arguing this point – I use both of these services extensively myself!), centralized syslog is still the best solution in some use cases. In this post, I’ll walk you through setting up aggregated logging in a Linux-based environment. While this seems like an intimidating task, in reality it should take less than an hour to configure; I’ve tried to make this tutorial as easy to follow as possible.
Setting up the server
First, we need to set up the syslog server so that everything else has somewhere to send their logs. I used syslog-ng, since it has the ability to log to a MySQL database easily. To install all of the packages you’ll need, run the following commands on a CentOS 7 server:
yum install epel-release
yum install mariadb-server syslog-ng httpd php php-mysql syslog-ng-lbidbi libdbi-drivers libdbi-devel libdbi-dbd-mysql
MySQL configuration
Once you’ve got the packages installed, go ahead and start the MariaDB service, and run mysql_secure_installation to set up your install. Next, create a database to log to, and create a username and password for that database. I’ll refer back to these later.
You’ll also need to create a table in your syslog database. Log into the server using your MySQL client of choice (command line should work fine; this is a pretty easy set of commands), and run the following:
USE 'your-syslog-database-name';
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;
Next, you’ll need to configure your firewall. That’s a bit outside the scope of this article, but if there’s interest I can write up some words on how to configure firewalld on CentOS 7. For now, I’ll simply link you to a pretty reasonable CertDepot article on firewalld. Keep in mind that port 514 will need to be open to any servers that you plan to set up on your logging server.
Syslog-ng configuration
Once you have your firewall configured, you’ll need to configure syslog-ng. This is what I used for my configuration, which should be set up in /etc/syslog-ng/conf.d/mysql.conf:
source s_mysql {
udp(port(514));
tcp(port(514));
};
destination d_mysql {
sql(type(mysql)
host("localhost")
username("your-mysql-syslog-user-name")
password("your-mysql-syslog-password")
database("your-mysql-syslog-database-name")
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", "program", "msg")
);
};
destination d_file {
file("/var/log/syslog/$HOST"
template("$FULLDATE $MSGHDR$MSG\n")
template_escape(no)
);
};
filter f_level {
level(warning..emerg);
};
log {
source(s_mysql);
filter(f_level);
destination(d_mysql);
};
log {
source(s_sys);
filter(f_level);
destination(d_mysql);
};
Note that I do not actually log to d_file as a destination. It’s still included in case you want to log to file as well as logging to MySQL. Also, you may need to make sure there are no duplicate sources or destinations in your main syslog-ng config file.
Once you’ve got the configuration in place, start up the syslog-ng service and make sure it turns on properly. If there are any errors, make sure they’re resolved before moving to the next step.
Setting up log viewing via LogAnalyzer
The last thing we want to do before moving to client configuration is make sure we have a way to see the logs that we’re aggregating. I used Adiscon LogAnalyzer for my server and it worked very well, although there are other options out there.
To get the sources, you can just run wget http://download.adiscon.com/loganalyzer/loganalyzer-4.1.3.tar.gz and unpack the sources folder in the archive to /var/www/html/logs. You’ll also need to run the following commands:
touch /var/www/html/logs/config.php
chmod 666 /var/www/html/logs/config.php
setenforce 0
I was unable to complete the setup of LogAnalyzer without turning SELinux off temporarily, and the program also requires you to have an empty config.php file ready to write to.
Start the Apache server, then browse to the directory in your web browser (http://ServerIP/logs, by default). This should bring up the setup page for LogAnalyzer. When it asks for database connection information, you can use the same connection information that you used for the syslog-ng config. Make sure you set the database driver to PDO! There should be no hiccups through the setup process from here.
Once you’re done, make sure you turn SELinux back on.
This concludes the syslog server setup. Now, let’s move on to setting up a client to talk to our new server.
Setting up a client
For the client, we can use the default syslog package included on CentOS 7, rsyslog. First, let’s redirect all of the logs to our server. Open up /etc/rsyslog.conf, and at the bottom of the file, add the following line:
*.* @@your-syslog-server-ip
After this, restart the rsyslog service. Now, anything that you have logging to local syslog (in my use case, I’m redirecting all of our PHP and Apache logs into syslog as well) will automatically redirect to the server. You can verify this by going back to http://SyslogServerIP/logs and seeing for yourself!