Kaspersky Unified Monitoring and Analysis Platform
2.0
English
KUMA resources  >  Connectors  >  Connector settings  >  Sql type

Sql type

KUMA supports multiple types of databases.

When creating a connector, you must specify general connector settings and specific database connection settings.

On the Basic settings tab, you must specify the following values for the connector:

  • Name (required)—unique name of the resource. Must contain from 1 to 128 Unicode characters.
  • Type (required)—connector type, sql.
  • Tenant (required)—name of the tenant that owns the resource.
  • Default query (required)—SQL query that is executed when connecting to the database.
  • Poll interval, sec —interval for executing SQL queries. This value is specified in seconds.

    The default value is 10 seconds.

  • Description—up to 256 Unicode characters describing the resource.

To connect to the database, you need to define the values of the following settings on the Basic settings tab:

  • URL (required)—secret that stores a list of URLs for connecting to the database.

    If necessary, you can edit or create a secret.

    When creating connections, strings containing account credentials with special characters may be incorrectly processed. If an error occurs when creating a connection but you are sure that the settings are correct, enter the special characters in percent encoding.

    Codes of special characters

  • Identity column (required)—name of the column that contains the ID for each row of the table.
  • Identity seed (required)—identity column value that will be used to determine the specific line to start reading data from the SQL table.
  • Query—field for an additional SQL query. The query indicated in this field is performed instead of the default query.
  • Poll interval, sec —interval for executing SQL queries. The interval defined in this field replaces the default interval for the connector.

    This value is specified in seconds. The default value is 10 seconds.

On the Advanced settings tab, you need to specify the following settings for the connector:

  • Character encoding—the specific encoding of the characters. The default value is UTF-8.

    KUMA converts SQL responses to UTF-8 encoding. You can configure the SQL server to send responses in UTF-8 encoding or change the encoding of incoming messages on the KUMA side.

  • Debug—a drop-down list where you can specify whether resource logging should be enabled. By default it is Disabled.

Within a single connector, you can create a connection for multiple supported databases.

Supported SQL types and their specific usage features

The UNION operator is not supported by the SQL Connector resources.

The following SQL types are supported:

  • MSSQL

    Example URLs:

    • sqlserver://{user}:{password}@{server:port}/{instance_name}?database={database} – (recommended option)
    • sqlserver://{user}:{password}@{server}?database={database}

    The characters @p1 are used as a placeholder in the SQL query.

    If you need to connect using domain account credentials, specify the account name in <domain>%5C<user> format. For example: sqlserver://domain%5Cuser:password@ksc.example.com:1433/SQLEXPRESS?database=KAV.

  • MySQL

    Example URL: mysql://{user}:{password}@tcp({server}:{port})/{database}

    The characters ? are used as placeholders in the SQL query.

  • PostgreSQL

    Example URL: postgres://{user}:{password}@{server}/{database}?sslmode=disable

    The characters $1 are used as a placeholder in the SQL query.

  • CockroachDB

    Example URL: postgres://{user}:{password}@{server}:{port}/{database}?sslmode=disable

    The characters $1 are used as a placeholder in the SQL query.

  • SQLite3

    Example URL: sqlite3://file:{file_path}

    A question mark (?) is used as a placeholder in the SQL query.

  • Oracle DB

    Example URL: oracle://{user}/{password}@{server}:{port}/{service_name}

    Easy Connect syntax is used. The characters :val are used as a placeholder in the SQL query.

    When querying the Oracle DB, if the initial value of the ID is in datetime format, the Oracle to_timestamp_tz function should be used to add the date conversion to the SQL query. For example, select * from connections where login_time > to_timestamp_tz(:val, 'YYYY-MM-DD"T"HH24:MI:SSTZH:TZM'). In this example, Connections is the Oracle DB table and the :val variable is taken from the Identity seed field, therefore it must be indicated in a format with the timezone (for example, 2021-01-01T00:10:00+03:00).

    To access the Oracle DB, the libaio1 package must be installed.

  • Firebird SQL

    Example URL: firebirdsql://{user}:{password}@{server}:{port}/{database}

    A question mark (?) is used as a placeholder in the SQL query.

A sequential request for database information is supported in SQL queries. For example, if you type select * from <name of data table> where id > <placeholder> in the Query field, the Identity seed field value will be used as the placeholder value the first time you query the table. In addition, the service that utilizes the SQL connector saves the ID of the last read entry, and the ID of this entry will be used as the placeholder value in the next query to the database.

Examples of SQL requests

Article ID: 220746, Last review: Mar 27, 2024
Page top