Chat now with support
Chat with Support

syslog-ng Store Box 5.0.3 - Administration Guide

Preface Introduction The concepts of SSB The Welcome Wizard and the first login Basic settings User management and access control Managing SSB Configuring message sources Storing messages on SSB Forwarding messages from SSB Log paths: routing and processing messages Configuring syslog-ng options Searching log messages Searching the internal messages of SSB Classifying messages with pattern databases The SSB RPC API Troubleshooting SSB Security checklist for configuring SSB About us Third-party contributions

Forwarding log messages to SQL databases

Purpose:

This section describes how to forward log messages from SSB to a remote SQL database server.

Tested SQL destinations:

SSB 5 LTS was tested with the following database servers:

  • MS SQL (with "select @@version")

    Microsoft SQL Server 2005 - 9.00.5057.00 (Intel X86)   Mar 25 2011 13:50:04   Copyright (c) 1988-2005 Microsoft Corporation  Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
  • PostgreSQL (with "select version()")

    PostgreSQL 8.3.15 on i486-pc-linux-gnu, compiled by GCC cc (GCC) 4.2.4 (Ubuntu 4.2.4-1ubuntu4)
  • MySQL (with "select version()")

    5.0.51a-3ubuntu5.8-log
  • Oracle (with "SELECT * FROM V$VERSION;")

    Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
    PL/SQL Release 11.2.0.4.0 - Production
    "CORE	11.2.0.4.0	Production"
    TNS for Linux: Version 11.2.0.4.0 - Production
    NLSRTL Version 11.2.0.4.0 - Production
    Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
    PL/SQL Release 12.1.0.2.0 - Production
    "CORE	12.1.0.2.0	Production"
    TNS for Linux: Version 12.1.0.2.0 - Production
    NLSRTL Version 12.1.0.2.0 - Production
Steps:
  1. To create a new remote destination, navigate to Log > Destinations and select .

  2. Enter a name for the destination.

    NOTE:

    This name will be used in the name of the database tables created by SSB. For compatibility reasons, it can contain only numbers, lowercase characters, and the underscore (_) character, for example example_database_destination.

  3. Select Database Server.

    Figure 114: Log > Destinations — Creating database destinations

  4. Select the type of the remote database from the Database type field.

  5. Enter the IP address or hostname of the database server into the Address field. If the database is running on a non-standard port, adjust the Port setting.

  6. Enter the name and password of the database user account used to access the database into the Username and Password fields, respectively. This user needs to have the appropriate privileges for creating new tables.

    NOTE:

    SSB accepts passwords that are not longer than 150 characters. The following special characters can be used: !"#$%&'()*+,-./:;<=>?@[]^-`{|}

  7. Enter the name of the database that will store the log messages into the Database name field.

  8. Optional step: Enter the number of log message lines into the Flush lines field that SSB should wait before sending them off in a single batch. Setting this number high increases throughput as fully filled frames are sent to the network. However, it also increases message latency.

    NOTE:

    Flush lines is in connection with the Output memory buffer value. (To set the Output memory buffer value, navigate to Log > Destinations). The value of Output memory buffer has to be greater than or equal to the value of Flush lines.

  9. SSB will automatically start a new table for every day or every month. Optionally, you can also create custom tables. Select the table naming template from the Table rotation field.

  10. Select which columns should SSB insert into the database. You can use one of the predefined templates, or select Custom columns to create a custom template. The available templates are described in SQL templates in SSB.

  11. SSB can automatically delete older messages and tables from the database. By default, messages are deleted after one month. Adjust the Retention time as needed for your environment.

  12. The logs stored in the database can be accessed using the search interface of SSB. Enter the name of the usergroup who can access the logs into the Access control > Group field. To add more groups (if needed), click .

  13. The timestamps of most log messages is accurate only to on second. SSB can include more accurate timestamps: set how many digits should be included in the Timestamp fractions of a second field. This option corresponds to the frac_digits() parameter of syslog-ng.

  14. If the server and SSB are located in a different timezone and you use the Legacy message template (which does not include timezone information), select the timezone of the server from the Timezone field.

  15. Set the size of the disk buffer (in Megabytes) in the Output disk buffer field. If the remote server becomes unavailable, SSB will buffer messages to the hard disk, and continue sending the messages when the remote server becomes available. This option corresponds to the log_disk_fifo_size() parameter of syslog-ng.

    Note that SSB does not pre-allocate the hard disk required for the disk buffer, so make sure that the required disk space is available on SSB. For details on creating archiving policies and adjusting the disk-fillup prevention, see Archiving and cleanup and Preventing disk space fill up.

    Example: Calculating disk buffer size

    The size of the disk buffer you need depends on the rate of the incoming messages, the size of the messages, and the length of the network outage that you want to cover. For example:

    • SSB is receiving 15000 messages per second

    • On the average, one message is 250 bytes long

    • You estimate that the longest time the destination will be unavailable is 4 hours

    In this case, you need a disk buffer for 250 [bytes] * 15000 [messages per second] * 4*60*60 [seconds] = 54000000000 [bytes], which is 54000 Megabytes (in other words, a bit over 50 GB).

  16. Click Commit.

  17. To start sending messages to the destination, include the new destination in a logpath. For details, see Log paths: routing and processing messages.

  18. To test if the database is accessible, select Test connection.

SQL templates in SSB

The following sections describe the SQL templates available in SSB:

The Legacy template

The Legacy template stores messages in the ssb_sql_messages_${R_YEAR}_${R_MONTH} table. The following columns are created:

  • insert_time: The date when SSB received the message in Unixtime format.

  • rule_id: ID of the pattern database rule that matched the message.

  • __row_id: Identifier of the row.

  • date_time: The date the message was sent in YEAR-MONTH-DAY HOUR:MINUTE:SECOND format.

  • facility: The facility that sent the message.

  • priority: The priority level of the message.

  • host: The IP address or hostname of the host were the message was generated.

  • program: The name of the application that generated the message.

  • pid: The ID number of the process that generated the message (this field is automatically set to zero if the PID is not included in the message).

  • message: The text of the log message.

The insert_time, rule_id, date_time, facility, host, and program columns are indexed.

The Full template

The Full template stores messages in the ssb_sql_messages_${R_YEAR}_${R_MONTH} table. The following columns are created:

  • insert_time: The date when SSB received the message in Unixtime format.

  • rule_id: ID of the pattern database rule that matched the message.

  • __row_id: Identifier of the row.

  • date_time: The date the message was sent in YEAR-MONTH-DAY HOUR:MINUTE:SECOND format.

  • facility: The facility that sent the message.

  • priority: The priority level of the message.

  • sourceip: The IP address of the host that sent the message.

  • host: The IP address or hostname of the host were the message was generated.

  • program: The name of the application that generated the message.

  • pid: The ID number of the process that generated the message (this field is automatically set to zero if the PID is not included in the message).

  • message: The text of the log message.

The insert_time, rule_id, date_time, facility, host, sourceip, and program columns are indexed.

Related Documents