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 settings for the connector:

• Name (required)—unique name of the resource. Must contain 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.
• Reconnect to the database every time a query is sent — the check box is cleared by default.
• Poll interval, sec —interval for executing SQL queries. This value is specified in seconds. The default value is 10 seconds.
• Description—resource description: up to 4,000 Unicode characters.

To connect to the database, you must specify the following settings on the Basic settings tab, in the Connection section:

• In the Database type drop-down list, you can select the type of the database you want to connect to. After selecting the type of database, 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.
• If the Secret separately check box is selected, the window displays the required URL field in which you can specify the connection URL, and a Secret drop-down list with secrets of the 'credentials' type. In this way, you can 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 (required):
  • field for selecting or creating a secret of the 'urls' type, which stores a list of URLs for connecting to the database. Available if the Secret separately check box is cleared.

    If necessary, you can edit or 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—urls.

          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>&encrypt=disable

            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—any additional information.
    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.
    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>&encrypt=disable

            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—any additional information.
    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.

    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

    !	#	$	%	&	'	(	)	*	+
    %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, [, ], :, /, #, %, \.

  • Field for specifying the URL of the connection. It is used together with a secret of the 'credentials' type. Available if the Secret separately check box is selected.
• Secret is a drop-down list for selecting an existing secret or creating a new secret of the 'credentials' type. The drop-down list is available if the Secret separately check box is selected.
• Authorization is the type of authorization when connecting to the specified URL Possible values:
  • Disabled is the default value.
  • If Plain is selected, you must specify the secret containing user account credentials for authorization when connecting to the connector.
  • If PublicPKI is selected, you must specify the secret containing the base64-encoded PEM private key and the public key.
• TLS mode specifies whether TLS encryption is used. Available values:
  • 'Disabled' means TLS encryption is not used.
  • 'Enabled' means encryption is used, but without certificate verification.
  • 'Custom CA' means encryption is used with verification of the certificate that must be signed by a Certificate Authority. The secret containing the certificate is selected from the 'Custom CA' drop-down list, which is displayed when this option is selected.

    Creating a certificate signed by a Certificate Authority

    To use this TLS mode, you must do the following on the KUMA Core server (OpenSSL commands are used in the examples below):

    1. Create the key that will be used by the Certificate Authority.

       Example command:

       openssl genrsa -out ca.key 2048

    2. Generate a certificate for the key that was just created.

       Example command:

       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.

       Example command:

       openssl req -newkey rsa:2048 -nodes -keyout server.key -subj "/CN=<common host name of KUMA server>" -out server.csr

    4. Create a certificate signed by the Certificate Authority. The subjectAltName must include the domain names or IP addresses of the server for which the certificate is being created.

       Example command:

       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. The obtained server.crt certificate should be uploaded in the KUMA web interface as a certificate-type secret, which should then be selected from the Custom CA drop-down list.

    When using TLS, it is impossible to specify an IP address as a URL.

• 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 toggle switch that lets you specify whether resource logging must be enabled. By default, this toggle switch is in the Disabled position.

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 that state file is empty, delete it and restart the collector.

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.

  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}

  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 accessing Oracle DB, if the initial ID value is used in the datetime format, you must consider the type of the field in the database itself and, if necessary, add conversions of the time string in the query to ensure correct operation of the sql connector. 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 database settings, 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). Then, in the Identity seed field, specify 2021-01-01T00:00:00Z, and perform the conversion in the query 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 type, then depending on the database settings, 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). Then, in the Identity seed field, specify 2021-01-01T00:00:00+03:00, and perform the conversion in the query 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 more details about the to_timestamp and to_timestamp_tz functions, refer to the official Oracle documentation.

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

• Firebird SQL

  Example URL:

  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

  This connector works with ClickHouse only on TCP port 9000 by default without TLS encryption and on port 9440 by default in TLS mode. If the TLS encryption mode is configured on the ClickHouse server, and the 'Disabled' mode is selected in the connector, or vice versa, the database connection is not 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 SQL connector settings for the ClickHouse connection, you must specify a TLS mode: the Disabled mode is not allowed if a certificate is used for authentication. If you select Custom CA, in the Identity column field, specify a secret ID of the 'certificate' type.

  You must also specify an Authorization type:

  • If Disabled is specified, the Identity column setting is left unset.
  • Plain is used when 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 is used when 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 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