Librenix
Headlines | Linux | Apps | Coding | BSD | Admin | News
Information for Linux System Administration 

Writing syslog messages to MySQL

Up
vote
Down

Writing syslog messages to MySQL

Written by Rainer Gerhards (2005-08-02)

Abstract

In this paper, I describe how to write syslog messages to a MySQL database. Having syslog messages in a database is often handy, especially when you intend to set up a front-end for viewing them. This paper describes an approach with rsyslogd, an alternative enhanced syslog daemon natively supporting MySQL. I describe the components needed to be installed and how to configure them.

Background

In many cases, syslog data is simply written to text files. This approach has some advantages, most notably it is very fast and efficient. However, data stored in text files is not readily accessible for real-time viewing and analysis. To do that, the messages need to be in a database. There are various ways to store syslog messages in a database. For example, some have the syslogd write text files which are later feed via a separate script into the database. Others have written scripts taking the data (via a pipe) from a non-database-aware syslogd and store them as they appear. Some others use database-aware syslogds and make them write the data directly to the database. In this paper, I use that "direct write" approach. I think it is superior, because the syslogd itself knows the status of the database connection and thus can handle it intelligently (well ... hopefully ;)). I use rsyslogd to acomplish this, simply because I have initiated the rsyslog project with database-awareness as one goal.

One word of caution: while message storage in the database provides an excellent foundation for interactive analysis, it comes at a cost. Database i/o is considerably slower than text file i/o. As such, directly writing to the database makes sense only if your message volume is low enough to allow a) the syslogd, b) the network, and c) the database server to catch up with it. Some time ago, I have written a paper on optimizing syslog server performance. While this paper talks about Window-based solutions, the ideas in it are generic enough to apply here, too. So it might be worth reading if you anticipate medium high to high traffic. If you anticipate really high traffic (or very large traffic spikes), you should seriously consider forgetting about direct database writes - in my opinion, such a situation needs either a very specialised system or a different approach (the text-file-to-database approach might work better for you in this case).

Overall System Setup

In this paper, I concentrate on the server side. If you are thinking about interactive syslog message review, you probably want to centralize syslog. In such a scenario, you have multiple machines (the so-called clients) send their data to a central machine (called server in this context). While I expect such a setup to be typical when you are interested in storing messages in the database, I do not describe how to set it up. This is beyond the scope of this paper. If you search a little, you will probably find many good descriptions on how to centralize syslog. If you do that, it might be a good idea to do it securely, so you might also be interested in my paper on ssl-encrypting syslog message transfer.

No matter how the messages arrive at the server, their processing is always the same. So you can use this paper in combination with any description for centralized syslog reporting.

As I already said, I use rsyslogd on the server. It has intrinsic support for talking to MySQL databases. For obvious reasons, we also need an instance of MySQL running. To keep us focussed, the setup of MySQL itself is also beyond the scope of this paper. I assume that you have successfully installed MySQL and also have a front-end at hand to work with it (for example, phpMyAdmin). Please make sure that this is installed, actually working and you have a basic understanding of how to handle it.

Setting up the system

You need to download and install rsyslogd first. Obtain it from the rsyslog site. Make sure that you disable stock syslogd, otherwise you will experience some difficulties.

It is important to understand how rsyslogd talks to the database. In rsyslogd, there is the concept of "templates". Basically, a template is a string that includes some replacement characters, which are called "properties" in rsyslog. Properties are accessed via the "Property Replacer". Simply said, you access properties by including their name between percent signs inside the template. For example, if the syslog message is "Test", the template "%msg%" would be expanded to "Test". Rsyslogd supports sending template text as a SQL statement to MySQL. As such, the template must be a valid SQL statement. There is no limit in what the statement might be, but there are some obvious and not so obvious choices. For example, a template "drop table xxx" is possible, but does not make an awful lot of sense. In practice, you will always use an "insert" statment inside the template.

An example: if you would just like to store the msg part of the full syslog message, you have probably created a table "syslog" with a single column "message". In such a case, a good template would be "insert into syslog(message) values ('%msg%')". With the example above, that would be expanded to "insert into syslog(message) values('Test')". This expanded string is then sent to the database. It's that easy, no special magic. The only thing you must ensure is that your template expands to a proper SQL statement and that this statement matches your database design.

