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://git.adiscon.com/git/rsyslog.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 imudp.so
$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.

Storing and forwarding remote messages

In this scenario, we want to store remote sent messages into a specific local file and forward the received messages to another syslog server. Local messages should still be locally stored.

Things to think about

How should this work out? Basically, we need a syslog listener for TCP and one for UDP, the local logging service and two rulesets, one for the local logging and one for the remote logging.

TCP recpetion is not a build-in capability. You need to load the imtcp plugin in order to enable it. This needs to be done only once in rsyslog.conf. Do it right at the top.

Note that the server port address specified in $InputTCPServerRun must match the port address that the clients send messages to.

Config Statements

# Modules
$ModLoad imtcp
$ModLoad imudp
$ModLoad imuxsock
$ModLoad imklog
# Templates
# log every host in its own directory
$template RemoteHost,"/var/syslog/hosts/%HOSTNAME%/%$YEAR%/%$MONTH%/%$DAY%/syslog.log"
### Rulesets
# Local Logging
$RuleSet local
kern.*                                                 /var/log/messages
*.info;mail.none;authpriv.none;cron.none                /var/log/messages
authpriv.*                                              /var/log/secure
mail.*                                                  -/var/log/maillog
cron.*                                                  /var/log/cron
*.emerg                                                 *
uucp,news.crit                                          /var/log/spooler
local7.*                                                /var/log/boot.log
# use the local RuleSet as default if not specified otherwise
$DefaultRuleset local

# Remote Logging
$RuleSet remote
*.* ?RemoteHost
# Send messages we receive to Gremlin
*.* @@W.X.Y.Z:514
### Listeners
# bind ruleset to tcp listener
$InputTCPServerBindRuleset remote
# and activate it:
$InputTCPServerRun 10514

$InputUDPServerBindRuleset remote
$UDPServerRun 514

How it works

The configuration basically works in 4 parts. First, we load all the modules (imtcp, imudp, imuxsock, imklog). Then we specify the templates for creating files. The we create the rulesets which we can use for the different receivers. And last we set the listeners.

The rulesets are somewhat interesting to look at. The ruleset “local” will be set as the default ruleset. That means, that it will be used by any listener if it is not specified otherwise. Further, this ruleset uses the default log paths vor various facilities and severities.

The ruleset “remote” on the other hand takes care of the local logging and forwarding of all log messages that are received either via UDP or TCP. First, all the messages will be stored in a local file. The filename will be generated with the help of the template at the beginning of our configuration (in our example a rather complex folder structure will be used). After logging into the file, all the messages will be forwarded to another syslog server via TCP.

In the last part of the configuration we set the syslog listeners. We first bind the listener to the ruleset “remote”, then we give it the directive to run the listener with the port to use. In our case we use 10514 for TCP and 514 for UDP.


There are some tricks in this configuration. Since we are actively using the rulesets, we must specify those rulesets before being able to bind them to a listener. That means, the order in the configuration is somewhat different than usual. Usually we would put the listener commands on top of the configuration right after the modules. Now we need to specify the rulesets first, then set the listeners (including the bind command). This is due to the current configuration design of rsyslog. To bind a listener to a ruleset, the ruleset object must at least be present before the listener is created. And that is why we need this kind of order for our configuration.

Using the syslog receiver module

We want to use rsyslog in its general purpose. We want to receive syslog. In rsyslog, we have two possibilities to achieve that.

Things to think about

First of all, we will determine, which way of syslog reception we want to use. We can receive syslog via UDP or TCP. The config statements are each a bit different in both cases.

In most cases, UDP syslog should be fully sufficient and performing well. But, you should be aware, that on large message bursts messages can be dropped. That is not the case with TCP syslog, since the sender and receiver communicate about the arrival of network packets. That makes TCP syslog more suitable for environments where log messages may not be lost or that must ensure PCI compliance (like banks).

Config Statements

module(load="imudp") # needs to be done just once
input(type="imudp" port="514")


module(load="imtcp") # needs to be done just once
input(type="imtcp" port="514")

How it works

The configuration part for the syslog server is pretty simple basically. Though, there are some parameters that can be set for both modules. But this is not necessary in most cases.

In general, first the module needs to be loaded. This is done via the directive module().

module(load="imudp / imtcp")

The module must be loaded, because the directives and functions rely on it. Just by using the right commands, rsyslog will not know where to get the functionality code from.

And next is the command that runs the syslog server itself is called input().

input(type="imudp" port="514")
input(type="imtcp" port="514")

Basically, the command says to run a syslog server on a specific port. Depending on the command, you can easily determine the UDP and the TCP server.

You can of course use both types of syslog server at the same time, too. You just need to load both modules for that and configure the server command to listen to specific ports. Then you can receive both UDP and TCP syslog.


In general, we suggest to use TCP syslog. It is way more reliable than UDP syslog and still pretty fast. The main reason is, that UDP might suffer of message loss. This happens when the syslog server must receive large bursts of messages. If the system buffer for UDP is full, all other messages will be dropped. With TCP, this will not happen. But sometimes it might be good to have a UDP server configured as well. That is, because some devices (like routers) are not able to send TCP syslog by design. In that case, you would need both syslog server types to have everything covered. If you need both syslog server types configured, please make sure they run on proper ports. By default UDP syslog is received on port 514. TCP syslog needs a different port because often the RPC service is using this port as well.

Scroll to top