sql: Store messages in an SQL database

• 1: Using the sql() driver with an Oracle database
• 2: Using the sql() driver with a Microsoft SQL database
• 3: Interacting with the database
• 3.1: MySQL-specific interaction methods
• 3.2: MsSQL-specific interaction methods
• 4: sql() destination options

   sql(database_type host_parameters database_parameters [options]);

   destination d_sql {
        sql(type(pgsql)
        host("logserver") username("syslog-ng") password("password")
        database("logs")
        table("messages_${HOST}_${R_YEAR}${R_MONTH}${R_DAY}")
        columns("datetime", "host", "program", "pid", "message")
        values("{$R_DATE}", "${HOST}", "${PROGRAM}", "${PID}", "${MSGONLY}")
        indexes("datetime", "host", "program", "pid", "message"));
    };

   destination d_sql {
        sql(type(pgsql)
        host("logserver") username("syslog-ng") password("password")
        database("logs")
        table("messages_${HOST}_${R_YEAR}${R_MONTH}${R_DAY}")
        columns("datetime varchar(16)", "host varchar(32)", "program  varchar(20)", "pid varchar(8)", "message  varchar(200)")
        values("${R_DATE}", "${HOST}", "${PROGRAM}", "${PID}", "${MSGONLY}")
        indexes("datetime", "host", "program", "pid", "message"));
    };

1 - Using the sql() driver with an Oracle database

The Oracle sql destination has some special aspects that are important to note.

• The hostname of the database server is set in the tnsnames.ora file, not in the host parameter of the sql() destination.

  If the tnsnames.ora file is not located in the /etc directory (or in the /var/opt/oracle directory on Solaris), set the following Oracle-related environment variables, so AxoSyslog will find the file: ORACLE_BASE, ORACLE_HOME, and ORACLE_SID. For details, see the documentation of the Oracle Instant Client.

• You cannot use the same database() settings in more than one destination, because the database() option of the SQL driver is just a reference to the connection string of the tnsnames.ora file. To overcome this problem, you can duplicate the connections in the tnsnames.ora file under a different name, and use a different table in each Oracle destination in AxoSyslog.

• As certain database versions limit the maximum length of table names, macros in the table names should be used with care.

• In the current version of AxoSyslog, the types of database columns must be explicitly set for the Oracle destination. The column used to store the text part of the syslog messages should be able to store messages as long as the longest message permitted by syslog-ng, therefore it is usually recommended to use the varchar2 or clob column type. (The maximum length of the messages can be set using the log-msg-size() option.) For details, see the following example.

• The Oracle Instant Client used by AxoSyslog supports only the following character sets:

  • Single-byte character sets: US7ASCII, WE8DEC, WE8MSWIN1252, and WE8ISO8859P1

  • Unicode character sets: UTF8, AL16UTF16, and AL32UTF8

Example: Using the sql() driver with an Oracle database

The following example sends the log messages into an Oracle database running on the logserver host, which must be set in the /etc/tnsnames.ora file. The messages are inserted into the LOGS database, the name of the table includes the exact date when the messages were sent. The AxoSyslog application automatically creates the required tables and columns, if the user account used to connect to the database has the required privileges.

   destination d_sql {
      sql(type(oracle)
      username("syslog-ng") password("password")
      database("LOGS")
      table("msgs_${R_YEAR}${R_MONTH}${R_DAY}")
      columns("datetime varchar(16)", "host varchar(32)", "program varchar(32)", "pid varchar(8)", "message varchar2")
      values("${R_DATE}", "${HOST}", "${PROGRAM}", "${PID}", "${MSGONLY}")
      indexes("datetime", "host", "program", "pid", "message"));
    };

The Oracle Instant Client retrieves the address of the database server from the /etc/tnsnames.ora file. Edit or create this file as needed for your configuration. A sample is provided below.

   LOGS =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)
    (HOST = logserver)
    (PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVICE_NAME = EXAMPLE.SERVICE)
    )
    )

2 - Using the sql() driver with a Microsoft SQL database

