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:
To connect to the database, you must define the values of the following settings on the Basic settings tab:
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.
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:
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 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:
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
.
Example URL: mysql://{user}:{password}@tcp({server}:{port})/{database}
The characters ?
are used as placeholders in the SQL query.
Example URL: postgres://{user}:{password}@{server}/{database}?sslmode=disable
The characters $1
are used as a placeholder in the SQL query.
Example URL: postgres://{user}:{password}@{server}:{port}/{database}?sslmode=disable
The characters $1
are used as a placeholder in the SQL query.
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).
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:
select * from connections where login_time > to_timestamp(:val, 'YYYY-MM-DD"T"HH24:MI:SS"Z"')
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.
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
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.