Connector, sql type

Expand all | Collapse all

KUMA supports multiple types of databases. When creating a connector of the sql type, you must specify general connector settings and specific database connection settings. Settings for a connector of the sql type are described in the following tables.

Basic settings tab

Setting	Description
Name	Unique name of the resource. The maximum length of the name is 128 Unicode characters. Required setting.
Tenant	The name of the tenant that owns the resource. Required setting.
Type	Connector type: sql. Required setting.
Tags
Default query	SQL query that is executed when connecting to the database. Required setting.
Reconnect to the database every time a query is sent	This toggle enables reconnection of the connector to the database every time a query is sent. This toggle switch is turned off by default.
Poll interval, sec	Interval for executing SQL queries in seconds. The default value is 10 seconds.
Connection	Database connection settings: Database type is the the type of the database to connect to. When you select a database type, the prefix corresponding to the communication protocol is displayed in the URL field. For example, for a ClickHouse database, the URL field contains the clickhouse:// prefix. The Secret separately check box allows viewing the connection information. URL is the connection URL. This lets you view connection information without having to re-create a large number of connections if the password of the user account that you used for the connections changes. 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 specified settings are correct, enter the special characters in percent encoding. Codes of special characters ! # $ % & ' ( ) * + %21 %23 %24 %25 %26 %27 %28 %29 %2A %2B , / : ; = ? @ [ ] \ %2C %2F %3A %3B %3D %3F %40 %5B %5D %5C The following special characters are not supported in passwords used to access SQL databases: space, [, ], :, /, #, %, \. If you select the Secret separately check box, you can select an existing URL or create a new URL. To create a new URL, select Create new. If you want to edit the settings of an existing URL, click the pencil icon next to it. Secret  is an urls secret that stores a list of URLs for connecting to the database. This field is displayed if the Secret separately check box is selected. Identity column is the name of the column that contains the ID for each row of the table. Required setting. Identity seed is the value in the identity column for determining the row from which you want to start reading data from the SQL table. Query is the additional SQL query that is executed instead of the default SQL query. Poll interval, sec is the SQL query execution interval in seconds. The specified interval is used instead of the default interval for the connector. The default value is 10 seconds. You can add multiple connections or delete a connection. To add a connection, click the + Add connection button. To remove a connection, click the delete icon next to it.
Description	Description of the resource. The maximum length of the description is 4000 Unicode characters.

Advanced settings tab

Setting	Description
Debug	Ths switch enables resource logging. This toggle switch is turned off by default.
Character encoding	Character encoding. The default 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.

Within a single connector, you can create a connection for multiple supported databases. If a collector with a connector of the sql type cannot be started, check if the /opt/kaspersky/kuma/collector/<collector ID>/sql/state-<file ID> state file is empty. If the state file is empty, delete it and restart the collector.

Supported SQL types and their specific usage features

The following SQL types are supported:

• MSSQL.

  For example:

  • sqlserver://{user}:{password}@{server:port}/{instance_name}?database={database}

  We recommend using this URL variant.

  • sqlserver://{user}:{password}@{server}?database={database}

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

  If you want 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/MariaDB

  For example:

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

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

• PostgreSQL.

  For example: postgres://{user}:{password}@{server}/{database}?sslmode=disable

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

• CockroachDB

  For example:

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

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

• SQLite3

  For example:

  sqlite3://file:{file_path}

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

  When querying SQLite3, if the initial value of the ID is in datetime format, you must add a date conversion with the sqlite datetime function to the SQL query. For example:

  select * from connections where datetime(login_time) > datetime(?, 'utc') order by login_time

  In this example, connections is the SQLite table, and the value of the variable ? is taken from the Identity seed field, and it must be specified in the {<date>}T{<time>}Z format, for example, 2021-01-01T00:10:00Z).

