back

Writing syslog messages to MySQL

Written by Rainer Gerhards (2008-02-28)

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. Please note that as of this writing, rsyslog supports a variety of databases. While this guide is still MySQL-focussed, you can probably use it together with other ones too. You just need to modify a few settings.

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. On some distributions  (Fedora 8 and above, for example), rsyslog may already by the default syslogd, in which case you obviously do not need to do anything specific. For many others, there are prebuild packages available. If you use either, please make sure that you have the required database plugins for your database available. It usually is a separate package and typically not installed by default.

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.

MySQL support in rsyslog is integrated via a loadable plug-in module. To use the database functionality, MySQL must be enabled in the config file BEFORE the first database table action is used. This is done by placing the

$ModLoad ommysql

directive at the begining of /etc/rsyslog.conf. For other databases, use their plugin name (e.g. ompgsql).

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:

*.*       :ommysql:database-server,database-name,database-userid,database-password

Again, other databases have other selector names, e.g. ":ompgsql:" instead of ":ommysql:". See the output plugin's documentation for details.

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:

*.*       :ommysql: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.*       :ommysql: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.

Message loss in this scenario can easily be prevented with rsyslog. All you need to do is run the database writer in queued mode. This is now described in a generic way and I do not intend to duplicate it here. So please be sure to read "Handling a massive syslog database insert rate with Rsyslog", which describes the scenario and also includes configuration examples.

Conclusion

With minimal effort, you can use rsyslogd to write syslog messages to a MySQL database. You can even make it absolutely fail-safe and protect it against database server downtime. 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 used queued mode (which in general is recommended with databases).

The method outlined in this paper provides an easy to setup and maintain solution for most use cases.

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-2008 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.

[manual index] [rsyslog.conf] [rsyslog site]

This documentation is part of the rsyslog project.
Copyright © 2008 by Rainer Gerhards and Adiscon. Released under the GNU GPL version 2 or higher.

id='n364' href='#n364'>364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 
#
#  tkextlib/tkHTML/htmlwidget.rb
#                               by Hidetoshi NAGAI (nagai@ai.kyutech.ac.jp)
#

require 'tk'

# call setup script for general 'tkextlib' libraries
require 'tkextlib/setup.rb'

# call setup script
require 'tkextlib/tkHTML/setup.rb'

# TkPackage.require('Tkhtml', '2.0')
TkPackage.require('Tkhtml')

module Tk
  class HTML_Widget < TkWindow
    PACKAGE_NAME = 'Tkhtml'.freeze
    def self.package_name
      PACKAGE_NAME
    end

    def self.package_version
      begin
        TkPackage.require('Tkhtml')
      rescue
        ''
      end
    end

    class ClippingWindow < TkWindow
    end
  end
end

class Tk::HTML_Widget::ClippingWindow
  WidgetClassName = 'HtmlClip'.freeze
  WidgetClassNames[WidgetClassName] = self

  HtmlClip_TBL = TkCore::INTERP.create_table

  TkCore::INTERP.init_ip_env{
    HtmlClip_TBL.mutex.synchronize{ HtmlClip_TBL.clear }
  }

  def self.new(parent, keys={})
    if parent.kind_of?(Hash)
      keys = TkComm._symbolkey2str(parent)
      parent = keys.delete('parent')
    end

    if parent.kind_of?(String)
      ppath = parent.path
    elsif parent
      ppath = parent
    else
      ppath = ''
    end
    HtmlClip_TBL.mutex.synchronize{
      return HtmlClip_TBL[ppath] if HtmlClip_TBL[ppath]
    }

    widgetname = keys.delete('widgetname')
    if widgetname =~ /^(.*)\.[^.]+$/
      ppath2 = $1
      if ppath2[0] != ?.
        ppath2 = ppath + '.' + ppath2
      end
      HtmlClip_TBL.mutex.synchronize{
        return HtmlClip_TBL[ppath2] if HtmlClip_TBL[ppath2]
      }

      ppath = ppath2
    end
    
    parent = TkComm._genobj_for_tkwidget(ppath)
    unless parent.kind_of?(Tk::HTML_Widget)
      fail ArgumentError, "parent must be a Tk::HTML_Widget instance"
    end

    super(parent)
  end

  def initialize(parent)
    @parent = parent
    @ppath = parent.path
    @path = @id = @ppath + '.x'
    HtmlClip_TBL.mutex.synchronize{
      HtmlClip_TBL[@ppath] = self
    }
  end

  def method_missing(m, *args, &b)
    @parent.__send__(m, *args, &b)
  end
