Chat now with support
Chat with Support

syslog-ng Premium Edition 7.0.34 - 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_bigquery(): Sending logs to a Google BigQuery table google_bigquery_managedaccount(): Sending logs to a Google BigQuery table authenticated by Google Cloud managed service account google_pubsub(): Sending logs to the Google Cloud Pub/Sub messaging service google_pubsub-managedaccount(): Sending logs to the Google Cloud Pub/Sub messaging service authenticated by Google Cloud managed service account hdfs: Storing messages on the Hadoop Distributed File System (HDFS) http: Posting messages over HTTP 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 Transport 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

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.

Caution:

Using unique column names in mssql() fetch-query() results is mandatory from syslog-ng PE 7.0.34. Explicit usage of "AS" SQL keyword could help to achieve this. Ignoring it may raise an error in mssql() source driver and queries will be dropped.

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.

    network: Collecting messages using the RFC3164 protocol (network() driver)

    The network() source driver can receive syslog messages conforming to RFC3164 from the network using the TCP, TLS, and UDP networking protocols.

    You can use the ALTP protocol as well. For details about the ALTP protocol, see Advanced Log Transport Protocol .

    • UDP is a simple datagram oriented protocol, which provides "best effort service" to transfer messages between hosts. It may lose messages, and no attempt is made to retransmit lost messages. The BSD-syslog protocol traditionally uses UDP.

      Use UDP only if you have no other choice.

    • TCP provides connection-oriented service: the client and the server establish a connection, each message is acknowledged, and lost packets are resent. TCP can detect lost connections, and messages are lost, only if the TCP connection breaks. When a TCP connection is broken, messages that the client has sent but were not yet received on the server are lost.

    • The syslog-ng application supports TLS (Transport Layer Security, also known as SSL) over TCP. For details, see Encrypting log messages with TLS.

    Declaration:
    network([options]);

    By default, the network() driver binds to 0.0.0.0, meaning that it listens on every available IPV4 interface on the TCP/514 port. To limit accepted connections to only one interface, use the localip() parameter. To listen on IPv6 addresses, use the ip-protocol(6) option.

    Example: Using the network() driver

    Using only the default settings: listen on every available IPV4 interface on the TCP/514 port.

    source s_network {
        network();
    };

    UDP source listening on 192.168.1.1 (the default port for UDP is 514):

    source s_network {
        network(
            ip("192.168.1.1")
            transport("udp")
        );
    };

    TCP source listening on the IPv6 localhost, port 2222:

    source s_network6 {
        network(
            ip("::1")
            transport("tcp")
            port(2222)
            ip-protocol(6)
        );
    };

    A TCP source listening on a TLS-encrypted channel.

    source s_network {
        network(
            transport("tcp")
            port(2222)
            tls(
                peer-verify("required-trusted")
                key-file("/opt/syslog-ng/etc/syslog-ng/syslog-ng.key")
                cert-file("/opt/syslog-ng/etc/syslog-ng/syslog-ng.crt")
            );
        );
    };

    A TCP source listening for messages using the IETF-syslog message format. Note that for transferring IETF-syslog messages, generally you are recommended to use the syslog() driver on both the client and the server, as it uses both the IETF-syslog message format and the protocol. For details, see syslog: Collecting messages using the IETF syslog protocol (syslog() driver).

    source s_tcp_syslog {
        network(
            ip("127.0.0.1")
            flags(syslog-protocol)
        );
    };

    For details on the options of the network() source, see network() source options.

    NOTE: To receive UDP messages at very high message rate, you can use the udp-balancer() source. For details, see udp-balancer: Receiving UDP messages at very high rate.

    Related Documents

    The document was helpful.

    Select Rating

    I easily found the information I needed.

    Select Rating