Generating an SQL query using a builder

In KUMA, you can use a query builder to generate an SQL query for filtering events.

To generate an SQL query using a builder:

1. In the Events section of the KUMA web interface, click the button.

   The filter constructor window opens.

2. Generate a search query by providing data in the following parameter blocks:
   • SELECT—event fields that should be returned. The * value is selected by default, which means that all available event fields must be returned. To optimize your search, you can use the drop-down list to select the specific fields so that data from other unnecessary fields will not be loaded.

     When selecting an event field, you can use the field on the right of the drop-down list to specify an alias for the column of displayed data, and you can use the right-most drop-down list to select the operation to perform on the data: count, max, min, avg, sum.

     If you are using aggregation functions in a query, you cannot customize the events table display, sort events in ascending or descending order, receive statistics, or perform a retroscan.

     When filtering by alert-related events in drilldown analysis mode, you cannot perform operations on the data of event fields or assign names to the columns of displayed data.

   • FROM—data source. Select the events value.
   • WHERE—conditions for filtering events.

     Conditions and groups of conditions can be added by using the Add condition and Add group buttons. The AND operator value is selected by default in a group of conditions, but the operator can be changed by clicking on this value. Available values: AND, OR, NOT. The structure of conditions and condition groups can be changed by using the icon to drag and drop expressions.

     Adding filter conditions:

     1. In the drop-down list on the left, select the event field that you want to use for filtering.
     2. Select the necessary operator from the middle drop-down list. The available operators depend on the type of value of the selected event field.
     3. Enter the value of the condition. Depending on the selected type of field, you may have to manually enter the value, select it from the drop-down list, or select it on the calendar.

     Filter conditions can be deleted by using the button. Group conditions are deleted using the Delete group button.

   • GROUP BY—event fields or aliases to be used for grouping the returned data.

     If you are using data grouping in a query, you cannot customize the events table display, sort events in ascending or descending order, receive statistics, or perform a retroscan.

     When filtering by alert-related events in drilldown analysis mode, you cannot group the returned data.

   • ORDER BY—columns used as the basis for sorting the returned data. In the drop-down list on the right, you can select the necessary order: DESC—descending, ASC—ascending.
   • LIMIT—number of strings displayed in the table.

     The default value is 250.

     If you are filtering events by user-defined period and the number of strings in the search results exceeds the defined value, you can click the Show next records button to display additional strings in the table. This button is not displayed when filtering events by the standard period.

3. Click the Apply button.

   The current SQL query will be overwritten. The generated SQL query is displayed in the search field.

   If you want to reset the builder settings, click the Default query button.

   If you want to close the builder without overwriting the existing query, click the button.

4. Click the button to display the data in the table.

The table will display the search results based on the generated SQL query.

When switching to another section of the web interface, the query generated in the builder is not preserved. If you return to the Events section from another section, the builder will display the default query.

After updating KUMA to version 1.6, event filtering that uses an SQL query containing the inSubnet condition may result in error Code: 441. DB::Exception: Invalid IPv4 value. If this is the case, you must add the directive <cast_ipv4_ipv6_default_on_conversion_error>true</cast_ipv4_ipv6_default_on_conversion_error> in the profiles → default section of the file /opt/kaspersky/kuma/clickhouse/cfg/config.d/users.xml on the storage servers (on each machine of the ClickHouse cluster).

For more details on SQL, refer to the ClickHouse documentation. See also the SQL functions and operators supported by KUMA.

See also: Complicated SQL queries About events Storage

Page top