Using a strgen module to write into a database

In many cases, log messages have to be transformed. This can be done in various ways with the property replacer for example. But processing messages this way can be rather slow, since the transformation part is no native code. In this case, strgen (string generator) is the way to go. A string generator is a separate plugin, that will be loaded in rsyslog. It provides a native C interface for template generation and thus speed up message transformation. String generators are not a very easy thing to create yourself. But Adiscon happily offers this as paid service to support the rsyslog project.

A string generator usually takes a message, that is in a certain format and transforms it into another format. That is where the speed bonus comes into play, since there is no dynamic message transformation.

In this example we want to show how a string generator is used that has been created for a customer. Basically, the string generator takes a message and transforms it into a specific MySQL INSERT statement to write the message into a database with a different database scheme. The database scheme is customer specific as well, so this is rather not usable by someone else. But the steps shown can be applied to every other strgen as well.

To test the correct transformation of messages, we can write messages into a file as well. But, this is in this case just to check the correct output. The file should then hold a SQL INSERT statement for every message that has been sent.

What we need

Basically we need a linux system. The steps will be described with a installation of Fedora 13. If another system is being used, some paths need to be changed. Please note, that rsyslog is already the default syslog daemon here. But we need a newer version. If the sysklogd or another syslog daemon is still present on the system you are using, you need to permanently disable it.

The setup

Our setup should reflect a configuration of rsyslog, which is able to receive syslog via UDP. The messages that are received should be “filtered” for several IP addresses. The message with IPs that evaluate the filter to true will be discarded (description in the configuration part). All other messages should be transformed into SQL statements via the strgen plugin and then injected into a MySQL database on the same system.

On this system we need the usual suspects.


These are most likely needed and should be installed. Further, you need a server that holds the MySQL database. Perhaps you have this installed on the same server. In that case, you need to check the MySQL documentation for instructions. We will only describe the installation of rsyslog here. For our example, the MySQL server will be on the same system.

Please note, that the syslog messages that can be processed by the strgen plugin are very specific and the created INSERT statements do only fit a certain database scheme.

Setting up rsyslog

Basically, we need to set up rsyslog. The strgen plugin we will use is called “sm_cust_bindcdr”. It has been released in the rsyslog v5.8.0 stable release. But, due to some changes afterwards, we need to use the latest v5-stable version from the git repository. Assuming the above mentioned packages are installed we will start directly.

Open up a terminal with root permission and change to the folder which should later hold the rsyslog files. In our case we will install rsyslog directly in the user directory, though this is not recommended. When you have switched to that folder, type the following:

git clone git://

You will see, that the current state of rsyslog development will be downloaded from the git repository into a folder rsyslog. By default, the downloaded git repository does reflect the current master branch (v6-devel currently). Therefore we need to switch to the newly created folder rsyslog and change the branch by doing the following:

git checkout v5-stable

You should get a confirmation message, that the branch has been switched accordingly. Now we can start installing rsyslog. This is done by using the following commands one after another:

./configure --libdir=/lib --sbindir=/sbin --enable-mysql --enable-smcustbindcdr
make install

Basically, we have rsyslog v5.8.0 stable now installed.

Configuring rsyslog

We are now ready to configure rsyslog. Open the configuration file for rsyslog. It is located here:


Usually, this is a basic configuration that has been shipped with the operating system. In the end, our configuration should look somehow like this (the minimum for our scenario):

$ModLoad ommysql
$ModLoad sm_cust_bindcdr

$UDPServerRun 514
$template sm,=Custom_BindCDR,sql
*.* :ommysql:,Data,test,pass;sm

At the top, we have the modules loaded which we need. The first module is for receiving UDP syslog. The second module handles the MySQL capabilities of rsyslog. The third module is our custom string generator plugin.

After that, the UDP receiver is enabled. We will use the default UDP port 514.

Now we define the “filters”. We will use the configuration $sgcustombindcdrallowedip for this. This directive is included in the strgen plugin and only available if the plugin was loaded. Basically we define a IP, which will be compared to the IP in the syslog message. If the IP is the same, the message will be discarded. If the IP is different, the message will be further processed. Behind this is a system where some service is offered. Some IPs are allowed to do that for free, others are not. Those which are not allowed to use the service for free are collected in the database for later billing. This directive can be used multiple times with different IPs. Basically the only limit for the amount of filters is either the address space or the main memory of the machine.

After the filters, we define a template which is used for the final processing. Basically, this works like a regular template. We define a custom name for the template (“sm” in this case) and after the comma we tell the template what it looks like. In this case, we use the template name of the strgen plugin – “Custom_BindCDR,sql”. The name is introduced by “=”. This is very important. If this is missed out, the template will not know, that it has to get the format information from the plugin. You could also say, that you call a template in a template.

Finally, we have our action. This is the last part of the configuration. Basically, we want to forward all messages (“*.*”) to our MySQL server. We access the server by defining its address, the database name, user and password. The final part of this statement is “;sm” which calls the template.

That’s it. Save the configuration file now and exit your editor.

Getting rsyslog to run

We have rsyslog installed and configured now. The only thing left to do is to restart rsyslog. Right now, the old version is still running. When we restart rsyslog, the new installation with our new configuration file will be loaded. Now use the following command to stop and start rsyslog:

service rsyslog restart

This works at least in Fedora. If it does not work for you, you can as well use this:

/etc/init.d/rsyslog restart

Final thoughts

We have now achieved what we wanted. We can receive our messages, filter and transform then and inject them into our database. The important thoughts were on the string generator. The format of the messages cannot be changed later. The problem in this case is the common use, which is virtualy not possible. The module will only work with messages of a certain format and create a INSERT statement that has a very customer specific form as well. So it will only work if the correct messages are received and the database scheme fits as well. All other mesages will be dropped.

Scroll to top