syslog-ng Open Source Edition 3.18 - Administration Guide

sql() destination options

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

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, syslog-ng OSE 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 syslog-ng OSE 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 syslog-ng OSE 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)

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:

Options reliable() and disk-buf-size() are required options.

destination d_demo {
    network(
        "127.0.0.1"
        port(3333)
        disk-buffer(
            mem-buf-size(10000)
            disk-buf-size(2000000)
            reliable(yes)
            dir("/tmp/disk-buffer")
        )
    );
};

destination d_demo {
    network(
        "127.0.0.1"
        port(3333)
           disk-buffer(
            mem-buf-length(10000)
            disk-buf-size(2000000)
            reliable(no)
            dir("/tmp/disk-buffer")
        )
    );
};

flags()

Description: Flags related to the sql() destination.

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

• explicit-commits: By default, syslog-ng OSE 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 flush-lines() parameter. The explicit-commits option is available in syslog-ng OSE version 3.2 and later.

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

flush-lines()

Description: Specifies how many lines are flushed to a destination at a time. The syslog-ng OSE 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.

The syslog-ng OSE application flushes the messages if it has sent flush-lines() number of messages, or the queue became empty. If you stop or reload syslog-ng OSE or in case of network sources, the connection with the client is closed, syslog-ng OSE automatically sends the unsent messages to the destination.

For optimal performance when sending messages to an syslog-ng OSE server, make sure that the flush-lines() is smaller than the window size set using the log-iw-size() option in the source of your server.

frac-digits()

Description: The syslog-ng 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. Note that syslog-ng can add the fractions to non-ISO8601 timestamps as well.

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 the hook-commands() when syslog-ng OSE starts or stops

To execute an external program when syslog-ng OSE starts or stops, use the following options:

Using the hook-commands() when syslog-ng OSE reloads

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

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 syslog-ng to request indexing on the default columns.

The syslog-ng OSE 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.

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(""));
};

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@@"));
};

password()

Description: Password of the database user.

port()

Description: The port number to connect to.

retries()

Description: The number of insertion attempts. If syslog-ng OSE 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, syslog-ng OSE 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 syslog-ng OSE 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-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.

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

stomp: Publishing messages using STOMP

The stomp() driver sends messages to servers (message brokers) using the Simple (or Streaming) Text Oriented Message Protocol (STOMP), formerly known as TTMP. syslog-ng OSE supports version 1.0 of the STOMP protocol. The syslog-ng OSE stomp() driver supports persistence.

The name-value pairs selected with the value-pairs() option will be sent as STOMP headers, while the body of the STOMP message is empty by default (but you can add custom content using the body() option). Publishing the name-value pairs as headers makes it possible to use the Headers exchange-type and subscribe only to interesting log streams.

For the list of available parameters, see stomp() destination options.

Declaration:

stomp( host("<stomp-server-address>") );

destination d_stomp {
    stomp(
        host("localhost")
        port(61613)
        destination("/topic/syslog")
        body("")             # optional, empty by default
        persistent(yes)
        ack(no)
        username("user")     # optional, empty by default
        password("password") # optional, empty by default
        value-pairs(scope(selected-macros, nv-pairs, sdata))
    );
};

stomp() destination options

The stomp() driver publishes messages using the Simple (or Streaming) Text Oriented Message Protocol (STOMP).

The stomp() destination has the following options:

ack()

Description: Request the STOMP server to acknowledge the receipt of the messages. If you enable this option, then after sending a message, syslog-ng OSE waits until the server confirms that it has received the message. This delay can seriously limit the performance of syslog-ng OSE if the message rate is high, and the server cannot acknowledge the messages fast enough.

body()

Description: The body of the STOMP message. You can also use macros and templates.

destination()

Description: The name of the destination (message queue) on the STOMP server. It can include macros and templates.

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:

Options reliable() and disk-buf-size() are required options.

destination d_demo {
    network(
        "127.0.0.1"
        port(3333)
        disk-buffer(
            mem-buf-size(10000)
            disk-buf-size(2000000)
            reliable(yes)
            dir("/tmp/disk-buffer")
        )
    );
};

destination d_demo {
    network(
        "127.0.0.1"
        port(3333)
           disk-buffer(
            mem-buf-length(10000)
            disk-buf-size(2000000)
            reliable(no)
            dir("/tmp/disk-buffer")
        )
    );
};

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 the hook-commands() when syslog-ng OSE starts or stops

To execute an external program when syslog-ng OSE starts or stops, use the following options:

Using the hook-commands() when syslog-ng OSE reloads

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

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

host()

Description: The hostname or IP address of the STOMP server.

password()

Description: The password used to authenticate on the STOMP server.

persistent()

Description: If this option is enabled, the STOMP server or broker will store the messages on its hard disk. That way, the messages will be retained if the STOMP server is restarted, if the message queue is set to be durable on the STOMP server.

port()

Description: The port number of the STOMP server.

retries()

Description: The number of times syslog-ng OSE attempts to send a message to this destination. If syslog-ng OSE could not send a message, it will try again until the number of attempts reaches retries, then drops the message.

throttle()

Description: Sets the maximum number of messages sent to the destination per second. Use this output-rate-limiting functionality only when using disk-buffer as well to avoid the risk of losing messages. Specifying 0 or a lower value sets the output limit to unlimited.

username()

Description: The username used to authenticate on the STOMP server.

value-pairs()

scope("selected-macros" "nv-pairs")

Description: The value-pairs() option creates structured name-value pairs from the data and metadata of the log message. For details on using value-pairs(), see Structuring macros, metadata, and other value-pairs.

syslog: Sending messages to a remote logserver using the IETF-syslog protocol

The syslog() driver sends messages to a remote host (for example a syslog-ng server or relay) on the local intranet or internet using the new standard syslog protocol developed by IETF (for details about the new protocol, see IETF-syslog messages). The protocol supports sending messages using the UDP, TCP, or the encrypted TLS networking protocols.

The required arguments of the driver are the address of the destination host (where messages should be sent). The transport method (networking protocol) is optional, syslog-ng uses the TCP protocol by default. For the list of available optional parameters, see syslog() destination options.

Declaration:

syslog(host transport [options]);

The udp transport method automatically sends multicast packets if a multicast destination address is specified. The tcp and tls methods do not support multicasting.

destination d_tcp { syslog("10.1.2.3" transport("tcp") port(1999) localport(999)); };

destination d_tcp { syslog("target_host" transport("tcp") port(1999) localport(999)); };

destination d_syslog_tls {
    syslog("10.100.20.40"
        transport("tls")
        port(6514)
        tls(peer-verify(required-trusted)
            ca-dir('/opt/syslog-ng/etc/syslog-ng/keys/ca.d/')
            key-file('/opt/syslog-ng/etc/syslog-ng/keys/client_key.pem')
            cert-file('/opt/syslog-ng/etc/syslog-ng/keys/client_certificate.pem')
        )
    );
};