The mssql database driver can access Microsoft SQL (MSSQL) destinations. This driver has some special aspects that are important to note.

• The date format used by the MSSQL database must be explicitly set in the /etc/locales.conf file of the AxoSyslog server. For details, see the following example.

• As certain database versions limit the maximum length of table names, macros in the table names should be used with care.

• In the current version of AxoSyslog, the types of database columns must be explicitly set for the MSSQL destination.

  Warning The following column types cannot be used in MSSQL destinations: nchar, nvarchar, ntext, and xml.

• The column used to store the text part of the syslog messages should be able to store messages as long as the longest message permitted by syslog-ng. The varchar column type can store maximum 4096 bytes-long messages. The maximum length of the messages can be set using the log-msg-size() option. For details, see the following example.

• Remote access for SQL users must be explicitly enabled on the Microsoft Windows host running the Microsoft SQL Server.

Example: Using the sql() driver with an MSSQL database

The following example sends the log messages into an MSSQL database running on the logserver host. The messages are inserted into the syslogng database, the name of the table includes the exact date when the messages were sent. The AxoSyslog application automatically creates the required tables and columns, if the user account used to connect to the database has the required privileges.

   destination d_mssql {
    sql(type(mssql) host("logserver") port("1433")
      username("syslogng") password("syslogng") database("syslogng")
      table("msgs_${R_YEAR}${R_MONTH}${R_DAY}")columns("datetime varchar(16)", "host varchar(32)",
      "program varchar(32)", "pid varchar(8)", "message varchar(4096)")
      values("${R_DATE}", "${HOST}", "${PROGRAM}", "${PID}", "${MSGONLY}")
      indexes("datetime", "host", "program", "pid"));
    };

The date format used by the MSSQL database must be explicitly set in the /etc/locales.conf file of the AxoSyslog server. Edit or create this file as needed for your configuration. A sample is provided below.

   [default]
    date = "%Y-%m-%d %H:%M:%S"

3 - Interacting with the database

SQL operations

Create table:

• If the given table does not exist, AxoSyslog tries to create it with the given column types.

• The AxoSyslog application automatically creates the required tables and columns, if the user account used to connect to the database has the required privileges.

• If AxoSyslog cannot create or alter a table, it tries to do it again when it reaches the next time-reopen().

Alter table:

• If the table structure is different from given structure in an existing table, AxoSyslog tries to add columns in this table but never will delete or modify an existing column.

• If AxoSyslog cannot create or alter a table, it tries to do it again when reach the next time-reopen().

• The AxoSyslog application requires read and write access to the SQL table, otherwise it cannot verify that the destination table exists.

Insert table:

• Insert new records in a table.

• Inserting the records into the database is performed by a separate thread.

• The AxoSyslog application automatically performs the escaping required to insert the messages into the database.

• If insert returns with error, AxoSyslog tries to insert the message +two times by default, then drops it. Retrying time is the value of time-reopen().

Encoding

The AxoSyslog application uses UTF-8 by default when writes logs into database.

Start/stop and reload

Start:

• The AxoSyslog application will connect to database automatically after starting regardless existing incoming messages.

Stop:

• The AxoSyslog application will close the connection to database before shutting down.

Possibility of losing logs:

• The AxoSyslog application cannot lose logs during shutting down if disk buffer was given and it is not full yet.

• The AxoSyslog application cannot lose logs during shutting down if disk buffer was not given.

Reload:

• The AxoSyslog application will close the connection to database if it received SIGHUP signal (reload).

• It will reconnect to the database when it tries to send a new message to this database again.

Macros:

The value of ${SEQNUM} macro will be overridden by sql driver regardless of local or relayed incoming message.

It will be grown continuously.

3.1 - MySQL-specific interaction methods

To specify the socket to use, set and export the MYSQL_UNIX_PORT environment variable, for example, MYSQL_UNIX_PORT=/var/lib/mysql/mysql.sock; export MYSQL_UNIX_PORT.

3.2 - MsSQL-specific interaction methods

