This section describes how to forward log messages from syslog-ng Store Box (SSB) to a remote SQL database server.

Tested SQL destinations:

SSB6.9.0 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

To forward log messages from SSB to a remote SQL database server

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 153: 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: The syslog-ng Store Box (SSB) appliance 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 time stamps of most log messages is accurate only to the second. The syslog-ng Store Box(SSB) appliance can include more accurate time stamps: 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 .

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.