Sql type

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. Maximum length of the name: 128 Unicode characters. Required setting.
Type	Connector type. You need to select sql. Required setting.
Tenant	The name of the tenant that owns the resource. Required setting.
Default query	SQL query that is executed when connecting to the database. Required setting.
Reconnect to the database every time a query is sent	The connector reconnects to the database every time a query is sent. This check box is cleared by default.
Poll interval, sec	Interval for executing SQL queries in seconds. The default value is 10 seconds.
Description	Description of the resource. Maximum length of the description: 4000 Unicode characters.

Connection section

Setting

Description

Database type

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.

Secret separately

Viewing information about the connection. If this check box is selected, the following settings are displayed in the window:

URL is the connection URL.
Secret is the secret of the 'credentials' type.

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.

If this check box is cleared, only the URL field is available for selecting or creating a secret of the 'urls' type.

This check box is cleared by default.

URL

Field for specifying one of the following values:

If you cleared the Secret separately check box, in this field, you need to specify the 'urls' secret containing a list of URLs for connecting to the database.
If necessary, you can edit or create a secret.
To create a secret:
1. Click the button.
  The secret window is displayed.
2. Define the values for the following settings:
  1. Name—the name of the added secret.
  2. Type. This value is set by default and cannot be changed.
  3. URL—URL of the database.
    You must keep in mind that each type of database uses its own URL format for connections.
    
    Available URL formats are as follows:
    - For SQLite:
      sqlite3://file:<file_path>
      
      A question mark (?) is used as a placeholder.
    - For MSSQL:
      sqlserver://<user>:<password>@<server:port>/<instance_name>?database=<database> (recommended)
      sqlserver://<user>:<password>@<server>?database=<database>
      The characters @p1 are used as a placeholder.
    - For MySQL:
      mysql://<user>:<password>@tcp(<server>:<port>)/<database>
      
      The characters %s are used as a placeholder.
    - For PostgreSQL:
      postgres://<user>:<password>@<server>/<database>?sslmode=disable
      
      The characters $1 are used as a placeholder.
    - For Cockroach:
      postgres://<user>:<password>@<server>:<port>/<database>?sslmode=disable
      The characters $1 are used as a placeholder.
    - For Firebird:
      firebirdsql://<user>:<password>@<server>:<port>/<database>
      
      A question mark (?) is used as a placeholder.
  4. Description—additional information about the secret.
3. If necessary, click Add and specify an additional URL. In this case, if one URL is not available, the program connects to the next URL specified in the list of addresses.
4. Click the Save button.
To edit the secret:
1. Click the button.
  The secret window is displayed.
2. Specify the values for the settings that you want to change.
  You can change the following values:
  1. Name—the name of the added secret.
  2. URL—URL of the database.
    You must keep in mind that each type of database uses its own URL format for connections.
    
    Available URL formats are as follows:
    - For SQLite:
      sqlite3://file:<file_path>
      
      A question mark (?) is used as a placeholder.
    - For MSSQL:
      sqlserver://<user>:<password>@<server:port>/<instance_name>?database=<database> (recommended)
      sqlserver://<user>:<password>@<server>?database=<database>
      The characters @p1 are used as a placeholder.
    - For MySQL:
      mysql://<user>:<password>@tcp(<server>:<port>)/<database>
      
      The characters ? are used as placeholders.
    - For PostgreSQL:
      postgres://<user>:<password>@<server>/<database>?sslmode=disable
      
      The characters $1 are used as a placeholder.
    - For Cockroach:
      postgres://<user>:<password>@<server>:<port>/<database>?sslmode=disable
      
      The characters $1 are used as a placeholder.
    - For Firebird:
      firebirdsql://<user>:<password>@<server>:<port>/<database>
      
      A question mark (?) is used as a placeholder.
  3. Description—additional information about the secret.
3. If necessary, click Add and specify an additional URL. In this case, if one URL is not available, the program connects to the next URL specified in the list of URLs.
4. Click Save.
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 the Secret separately check box is selected, you need to specify the connection URL in this field. The URL is used with a secret of the 'credentials' type.

Secret

Secret of the 'credentials' type. This setting is available if you have selected the Secret separately check box.

Authorization

Type of authorization when connecting to the specified URL: Available values:

Disabled. The default value.
Plain. If this option is selected, you must indicate the secret containing user account credentials for authorization when connecting to the connector.
PublicPKI. If you select this value, you must specify the secret containing the base64-encoded PEM private key and the public key.

TLS mode

TLS encryption mode. Available values:

Disabled means TLS encryption is not used. The default value.
Enabled means TLS encryption is used, but certificates are not verified.
Custom CA means TLS encryption is used with verification that the certificate was signed by a Certificate Authority. If you select this value, from the Custom CA drop-down list, select a secret with a certificate signed by the CA.
How to create a certificate signed by a Certificate Authority?
You can create a CA-signed certificate on the KUMA Core server (the following command examples use OpenSSL).

To create a certificate signed by a Certificate Authority:
1. Generate a key to be used by the Certificate Authority, for example:
  openssl genrsa -out ca.key 2048
2. Create a certificate for the generated key, for example:
  openssl req -new -x509 -days 365 -key ca.key -subj "/CN=<common host name of Certificate Authority>" -out ca.crt
3. Create a private key and a request to have it signed by the Certificate Authority, for example:
  openssl req -newkey rsa:2048 -nodes -keyout server.key -subj "/CN=<common host name of KUMA server>" -out server.csr
4. Create the certificate signed by the Certificate Authority. You need to include the domain names or IP addresses of the server for which you are creating the certificate in the subjectAltName variable, for example:
  openssl x509 -req -extfile <(printf "subjectAltName=DNS:domain1.ru,DNS:domain2.com,IP:192.168.0.1") -days 365 -in server.csr -CA ca.crt -CAkey ca.key -CAcreateserial -out server.crt
5. Upload the generated server.crt certificate to the KUMA web interface into a secret of the certificate type, then select the secret of the certificate type in the Custom CA drop-down list.
When using TLS encryption, you cannot specify an IP address as the URL.

Special considerations for TLS encryption mode

The TLS versions that are available for an sql connector are determined by the database driver used for the connection. The database drivers are added to KUMA statically.

Depending on the database being used, the TLS mode can be specified in the connection string or in the configuration file. If the TLS mode is not specified explicitly in the configuration of the connection, the driver supports the ciphersets and the TLS protocol version that are specified for it in the std library of the Go language.

TLS versions 1.0 and 1.1 are disabled in the Go language. For details, see https://go.dev/doc/go1.18#tls10.

Identity column

Name of the column that contains the ID for each row of the table.

Required setting.

Identity seed

The value in the identity column for determining the row from which you want to start reading data from the SQL table.

Query

Additional SQL query that is executed instead of the default SQL query.

Poll interval, sec

Interval for executing SQL queries in seconds. The specified interval is used instead of the default interval for the connector. The default value is 10 seconds.

Description

Description of the resource. Maximum length of the description: 4000 Unicode characters.

Advanced settings tab

Setting	Description
Character encoding	Character encoding. 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	Resource logging. The toggle switch is turned off by default.

Setting

Description

Character encoding

Character encoding. 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

Resource logging. The toggle switch is turned off by default.

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.
For example:

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

The characters ? are used as placeholders in the SQL query.
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