Skip to main content
Version: 5.0

Clickhouse search basics

Syntax​

To search the Clickhouse repository, use the source command .

Command syntax:

source clk:<connection_name>:<database_name>.<table_name>:<qsize>
ParameterSyntaxDefault valueDescription
connection_name<string>See. [default-connection](#default connection).Connection name
database_name<string>defaultName of the database in which the table is located
table_name<string>The name of the table from which data is requested
qsize<int>1000000, when using the web interface 1000Maximum number of data to be selected
OPTIONS

Clickhouse source supports the following options:

  • qsize - number of processed events in the search
  • timefield - name of the field in which the timestamp is stored
  • earliest - search start timestamp
  • latest - timestamp of the end of the search

For more information, see source.

Default connection​

The default connection name is stored in _cluster/settings in the sme.sa.click.default_connection field.

Example of search queries in Clickhouse​

Example 1​

Query 150 events. Connection name: click. Database: sysmon. Table: sysmon.default.

source clk:click:sysmon:150

Example 2​

Query 1000 events from the default.zabbix table. Connection name is default.

note

Parameter qsize=1000 when searching through the web interface and qsize=1000000 when searching through the api.

source clk:zabbix

Example 3​

Query 2000 events from the server.logs table. Connection name is default. Time range two days ago and one hour ago, timestamp is stored in the field timestamp.

Example №3
source clk:server.logs qsize = 2000 timefield=timestamp earliest=-2d latest=-1h

Features of using multiple sources​

Using the source command, you can query data from multiple sources in a single search query. This could be several tables from Clickhouse and a combination of queries to OpenSearch, Clickhouse and other sources. To request multiple sources, you need to specify them separated by commas. Combining the results obtained is performed using the command append.

For more information, see source.

Examples of queries from multiple sources​

Example 1​

Query 1000 events from the zabbix table and query 1000 events from the sysmon table

source clk:zabbix, clk:sysmon

Example 2​

Query 100 events from the sysmon table, 250 events from the zabbix table in time range 1 day ago and now, 2000 events events from the Opensearch index sysmon_operational.

source clk:sysmon:100, (clk:zabbix qsize=250 earliest=-1d), sysmon_operational qsize=2000

Timeline​

The Clickhouse source supports Timeline calculation using internal storage algorithms. In this case, the Timeline will be calculated for all events in the requested time period.

When Timeline calculation using Clickhouse is not possible, it will be performed based on the events included in the final selection.

Conditions for calculating Timeline

Timeline can be calculated using internal storage algorithms only if one table is queried and the commands do not change the fields in the search results.

List of commands for calculating timeline using Clickhouse:

Source Clickhouse supports Sidebar calculation using internal storage algorithms. The names of the fields and their type will be displayed on the side panel, and when clicked, statistics for this field will be calculated for all events in the requested time period.

When Sidebar calculation using Clickhouse is not possible, it will be performed based on the events included in the final selection.

Conditions for calculating Sidebar

Sidebar can be calculated using internal storage algorithms only if a single table is queried and the commands do not change the fields in the search results.

List of commands for calculating Sidebar using Clickhouse:

Nullable​

In order for null to be written to it if there is no value in a field, or to assign a null value to a field, the field must have the Nullable property. Otherwise the field will take a default value depending on its type:

  • Int - 0
  • String - ""
  • Datetime - 1970-01-01 00:00:00