Does that mean you need to create database schema yourself and also must fully understand rsyslogd's properties? No, that's not needed. Because we anticipated that folks are probably more interested in getting things going instead of designing them from scratch. So we have provided a default schema as well as build-in support for it. This schema also offers an additional benefit: rsyslog is part of Adiscon's MonitorWare product line (which includes open source and closed source members). All of these tools share the same default schema and know how to operate on it. For this reason, the default schema is also called the "MonitorWare Schema". If you use it, you can simply add phpLogCon, a GPLed syslog web interface, to your system and have instant interactive access to your database. So there are some benefits in using the provided schema.

The schema definition is contained in the file "createDB.sql". It comes with the rsyslog package. Review it to check that the database name is acceptable for you. Be sure to leave the table and field names unmodified, because otherwise you need to customize rsyslogd's default sql template, which we do not do in this paper. Then, run the script with your favourite MySQL tool. Double-check that the table was successfully created.

Next, we need to tell rsyslogd to write data to the database. As we use the default schema, we do NOT need to define a template for this. We can use the hardcoded one (rsyslogd handles the proper template linking). So all we need to do is add a simple selector line to /etc/rsyslog.conf:

*.* >database-server,database-name,database-userid,database-password

In many cases, MySQL will run on the local machine. In this case, you can simply use "127.0.0.1" for database-server. This can be especially advisable, if you do not need to expose MySQL to any process outside of the local machine. In this case, you can simply bind it to 127.0.0.1, which provides a quite secure setup. Of course, also supports remote MySQL instances. In that case, use the remote server name (e.g. mysql.example.com) or IP-address. The database-name by default is "syslog". If you have modified the default, use your name here. Database-userid and -password are the credentials used to connect to the database. As they are stored in clear text in rsyslog.conf, that user should have only the least possible privileges. It is sufficient to grant it INSERT privileges to the systemevents table, only. As a side note, it is strongly advisable to make the rsyslog.conf file readable by root only - if you make it world-readable, everybody could obtain the password (and eventually other vital information from it). In our example, let's assume you have created a MySQL user named "syslogwriter" with a password of "topsecret" (just to say it bluntly: such a password is NOT a good idea...). If your MySQL database is on the local machine, your rsyslog.conf line might look like in this sample:

*.* >127.0.0.1,syslog,syslogwriter,topsecret

Save rsyslog.conf, restart rsyslogd - and you should see syslog messages being stored in the "systemevents" table!

The example line stores every message to the database. Especially if you have a high traffic volume, you will probably limit the amount of messages being logged. This is easy to acomplish: the "write database" action is just a regular selector line. As such, you can apply normal selector-line filtering. If, for example, you are only interested in messages from the mail subsystem, you can use the following selector line:

mail.* >127.0.0.1,syslog,syslogwriter,topsecret

Review the rsyslog.conf documentation for details on selector lines and their filtering.

You have now completed everything necessary to store syslog messages to the MySQL database. If you would like to try out a front-end, you might want to look at phpLogCon, which displays syslog data in a browser. As of this writing, phpLogCon is not yet a powerful tool, but it's open source, so it might be a starting point for your own solution.

On Reliability...

Rsyslogd writes syslog messages directly to the database. This implies that the database must be available at the time of message arrival. If the database is offline, no space is left or something else goes wrong - rsyslogd can not write the database record. If rsyslogd is unable to store a message, it performs one retry. This is helpful if the database server was restarted. In this case, the previous connection was broken but a reconnect immediately succeeds. However, if the database is down for an extended period of time, an immediate retry does not help. While rsyslogd could retry until it finally succeeds, that would have negative impact. Syslog messages keep coming in. If rsyslogd would be busy retrying the database, it would not be able to process these messages. Ultimately, this would lead to loss of newly arrived messages.

In most cases, rsyslogd is configured not only to write to the database but to perform other actions as well. In the always-retry scenario, that would mean no other actions would be carried out. As such, the design of rsyslogd is limited to a single retry. If that does not succeed, the current message is will not be written to the database and the MySQL database writer be suspended for a short period of time. Obviously, this leads to the loss of the current message as well as all messages received during the suspension period. But they are only lost in regard to the database, all other actions are correctly carried out. While not perfect, we consider this to be a better approach then the potential loss of all messages in all actions.

In short: try to avoid database downtime if you do not want to experience message loss.

Please note that this restriction is not rsyslogd specific. All approachs to real-time database storage share this problem area.

