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

Creating SQL message sources in SSB

There are many applications that natively store their log messages in SQL databases. SSB can pull messages from SQL database tables in real-time, similarly to receiving messages over the network.

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 9.3.14 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu
    4.8.4-2ubuntu1~14.04.3) 4.8.4, 64-bit
  • MySQL (with "select version()")

    5.5.52-0ubuntu0.14.04.1
  • 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

Fetching the SQL database

Purpose:

To configure the parameters of the SQL database that you want to use as the message source, complete the following steps.

Steps:
  1. Navigate to Log > Sources and click .

  2. Enter a name for the source into the top field. Use descriptive names that help you to identify the source easily.

    Figure 98: Log > Sources — Fetching the SQL database

  3. Select SQL.

  4. Select the Database type to collect log messages from.

  5. Enter the hostname or the IP address of the database server to collect messages from.

  6. Enter the port of the database server to connect to. To use the default port of the database, click Set Default Port.

  7. Enter the name and the password of the database user.

    NOTE:

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

  8. Enter the database to connect to.

  9. Click Test connection and fetch tables. SSB reads the tables from the database.

    NOTE:

    SSB can only read table names that contain numbers, uppercase and lowercase characters, hyphen (-), underscore (_), hashtag (#), at sign (@), or the dollar sign ($). Tables with names that contain other characters, including full stop (.), cannot be monitored.

Configuring message parts in Basic mode

Purpose:

To create an SQL message source with only a few clicks, complete the following steps.

Steps:
  1. Select Basic mode for simple configuration mode. For advanced configuration settings (manually creating fetch queries, and so on), see Configuring message parts in Advanced mode.

    Figure 99: Log > Sources — Configuring message parts in Basic mode

  2. Select the name of the monitored Table.

    NOTE:

    SSB can only read table names that contain numbers, uppercase and lowercase characters, hyphen (-), underscore (_), hashtag (#), at sign (@), or the dollar sign ($). Tables with names that contain other characters, including full stop (.), cannot be monitored.

  3. Select the Unique ID column. This is the monotonically increasing unique ID of the monitored table. It must be a numeric column.

    NOTE:

    SSB reads only those rows where the Unique ID column contains a value larger than 0.

  4. Select the column containing the timestamp.

    • If the timestamp column contains both date and time, select it from the list.

    • If the timestamp date and timestamp time are in separate columns, select [Set date and time separately]. Then set the timestamp date and time columns from the respective drop-down menus.

  5. Optionally, select the Host and Program columns.

  6. Select the Timezone.

  7. Select the part of the system sending the message in Facility.

  8. Select the importance of the message in Severity.

  9. To put all columns into SDATA for further processing, enable Put all columns into SDATA.

    NOTE:

    In Advanced mode, it is possible to put only certain selected columns (that were retrieved by the SQL query) into SDATA.

  10. Enable Fast follow mode to make syslog-ng read the database table as fast as possible.

    NOTE:

    SSB reads the database periodically, each time performing one query. Each query fetches up to 3000 records. With Fast follow mode enabled, SSB continues querying the database until it fetched all records available at the time.

  11. Enable Read old records to make syslog-ng start reading the records from the beginning of the table, if the table has not been read yet. If it is disabled, syslog-ng will read only the new records.

  12. Specify the time interval between two queries by setting Fetch data in every X seconds. The syslog-ng application executes one query in the given timeframe (maximum 3000 records within one read operation).

  13. Enable Message rate alerting to detect abnormalities in SSB. For details, see Configuring message rate alerting.

    NOTE:

    In case of SQL sources, only Messages can be measured.

  14. Click Test data retrieving. The results are displayed in a pop-up window.

Configuring message parts in Advanced mode

Purpose:

For more flexible SQL source configuration, such as manual fetch query configuration, complete the following steps.

Steps:
  1. Select Advanced mode for advanced configuration settings. For a simpler configuration, see Configuring message parts in Basic mode.

    Figure 100: Log > Sources — Configuring message parts in Advanced mode

  2. Create the fetch query manually. For details, see Creating a fetch query manually

  3. If you are using MSSQL database, or you encounter SQL errors or unexpected results, specify a custom query to find the last UID in the database.

  4. Select the Timezone.

  5. Select the part of the system sending the message in Facility.

  6. Select the importance of the message in Severity.

  7. To put all columns into SDATA for further processing, enable Put all columns into SDATA.

    NOTE:

    In Advanced mode, it is possible to put only certain selected columns (that were retrieved by the SQL query) into SDATA.

  8. Enable Fast follow mode to make syslog-ng read the database table as fast as possible.

    NOTE:

    SSB reads the database periodically, each time performing one query. Each query fetches up to 3000 records. With Fast follow mode enabled, SSB continues querying the database until it fetched all records available at the time.

  9. Enable Read old records to make syslog-ng start reading the records from the beginning of the table, if the table has not been read yet. If it is disabled, syslog-ng will read only the new records.

  10. Specify the time interval between two queries by setting Fetch data in every X seconds. The syslog-ng application executes one query in the given timeframe (maximum 3000 records within one read operation).

  11. Enable Message rate alerting to detect abnormalities in SSB. For details, see Configuring message rate alerting.

    NOTE:

    In case of SQL sources, only Messages can be measured.

  12. Click Test data retrieving. The results are displayed in a pop-up window.

Related Documents