end

class Tk::HTML_Widget
  include Scrollable

  TkCommandNames = ['html'.freeze].freeze
  WidgetClassName = 'Html'.freeze
  WidgetClassNames[WidgetClassName] = self

  def create_self(keys)
    if keys and keys != None
      tk_call_without_enc(self.class::TkCommandNames[0], @path, 
                          *hash_kv(keys, true))
    else
      tk_call_without_enc(self.class::TkCommandNames[0], @path)
    end
  end
  private :create_self

  def __strval_optkeys
    super() << 'base' << 'selectioncolor' << 'unvisitedcolor' << 'visitedcolor'
  end
  private :__strval_optkeys

  ###################################
  #  class methods
  ###################################
  def self.reformat(src, dst, txt)
    tk_call('html', 'reformat', src, dst, txt)
  end

  def self.url_join(*args) # args := sheme authority path query fragment
    tk_call('html', 'urljoin', *args)
  end

  def self.url_split(uri)
    tk_call('html', 'urlsplit', uri)
  end

  def self.lockcopy(src, dst)
    tk_call('html', 'lockcopy', src, dst)
  end

  def self.gzip_file(file, dat)
    tk_call('html', 'gzip', 'file', file, dat)
  end

  def self.gunzip_file(file, dat)
    tk_call('html', 'gunzip', 'file', filet)
  end

  def self.gzip_data(dat)
    tk_call('html', 'gzip', 'data', file, dat)
  end

  def self.gunzip_data(dat)
    tk_call('html', 'gunzip', 'data', filet)
  end

  def self.base64_encode(dat)
    tk_call('html', 'base64', 'encode', dat)
  end

  def self.base64_decode(dat)
    tk_call('html', 'base64', 'encode', dat)
  end

  def self.text_format(dat, len)
    tk_call('html', 'text', 'format', dat, len)
  end

  def self.xor(cmd, *args)
    tk_call('html', 'xor', cmd, *args)
  end

  def self.stdchan(cmd, channel)
    tk_call('html', 'stdchan', cmd, channel)
  end

  def self.crc32(data)
    tk_call('html', 'crc32', data)
  end

  ###################################
  #  instance methods
  ###################################
  def clipping_window
    ClippingWindow.new(self)
  end
  alias clipwin  clipping_window
  alias htmlclip clipping_window

  def bgimage(image, tid=None)
    tk_send('bgimage', image, tid)
    self
  end

  def clear()
    tk_send('clear')
    self
  end

  def coords(index=None, percent=None)
    tk_send('coords', index, percent)
  end

  def forminfo(*args)
    tk_send('forminfo', *args)
  end
  alias form_info forminfo

  def href(x, y)
    simplelist(tk_send('href', x, y))
  end

  def image_add(id, img)
    tk_send('imageadd', id, img)
    self
  end

  def image_at(x, y)
    tk_send('imageat', x, y)
  end

  def images()
    list(tk_send('images'))
  end

  def image_set(id, num)
    tk_send('imageset', id, num)
    self
  end

  def image_update(id, imgs)
    tk_send('imageupdate', id, imgs)
    self
  end

  def index(idx, count=None, unit=None)
    tk_send('index', idx, count, unit)
  end

  def insert_cursor(idx)
    tk_send('insert', idx)
  end

  def names()
    simple_list(tk_send('names'))
  end

  def on_screen(id, x, y)
    bool(tk_send('onscreen', id, x, y))
  end

  def over(x, y)
    list(tk_send('over', x, y))
  end

  def over_markup(x, y)
    list(tk_send('over', x, y, '-muponly'))
  end

  def over_attr(x, y, attrs)
    list(tk_send('overattr', x, y, attrs))
  end

  def parse(txt)
    tk_send('parse', txt)
    self
  end

  def resolver(*uri_args)
    tk_send('resolver', *uri_args)
  end

  def selection_clear()
    tk_send('selection', 'clear')
    self
  end

  def selection_set(first, last)
    tk_send('selection', 'set', first, last)
    self
  end

  def refresh(*opts)
    tk_send('refresh', *opts)
  end

  def layout()
    tk_send('layout')
  end

  def sizewindow(*args)
    tk_send('sizewindow', *args)
  end

  def postscript(*args)
    tk_send('postscript', *args)
  end

  def source()
    tk_send('source')
  end

  def plain_text(first, last)
    tk_send('text', 'ascii', first, last)
  end
  alias ascii_text plain_text
  alias text_ascii plain_text

  def text_delete(first, last)
    tk_send('text', 'delete', first, last)
    self
  end

  def html_text(first, last)
    tk_send('text', 'html', first, last)
  end
  alias text_html html_text

  def text_insert(idx, txt)
    tk_send('text', 'insert', idx, txt)
    self
  end

  def break_text(idx)
    tk_send('text', 'break', idx)
  end
  alias text_break break_text

  def text_find(txt, *args)
    tk_send('text', 'find', txt, *args)
  end

  def text_table(idx, imgs=None, attrs=None)
    tk_send('text', 'table', idx, imgs, attrs)
  end

  def token_append(tag, *args)
    tk_send('token', 'append', tag, *args)
    self
  end

  def token_delete(first, last=None)
    tk_send('token', 'delete', first, last)
    self
  end

  def token_define(*args)
    tk_send('token', 'defile', *args)
    self
  end

  def token_find(tag, *args)
    list(tk_send('token', 'find', tag, *args))
  end

  def token_get(first, last=None)
    list(tk_send('token', 'get', first, last))
  end

  def token_list(first, last=None)
    list(tk_send('token', 'list', first, last))
  end

  def token_markup(first, last=None)
    list(tk_send('token', 'markup', first, last))
  end

  def token_DOM(first, last=None)
    list(tk_send('token', 'domtokens', first, last))
  end
  alias token_dom token_DOM
  alias token_domtokens token_DOM
  alias token_dom_tokens token_DOM

  def token_get_end(idx)
    tk_send('token', 'getend', idx)
  end
  alias token_getend token_get_end

  def token_offset(start, num1, num2)
    list(tk_send('token', 'offset', start, num1, num2))
  end

  def token_get_attr(idx, name=None)
    list(tk_send('token', 'attr', idx, name))
  end

  def token_set_attr(idx, name=None, val=None)
    tk_send('token', 'attr', idx, name, val)
    self
  end

  def token_handler(tag, cmd=nil, &b)
    cmd = Proc.new(&b) if !cmd && b
    if cmd
      tk_send('token', 'handler', tag, cmd)
      return self
    else
      return tk_send('token', 'handler', tag)
    end
  end

  def token_insert(idx, tag, *args)
    tk_send('token', 'insert', idx, tag, *args)
    self
  end

  def token_attrs(*args)
    list(tk_send('token', 'attrs', *args))
  end

  def token_unique(*args)
    list(tk_send('token', 'unique', *args))
  end

  def token_on_events(*args)
    list(tk_send('token', 'onEvents', *args))
  end

  def dom_nameidx(tag, name)
    number(tk_send('dom', 'nameidx', tag, name))
  end
  alias dom_name_index dom_nameidx

  def dom_radioidx(tag, name)
    number(tk_send('dom', 'radioidx', tag, name))
  end
  alias dom_radio_index dom_radioidx

  def dom_id(*spec)
    tk_send('dom', 'id', *spec)
  end

  def dom_ids(*spec)
    list(tk_send('dom', 'ids', *spec))
  end

  def dom_value(*spec)
    list(tk_send('dom', 'value', *spec))
  end

  def dom_attr(idx)
    tk_send('dom', 'attr', idx)
  end

  def dom_formel(name)
    tk_send('dom', 'formel', name)
  end
  alias dom_form_element dom_formel

  def dom_tree(idx, val)
    list(tk_send('dom', 'tree', idx, val))
  end
end
generated by cgit (git 2.34.1) at 2026-03-30 20:31:32 +0000