Conclusion

With minumal effort, you can use rsyslogd to write syslog messages to a MySQL database. Once the messages are arrived there, you can interactivley review and analyse them. In practice, the messages are also stored in text files for longer-term archival and the databases are cleared out after some time (to avoid becoming too slow). If you expect an extremely high syslog message volume, storing it in real-time to the database may outperform your database server. In such cases, either filter out some messages or think about alternate approaches involving non-real-time database writing (beyond the scope of this paper).

The method outline in this paper provides an easy to setup and maintain solution for most use cases, especially with low and medium syslog message volume (or fast database servers).

Feedback Requested

I would appreciate feedback on this paper. If you have additional ideas, comments or find bugs, please let me know.

References and Additional Material

Revision History

Copyright

Copyright (c) 2005 Rainer Gerhards and Adiscon.

Permission is granted to copy, distribute and/or modify this document under the terms of the GNU Free Documentation License, Version 1.2 or any later version published by the Free Software Foundation; with no Invariant Sections, no Front-Cover Texts, and no Back-Cover Texts. A copy of the license can be viewed at http://www.gnu.org/copyleft/fdl.html.

mail this link | permapage | score:9319 | -rgerhards, August 4, 2005 (Updated: March 21, 2007)

SSL Encrypting Syslog via Stunnel

Up
vote
Down

SSL Encrypting Syslog with Stunnel

Written by Rainer Gerhards (2005-07-22)

Abstract

In this paper, I describe how to encrypt syslog messages on the network. Encryption is vital to keep the confidiental content of syslog messages secure. I describe the overall approach and provide an HOWTO do it with the help of rsyslogd and stunnel.

Background

Syslog is a clear-text protocol. That means anyone with a sniffer can have a peek at your data. In some environments, this is no problem at all. In others, it is a huge setback, probably even preventing deployment of syslog solutions. Thankfully, there is an easy way to encrypt syslog communication. I will describe one approach in this paper.

The most straigthforward solution would be that the syslogd itself encrypts messages. Unfortuantely, encryption is only standardized in RFC 3195. But there is currently no syslogd that implements RFC 3195's encryption features, so this route leads to nothing. Another approach would be to use vendor- or project-specific syslog extensions. There are a few around, but the problem here is that they have compatibility issues. However, there is one surprisingly easy and interoperable solution: though not standardized, many vendors and projects implement plain tcp syslog. In a nutshell, plain tcp syslog is a mode where standard syslog messages are transmitted via tcp and records are separated by newline characters. This mode is supported by all major syslogd's (both on Linux/Unix and Windows) as well as log sources (for example, EventReporter for Windows Event Log forwarding). Plain tcp syslog offers reliability, but it does not offer encryption in itself. However, since it operates on a tcp stream, it is now easy to add encryption. There are various ways to do that. In this paper, I will describe how it is done with stunnel (another alternative would be IPSec, for example).

Stunnel is open source and it is available both for Unix/Linux and Windows. It provides a way to use ssl communication for any non-ssl aware client and server - in this case, our syslogd.

Stunnel works much like a wrapper. Both on the client and on the server machine, tunnel portals are created. The non-ssl aware client and server software is configured to not directly talk to the remote partner, but to the local (s)tunnel portal instead. Stunnel, in turn, takes the data received from the client, encrypts it via ssl, sends it to the remote tunnel portal and that remote portal sends it to the recipient process on the remote machine. The transfer to the portals is done via unencrypted communication. As such, it is vital that the portal and the respective program that is talking to it are on the same machine, otherwise data would travel partly unencrypted. Tunneling, as done by stunnel, requires connection oriented communication. This is why you need to use tcp-based syslog. As a side-note, you can also encrypt a plain-text RFC 3195 session via stunnel, though this definitely is not what the protocol designers had on their mind ;)

In the rest of this document, I assume that you use rsyslog on both the client and the server. For the samples, I use Debian. Interestingly, there are some annoying differences between stunnel implementations. For example, on Debian a comment line starts with a semicolon (';'). On Red Hat, it starts with a hash sign ('#'). So you need to watch out for subtle issues when setting up your system.

Overall System Setup

In ths paper, I assume two machines, one named "client" and the other named "server". It is obvious that, in practice, you will probably have multiple clients but only one server. Syslog traffic shall be transmitted via stunnel over the network. Port 60514 is to be used for that purpose. The machines are set up as follows:

