syslog-ng Premium Edition 7.0.31 - Administration Guide

NOTE: To use this destination, syslog-ng Premium Edition (syslog-ng PE) must run in server mode. Typically, only the central syslog-ng PE server uses this destination. For more information on the server mode, see Server mode.

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

columns()

Type:	string list
Default:	"date", "facility", "level", "host", "program", "pid", "message"

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 PE creates text columns. Note that not every database engine can index text fields.

Caution:

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

database()

Type:	string
Default:	logs

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()

Type:	string
Default:	empty string

Description: Specify database options that are set whenever syslog-ng PE 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 file of the destination to avoid message loss in case of a system failure on the destination side. It has the following options:

dir()

Type:

string

Default:

N/A

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

Note that changing the value the dir() option will not move or copy existing files from the old directory to the new one.

Caution:

When creating a new dir() option for a disk-buffer file, or modifying an existing one, make sure you delete the persist file.

syslog-ng PE creates disk-buffer files based on the path recorded in the persist file. Therefore, if the persist file is not deleted after modifying the dir() option, then following a restart, syslog-ng PE will look for or create disk-buffer files in their old location. To ensure that syslog-ng PE uses the new dir() setting, the persist file must not contain any information about the destinations which the disk-buffer file in question belongs to.

disk-buf-size()
Type:	number [bytes]
Default:
Description: This is a required option. The maximum size of the disk-buffer file 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.

mem-buf-length()
Type:	number [messages]
Default:	10000
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.

mem-buf-size()
Type:	number [bytes]
Default:	163840000
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 file. 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.

quot-size()
Type:	number [messages]
Default:	1000
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 file already exists, the change will take effect when the disk-buffer file becomes empty.

reliable()

Type:

yes|no

Default:

Description: If set to yes, syslog-ng PE cannot lose logs in case of reload/restart, unreachable destination or syslog-ng PE 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 option will be used. This provides a faster, but less reliable disk-buffer option.

Caution: Hazard of data loss!

If you change the value of reliable() option when there are messages in the disk-buffer file, the messages stored in the disk-buffer file will be lost.

truncate-size-ratio()

Type:

number (for percentage) between 0 and 1

Default:

0.1 (10%)

Description: Limits the truncation of the disk-buffer file. Truncating the disk-buffer file can slow down disk I/O operations, but it saves disk space. As a result, syslog-ng PE only truncates the file if the possible disk gain is more than truncate-size-ratio() times disk-buf-size().

Caution:

One Identity recommends that you do not modify the value of the truncate-size-ratio() option unless you are fully aware of the potential performance implications.

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(
            mem-buf-size(10000)
            disk-buf-size(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(
            mem-buf-length(10000)
            disk-buf-size(2000000)
            reliable(no)
            dir("/tmp/disk-buffer")
        )
    );
};

flags()

Type:	list of flags
Default:	empty string

Description: Flags related to the sql() destination.

dont-create-tables: Enable this flag to prevent syslog-ng PE from creating non-existing database tables automatically. The syslog-ng PE application typically has to create tables if you use macros in the table names. Available in syslog-ng PE version 4.0 and later.
explicit-commits: By default, syslog-ng PE 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() and flush-timeout() parameters. The explicit-commits option is available in syslog-ng PE version 4.0 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)

flush-lines()

Type:	number
Default:	Use global setting.

Description: Specifies how many lines are flushed to a destination at a time. The syslog-ng PE 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 PE 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 PE or in case of network sources, the connection with the client is closed, syslog-ng PE automatically sends the unsent messages to the destination.

For optimal performance when sending messages to a syslog-ng PE 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.

flush-timeout() (DEPRECATED)

Type:	time in milliseconds
Default:	Use global setting.

Description: This is a deprecated option. Specifies the time syslog-ng waits for lines to accumulate in its output buffer. For details, see the flush-lines() option.

frac-digits()

