Kaspersky Unified Monitoring and Analysis Platform

Connector, sql type

Expand all | Collapse all

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

Basic settings tab

Advanced settings tab

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

  For example:

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

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

  KUMA supports the following data types:

  • Data that can be cast to string (such as strings, numeric values, and BLOBs) is displayed as strings.
  • Arrays and maps are displayed in JSON format or using the built-in go fmt.Sprintf("%v",v) function to display them in the best possible way.

  Two methods of connecting to ClickHouse are possible:

  • Without credentials, by entering a URL: clickhouse://host:port/database
  • With credentials, by entering a URL: clickhouse://user:password@host:port/database

  When using TLS encryption, by default, the connector works only on port 9440. If TLS encryption is not used, by default, the connector works with ClickHouse only on port 9000.

  The connector does not work over HTTP.

  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.

  The TLS mode is used only if the ClickHouse driver is specified.

  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.

  If the initial value of the ID contains an indication of time (datetime), in the query, you must use a variable for time conversion (parseDateTimeBestEffort). For example, if the time is specified as 2021-01-01 00:10:00, the following query may be used:

  select connections, username, host, login_time from connections where login_time > parseDateTimeBestEffort(?) order by login_time

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.

We recommend adding the order by command to the query string, followed by the sorting field. For example, select * from table_name where id > ? order by id.

Examples of SQL requests