In SQL Server 2005 this restriction is lifted - kind of. The total length of all key columns in an index cannot exceed 900 bytes.

If you are using null() in your configuration, be sure that the columns allow NULL to insert. Give the column as the following example: "datetime varchar(16) NULL".

The date format used by the MSSQL database must be explicitly set in the /etc/locales.conf file of the AxoSyslog server. [default] date = "%Y-%m-%d %H:%M:%S".

4 - sql() destination options

This driver sends messages into an SQL database. The sql() destination has the following options:

batch-bytes()

Description: Sets the maximum size of payload in a batch. If the size of the messages reaches this value, AxoSyslog sends the batch to the destination even if the number of messages is less than the value of the batch-lines() option.

Note that if the batch-timeout() option is enabled and the queue becomes empty, AxoSyslog flushes the messages only if batch-timeout() expires, or the batch reaches the limit set in batch-bytes().

Available in AxoSyslog version 3.19 and later.

batch-idle-timeout()

Available in AxoSyslog 4.24 and later.

Description: batch-idle-timeout() measures the elapsed time since the last message was added to the batch (batch-timeout() defines the maximum time used to collect a batch, starting from the first message). If either batch-idle-timeout() or batch-timeout() expires, AxoSyslog closes and sends the batch.

batch-lines()

Description: Specifies how many lines are flushed to a destination in one batch. The AxoSyslog application waits for this number of lines to accumulate and sends them off in a single batch. Increasing this number increases throughput as more messages are sent in a single batch, but also increases message latency. To disable batch-lines(), set it to 0.

For example, if you set batch-lines() to 100, AxoSyslog waits for 100 messages.

If the batch-timeout() option is disabled, the AxoSyslog application flushes the messages if it has sent batch-lines() number of messages, or the queue became empty. If you stop or reload AxoSyslog or in case of network sources, the connection with the client is closed, AxoSyslog automatically sends the unsent messages to the destination.

Note that if the batch-timeout() option is enabled and the queue becomes empty, AxoSyslog flushes the messages only if batch-timeout() expires, or the batch reaches the limit set in batch-lines().

For optimal performance, make sure that the AxoSyslog source that feeds messages to this destination is configured properly: the value of the log-iw-size() / max-connections() of the source must be higher than the batch-lines() * workers() of the destination. Otherwise, the size of the batches cannot reach the batch-lines() limit.

batch-timeout()

Description: Specifies the time AxoSyslog waits for lines to accumulate in the output buffer. The AxoSyslog application sends batches to the destinations evenly. The timer starts when the first message arrives to the buffer, so if only few messages arrive, AxoSyslog sends messages to the destination at most once every batch-timeout() milliseconds.

columns()

Description: Name of the columns storing the data in fieldname [dbtype] format. The [dbtype] parameter is optional, and specifies the type of the field. By default, AxoSyslog creates text columns. Note that not every database engine can index text fields.

create-statement-append()

Description: Specifies additional SQL options that are appended to the CREATE statement. That way you can customize what happens when AxoSyslog creates a new table in the database. Consult the documentation of your database server for details on the available options. Syntax:

   create-statement-append(<options-to-append>)

For example, you can appends the ROW_FORMAT=COMPRESSED option to MySQL create table statements:

   create-statement-append(ROW_FORMAT=COMPRESSED)

database()

Description: Name of the database that stores the logs. Macros cannot be used in database name. Also, when using an Oracle database, you cannot use the same database() settings in more than one destination.

dbd-option()

Description: Specify database options that are set whenever AxoSyslog connects to the database server. Consult the documentation of your database server for details on the available options. Syntax:

   dbd-option(OPTION_NAME VALUE)

OPTION_NAME is always a string, VALUE is a string or a number. For example:

   dbd-option("null.sleep.connect" 1)
    dbd-option("null.sleep.query" 5)

dbdi-driver-dir()

Description: Defines an optional DBI driver location for DBD initialization.

disk-buffer()