Type:	number
Default:	0

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

NOTE: As syslog-ng PE is precise up to the microsecond, when the frac-digits() option is set to a value higher than 6, syslog-ng PE will truncate the fraction seconds in the timestamps after 6 digits.

host()

Type:	hostname or IP address
Default:	n/a

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, unless you set ignore-tns-ora(yes).

NOTE: If you specify host="localhost", syslog-ng will use a socket to connect to the local database server. Use host="127.0.0.1" to force TCP communication between syslog-ng and the local database server.

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.

ignore-tns-ora()

Type:	yes or no
Default:	no

Description: If set to yes, the Oracle sql destination does not use the /etc/tnsnames.ora file, but uses the hostname set in the host() option. Available in syslog-ng Premium Edition version 7.0.9.

indexes()

Type:	string list
Default:	"date", "facility", "host", "program"

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 PE application will create the name of indexes automatically 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()

Type:	name of the timezone, or the timezone offset
Default:	The local timezone.

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

The timezone can be specified as using the name of the (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()

Type:	number
Default:	Use global setting.

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

null()

Type:	string
Default:

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()

Type:	string
Default:	n/a

Description: Password of the database user.

port()

Type:	number
Default:	1433 TCP for MSSQL, 3306 TCP for MySQL, 1521 for Oracle, and 5432 TCP for PostgreSQL

Description: The port number to connect to.

retries()

Type:	number (insertion attempts)
Default:	3

Description: The number of insertion attempts. If syslog-ng PE 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 PE 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()

Type:	comma-separated list of SQL statements
Default:	empty string

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

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

Caution:

The syslog-ng PE application does not validate or limit the contents of customized queries. Consequently, queries performed with a user with write-access can potentially modify or even harm the database. Use customized queries with care, and only for your own responsibility.

table()

Type:	string
Default:	messages

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()

Type:	name of the timezone, or the timezone offset
Default:	unspecified

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.

type()

Type:	mssql, mysql, oracle, pgsql, or sqlite3
Default:	mysql

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()

Type:	string
Default:	n/a

Description: Name of the database user.

values()

Type:	string list
Default:	"${R_YEAR}-${R_MONTH}-${R_DAY}, ${R_HOUR}:${R_MIN}:${R_SEC}", "${FACILITY}", "${LEVEL}", "${HOST}", "${PROGRAM}", "${PID}", "${MSGONLY}"

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)

The stackdriver() destination of syslog-ng PE can send log messages to the Google Stackdriver cloud. Google Stackdriver is a widely used metrics, event, and log aggregator and analyzer system. The stackdriver destination is available in syslog-ng PE version 7.0.14 and later.

How the stackdriver() destination works

The stackdriver() destination uses the HTTP REST API to perform OAuth2 authentication to Google Stackdriver and obtains an access token from Stackdriver using the key specified in a JSON file. This access token is required to send logs to Stackdriver using the Stackdriver Logging API.

The syslog-ng PE application automatically refreshes the token when it expires (usually every 60 minutes). The syslog-ng PE application stores the token it obtains, even if you restart or reload syslog-ng PE. If you change the JSON key on your syslog-ng PE host, syslog-ng PE will start using the new key only when the stored access token expires or becomes invalid.

By default, syslog-ng PE uses the default system CA certificate store to validate the certificate sent by Google Stackdriver. If the Certificate Authority of the certificate sent by Google Stackdriver is not available on your host, you must download the CA certificate and add it to the certificate store. The location of the certificate store depends on your platform. Most commonly, it is one of the following (or a similar location):

/etc/ssl/certs/
/etc/pki/ca-trust/
/etc/pki/tls/certs/

Limitations

