The way syslog-ng interacts with the database
7.4.4. sql() destination options
This driver sends messages into an SQL database. Thesql()destination has the following options:
columns() string list Type:
"date", "facility", "level", "host", "program", "pid", "message"
Default:
Description: Name of the columns storing the data infieldname [dbtype]format. The[dbtype]parameter is optional, and specifies the type of the field. By default, syslog-ng createstextcolumns. Note that not every database engine can index text fields.
database() string Type:
logs Default:
Description: Name of the database that stores the logs. Macros cannot be used in database name.
sql() destination options
flags()
list of flags Type:
empty string Default:
Description: Flags related to thesql()destination.
■ dont-create-tables: Enable this flag to prevent syslog-ng OSE from creating non-existing database tables automatically. The syslog-ng OSE application typically has to create tables if you use macros in the table names. Available in syslog-ng OSE version 3.2 and later.
■ explicit-commits: By default, syslog-ng OSE commits every log message to the target database individually.
When theexplicit-commitsoption 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 theflush_linesandflush_timeout parameters. Theexplicit-commitsoption is available in syslog-ng OSE version 3.2 and later.
flush_lines() number Type:
Use global setting.
Default:
Description: Specifies how many lines are flushed to a destination at a time. Syslog-ng 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. The latency can be limited by the use of theflush_timeoutoption.
Description: Specifies the time syslog-ng waits for lines to accumulate in its output buffer. For details, see the flush_linesoption.
Description: The syslog-ng application can store fractions of a second in the timestamps according to the ISO8601 format.. Thefrac_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.
host()
hostname or IP address Type:
sql() destination options
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.
indexes()
string list Type:
"date", "facility", "host", "program"
Default:
Description: The list of columns that are indexed by the database to speed up searching. To disable indexing for the destination, include the emptyindexes()parameter in the destination, simply omitting theindexes para-meter will cause syslog-ng to request indexing on the default columns.
The syslog-ng OSE 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}-1and the first character will be replaced by "i" character and the md5sum will be truncated to 30 characters.
local_time_zone()
name of the timezone or the timezone offset Type:
The local timezone.
Default:
Description: Sets the timezone used when expanding filename and tablename templates. The timezone can be specified as using the name of the (for exampletime_zone("Europe/Budapest")), or as the timezone offset (for example+01:00). The valid timezone names are listed under the/usr/share/zoneinfodirectory.
log_fifo_size() number Type:
Use global setting.
Default:
Description: The number of messages that the output queue can store.
null()
string Type:
Default:
sql() destination options
Description: If the content of a column matches the string specified in thenull() 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.9, Using SQL NULL values (p. 106).
Example 7.9. Using SQL NULL values
Thenull()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 thenull("")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 examplepid) if it is empty, assign a default value to the column, and use this default value in thenull()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.
password() string Type:
n/a Default:
Description: Password of the database user.
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.
table()
time_zone()
timezone in +/-HH:MM format Type:
unspecified Default:
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.
type()
mssql, mysql, oracle, pgsql, or sqlite3 Type:
mysql 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.
username() string Type:
n/a Default:
Description: Name of the database user.
values()
string list Type:
"${R_YEAR}-${R_MONTH}-${R_DAY} ${R_HOUR}:${R_MIN}:${R_SEC}", "$FACILITY",
"$LEVEL", "$HOST", "$PROGRAM", "$PID", "$MSGONLY"
Default:
Description:The parts of the message to store in the fields specified in thecolumnsparameter.
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.10. Value: default
columns("date datetime", "host varchar(32)", "row_id serial") values("$R_DATE", "$HOST", default)