4.4.4. Storing messages in an SQL database

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

The sql() driver has the following required parameters:

type

Type:	mssql, mysql, oracle, pgsql, or sqlite3
Default:	n/a

Description: 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.

database

Type:	string
Default:	n/a

Description: Name of the database that stores the logs.

table

Type:	string
Default:	n/a

Description: Name of the database table to use (can include macros). When using macros, note that some databases limit the length of table names.

columns

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 creates text columns. Note that not every database engine can index text fields.

values

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.

For the list of available optional parameters, see Section 6.2.4, “sql()”.

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

Warning

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

	Note
In addition to the standard syslog-ng packages, the `sql()` destination requires database-specific packages to be installed. These packages are automatically installed by the binary syslog-ng installer. The `sql()` driver is currently not available for every platform that is supported by syslog-ng. For a list of platforms that support the `sql()` driver, visit http://www.balabit.com/network-security/syslog-ng/central-syslog-server/.

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

The sql() driver is currently not available for every platform that is supported by syslog-ng. For a list of platforms that support the sql() driver, visit http://www.balabit.com/network-security/syslog-ng/central-syslog-server/.

The table and value parameters can include macros to create tables and columns dynamically (see Section 6.5, “Macros” for details).

	Warning
	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 4.19. Using the sql() driver

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

4.4.4.1. Using the sql() driver with an Oracle database

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.

Make sure to set the Oracle-related environment variables properly, so syslog-ng and the Oracle client will find the file. The following variables must be set: ORACLE_BASE, ORACLE_HOME, and ORACLE_SID. See the documentation of the Oracle Instant Client for details.
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.) See the following example for details.

Example 4.20. Using the sql() driver with an Oracle database

	Example 4.20. 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 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)
)
)

4.4.4.2. 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 server. See the following example for details.
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. 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. See the following example for details.
Remote access for SQL users must be explicitly enabled on the Microsoft Windows host running the Microsoft SQL Server. See Section 3.6, “Configuring Microsoft SQL Server to accept logs from syslog-ng” for details.

Example 4.21. Using the sql() driver with an MSSQL database

	Example 4.21. 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"

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"

Prev	Up	Next
4.4.3. Sending messages to external applications	Home \| Contents	4.4.5. Sending messages to a remote logserver using the IETF-syslog protocol

The syslog-ng Open Source Edition 3.1 Administrator Guide

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