The log_id() option of the destination currently does not support macros or templates, only strings. As a result, every log entry has the same log id.
Currently the following resource types are supported: generic_node, generic_task, and global. You can configure other resource types, but they are untested.
Each syslog-ng PE stackdriver() destination can use only one resource type. If you want to send logs using multiple resource types, you must configure multiple stackdriver() destinations.
When referring to options in the syslog-ng PE configuration file, the hyphen (-) and underscore (_) characters are usually interchangeable. In the stackdriver destination, you must use underscore (_) in the options that syslog-ng PE passes directly to Google Stackdriver. These options are the following:
- gcp_auth_header and its contents
- log_id
- project_id
- resource() and its contents.

NOTE: Typically, only the central syslog-ng PE server uses this destination. For more information on the server mode, see Server mode.

Declaration

destination d_stackdriver {
  stackdriver(
    gcp_auth_header(
      credentials("<path-to-the-service.json>")
    )
    log_id("<folder-name-for-logs-in-stackdriver>")
    resource(
      project_id("<identifier-of-the-GCP-project>")
      <parameters-of-the-monitored-resource>
    )
  );
};

Example: Sending log messages to Google Stackdriver

Using a generic_node() resource type to send log messages to Google Stackdriver

destination d_stackdriver {
  stackdriver(
    gcp_auth_header(
      credentials("/opt/syslog-ng/etc/service.json")
    )
    log_id("123456")
    resource(
      generic_node(
        project_id("my-test-project")
        location("EU/Budapest")
        namespace("my cluster")
        node_id("$HOST")
      )
    )
    [...]
  );
};

Batch size

The batch-lines(), batch-lines(), and batch-timeout() options of the destination determine how many log messages syslog-ng PE sends in a batch. The batch-lines() option determines the maximum number of messages syslog-ng PE puts in a batch in. This can be limited based on size and time:

syslog-ng PE sends a batch every batch-timeout() milliseconds, even if the number of messages in the batch is less than batch-lines(). This ensures that the destination receives every message in a timely manner even if suddenly there are no more messages.
syslog-ng PE sends the batch if the total size of the messages in the batch reaches batch-bytes() bytes.

To increase the performance of the destination, increase the number of worker threads for the destination using the workers() option, or adjust the batch-bytes(), batch-lines(), batch-timeout() options.

syslog-ng Premium Edition 7.0.31 - Administration Guide

MsSQL-specific interaction methods

sql() destination options

columns()

database()

dbd-option()

disk-buffer()

Example: Examples for using disk-buffer()

flags()

Example: Setting flags for SQL destinations

flush-lines()

flush-timeout() (DEPRECATED)

frac-digits()

host()

ignore-tns-ora()

indexes()

local-time-zone()

log-fifo-size()

null()

Example: Using SQL NULL values

password()

port()

retries()

session-statements()

table()

time-zone()

type()

username()

values()

Example: Value: default

stackdriver: Sending logs to the Google Stackdriver cloud

How the stackdriver() destination works

Limitations

Declaration

Example: Sending log messages to Google Stackdriver

Batch size

Configuring syslog-ng PE to send logs to Google Stackdriver

Prerequisites

Please select your product:

To serve you better, please complete the Purpose of your Chat:

Recommended Solutions for Your Problem

syslog-ng Premium Edition 7.0.31 - Administration Guide

MsSQL-specific interaction methods

sql() destination options

columns()

database()

dbd-option()

disk-buffer()

Example: Examples for using disk-buffer()

flags()

Example: Setting flags for SQL destinations

flush-lines()

flush-timeout() (DEPRECATED)

frac-digits()

host()

ignore-tns-ora()

indexes()

local-time-zone()

log-fifo-size()

null()

Example: Using SQL NULL values

password()

port()

retries()

session-statements()

table()

time-zone()

type()

username()

values()

Example: Value: default

stackdriver: Sending logs to the Google Stackdriver cloud

How the stackdriver() destination works

Limitations

Declaration

Example: Sending log messages to Google Stackdriver

Batch size

Configuring syslog-ng PE to send logs to Google Stackdriver

Prerequisites