Client

  • rsyslog forwards  message to stunnel local portal at port 61514
  • local stunnel forwards data via the network to port 60514 to its remote peer

Server

  • stunnel listens on port 60514 to connections from its client peers
  • all connections are forwarded to the locally-running rsyslog listening at port 61514

Setting up the system

For Debian, you need the "stunnel4" package. The "stunnel" package is the older 3.x release, which will not support the configuration I describe below. Other distributions might have other names. For example, on Red Hat it is just "stunnel". Make sure that you install the appropriate package on both the client and the server. It is also a good idea to check if there are updates for either stunnel or openssl (which stunnel uses) - there are often security fixes available and often the latest fixes are not included in the default package.

In my sample setup, I use only the bare minimum of options. For example, I do not make the server check client cerficiates. Also, I do not talk much about certificates at all. If you intend to really secure your system, you should probably learn about certificates and how to manage and deploy them. This is beyond the scope of this paper. For additional information, http://www.stunnel.org/faq/certs.html is a good starting point.

You also need to install rsyslogd on both machines. Do this before starting with the configuration. You should also familarize yourself with its configuration file syntax, so that you know which actions you can trigger with it. Rsyslogd can work as a drop-in replacement for stock sysklogd. So if you know the standard syslog.conf syntax, you do not need to learn any more to follow this paper.

Server Setup

At the server, you need to have a digital certificate. That certificate enables SSL operation, as it provides the necessary crypto keys being used to secure the connection. Many versions of stunnel come with a default certificate, often found in /etc/stunnel/stunnel.pem. If you have it, it is good for testing only. If you use it in production, it is very easy to break into your secure channel as everybody is able to get hold of your private key. I didn't find an stunnel.pem on my Debian machine. I guess the Debian folks removed it because of its insecurity.

You can create your own certificate with a simple openssl tool - you need to do it if you have none and I highly recommend to create one in any case. To create it, cd to /etc/stunnel and type:

openssl req -new -x509 -days 3650
-nodes -out stunnel.pem -keyout stunnel.pem

That command will ask you a number of questions. Provide some answer for them. If you are unsure, read http://www.stunnel.org/faq/certs.html. After the command has finished, you should have a usable stunnel.pem in your working directory.

Next is to create a configuration file for stunnel. It will direct stunnel what to do. You can used the following basic file:

; Certificate/key is needed in server mode
cert = /etc/stunnel/stunnel.pem

; Some debugging stuff useful
; for troubleshooting
debug = 7 foreground=yes


[ssyslog]
accept = 60514
connect = 61514

Save this file to e.g. /etc/stunnel/syslog-server.conf. Please note that the settings in italics are for debugging only. They run stunnel with a lot of debug information in the foreground. This is very valuable while you setup the system - and very useless once everything works well. So be sure to remove these lines when going to production.

Finally, you need to start the stunnel daemon. Under Debian, this is done via "stunnel /etc/stunnel/syslog.server.conf". If you have enabled the debug settings, you will immediately see a lot of nice messages.

Now you have stunnel running, but it obviously unable to talk to rsyslog - because it is not yet running. If not already done, configure it so that it does everything you want. If in doubt, you can simply copy /etc/syslog.conf to /etc/rsyslog.conf and you probably have what you want. The really important thing in rsyslogd configuration is that you must make it listen to tcp port 61514 (remember: this is where stunnel send the messages to). Thankfully, this is easy to achive: just add "-t 61514" to the rsyslogd startup options in your system startup script. After done so, start (or restart) rsyslogd.

The server should now be fully operational.

Client Setup

The client setup is simpler. Most importantly, you do not need a certificate (of course, you can use one if you would like to authenticate the client, but this is beyond the scope of this paper). So the basic thing you need to do is create the stunnel configuration file.

; Some debugging stuff 
; useful for troubleshooting
debug = 7
foreground=yes


client=yes

[ssyslog]
accept = 127.0.0.1:61514
connect = 192.0.2.1:60514

Again, the text in italics is for debugging purposes only. I suggest you leave it in during your initial testing and then remove it. The most important difference to the server configuration outlined above is the "client=yes" directive. It is what makes this stunnel behave like a client. The accept directive binds stunnel only to the local host, so that it is protected from receiving messages from the network (somebody might fake to be the local sender). The address "192.0.2.1" is the address of the server machine. You must change it to match your configuration. Save this file to /etc/stunnel/syslog-client.conf.

