The sql()
driver sends messages into an SQL database. Currently the Microsoft SQL (MSSQL), MySQL, Oracle, PostgreSQL, and SQLite databases are supported.
|
NOTE:
In order to use this destination, syslog-ng Premium Edition must run in server mode. Typically, only the central syslog-ng Premium Edition server uses this destination. For details on the server mode, see the section called “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 application requires read and write access to the SQL table, otherwise it cannot verify that the destination table exists. Currently the syslog-ng 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. |
|
NOTE:
In addition to the standard syslog-ng packages, the The |
The table
and value
parameters can include macros to create tables and columns dynamically (for details, see the section called “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 application automatically performs the escaping required to insert the messages into the database.
Example 7.34. 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 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")); };
The Oracle sql destination has some special aspects that are important to note.
The hostname of the database server is set 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.
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.
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, 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 7.35. 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 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) ) )
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 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: |
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. 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 Procedure 3.12, “Configuring Microsoft SQL Server to accept logs from syslog-ng”.
Example 7.36. 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 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 server. Edit or create this file as needed for your configuration. A sample is provided below.
[default] date = "%Y-%m-%d %H:%M:%S"
Used SQL operations by syslog-ng.
Create table:
If the given table does not exist, syslog-ng 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 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 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 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 disk buffer was given and it is not full yet.
The syslog-ng PE application cannot lose logs during shutting down if disk buffer was not 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 be grown continously.
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
.
In SQL Server 2005 this restriction is lifted - kind of. The total length of all key columns in an index cannot exceed 900 bytes.
If you are using null()
in your configuration, be sure that the columns allow NULL
to insert. Give the column as the following example: "datetime varchar(16) NULL"
.
The date format used by the MSSQL database must be explicitly set in the /etc/locales.conf
file of the syslog-ng server. [default] date = "%Y-%m-%d %H:%M:%S"
.
Table 7.2. Supported SQL destinations by platform
Platform \ Database | MSSQL | MySQL | PgSQL | SQLite | Oracle |
---|---|---|---|---|---|
AIX | ✔ | ✔ | ✔ | ✔ | - |
Linux (linux_glibc236): CentOS 5 | ✔ | ✔ | ✔ | ✔ | ✔ |
Linux (linux_glibc236): CentOS 6 | ✔ | ✔ | ✔ | ✔ | ✔ |
Linux (linux_glibc236): CentOS 7 | ✔ | ✔ | ✔ | ✔ | ✔ |
Linux (linux_glibc236): Debian 7 | ✔ | ✔ | ✔ | ✔ | ✔ |
Linux (linux_glibc236): openSUSE 11 | ✔ | ✔ | ✔ | ✔ | ✔ |
Linux (linux_glibc236): Oracle Linux 5 | ✔ | ✔ | ✔ | ✔ | ✔ |
Linux (linux_glibc236): Oracle Linux 6 | ✔ | ✔ | ✔ | ✔ | ✔ |
Linux (linux_glibc236): Oracle Linux 7 | ✔ | ✔ | ✔ | ✔ | ✔ |
Linux (linux_glibc236): SLES 11 | ✔ | ✔ | ✔ | ✔ | ✔ |
Linux (linux_glibc236): Red Hat EL 5 | ✔ | ✔ | ✔ | ✔ | ✔ |
Linux (linux_glibc236): Red Hat EL 6 | ✔ | ✔ | ✔ | ✔ | ✔ |
Linux (linux_glibc236): Red Hat EL 7 | ✔ | ✔ | ✔ | ✔ | ✔ |
Linux (linux_glibc236): Ubuntu 12.04 | ✔ | ✔ | ✔ | ✔ | ✔ |
Linux (linux_glibc236): Ubuntu 14.04 | ✔ | ✔ | ✔ | ✔ | ✔ |
FreeBSD 8 | ✔ | ✔ | ✔ | ✔ | - |
FreeBSD 9 | ✔ | ✔ | ✔ | ✔ | - |
FreeBSD 10 | ✔ | ✔ | ✔ | ✔ | - |
HP-UX 11v2_on IA64 | ✔ | ✔ | - | ✔ | ✔ |
Solaris 10 on SPARC and SPARC64 | ✔ | ✔ | ✔ | ✔ | ✔ |
Solaris 11 on SPARC and SPARC64 | ✔ | ✔ | ✔ | ✔ | ✔ |
Solaris 10 on x86_64 | ✔ | ✔ | - | ✔ | ✔ |
Solaris 11 on x86_64 | ✔ | ✔ | - | ✔ | ✔ |
|
NOTE:
In order to use this destination, syslog-ng Premium Edition must run in server mode. Typically, only the central syslog-ng Premium Edition server uses this destination. For details on the server mode, see the section called “Server mode”. |
This driver sends messages into an SQL database. The sql()
destination has the following options:
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: |
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.
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:
reliable() | |
Type: | yes|no |
Default: | no |
Description: If set to |
disk-buf-size() | |
Type: | number (bytes) |
Default: | |
Description: This is a required option. The maximum size of the disk-buffer 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. 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: | 64 |
Description: The number of messages stored in the output buffer of the destination. |
Options reliable()
and disk-buf-size()
are required options.
Example 7.37. 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") ) ); };
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()
parameter. The explicit-commits
option is available in syslog-ng PE version 4.0 and later.
Type: | number (messages) |
Default: | Use global setting. |
Description: Specifies how many lines are sent 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. Setting this number high increases throughput as fully filled frames are sent to the destination, but also increases message latency.
For optimal performance when sending messages to an 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.
Type: | time in milliseconds |
Default: | Use global setting. |
Description: This is an obsolete option. Specifies the time syslog-ng waits for lines to accumulate in its output buffer. For details, see the flush-lines()
option.
|
NOTE:
This option will be removed from the list of acceptable options. After that, your configuration will become invalid if it still contains the |
Type: | number (digits of fractions of a second) |
Default: | Value of the global option (which defaults to 0) |
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.
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.
|
NOTE:
If you specify To specify the socket to use, set and export the |
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 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.
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.
Type: | number (messages) |
Default: | Use global setting. |
Description: The number of messages that the output queue can store.
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 7.39, “Using SQL NULL values”.
Example 7.39. 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.
Type: | number (port 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.
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).
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. |
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.
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 the section called “Date-related macros”.
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.
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.
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 7.40. Value: default
columns("date datetime", "host varchar(32)", "row_id serial") values("${R_DATE}", "${HOST}", default)
© 2024 One Identity LLC. ALL RIGHTS RESERVED. Feedback 利用規約 プライバシー Cookie Preference Center