Description: This option enables putting outgoing messages into the disk buffer of the destination to avoid message loss in case of a system failure on the destination side. It has the following options:

capacity-bytes()

Description: This is a required option. The maximum size of the disk-buffer in bytes. The minimum value is 1048576 bytes. If you set a smaller value, the minimum value will be used automatically. It replaces the old log-disk-fifo-size() option.

In AxoSyslog version 4.2 and earlier, this option was called disk-buf-size().

compaction()

Description: If set to yes, AxoSyslog prunes the unused space in the LogMessage representation, making the disk queue size smaller at the cost of some CPU time. Setting the compaction() argument to yes is recommended when numerous name-value pairs are unset during processing, or when the same names are set multiple times.

dir()

Description: Defines the folder where the disk-buffer files are stored.

flow-control-window-bytes()

Description: Use this option if the option reliable() is set to yes. This option contains the size of the messages in bytes that is used in the memory part of the disk buffer. It replaces the old log-fifo-size() option. It does not inherit the value of the global log-fifo-size() option, even if it is provided. Note that this option will be ignored if the option reliable() is set to no.

In AxoSyslog version 4.2 and earlier, this option was called mem-buf-size().

flow-control-window-size()

Description: Use this option if the option reliable() is set to no. This option contains the number of messages stored in overflow queue. It replaces the old log-fifo-size() option. It inherits the value of the global log-fifo-size() option if provided. If it is not provided, the default value is 10000 messages. Note that this option will be ignored if the option reliable() is set to yes.

In AxoSyslog version 4.2 and earlier, this option was called mem-buf-length().

front-cache-size()

Description: The number of messages stored in the output buffer of the destination. Note that if you change the value of this option and the disk-buffer already exists, the change will take effect when the disk-buffer becomes empty.

Options reliable() and capacity-bytes() are required options.

In AxoSyslog version 4.2 and earlier, this option was called qout-size().

prealloc()

Description:

By default, AxoSyslog doesn’t reserve the disk space for the disk-buffer file, since in a properly configured and sized environment the disk-buffer is practically empty, so a large preallocated disk-buffer file is just a waste of disk space. But a preallocated buffer can prevent other data from using the intended buffer space (and elicit a warning from the OS if disk space is low), preventing message loss if the buffer is actually needed. To avoid this problem, when using AxoSyslog 4.0 or later, you can preallocate the space for your disk-buffer files by setting prealloc(yes).

In addition to making sure that the required disk space is available when needed, preallocated disk-buffer files provide radically better (3-4x) performance as well: in case of an outage the amount of messages stored in the disk-buffer is continuously growing, and using large continuous files is faster, than constantly waiting on a file to change its size.

If you are running AxoSyslog on a dedicated host (always recommended for any high-volume settings), use prealloc(yes).

Available in AxoSyslog 4.0 and later.

reliable()

Description: If set to yes, AxoSyslog cannot lose logs in case of reload/restart, unreachable destination or AxoSyslog crash. This solution provides a slower, but reliable disk-buffer option. It is created and initialized at startup and gradually grows as new messages arrive. If set to no, the normal disk-buffer will be used. This provides a faster, but less reliable disk-buffer option.

truncate-size-ratio()

Description: Limits the truncation of the disk-buffer file. Truncating the disk-buffer file can slow down the disk IO operations, but it saves disk space. By default, AxoSyslog version 4.0 and later doesn’t truncate disk-buffer files by default (truncate-size-ratio(1)). Earlier versions freed the disk-space when at least 10% of the disk-buffer file could be freed (truncate-size-ratio(0.1)).

AxoSyslog only truncates the file if the possible disk gain is more than truncate-size-ratio() times capacity-bytes().

• Smaller values free disk space quicker.
• Larger ratios result in better performance.

If you want to avoid performance fluctuations:

• use truncate-size-ratio(1) (never truncate), or
• use prealloc(yes) to reserve the entire size of the disk-buffer on disk.

Example: Examples for using disk-buffer()

In the following case reliable disk-buffer() is used.

