Chat now with support
Chat with Support

syslog-ng Store Box 6.10.0 - 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 Monitoring SSB Troubleshooting SSB Security checklist for configuring SSB Glossary

Configuring your own, customized SQL type message source

When configuring your own, customized message sources in syslog-ng Store Box (SSB), you can configure two source types: Syslog, or SQL.

For more information about configuring a Syslog source type in your own, customized message source, see Configuring your own, customized Syslog type message source.

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

Figure 130: Log > Sources > <your-new-source> — SQL source type in your own, customized message source

Prerequisites for configuring your own, customized SQL type message source

Currently, configuring your own, customized SQL type message source in SSB has the following prerequisites:

  • The Address for the SQL type message source (that is, IP address of the remote database server to collect messages from).

  • The Port of the database server that the SQL type message source will connect to.

  • The Username of the database user.

  • The Password of the database user.

  • A previously configured SQL database that you can specify the SQL type message source to connect to, by entering the database name into Database name.

Limitations to configuring your own, customized SQL type message source

When configuring your message sources, consider that currently, configuring your own, customized SQL type message source in SSB has the following limitations:

  • Database servers

    SSB 6.10.0 was tested with the following database servers:

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

      Microsoft SQL Server 2017 (RTM-CU13) (KB4466404) - 14.0.3048.4 (X64) Nov 30 2018 12:57:58
Copyright (C) 2017 Microsoft Corporation Developer Edition (64-bit) on Linux (Ubuntu 16.04.5 LTS)

    • 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

  • Remote server IP addresses

    NOTE: The current version (version 6.10.0) of SSB only supports configuring IPv4 addresses for Source type > SQL > Database connection > Address in message sources.

Configuration options for your own, customized SQL type message source

While configuring your own, customized SQL type message source, you can customize the following:

  • Configuring and testing the Database connection of your SQL source.

  • Customizing your fetch queries and message fetching settings under Fetching messages.

  • Customizing your fetch history and test message fetching under Fetch history.

  • Customizing your Fetching frequency.

  • Customizing your Monitoring options.

For further details on the configuration options, see the following subsections:

Topics:

Setting up and testing the SQL database connection of your SQL type message source

The following section describes how to setup the SQL database connection of your SQL type message source, and how the connection is tested.

To setup and test the SQL database connection in your SQL type message source

  1. Navigate to Log > Sources > <your-new-source> SQL > Database connection.

    Figure 131: Log > Sources > <your-new-source> > SQL > Database connection — Setting up and testing the SQL database connection for your SQL type message source

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

  3. Enter the name or the IP address of the remote database server to collect messages from.

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

    NOTE: The default port depends on the database type, but you can configure it according to your preferences.

  5. Enter the Username and the Password of the database user.

    NOTE: The syslog-ng Store Box (SSB) appliance accepts passwords that are not longer than 150 characters. The following special characters can be used:

    ! " # $ % &amp; ' ( ) * + , - . / : ; &lt; &gt; = ? @ [ ] ^ - ` { | }

  6. Enter the Database name to connect to.

  7. Click Test database connection. SSB reads the tables from the database.

    NOTE: The syslog-ng Store Box (SSB) appliance 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.

Customizing fetching messages when using your SQL type message source

For a more flexible SQL source configuration, you can customize your message fetching preferences (including customizing your fetch query and fetch history) for your SQL type message source.

Figure 132: Log > Sources > <your-new-source> > SQL > Fetching messages — Customizing fetching messages for your SQL type message source

For more information about creating your customized fetch query and customizing your fetch history, see the following subsections:

Topics:

Customizing the fetch query for your SQL type message source

Under Log > Sources > <your-new-source> > SQL > Fetching messages, you can create a customized fetch query for your SQL type message source.

To create a customized fetch query for your SQL type message source

  1. Navigate to Log > Sources > <your-new-source> > SQL > Fetching messages.

    Figure 133: Log > Sources > <your-new-source> > SQL > Fetching messages — Customizing the fetch query for your SQL type message source

  2. Enter your customized fetch query in the suitable format.

    For more information, see Limitations, considerations, and best practices.

  3. Select the Timezone of your choice.

  4. Select the Facility level of your choice.

  5. Select the Severity level of your choice.

  6. (Optional) Enable Put all columns to SDATA.

Limitations, considerations, and best practices

Before you configure your customized fetch query, consider the limitations, considerations, and best practices in the current version (version 6.10.0 ) of syslog-ng Store Box (SSB):

  • Required column names

    The custom fetch query has the following required column names it must return:

    • uid:

      The uid column must contain a unique number. This number must increase monotonously. SSB will store the last read uid.

      NOTE: SSB automatically substitutes the last read uid after the initial fetch.

    • datetime or date and time:

      SSB will use the content of the datetime column as the time stamp of the log message. The following column types are supported:

      • Oracle: timestamp, int

      • MSSQL: datetime, int

      If the type is int, SSB will assume that it contains a UNIX time stamp.

      When using separate date and time columns, the date column must be date type, the time column must be time type.

    • message:

      The message field must contain the message to be logged.

      For example:

      SELECT "uniq_id_number" AS "uid", "bsd_datetime" AS "datetime", "message" AS "message" FROM "test_table"

  • Optional column names

    The custom fetch query may contain the following optional column names:

    • host (optional):

      Optional column name. When in use, host overwrites the host field of the incoming message.

    • program (optional):

      Optional column name. When in use, program overwrites the program field of the incoming message.

  • Considerations and best practices

    NOTE: The query must not contain any comments.

    NOTE: The host, program, and timezone parameters can be selected from columns or set as a fix value. The timezone must contain time-shifting value and not the name of the time zone. For example:

    SELECT "myhost" AS "host", "myprogram" AS "program", "+01:00" AS "timezone", <further-parts-of-the-query>

    		 Caution:

    The syslog-ng Store Box (SSB) appliance does not validate or limit the contents of customized queries. Consequently, queries performed with a user with write-access can potentially modify or even harm the database. Use customized queries with care, and only for your own responsibility.

Examples: creating a customized fetch query
Example: SQL source fetch_query

The following custom fetch query queries records that are older than the last read record:

SELECT * FROM <table_name>

Or a more detailed example:

SELECT "uniq_id_number" AS "uid", "date_string" AS "datetime", "message" AS "message" FROM "test_mysql"
Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating