syslog-ng Premium Edition 7.0.34 - Administration Guide

The sql() driver sends messages into an SQL database. Currently the Microsoft SQL (MSSQL), MySQL, Oracle, PostgreSQL, and SQLite databases are supported.

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.

Declaration

sql(database_type host_parameters database_parameters [options]);

The sql() driver has the following required parameters: type(), database(), table(), columns(), and values().

Caution:

The syslog-ng Premium Edition (syslog-ng PE) application requires read and write access to the SQL table, otherwise it cannot verify that the destination table exists.

Currently the syslog-ng PE application has default schemas for the different databases and uses these defaults if the database schema (for example, columns and column types) is not defined in the configuration file. However, these schemas will be deprecated and specifying the exact database schema will be required in later versions of syslog-ng PE.

NOTE: In addition to the standard syslog-ng PE packages, the sql() destination requires database-specific packages to be installed. These packages are automatically installed by the binary syslog-ng PE installer.

The sql() driver is currently not available for every platform that is supported by syslog-ng PE. .

The table and value parameters can include macros to create tables and columns dynamically (for details, see Macros of syslog-ng PE).

Caution:

When using macros in table names, note that some databases limit the maximum allowed length of table names. Consult the documentation of the database for details.

Inserting the records into the database is performed by a separate thread. The syslog-ng PE application automatically performs the escaping required to insert the messages into the database.

Example: Using the sql() driver

The following example sends the log messages into a PostgreSQL database running on the logserver host. The messages are inserted into the logs database, the name of the table includes the exact date and the name of the host sending the messages. The syslog-ng PE 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(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")
    );
};

The following example specifies the type of the database columns as well:

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

Using the sql() driver with an Oracle database

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

There are two ways to set the hostname of the database server:
- Set the hostname 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 syslog-ng PE will find the file: ORACLE_BASE, ORACLE_HOME, and ORACLE_SID. For details, see the documentation of the Oracle Instant Client.
  
  Note that in this case 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 syslog-ng PE.
- Set the hostname in the host() option of the destination, and set the ignore-tns-ora() to yes. In this case, syslog-ng PE ignores the tnsnames.ora file, and you can use the same database settings in multiple destinations. Note that the ignore-tns-ora() option is available in syslog-ng Premium Edition version 7.0.9.
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 syslog-ng PE, 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 PE, 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 syslog-ng PE 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 syslog-ng PE 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)
)
)

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 syslog-ng PE 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 syslog-ng PE, the types of database columns must be explicitly set for the MSSQL destination.

Caution:
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 PE. 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. For details, see Configuring Microsoft SQL Server to accept logs from syslog-ng.

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 syslog-ng PE 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 syslog-ng PE server. Edit or create this file as needed for your configuration.

You can copy and customize the following sample:

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

The way syslog-ng PE interacts with the database

SQL operations syslog-ng Premium Edition (syslog-ng PE) uses

Create table:

If the given table does not exist, syslog-ng PE tries to create it with the given column types.
The syslog-ng PE application automatically creates the required tables and columns, if the user account used to connect to the database has the required privileges.
If syslog-ng PE 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, syslog-ng PE tries to add columns in this table but never will delete or modify an existing column.
If syslog-ng PE cannot create or alter a table, it tries to do it again when reach the next time-reopen().
The syslog-ng PE 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 syslog-ng PE application automatically performs the escaping required to insert the messages into the database.
If insert returns with error, syslog-ng PE tries to insert the message +two times by default, then drops it. Retrying time is the value of time-reopen().

Encoding

The syslog-ng PE application uses UTF-8 by default when writes logs into database.

Start/stop and reload

Start:

The syslog-ng PE application will connect to database automatically after starting regardless existing incoming messages.

Stop:

The syslog-ng PE application will close the connection to database before shutting down.

Possibility of losing logs:

The syslog-ng PE application cannot lose logs during shutting down if a disk-buffer file was given and it is not full yet.
The syslog-ng PE application cannot lose logs during shutting down if no disk-buffer file was given.

Reload:

The syslog-ng PE 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 overrided by sql driver regardless of local or relayed incoming message.

It will keep growing continuously.

Please select your product:

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

Recommended Solutions for Your Problem