destination d_demo {
    network(
        "127.0.0.1"
        port(3333)
        disk-buffer(
            flow-control-window-bytes(10000)
            capacity-bytes(2000000)
            reliable(yes)
            dir("/tmp/disk-buffer")
        )
    );
};

In the following case normal disk-buffer() is used.

destination d_demo {
    network(
        "127.0.0.1"
        port(3333)
            disk-buffer(
            flow-control-window-size(10000)
            capacity-bytes(2000000)
            reliable(no)
            dir("/tmp/disk-buffer")
        )
    );
};

flags()

Description: Flags related to the sql() destination.

• dont-create-tables: Enable this flag to prevent AxoSyslog from creating non-existing database tables automatically. The AxoSyslog application typically has to create tables if you use macros in the table names. Available in AxoSyslog version 3.2 and later.

• explicit-commits: By default, AxoSyslog commits every log message to the target database individually. When the explicit-commits option is enabled, messages are committed in batches. This improves the performance, but results in some latency, as the messages are not immediately sent to the database. The size and frequency of batched commits can be set using the batch-lines() parameter. The explicit-commits option is available in AxoSyslog version 3.2 and later.

Example: Setting flags for SQL destinations

The following example sets the dont-create-tables and explicit-commits flags for an sql() destination.

   flags(dont-create-tables,explicit-commits)

frac-digits()

Description: The AxoSyslog application can store fractions of a second in the timestamps according to the ISO8601 format. The frac-digits() parameter specifies the number of digits stored. The digits storing the fractions are padded by zeros if the original timestamp of the message specifies only seconds. Fractions can always be stored for the time the message was received.

hook-commands()

Description: This option makes it possible to execute external programs when the relevant driver is initialized or torn down. The hook-commands() can be used with all source and destination drivers with the exception of the usertty() and internal() drivers.

Using hook-commands() when AxoSyslog starts or stops

To execute an external program when AxoSyslog starts or stops, use the following options:

startup()

Description: Defines the external program that is executed as AxoSyslog starts.

shutdown()

Description: Defines the external program that is executed as AxoSyslog stops.

Using the hook-commands() when AxoSyslog reloads

To execute an external program when the AxoSyslog configuration is initiated or torn down, for example, on startup/shutdown or during a AxoSyslog reload, use the following options:

setup()

Description: Defines an external program that is executed when the AxoSyslog configuration is initiated, for example, on startup or during a AxoSyslog reload.

teardown()

Description: Defines an external program that is executed when the AxoSyslog configuration is stopped or torn down, for example, on shutdown or during a AxoSyslog reload.

Example: Using hook-commands() with a network source

In the following example, the hook-commands() is used with the network() driver and it opens an iptables port automatically as AxoSyslog is started/stopped.

The assumption in this example is that the LOGCHAIN chain is part of a larger ruleset that routes traffic to it. Whenever the AxoSyslog created rule is there, packets can flow, otherwise the port is closed.

source {
    network(transport(udp)
    hook-commands(
          startup("iptables -I LOGCHAIN 1 -p udp --dport 514 -j ACCEPT")
          shutdown("iptables -D LOGCHAIN 1")
        )
     );
};

host()

Description: Hostname of the database server. Note that Oracle destinations do not use this parameter, but retrieve the hostname from the /etc/tnsnames.ora file.

indexes()

Description: The list of columns that are indexed by the database to speed up searching. To disable indexing for the destination, include the empty indexes() parameter in the destination, simply omitting the indexes parameter will cause AxoSyslog to request indexing on the default columns.

The AxoSyslog application will create the name of indexes automaticaly with the following method:

• In case of MsSQL, PostgreSQL, MySQL or SQLite or (Oracle but tablename < 30 characters): {table}_{column}_idx.

• In case of Oracle and tablename > 30 characters: md5sum of {table}_{column}-1 and the first character will be replaced by “i” character and the md5sum will be truncated to 30 characters.

local-time-zone()

Description: Sets the timezone used when expanding filename and tablename templates.

