Chat now with support
Chat with Support

syslog-ng Premium Edition 7.0.29 - Administration Guide

Preface Introduction to syslog-ng The concepts of syslog-ng Installing syslog-ng PE The syslog-ng PE quick-start guide The syslog-ng PE configuration file Collecting log messages — sources and source drivers
How sources work default-network-drivers: Receive and parse common syslog messages internal: Collecting internal messages file: Collecting messages from text files google-pubsub: collecting messages from the Google Pub/Sub messaging service wildcard-file: Collecting messages from multiple text files linux-audit: Collecting messages from Linux audit logs mssql, oracle, sql: collecting messages from an SQL database network: Collecting messages using the RFC3164 protocol (network() driver) office365: Fetching logs from Office 365 osquery: Collect and parse osquery result logs pipe: Collecting messages from named pipes program: Receiving messages from external applications python: writing server-style Python sources python-fetcher: writing fetcher-style Python sources snmptrap: Read Net-SNMP traps syslog: Collecting messages using the IETF syslog protocol (syslog() driver) system: Collecting the system-specific log messages of a platform systemd-journal: Collecting messages from the systemd-journal system log storage systemd-syslog: Collecting systemd messages using a socket tcp, tcp6, udp, udp6: Collecting messages from remote hosts using the BSD syslog protocol udp-balancer: Receiving UDP messages at very high rate unix-stream, unix-dgram: Collecting messages from UNIX domain sockets windowsevent: Collecting Windows event logs
Sending and storing log messages — destinations and destination drivers
elasticsearch2: Sending messages directly to Elasticsearch version 2.0 or higher (DEPRECATED) elasticsearch-http: Sending messages to Elasticsearch HTTP Event Collector file: Storing messages in plain-text files google_pubsub(): Sending logs to the Google Cloud Pub/Sub messaging service hdfs: Storing messages on the Hadoop Distributed File System (HDFS) http: Posting messages over HTTP without Java kafka(): Publishing messages to Apache Kafka (Java implementation) (DEPRECATED) kafka-c(): Publishing messages to Apache Kafka using the librdkafka client (C implementation) logstore: Storing messages in encrypted files mongodb: Storing messages in a MongoDB database network: Sending messages to a remote log server using the RFC3164 protocol (network() driver) pipe: Sending messages to named pipes program: Sending messages to external applications python: writing custom Python destinations sentinel(): Sending logs to the Microsoft Azure Sentinel cloud snmp: Sending SNMP traps smtp: Generating SMTP messages (email) from logs splunk-hec: Sending messages to Splunk HTTP Event Collector sql(): Storing messages in an SQL database stackdriver: Sending logs to the Google Stackdriver cloud syslog: Sending messages to a remote logserver using the IETF-syslog protocol syslog-ng(): Forward logs to another syslog-ng node tcp, tcp6, udp, udp6: Sending messages to a remote log server using the legacy BSD-syslog protocol (tcp(), udp() drivers) unix-stream, unix-dgram: Sending messages to UNIX domain sockets usertty: Sending messages to a user terminal — usertty() destination Client-side failover
Routing messages: log paths, flags, and filters Global options of syslog-ng PE TLS-encrypted message transfer Advanced Log Transfer Protocol Reliability and minimizing the loss of log messages Manipulating messages parser: Parse and segment structured messages Processing message content with a pattern database Correlating log messages Enriching log messages with external data Monitoring statistics and metrics of syslog-ng Multithreading and scaling in syslog-ng PE Troubleshooting syslog-ng Best practices and examples The syslog-ng manual pages Glossary

mssql(), oracle(), and sql() source options

The mssql(), oracle(), and sql() drivers have the following options.

columns()
Type: string list
Default: empty

Description: The list of the name of the columns that will be queried. The default value is empty, meaning that all of the columns will be queried.

Example
columns("id","date","message")

connection-timeout()
Type: nonnegative integer
Default: 5

Description: Optional. The syslog-ng PE application waits connection-timeout() seconds for any request to complete. If a request does not succeed in the given time, syslog-ng PE will disconnect, and tries to reconnect after time-reopen seconds. 0 value means infinite timeout.

connect-query()
Type: string
Default:

Description: The SQL-like statement which is executed after syslog-ng PE has successfully connected to the database.

 Caution:

