The syslog-ng 3.0 Administrator Guide

Document version: Thirteenth
Release date: September 23, 2010
Latest version available at the BalaBit Documentation Page

8.2.5. sql()

This driver sends messages into an SQL database. The sql() driver has the following required parameters: type, database, table, columns, values.

Declaration: 
    sql(database_type host_parameters database_parameters [options]);

The sql() destination has the following options:

Name Type Default Description
columns string list "date", "facility", "level", "host", "program", "pid", "message" 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 creates text columns. Note that not every database engine can index text fields.
database string n/a Name of the database that stores the logs.
frac_digits() number 0 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.
host hostname or IP address n/a Hostname of the database server. Note that Oracle destinations do not use this parameter, but retrieve the hostname from the /etc/tnsnames.ora file.
indexes string list "date", "facility", "host", "program" 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.
local_time_zone() name of the timezone or the timezone offset The local timezone. Sets the timezone used when expanding filename and tablename templates. The timezone can be specified as using the name of the (e.g., time_zone("Europe/Budapest")), or as the timezone offset (e.g., +01:00). The valid timezone names are listed under the /usr/share/zoneinfo directory.
log_disk_fifo_size() number 0 Size of the hard disk space in bytes that is used as disk buffer. Available only in syslog-ng Premium Edition when using the tcp(), tcp6(), syslog() (when using the tcp or tls transport methods), and sql() destinations. Can be also defined as a global option. See Section 2.14, “Using disk-based buffering” for details on using the disk buffer.
log_fifo_size() number Use global setting. The number of entries in the output buffer (output fifo).
null string   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. See the Example 8.20, “Using SQL NULL values” for details.
password string n/a Password of the database user.
table string n/a Name of the database table to use (can include macros). When using macros, note that some databases limit the length of table names.
time_zone() timezone in +/-HH:MM format unspecified 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 n/a Specifies the type of the database, i.e., the DBI database driver to use. Use the mssql option to send logs to an MSSQL database. See the examples of the databases on the following sections for details.
username string n/a Name of the database user.
values string list "${R_YEAR}-${R_MONTH}-${R_DAY} ${R_HOUR}:${R_MIN}:${R_SEC}", "$FACILITY", "$LEVEL", "$HOST", "$PROGRAM", "$PID", "$MSGONLY" The parts of the message to store in the fields specified in the columns parameter.

Table 8.12. Options for sql()


[Note] 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, e.g., MYSQL_UNIX_PORT=/var/lib/mysql/mysql.sock; export MYSQL_UNIX_PORT.

[Example] Example 8.17. 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"));
};
[Example] Example 8.18. 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)
)
)
[Example] Example 8.19. 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"
[Example] Example 8.20. 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, e.g.,

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 (e.g., 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.

Prev  Up  Next
8.2.4. program()  Home | Contents  8.2.6. syslog()

© 2007-2010 BalaBit IT Security
Please send your comments or documentation bugs to: documentation@balabit.com