Then, start stunnel via "stunnel4 /etc/stunnel/syslog-client.conf".  Now you should see some startup messages. If no errors appear, you have a running client stunnel instance.

Finally, you need to tell rsyslogd to send data to the remote host. In stock syslogd, you do this via the "@host" forwarding directive. The same works with rsyslog, but it suppports extensions to use tcp. Add the following line to your /etc/rsyslog.conf:

*.*  @@127.0.0.1:61514 

Please note the double at-sign (@@). This is no typo. It tells rsyslog to use tcp instead of udp delivery. In this sample, all messages are forwarded to the remote host. Obviously, you may want to limit this via the usual rsyslog.conf settings (if in doubt, use man rsyslog.con).

You do not need to add any special startup settings to rsyslog on the client. Start or restart rsyslog so that the new configuration setting takes place.

Done

After following these steps, you should have a working secure syslog forwarding system. To verify, you can type "logger test" or a similar smart command on the client. It should show up in the respective server log file. If you dig out you sniffer, you should see that the traffic on the wire is actually protected. In the configuration use above, the two stunnel endpoints should be quite chatty, so that you can follow the action going on on your system.

If you have only basic security needs, you can probably just remove the debug settings and take the rest of the configuration to production. If you are security-sensitve, you should have a look at the various stunnel settings that help you further secure the system.

Preventing Systems from talking directly to the rsyslog Server

It is possible that remote systems (or attackers) talk to the rsyslog server by directly connecting to its port 61514. Currently (Jule of 2005), rsyslog does not offer the ability to bind to the local host, only. This feature is planned, but as long as it is missing, rsyslog must be protected via a firewall. This can easily be done via e.g iptables. Just be sure not to forget it.

Conclusion

With minumal effort, you can set up a secure logging infrastructure employing ssl encrypted syslog message transmission. As a side note, you also have the benefit of reliable tcp delivery which is far less prone to message loss than udp.

Feedback requested

I would appreciate feedback on this tutorial. If you have additional ideas, comments or find bugs (I *do* bugs - no way... ;)), please let me know.

Revision History

Copyright

Copyright (c) 2005 Rainer Gerhards and Adiscon.

Permission is granted to copy, distribute and/or modify this document under the terms of the GNU Free Documentation License, Version 1.2 or any later version published by the Free Software Foundation; with no Invariant Sections, no Front-Cover Texts, and no Back-Cover Texts. A copy of the license can be viewed at http://www.gnu.org/copyleft/fdl.html.

mail this link | permapage | score:8365 | -rgerhards, August 10, 2005
Colorful Abstract Art

Selected articles

The life cycle of a programmer

Hacker Haiku

VPS: Xen vs. OpenVZ

Beneficial Computer Viruses

Testing the Digital Ocean $5 Cloud Servers with an MMORPG

No, RMS, Linux is not GNU/Linux

Missing the point of the Mac Mini

Why Programmers are not Software Engineers

Mono-culture and the .NETwork effect

Closed Source Linux Distribution Launched

Programming Language Tradeoffs: 3GL vs 4GL

Scripting: A parallel Linux backup script

Linux dominates Windows

The Real Microsoft Monopoly

Download: Linux 3D Client for Starship Traders

Space Tyrant: Multithreading lessons learned on SMP hardware

Apple to Intel move no threat to Linux

Linux vs. Windows: Why Linux will win

The Network Computer: An opportunity for Linux

The short life and hard times of a Linux virus

Graffiti Server Download Page

Tutorial: Introduction to Linux files

Shadow.sh: A simple directory shadowing script for Linux

How to install Ubuntu Linux on the decTOP SFF computer

Space Tyrant: A multiplayer network game for Linux

Space Tyrant: A threaded C game project: First Code

The Supreme Court is wrong on Copyright Case

Space Tyrant: A threaded game server project in C

Microsoft to push unlicensed users to Linux

Librenix T-Shirts and Coffee Mugs!

Why software sucks

Apple DIY Repair

MiniLesson: An introduction to Linux in ten commands

 

Firefox sidebar

Site map

Site info

News feed

Features

Login
(to post)

Search

 
Articles are owned by their authors.   © 2000-2012 Ray Yeargin