Hazard of data loss!

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 on your own responsibility.

Example: sample connect-query() statement

The following example is a sample connect-query() statement:

connect-query("SET COLLATION_CONNECTION='utf8_general_ci'")
database()
Type: string
Default: logs

Description: Name of the database that stores the logs. Macros cannot be used in database name.

date-column(col_name, [format])
Type: date, string
Default:

Description: The column containing the date of the logrecord. The format value has to be in strptime format. For details, see strptime(3) - Linux man page.

datetime-column(col_name, [format])
Type: string
Default:

Description: The column containing the timestamp. If the type is int, it is considered to contain a UNIX timestamp. The format value is required if the type is string, and has to be in strptime format. For details, see strptime(3) - Linux man page.

The following column types are supported:

  • oracle(): timestamp, int

  • mssql(): datetime, int

Example: sample datetime-column(col_name, [format])

The following example is a sample datetime-column(col_name, [format]) column containing the timestamp:

columns("id","date","message")datetime("timestampcol", "%Y-%m-%d")
default-facility()
Type: facility string
Default: user

Description: This parameter assigns a facility value to the messages received from the sql() source.

default-severity()
Type: severity string
Default: notice

Description: This parameter assigns a severity level to the messages received from the sql() source.

fast-follow-mode()
Type: yes | no
Default: yes

Description: If set to yes, syslog-ng PE reads the database table as fast as possible, until it reaches the last record. After this, it will execute only one query in follow-freq() time. If it is set to no, syslog-ng PE executes only one query in follow-freq() time.

fetch-query()
Type: string
Default:

Default value is generated in running time of syslog-ng PE

Description: The SQL-like statement used to collect the records from the database.

NOTE: If this parameter is defined, syslog-ng PE does not check or validate it whether it is correct. Ensure that the customized statements are correct.

For details on customizing queries, see Customizing mssql() queries.

 Caution:

Hazard of data loss!

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 on your own responsibility.

Example: sample fetch-query() statement
The following example is a sample fetch-query() statement:
fetch-query("SELECT * FROM $table WHERE id > $last_read_uid AND test_logs.log LIKE '%ERROR%' ORDER BY $uid")

The default fetch-query() statements for the mssql() and oracle() sources are the following:

  • mssql():

    SELECT TOP $fetch_limit $columns FROM $table WHERE $uid > '$last_read_uid' ORDER BY $uid

  • oracle():

    SELECT $columns FROM (SELECT * FROM $table WHERE $uid > $last_read_uid ORDER BY $uid) WHERE rownum <= $fetch_limit
follow-freq()
Type: number(seconds)
Default: If time-reopen() is set to a value other than the default 60, the value of time-reopen(). Otherwise 60.

Description: The syslog-ng PE application checks whether the SQL source changed every time the follow-freq() interval (in seconds) has elapsed. Floating-point numbers (for example, 1.5) can be used as well.

host()
Type: hostname or IP address
Default: n/a

Description: Hostname of the database server.

host-template()
Type: string
Default: empty string

Description: The template for defining the HOST part of the message. If the host-template() option is not specified, the value of the host() option will be used in the HOST part of the message.

log-fetch-limit()
Type:
Default: 100

Description: The maximum number of messages fetched from a source during a single poll loop.

log-iw-size()
Type: number (messages)
Default: 100

Description: The size of the initial window, this value is used during flow control.

login-timeout()
Type: nonnegative integer
Default: 5

Description: Optional. The syslog-ng PE application waits login-timeout() seconds for the login request to complete. If the request does not succeed in the given time, syslog-ng PE will try to reconnect after time-reopen() seconds. 0 value means infinite timeout.

max-uid-query()
Type: string
Default:

SELECT max($uid) FROM $table

Description: Used for retrieving the ID of the last row (that is, the last row of the last fetch) from the database source.

message-template()
Type: string
Default:

Description: The alias of the template() parameter.

password()
Type: string
Default: n/a

Description: Password of the database user.

port()
Type: number (port number)
Default:

1433 TCP for MSSQL, 1521 for Oracle

Description: The port number to connect to.

prefix()
Type: string
Default: .sql.

Description: This prefix will be added to the name of the macros created from the database columns.

Example: sample prefix() - database column name - macro name correlation

The prefix() and database column name affect the name of the macro created from the database column.

