value-pairs()
Chapter 6. Collecting log messages — sources and source drivers
6.8. Collecting messages from tables or relational database
6.8.2. sql() source options
Solaris 10, 11 on ✔
x86_64
Table 6.3. Supported SQL sources by platform
6.8.2. sql() source options
Warning
This feature is currently not available when running the syslog-ng PE application on Microsoft Windows platforms. For a complete list of limitations, see Section 1.6.1, Limitations on Microsoft Windows platforms (p. 5).
Thesql()driver has the following options.
archive-query() string Type:
Default:
Description: The SQL-like statement which is executed after syslog-ng PE has queried a batch of records (as set in thelog-fetch-limit()option). This statement can be used for example to archive or delete the records processed by syslog-ng PE. Note that the user account that syslog-ng PE uses to access the database requires the appropriate privileges to execute the statement. If executing the statement fails, syslog-ng PE will log the error message returned by the database, and continue processing the other records.
For details on customizing queries, see Section 6.8.3, Customizing SQL queries (p. 120).
Warning
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.
Example 6.22. A sample archive query
The following statement deletes the records already retreived from the database if the table is read from the beginning.
archive-query("DELETE FROM test_logs WHERE id <= $last_read_uid")
columns() string list Type:
empty Default:
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 6.23. SQL source option columns columns("id","date","message")
connect-query() string Type:
Default:
Description: The SQL-like statement which is executed after syslog-ng PE has successfully connected to the database.
For details on customizing queries, see Section 6.8.3, Customizing SQL queries (p. 120).
Warning
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.
Example 6.24. A sample connect query
connect-query("SET COLLATION_CONNECTION='utf8_general_ci'")
database() string Type:
logs Default:
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 samedatabase()settings in more than one destination.
date-column(col_name, [format]) date, string
Type:
Default:
Description: The column containing the date of the logrecord. The format value has to be in strptime format.
For details, see the strptime manual page (man strptime).
Note
If the type of the column is string, this is a required parameter.
datetime-column(col_name, [format]) string
Type:
Default:
The following column types are supported:
■ MySQL: timestamp, datetime, int
■ PostgreSQL: timestamp, int
■ Oracle: timestamp, int
■ MSSQL: datetime, int
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 the strptime manual page (man strptime).
Example 6.25. SQL source option datetime-column(col_name, [format]) datetime("timestampcol", "%Y-%m-%d")
default-facility() facility string Type:
local0 Default:
Description: This parameter assigns a facility value to the messages received from the sql source.
default-priority() priority string Type:
info Default:
Description: This parameter assigns an emergency level to the messages received from the sql source.
fast-follow-mode() yes|no Type:
yes Default:
Description: If set toyes, 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 infollow-freq()time.
fetch-query() string Type:
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 Section 6.8.3, Customizing SQL queries (p. 120).
Warning
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.
Example 6.26. A sample fetch query
fetch-query("SELECT * FROM $table WHERE id > $last_read_uid AND test_logs.log LIKE '%ERROR%' ORDER BY $uid")
The default fetch queries are the following:
■ MSSQL:
SELECT TOP $fetch_limit $columns FROM $table WHERE $uid > '$last_read_uid' ORDER BY $uid
■ MySQL:
SELECT $columns FROM $table WHERE $uid > '$last_read_uid' ORDER BY $uid LIMIT 0,$fetch_limit
■ Oracle:
SELECT $columns FROM (SELECT $table.*, Row_Number() OVER (ORDER BY $uid) FetchRow FROM $table WHERE $uid > '$last_read_uid' ) WHERE FetchRow BETWEEN
0 AND $fetch_limit
■ PostgreSQL:
SELECT $columns FROM $table WHERE $uid > '$last_read_uid' ORDER BY $uid LIMIT $fetch_limit
follow-freq()
number (seconds) Type:
10 Default:
Indicates that the source should be checked periodically. This is useful for SQL sources which always indicate readability, even though no new records were appended. If this value is higher than zero, syslog-ng will not attempt to use poll() on the SQL source, but checks whether the SQL source changed every time the follow-freq()interval (in seconds) has elapsed. Floating-point numbers (for example1.5) can be used as well.
host()
hostname or IP address Type:
n/a Default:
Description: Hostname of the database server. Note that Oracle destinations do not use this parameter, but retrieve the hostname from the/etc/tnsnames.orafile.
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.
host-template() string Type:
Empty string Default:
Description: The template for defining the HOST part of the message. If thehost-template()option is not specified, the value of thehost()option will be used in the HOST part of the message.
Note
This option requires the optionkeep-hostname()to be enabled:keep-hostname(yes).
log-fetch-limit() Type:
100 Default:
Description: The maximum number of messages fetched from a source during a single poll loop. The destination queues might fill up before flow-control could stop reading iflog-fetch-limit()is too high.
log-iw-size()
number (messages) Type:
1000 Default:
Description: The size of the initial window, this value is used during flow control. If themax-connections() option is set, thelog-iw-size()will be divided by the number of connections, otherwiselog-iw-size() is divided by 10 (the default value of the max-connections()option). The resulting number is the initial window size of each connection. For optimal performance when receiving messages from syslog-ng PE clients, make sure that the window size is larger than theflush-lines()option set in the destination of your clients.
Example 6.27. Initial window size of a connection
Iflog-iw-size(1000)andmax-connections(10), then each connection will have an initial window size of 100.
message-template() string
Type:
Default:
The alias of thetemplate()parameter.
password() string Type:
n/a Default:
Description: Password of the database user.
port()
number (port number) Type:
1433 TCP for MSSQL, 3306 TCP for MySQL, 1521 for Oracle, and 5432 TCP for PostgreSQL Default:
Description: The port number to connect to.
prefix() string Type:
".sql"
Default:
Description: This prefix will be added to the name of the macros created from the database columns.
Example 6.28. SQL source option prefix()
If a database column is calledcolumn1, and the prefix option is set asprefix("customprefix."), the macro for the column will be calledcustomprefix.column1.
program-template() string Type:
Empty string Default:
Description: The template for defining the PROGRAM part of the message. If not specified, the PROGRAM message part will be empty.
read-old-records() yes|no Type:
no Default:
Description: If set toyes, syslog-ng PE will start reading the records from the beginning of the table, if the table has not been read yet. If set tono, syslog-ng PE will read only the new records.
table()
string Type:
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() string Type:
Default:
Description: The SQL-like statement which is executed before fetching the first batch of records.
For details on customizing queries, see Section 6.8.3, Customizing SQL queries (p. 120).
Warning
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.
tags()
string Type:
Default:
Description: Label the messages received from the source with custom tags. Tags must be unique, and enclosed between double quotes. When adding multiple tags, separate them with comma, for exampletags("dmz",
"router"). This option is available only in syslog-ng 3.1 and later.
template() string Type:
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 <prefix> is the value of theprefix()option.
This template converts the retrieved records into the WebTrends Enhanced Log file Format (WELF).
■ For details on the WELF format, see https://www3.trustwave.com/support/kb/article.aspx?id=10899.
■ For details on theformat-welf()template function, see Section format-welf (p. 323).
Note
Theformat-welffunction does not keep the order of columns between queries.
Example 6.29. SQL source option template()
Using the default template for a table that has two columns (idandmessage) The result of the first query is the following:
'.sql.id=12 .sql.message="test message"', whereas the result of the second query can be:
'.sql.message="test message" .sql.id=12 '
time-column(col_name, [format]) time, string
Type:
Default:
Description: The column containing the time of the logrecord. The format value has to be in strptime format.
Note
If the type of the column is string, this is a required parameter.
time-reopen()
number (seconds) Accepted values:
60 Default:
Description: The time to wait in seconds before a dead connection is reestablished.
time-zone()
timezone in +/-HH:MM format Type:
Default:
Description: The default timezone, if set. If this option is not set, the default timezone is the local timezone.
type()
mssql, mysql, oracle, or pgsql Type:
Default:
Description: Specifies the type of the database, that is, the DBI database driver to use. Use themssqloption to send logs to an MSSQL database. For details, see the examples of the databases on the following sections.
uid-column() string Type:
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.
Note
The value of the first record of this column should not be 0: syslog-ng PE will skip this value.
use-syslogng-pid() yes or no Type:
no Default:
Description: If the value of this option isyes, then the PID value of the message will be overridden with the PID of the running syslog-ng process.
username() string Type:
n/a Default:
Description: Name of the database user.