• Oracle DB

  In version 2.1.3 or later, KUMA uses a new driver for connecting to oracle. When upgrading, KUMA renames the connection secret to 'oracle-deprecated' and the connector continues to work. If no events are received after starting the collector with the 'oracle-deprecated' driver type, create a new secret with the 'oracle' driver and use it for connecting. We recommend using the new driver.

  Example URL of a secret with the new 'oracle' driver:

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

  oracle://{user}:{password}@{server}:{port}/?SID={SID_VALUE}

  If the query execution time exceeds 30 seconds, the oracle driver aborts the SQL request, and the following error appears in the collector log: user requested cancel of current operation. To increase the execution time of an SQL query, specify the value of the timeout parameter in seconds in the connection string, for example:

  oracle://{user}:{password}@{server}:{port}/{service_name}?timeout=300

  Example URL of a secret with the legacy 'oracle-deprecated' driver:

  oracle-deprecated://{user}/{password}@{server}:{port}/{service_name}

  The :val SQL variable is used as a placeholder in.

  When querying Oracle DB, if the identity seed is in the datetime format, you must consider the type of the field in the database and, if necessary, add conversions of the time string in the SQL query to make sure the SQL connector works correctly. For example, if the Connections table in the database has a login_time field, the following conversions are possible:

  • If the login_time field has the TIMESTAMP type, then depending on the configuration of the database, the login_time field may contain a value in the YYYY-MM-DD HH24:MI:SS format, for example, 2021-01-01 00:00:00. In this case, you need to specify 2021-01-01T00:00:00Z in the Identity seed field, and in the SQL query, perform the conversion using the to_timestamp function, for example:

    select * from connections where login_time > to_timestamp(:val, 'YYYY-MM-DD"T"HH24:MI:SS"Z"')

  • If the login_time field has the TIMESTAMP WITH TIME ZONE type, then depending on the configuration of the database, the login_time field may contain a value in the YYYY-MM-DD"T"HH24:MI:SSTZH:TZM format (for example, 2021-01-01T00:00:00+03:00). In this case, you need to specify 2021-01-01T00:00:00+03:00 in the Identity seed field, and in the SQL query, perform the conversion using the to_timestamp_tz function, for example:

    select * from connections_tz where login_time > to_timestamp_tz(:val, 'YYYY-MM-DD"T"HH24:MI:SSTZH:TZM')

    For details about the to_timestamp and to_timestamp_tz functions, please refer to the official Oracle documentation.

  To interact with Oracle DB, you must install the libaio1 Astra Linux package.

• Firebird® SQL

  For example:

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

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

  If a problem occurs when connecting Firebird on Windows, use the full path to the database file, for example:

  firebirdsql://{user}:{password}@{server}:{port}/C:\Users\user\firebird\db.FDB

• ClickHouse

  When using TLS encryption, by default, the connector works with ClickHouse only on port 9000. If TLS encryption is not used, by default, the connector works with ClickHouse only on port 9440. If TLS encryption mode is configured on the ClickHouse server, and in connector settings, in the TLS mode drop-down list, you have selected Disabled or vice versa, the database connection cannot be established.

  If you want to connect to the KUMA ClickHouse, in the SQL connector settings, specify the PublicPki secret type, which contains the base64-encoded PEM private key and the public key.

  In the parameters of the SQL connector for the ClickHouse connection type, you need to select Disabled in the TLS mode drop-down list. This value must not be specified if a certificate is used for authentication. If in the TLS mode drop-down list, you select Custom CA, you need to specify the ID of a secret of the 'certificate' type in the Identity column field. You also need to select one of the following values in the Authorization type drop-down list:

  • Disabled. If you select this value, you need to leave the Identity column field blank.
  • Plain. Select this value if the Secret separately check box is selected and the ID of a secret of the 'credentials' type is specified in the Identity column field.
  • PublicPki. Select this value if the Secret separately check box is selected and the ID of a secret of the 'PublicPki' type is specified in the Identity column field.

  The Secret separately check box lets you specify the URL separately, not as part of the secret.

A sequential request for database information is supported in SQL queries. For example, if in the Query field, you enter select * from <name of data table> where id > <placeholder>, the value of the Identity seed field is 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

Page top