The timezone can be specified by using the name, for example, time-zone("Europe/Budapest")), or as the timezone offset in +/-HH:MM format, for example, +01:00). On Linux and UNIX platforms, the valid timezone names are listed under the /usr/share/zoneinfo directory.

log-fifo-size()

Description: The number of messages that the output queue can store.

null()

Description: If the content of a column matches the string specified in the null() parameter, the contents of the column will be replaced with an SQL NULL value. If unset (by default), the option does not match on any string. For details, see the Example: Using SQL NULL values.

Example: Using SQL NULL values

The null() parameter of the SQL driver can be used to replace the contents of a column with a special SQL NULL value. To replace every column that contains an empty string with NULL, use the null("") option, for example

   destination d_sql {
        sql(type(pgsql)
        host("logserver") username("syslog-ng") password("password")
        database("logs")
        table("messages_${HOST}_${R_YEAR}${R_MONTH}${R_DAY}")
        columns("datetime", "host", "program", "pid", "message")
        values("${R_DATE}", "${HOST}", "${PROGRAM}", "${PID}", "${MSGONLY}")
        indexes("datetime", "host", "program", "pid", "message")
        null(""));
    };

To replace only a specific column (for example, pid) if it is empty, assign a default value to the column, and use this default value in the null() parameter:

   destination d_sql {
        sql(type(pgsql)
        host("logserver") username("syslog-ng") password("password")
        database("logs")
        table("messages_${HOST}_${R_YEAR}${R_MONTH}${R_DAY}")
        columns("datetime", "host", "program", "pid", "message")
        values("${R_DATE}", "${HOST}", "${PROGRAM}", "${PID:-@@NULL@@}", "${MSGONLY}")
        indexes("datetime", "host", "program", "pid", "message")
        null("@@NULL@@"));
    };

Ensure that the default value you use does not appear in the actual log messages, because other occurrences of this string will be replaced with NULL as well.

password()

Description: Password of the database user.

port()

Description: The port number to connect to.

quote-char()

Available in AxoSyslog version 4.3.0 and newer.

Description: Set custom quoting for table and index names (for example, MySQL needs sometimes this for certain identifiers).

Note: Using a backtick character needs special formatting, because AxoSyslog uses backticks for configuration parameter names. To use backticks as quote character, set a double backtick: quote-char("``")

retries()

Description: The number of insertion attempts. If AxoSyslog could not insert a message into the database, it will repeat the attempt until the number of attempts reaches retries, then drops the connection to the database. For example, AxoSyslog will try to insert a message maximum three times by default (once for first insertion and twice if the first insertion was failed).

session-statements()

Description: Specifies one or more SQL-like statement which is executed after AxoSyslog has successfully connected to the database. For example:

   session-statements("SET COLLATION_CONNECTION='utf8_general_ci'")

table()

Description: Name of the database table to use (can include macros). When using macros, note that some databases limit the length of table names.

time-reopen()

Description: The time to wait in seconds before a dead connection is reestablished.

time-zone()

Description: Convert timestamps to the timezone specified by this option. If this option is not set, then the original timezone information in the message is used. Converting the timezone changes the values of all date-related macros derived from the timestamp, for example, HOUR. For the complete list of such macros, see Date-related macros.

The timezone can be specified by using the name, for example, time-zone("Europe/Budapest")), or as the timezone offset in +/-HH:MM format, for example, +01:00). On Linux and UNIX platforms, the valid timezone names are listed under the /usr/share/zoneinfo directory.

type()

Description: Specifies the type of the database, that is, the DBI database driver to use. Use the mssql option to send logs to an MSSQL database. For details, see the examples of the databases on the following sections.

username()

Description: Name of the database user.

values()

Description: The parts of the message to store in the fields specified in the columns() parameter.

It is possible to give a special value calling: default (without quotation marks).It means that the value will be used that is the default of the column type of this value.

Example: Value: default

   columns("date datetime", "host varchar(32)", "row_id serial")
        values("${R_DATE}", "${HOST}", default)