ClickHouse Alert Rule Configuration

This document provides detailed instructions on configuring alert rules for ClickHouse data sources in Monitors. Monitors supports using standard SQL syntax to query ClickHouse and trigger alerts based on query results.

Prerequisites

Query language: Uses ClickHouse SQL syntax.

Field handling: The alert engine automatically converts all field names in query results to lowercase. When configuring value fields and label fields, always use lowercase letters.

Type conversion: ClickHouse has rich data types (such as DateTime, Nullable, Array, etc.), and the alert engine may not be able to parse complex types directly. It's recommended to use functions like toString(), toFloat64() in SQL to convert results to basic types.

1. Threshold mode

This mode is suitable for scenarios requiring threshold comparisons on aggregated values.

Configuration

Query: Write a SQL aggregation query that returns numeric columns and (optional) label columns.

Example: Count error logs per service in the last 5 minutes.

Field mapping:

Label fields: Fields used to distinguish different alert objects. In the example above, this is service_name. This field can be left empty, and Monitors will automatically treat all fields except value fields as label fields.

Value fields: Numeric fields used for threshold evaluation. In the example above, this is error_cnt.

Threshold conditions:

Use $A.field_name to reference values.

Example: Critical: $A.error_cnt > 50, Warning: $A.error_cnt > 10.

How it works

The engine executes the SQL query and retrieves the result set. It groups data by label fields, then extracts value field values for comparison against threshold expressions.

Recovery logic

Auto recovery: When the latest SQL query result shows that a data group's value no longer meets any alert threshold, a recovery event is automatically generated.

Specific recovery conditions: Configure additional recovery expressions (e.g., $A.error_cnt < 5).

Recovery query:

Supports configuring an independent SQL statement for recovery evaluation.

Supports ${label_name} variable substitution.

Example: The alert SQL found that network card with network_host="a", interface="b" is down. The recovery SQL can be:

The engine replaces ${network_host} and ${interface} with actual values before executing the query. If data is found, recovery is confirmed.

2. Data exists mode

This mode is suitable for scenarios where filtering logic is written directly in SQL.

Configuration

Query: Use a HAVING clause in SQL to directly filter out anomalous data.

Example: Directly query services with more than 50 errors.

Evaluation rule: An alert is triggered as soon as the SQL query returns data.

Pros and cons

Pros: Leverages ClickHouse's powerful OLAP capabilities for computation and filtering, delivering excellent performance.

Cons: Cannot distinguish between multiple severity levels.

Recovery logic

Data disappearance means recovery: Recovery is confirmed when the SQL query result is empty.

Recovery query: Supports configuring additional query statements to assist in determining recovery status.

3. No data mode

This mode monitors scenarios where data is expected but actually missing.

Configuration

Query: Write a SQL query that should continuously return data.

Example: Query heartbeat reports from all probes.

Evaluation rule: If a probe_id appeared in previous cycles but cannot be found in the current and N consecutive cycles, a "no data" alert is triggered.

4. Best practices and considerations

Type conversion

ClickHouse drivers may return formats that the engine cannot recognize when processing certain complex types.

Recommendation: Explicitly convert types in the SELECT clause.

toString(uuid)

toFloat64(avg_duration)

Time filtering

ClickHouse is very sensitive to time partitions. Always include time range filtering in the WHERE clause to leverage indexes for query acceleration.

Recommended: timestamp > now() - INTERVAL 5 MINUTE or timestamp > toDateTime(now()) - 300.

Field case sensitivity

Monitors engine converts column names returned by ClickHouse to lowercase.

Configuration: Always use lowercase letters when filling in label fields and value fields.

ClickHouse

Prerequisites#

1. Threshold mode#

Configuration#

How it works#

Recovery logic#

2. Data exists mode#

Configuration#

Pros and cons#

Recovery logic#

3. No data mode#

Configuration#

4. Best practices and considerations#

Type conversion#

Time filtering#

Field case sensitivity#

Prerequisites

1. Threshold mode

Configuration

How it works

Recovery logic

2. Data exists mode

Configuration

Pros and cons

Recovery logic

3. No data mode

Configuration

4. Best practices and considerations

Type conversion

Time filtering

Field case sensitivity