Chat now with support
Chat with Support

syslog-ng Open Source Edition 3.16 - Administration Guide

Preface Introduction to syslog-ng The concepts of syslog-ng Installing syslog-ng The syslog-ng OSE quick-start guide The syslog-ng OSE configuration file source: Read, receive, and collect log messages
How sources work default-network-drivers: Receive and parse common syslog messages internal: Collecting internal messages file: Collecting messages from text files wildcard-file: Collecting messages from multiple text files network: Collecting messages using the RFC3164 protocol (network() driver) nodejs: Receiving JSON messages from nodejs applications mbox: Converting local e-mail messages to log messages osquery: Collect and parse osquery result logs pipe: Collecting messages from named pipes pacct: Collecting process accounting logs on Linux program: Receiving messages from external applications snmptrap: Read Net-SNMP traps sun-streams: Collecting messages on Sun Solaris syslog: Collecting messages using the IETF syslog protocol (syslog() driver) system: Collecting the system-specific log messages of a platform systemd-journal: Collecting messages from the systemd-journal system log storage systemd-syslog: Collecting systemd messages using a socket tcp, tcp6, udp, udp6: Collecting messages from remote hosts using the BSD syslog protocol— OBSOLETE unix-stream, unix-dgram: Collecting messages from UNIX domain sockets stdin: Collecting messages from the standard input stream
destination: Forward, send, and store log messages
amqp: Publishing messages using AMQP elasticsearch: Sending messages directly to Elasticsearch version 1.x elasticsearch2: Sending logs directly to Elasticsearch and Kibana 2.0 or higher file: Storing messages in plain-text files graphite: Sending metrics to Graphite Sending logs to Graylog hdfs: Storing messages on the Hadoop Distributed File System (HDFS) Posting messages over HTTP http: Posting messages over HTTP without Java kafka: Publishing messages to Apache Kafka loggly: Using Loggly logmatic: Using Logmatic.io mongodb: Storing messages in a MongoDB database network: Sending messages to a remote log server using the RFC3164 protocol (network() driver) osquery: Sending log messages to osquery's syslog table pipe: Sending messages to named pipes program: Sending messages to external applications pseudofile() redis: Storing name-value pairs in Redis riemann: Monitoring your data with Riemann smtp: Generating SMTP messages (e-mail) from logs Splunk: Sending log messages to Splunk sql: Storing messages in an SQL database stomp: Publishing messages using STOMP syslog: Sending messages to a remote logserver using the IETF-syslog protocol syslog-ng: Forwarding messages and tags to another syslog-ng node tcp, tcp6, udp, udp6: Sending messages to a remote log server using the legacy BSD-syslog protocol (tcp(), udp() drivers) Telegram: Sending messages to Telegram unix-stream, unix-dgram: Sending messages to UNIX domain sockets usertty: Sending messages to a user terminal: usertty() destination Write your own custom destination in Java or Python
log: Filter and route log messages using log paths, flags, and filters Global options of syslog-ng OSE TLS-encrypted message transfer template and rewrite: Format, modify, and manipulate log messages parser: Parse and segment structured messages db-parser: Process message content with a pattern database (patterndb) Correlating log messages Enriching log messages with external data Statistics of syslog-ng Multithreading and scaling in syslog-ng OSE Troubleshooting syslog-ng Best practices and examples The syslog-ng manual pages Third-party contributions Creative Commons Attribution Non-commercial No Derivatives (by-nc-nd) License About us

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. For details, see the following example.

  • 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 OSE, the types of database columns must be explicitly set for the MSSQL destination.

    Caution:

    The following column types cannot be used in MSSQL destinations: nchar, nvarchar, ntext, and xml.

  • 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. For details, see the following example.

  • Remote access for SQL users must be explicitly enabled on the Microsoft Windows host running the Microsoft SQL Server. For details, see Configuring Microsoft SQL Server to accept logs from syslog-ng.

Example: 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 way syslog-ng interacts with the database

Used SQL operations by syslog-ng

Create table:

  • If the given table does not exist, syslog-ng tries to create it with the given column types.

  • 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.

  • If syslog-ng cannot create or alter a table, it tries to do it again when it reaches the next time-reopen().

Alter table:

  • If the table structure is different from given structure in an existing table, syslog-ng tries to add columns in this table but never will delete or modify an existing column.

  • If syslog-ng OSE cannot create or alter a table, it tries to do it again when reach the next time-reopen().

  • The syslog-ng OSE application requires read and write access to the SQL table, otherwise it cannot verify that the destination table exists.

Insert table:

  • Insert new records in a table.

  • 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.

  • If insert returns with error, syslog-ng tries to insert the message +two times by default, then drops it. Retrying time is the value of time-reopen().

Encoding

The syslog-ng OSE application uses UTF-8 by default when writes logs into database.

Start/stop and reload

Start:

  • The syslog-ng OSE application will connect to database automatically after starting regardless existing incoming messages.

Stop:

  • The syslog-ng OSE application will close the connection to database before shutting down.

Possibility of losing logs:

  • The syslog-ng OSE application cannot lose logs during shutting down if disk buffer was given and it is not full yet.

  • The syslog-ng OSE application cannot lose logs during shutting down if disk buffer was not given.

Reload:

  • The syslog-ng OSE application will close the connection to database if it received SIGHUP signal (reload).

  • It will reconnect to the database when it tries to send a new message to this database again.

Macros:

The value of ${SEQNUM} macro will be overrided by sql driver regardless of local or relayed incoming message.

It will be grown continously.

MySQL-specific interaction methods

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.

MsSQL-specific interaction methods

In SQL Server 2005 this restriction is lifted - kind of. The total length of all key columns in an index cannot exceed 900 bytes.

If you are using null() in your configuration, be sure that the columns allow NULL to insert. Give the column as the following example: "datetime varchar(16) NULL".

The date format used by the MSSQL database must be explicitly set in the /etc/locales.conf file of the syslog-ng server. [default] date = "%Y-%m-%d %H:%M:%S".

Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating