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

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 OSE application requires read and write access to the SQL table, otherwise it cannot verify that the destination table exists.
  • Currently the syslog-ng OSE 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 OSE packages, the sql() destination requires database-specific packages to be installed. These packages are automatically installed by the binary syslog-ng OSE installer.

The table and value parameters can include macros to create tables and columns dynamically (for details, see Macros of syslog-ng OSE.

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 OSE application automatically performs the escaping required to insert the messages into the database.

Example: 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 OSE 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"));
};

Updated: