o11y-agent-skills/plugins/signoz/skills/signoz-clickhouse-query/SKILL.md

name, description

name	description
signoz-clickhouse-query	Write ClickHouse queries for SigNoz dashboards over OpenTelemetry logs and traces. Use this skill whenever the user asks for SigNoz ClickHouse queries for logs or traces, SigNoz dashboard queries, log analysis, span counts, latency, or trace breakdowns.

Writing ClickHouse Queries for SigNoz Dashboards

When to Use

Use this skill when the user asks for SigNoz queries involving:

• Logs: severity, body text, log volume, structured fields, containers, services, or environments.
• Traces: spans, latency, duration, p95 or p99, HTTP operations, DB operations, or error spans.
• Dashboard panels: timeseries charts, value widgets, and table breakdowns.

If the user asks for a dashboard panel but does not mention ClickHouse, still use this skill.

Signal Detection

Identify whether the request is about logs or traces.

• Logs: log lines, severity, body text, log volume, container logs, or structured log fields.
• Traces: spans, latency, duration, p99, trace analysis, HTTP operations, DB operations, or error spans.

If the request is ambiguous, ask the user to clarify.

Reference Routing

• Logs: read references/clickhouse-logs-reference.md before writing any query.
• Traces: read references/clickhouse-traces-reference.md before writing any query.

Each reference covers table schemas, optimization patterns, attribute access syntax, dashboard templates, query examples, and a validation checklist.

Quick Reference

• Timeseries panel: return rows of (ts, value) for a chart over time.
• Value panel: return a single value for a stat or counter widget.
• Table panel: return labelled columns for a grouped breakdown.

Key Variables by Signal

Logs

• Timestamp type: UInt64 in nanoseconds.
• Time filter: $start_timestamp_nano and $end_timestamp_nano.
• Bucket filter: $start_timestamp and $end_timestamp.
• Display conversion: fromUnixTimestamp64Nano(timestamp).
• Main table: signoz_logs.distributed_logs_v2.
• Resource table: signoz_logs.distributed_logs_v2_resource.

Traces

• Timestamp type: DateTime64(9).
• Time filter: $start_datetime and $end_datetime.
• Bucket filter: $start_timestamp and $end_timestamp.
• Display conversion: use the timestamp directly.
• Main table: signoz_traces.distributed_signoz_index_v3.
• Resource table: signoz_traces.distributed_traces_v3_resource.

Top Anti-Patterns

• Missing ts_bucket_start BETWEEN $start_timestamp - 1800 AND $end_timestamp.
• Using plain IN instead of GLOBAL IN on the resource fingerprint subquery.
• Adding a resource CTE when there is no resource attribute filter.
• Logs query with $start_datetime or $end_datetime.
• Traces query with $start_timestamp_nano or $end_timestamp_nano.
• Traces query with resources_string['service.name'] instead of resource_string_service$$name.

Workflow

1. Detect the signal: logs or traces.
2. Read the matching reference file before writing the query.
3. Pick the panel type: timeseries, value, or table.
4. Build the query using the required patterns from the reference.
5. Validate the result with the checklist in the reference.