For example, if a database column is called column1, and the prefix option is set as prefix("customprefix."), the macro for the column will be called customprefix.column1.

program-template()
Type: string
Default: empty string

Description: The template for defining the PROGRAM part of the message. If not specified, the PROGRAM message part will be empty.

start-uid()
Type: string
Default:

Description:

  • mssql(): Optional.
  • oracle(): Mandatory. If start-uid() is specified, syslog-ng PE will only query entries with a strictly larger uid. After the first successfully fetched message, syslog-ng PE does not use this option anymore, even after restart or reload. If start-uid() is not specified, syslog-ng PE will read only the new records.
table()
Type: string
Default:

Description: The name of the monitored table. Only a single literal name is accepted, macros cannot be used in the name of the table. Monitoring rotated tables is not supported.

table-init-query()
Type: string
Default:

Description: The SQL-like statement which is executed before fetching the first batch of records.

For details on customizing MSSQL queries, see Customizing mssql() queries.

 Caution:

Hazard of data loss!

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 on your own responsibility.

tags()
Type: string
Default:

Description: Label the messages received from the source with custom tags. Tags must be enclosed between double quotes. When adding multiple tags, separate them with commas, for example, tags("dmz", "router").

template()
Type: string
Default:

Description: The template of the message (${MSG}) to be generated. If not specified, the following template will be used: "$(format-welf --key <prefix>*)") where the <prefix> is the value of the prefix() option. This template converts the retrieved records into the WebTrends Enhanced Log file Format (WELF).

NOTE: The format-welf function does not keep the order of columns between queries.

Example: sample queries' results for a table with two columns using the template() option

The following examples show the results of the first, and second queries when using the default template for a table that has two columns (id and message):

The result of the first query is the following:

'.sql.id=12 .sql.message="test message"'

The result of the second query can be:

'.sql.message="test message" .sql.id=12 '
time-column(col_name, [format])
Type: time, string
Default:

Description: The column containing the time of the logrecord. The format value has to be in strptime format.

time-reopen()
Type: number (seconds)
Default: 60

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

tls()
Type:

tls options

Default: none

Description: The tls() configuration block enables TLS encryption for MSSQL Server.

NOTE: The tls() configuration block used for the network() source is similar in functionality, but there is no 100% feature parity between them. Consider the details for each tls() configuration block option before using them with your mssql() source.

The tls() configuration block has the following options:

  • ca-file()

    Type: Filename

    Default: none

    Description: The name of a file that contains an X.509 CA certificate (or a certificate chain) in PEM format. The syslog-ng PE application uses this certificate to validate the certificate of the MSSQL server.

  • peer-verify()

    Type: yes | no

    Default: yes

    Description: In syslog-ng PE version 7.0.28, this option only enables an extra check for the server CN matching against the hostname (configured in syslog-ng PE via the host() option).

    NOTE: This option is only effective if you also specify a ca-file() in your configuration block.

  • ssl-options()

    Type: comma-separated list

    Possible values: no-tlsv1 | none

    Default: none

    Description: This option allows disabling TLS protocols. Consider that it only disables the current version, but not older versions.

    NOTE: The default none value does not restrict TLS protocols, but uses the default TLS value set for the mssql() source ( 1.0, 1.2).

type()
Type:

mssql, or oracle

Default:

Description: Specifies the type of the database. Only use it with the generic sql() source.

uid-column()
Type: string or string type(string)
Supported column types are: integer, character type, datetime, datetime2
Default:

Description: The monotonically increasing unique ID of the monitored table (for example, auto_increment). This column must be a type where the greater (>) operation is interpreted.

