Manually creating an SQL query

You can use the search string to manually create SQL queries of any complexity for filtering events.

To manually generate an SQL query:

  1. Go to the Events section of the KUMA web interface.

    An input form opens.

  2. Enter your SQL query into the input field. You must use single quotes in queries.
  3. Click the SearchField button.

You will see a table of events that satisfy the criteria of your query. If necessary, you can filter events by period.

Supported functions and operators

If you want to use a special character in a query, you need to escape this character by placing a backslash (\) character in front of it.

Example:

SELECT * FROM `events` WHERE match(Message, 'ssh:\'connection.*') ORDER BY Timestamp DESC LIMIT 250

In the events table, all events whose Message field contains text corresponding to the ssh: 'connection' template are sorted by the Timestamp column in descending order. The number of strings that can be displayed in the table is 250.

When creating a normalizer for events, you can choose whether to retain the field values of the raw event. The data is stored in the Extra event field. This field is searched for events by using the LIKE operator.

Example:

SELECT * FROM `events` WHERE DeviceAddress = '00.00.00.000' AND Extra LIKE '%"app":"example"%' ORDER BY Timestamp DESC LIMIT 250

In the events table, all events for hosts with the IP address 00.00.00.000 where the example process is running are sorted by the Timestamp column in descending order. The number of strings that can be displayed in the table is 250.

When switching to the query builder, the query parameters that were manually entered into the search string are not transferred to the builder so you will need to create your query again. Also, the query created in the builder does not overwrite the query that was entered into the search string until you click the Apply button in the builder window.

Aliases must not contain spaces.

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

See also:

Generating an SQL query using a builder

Limiting the complexity of queries in alert investigation mode

About events

Storage

Page top