The second optional parameter is a type hint (for example, uid-column(uid' type('DATE')). If provided, syslog-ng PE uses the given string in the SQL queries.

username()
Type: string
Default: n/a

Description: Name of the database user.

use-syslogng-pid()
Type: yes | no
Default: no

Description: If the value of this option is yes, then the pid value of the message will be overridden with the pid of the running syslog-ng PE process.

use-tnsnames()
Type: yes | no
Default: For sql(): yes
For oracle(): no

Description: Optional. This option is used only in case of oracle(), or sql() source with type("oracle") option. If set to yes, syslog-ng PE will use the connection parameters defined in the tnsnames.ora configuration file.

Customizing mssql() queries

Every query executed by the mssql() source can be customized. These customized queries are similar to SQL statements, but in can also refer to syslog-ng PE-specific variables with the prefix $. For example, $table is the name of the table.

 Caution:

Hazard of data loss!

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 on your own responsibility.

The available variables are the following:

  • $last_read_uid: The uid value of the last read record.
    • $columns: Reach the columns() option of the MSSQL source (the default is *). If $columns is defined in the configuration (for example, columns("id" "message")), then the value of this variable will be a comma separated list (for example, "id,message").
    • $fetch_limit: Reach the value of the log-fetch-limit() option.
    • $log_fetch_limit: Alias for $fetch_limit.
    • $table: The name of the table() option.
    • $uid: The name of the uid-column().
    • $uid_column: Alias for $uid.

    NOTE: The variables in the statement are not macro references.

    Example: mssql() source fetch-query()

    The following mssql() source fetch-query() example queries records that are older than the last record:

    SELECT * FROM $table WHERE $table.$uid > $last_read_uid ORDER BY $table.$uid

    • Configuring TLS encryption for MSSQL servers

    From version 7.0.28, syslog-ng Premium Edition (syslog-ng PE) supports TLS encryption for MSSQL servers.

    For more information, see Enable encrypted connections to the Database Engine in the Microsoft SQL Docs online documentation.

    Prerequisites

    Using TLS encryption for the MSSQL server with syslog-ng PE requires CA certificates correctly configured on the MSSQL server side.

    Limitations

    Using TLS encryption for the MSSQL server with syslog-ng PE has the following limitations:

    NOTE: The tls() configuration block used for the network() source is similar in functionality, but there is no 100% feature parity between them. Consider the details for each tls() configuration block option before using them with your mssql() source.

    Configuration

    You can enable TLS encryption for your mssql() source on the syslog-ng PE side by including it in your configuration as follows:

    source{ 
  mssql( 
    host(...) 
    ... 
    tls( ca-file(path/to/ca-file.pem) peer-verify(yes) ssl-options(no-tlsv1) )
  );
};

    For further details about TLS encryption for your mssql() source, see the description of the tls() configuration block.

    Enabling TLS encryption for your MSSQL server

    To enable TLS encryption for MSSQL Server,

    1. Open the SQL Server Configuration Manager (mmc snapin), navigate to SQL Server Network Configuration > Protocols for SQL Server, and right click Protocols for SQL Server to open the Properties page.

    2. On the Flags tab, set Force Encryption to yes.

    3. On the Certificate tab, use TLS CA Certificate to browse or import custom TLS CA Certificates for your encrypted connections.

    4. To confirm your settings, click OK.

    5. Under SQL Server Services, then restart SQL Server instance.

    Possible connection errors between the MSSQL server (2019) and syslog-ng PE 7 LTS

    While using MSSQL servers with syslog-ng Premium Edition (syslog-ng PE), you may run into connection errors.

    This section provides information about possible connection errors, the return errors you encounter in your syslog-ng PE console log in these cases, the possible reasons behind the issues, and their possible resolutions.

    For details about configuring TLS encryption for your MSSQL server, see Configuring TLS encryption for MSSQL servers.

    Connection errors listed in this section:

    TCP/IP connection error between syslog-ng PE and the MSSQL server

    Console return error: 

    [2021-10-06T09:02:13.708984] Failed to connect to DB; connection-string='DRIVER=libtdsodbc.so;SERVER=mssql-server;PORT=1433;UID=sa;PWD=password;DATABASE=master;ClientCharset=UTF-8', ODBC-Diag='SQLRETURN: SQL_ERROR(-1) | entry: 1, SQLSTATE: 08S01, error_code: 20009, error_string: [FreeTDS][SQL Server]Unable to connect: Adaptive Server is unavailable or does not exist | entry: 2, SQLSTATE: 08001, error_code: 0, error_string: [FreeTDS][SQL Server]Unable to connect to data source', driver='s_mssql_win_2019#0'

    Possible reason: There is a TCP/IP connection error between syslog-ng PE and the MSSQL server.

    Possible resolution: Enable TCP/IP protocol over the SQL server.

    To enable TCP/IP protocol over SQL server,

    1. Open the SQL Server Configuration Manager (mmc snapin), and navigate to SQL Server Network Configuration > Protocols for SQL Server.

    2. Next to TCP/IP, double click on the Disabled status.

    3. Next to Enable, change the option from No to Yes.

    4. Switch to the IP Addresses tab.

    5. In the IPAll settings node list, find TCP port, and set it to 1433. .

    6. To confirm the settings, click OK.

    7. Under SQL Server Services, select the SQL server instance you want to restart.

    8. Check your syslog-ng PE connection again.

    Incorrect SQL server settings for allowed connections and user settings

    Console return error: 

    [2021-10-06T09:16:04.719213] Failed to connect to DB; connection-string='DRIVER=libtdsodbc.so;SERVER=mssql-server;PORT=1433;UID=test_user;PWD=password;DATABASE=master;ClientCharset=UTF-8', ODBC-Diag='SQLRETURN: SQL_ERROR(-1) | entry: 1, SQLSTATE: 42000, error_code: 18456, error_string: [FreeTDS][SQL Server]Login failed for user \'test_user\'. | entry: 2, SQLSTATE: 08001, error_code: 0, error_string: [FreeTDS][SQL Server]Unable to connect to data source', driver='s_mssql_win_2019#0'

    Possible reason: Your SQL server settings for allowed connections and user settings are incorrect.

    Possible resolutions:

    To resolve the issue, try one of the following methods:

    • Check Server Authentication mode in your MSSQL server settings

      1. Start Microsoft SQL Server Management Studio and log in with the following credentials:

        • Server type: Database Engine

        • Server name: <your-current-installation> (default)

        • Authentication:Windows Authentication

      2. Open Server Properties and navigate to Security page.

      3. Under Server Authentication, enable the following SQL Server and Windows Authentication mode.

      4. Open the SQL Server Configuration Manager (mmc snapin), and navigate to SQL Server Services, Restart SQL Server instance.

    • Check Remote server connections in your MSSQL server settings

      1. Log in to Microsoft SQL Server Management Studio with the previously used mode.

      2. Open Server Properties and navigate to the Connections page.

      3. Under Remote server connections, check if Allow remote connections to this server is enabled.

    • Check SQL Login status and Roles

      1. Log in to Microsoft SQL Server Management Studio with the previously used mode.

      2. Navigate to Security > Logins, and right click Login to open the Properties page for your Login user.

      3. Check that all of the following pages have the right settings for your Login user:

        • General

        • Server Roles

        • User mapping

        • Securables

        • Status

    Login may not have proper permissions to the SQL server

    Console return error: 

    [2021-10-06T09:22:51.479930] Failed to execute fetch-query; ODBC-Diag='SQLRETURN: SQL_ERROR(-1) | entry: 1, SQLSTATE: 42000, error_code: 297, error_string: [FreeTDS][SQL Server]The user does not have permission to perform this action. | entry: 2, SQLSTATE: 42000, error_code: 300, error_string: [FreeTDS][SQL Server]VIEW SERVER STATE permission was denied on object \'server\', database \'master\'.', driver='s_mssql_win_2019#0'

    Possible reason: Login may not have proper permissions to the SQL server.

    Possible resolutions:

    To resolve the issue, try one of the following methods check SQL Login status and Roles:

    1. Log in to Microsoft SQL Server Management Studio with the previously used mode.

    2. Navigate to Security > Logins, and right click Login to open the Properties page for your Login user.

    3. Check that the Server Roles page has the right status and roles for your Login user.

    Low level logging with the mssql() source

    In case of the mssql() source, the user can enable an extra level of logging. Developers and users new to syslog-ng PE commonly use this option.

    You can use the following debug logging methods:

    • The syslog-ng PE PE7 FreeTDS verbose debug log method

      If you start syslog-ng PE with the following enabled environment variable, you can find FreeTDS debug logs in the configured output file:

      export TDSDUMP=/tmp/freetds_debug.log

      TIP: This debug logging method is recommended if you start syslog-ng PE from service.

    • The syslog-ng PE PE7 FreeTDS stdout debug log method

      If you start syslog-ng PE with the following enabled environment variable, you can find FreeTDS debug logs in syslog-ng PE's stdout:

      export TDSDUMP=stdout

      TIP: This debug logging method is recommended if you start syslog-ng PE from the foreground.

    Related Documents

    The document was helpful.

    Select Rating

    I easily found the information I needed.

    Select Rating