KubeSense SQL
Requires the KubeSense MCP server; see
kubesense-mcp for
connection and auth. Tools here resolve RBAC against the
or
module,
matching the
you pass.
Tools
| Tool | Use when | Returns |
|---|
| Always, first | Field catalog for your window |
| Before running anything non-trivial | + the SQL that would run |
| The query is right | Result rows as TSV |
Both SQL tools take the same arguments:
(
or
),
,
,
, and optional
.
Use SQL Only When analyze-* Cannot
/
cover filter → group → aggregate, which is most
questions. They are cheaper, their arguments are checked more strictly, and their output
is smaller. Reach for SQL only when the shape genuinely does not fit:
| Question shape | Tool |
|---|
| count / rate / p95 / top-N, grouped | , |
| two metrics divided (error rate %) | with a formula |
| read individual rows | , |
| join two result sets | SQL |
| CTE chain / multi-step derivation | SQL |
| window function (, , running total) | SQL |
| arithmetic between two aggregates in one pass | SQL |
| , , , | SQL |
| compare two time ranges in one result | SQL |
If you find yourself writing
SELECT field, count(*) ... GROUP BY field
, stop — that is
with
value_operation: row_count
.
Tables
| Write | Reads |
|---|
| the logs table |
| the traces table |
Nothing else is addressable. A database-qualified name (
) is
rejected outright — this is the first thing that breaks when pasting a query from another
SQL tool.
must match the table you select
.
Field Names
Same catalog labels as every other KubeSense tool — the set
returns. Storage column names are rejected:
expands to the catalog columns, not the raw table.
SQL accepts four things discovery does not list
hides fields flagged non-filterable, but SQL still takes them.
Do not conclude a name is invalid because discovery omitted it:
| Field | Signal | Note |
|---|
| both | Not listed by discovery, yet every query needs it for |
| traces | Milliseconds. Discovery would show (nanoseconds) — SQL rejects that name |
| , | traces | The perspective columns |
| logs | Computed as |
Also queryable but not catalog fields:
(same column as
, and what
expands to), plus the raw attribute containers —
/
on logs,
/
on traces.
[!WARNING]
vs is the trap that bites hardest. takes
in nanoseconds. SQL takes
in
milliseconds and rejects
entirely. A threshold carried across from an analyze call is off by 10⁶.
Macros
Expanded server-side before the query runs.
| Macro | Becomes |
|---|
| the / window |
| / | the bounds as scalars, for use in expressions |
| the cluster filter; when is empty |
sql
SELECT workload, count(*) AS cnt
FROM logs
WHERE $__timeFilter(timestamp) AND $__clusters AND type = 'ERROR'
GROUP BY workload ORDER BY cnt DESC
takes the
column name on both signals. Plain SQL time
predicates work too (
WHERE timestamp >= now() - INTERVAL 1 HOUR
), but the macro keeps
the query aligned with the window you passed.
[!NOTE]
String values in SQL use
single quotes (
), unlike the
argument of the analyze/search tools, which uses bare words or double quotes. Enum
casing is unchanged:
uppercase on logs,
lowercase on traces
.
Custom Attributes
-prefixed, as everywhere else.
, or
.
sql
SELECT @http.status_code, count(*) AS cnt
FROM logs
WHERE $__timeFilter(timestamp) AND @http.status_code != ''
GROUP BY 1 ORDER BY cnt DESC
Attributes are stored as strings — cast before comparing numerically:
sql
WHERE toFloat64OrNull(@latency_ms) > 500
[!WARNING]
Attribute names are never validated — by any tool, at any stage. expressions
are compiled into a map lookup before the query is even parsed, so a misspelled key is
not an error: it yields
on logs and
on traces.
will report
. A query returning all-empty values for one column almost always means the
attribute key is wrong, not that the data is missing. Confirm every key with
first.
validate-sql
Call it before
for anything with a CTE, a join, or a field you have not used
before. It scans no data, so it is cheap to call repeatedly.
It checks two things:
- The rewrite — parse, single statement, only, table access, blocked
functions, and column names. The column check covers bare column references in the
top-level SELECT list only, and is skipped entirely when the query has a clause.
- ClickHouse's own analysis of the rewritten query, which resolves every identifier.
This is what catches a bad column in , , or inside a CTE.
On success it returns the SQL that would actually run, under
.
Read
it. It shows which storage column each label resolved to (
,
), which is the only way to confirm you addressed the field you
meant — and the fastest way to catch a name that quietly resolved to something else.
Reading the Output
returns TSV: a
line, then a header row of
your own
SELECT columns in your own order, then one row per result.
# signal=logs rows=2
workload cnt
checkout 412
payments 87
An empty result is the header line alone. That means the query ran and matched nothing —
it is not a failure, and rewriting the query is usually the wrong response. Check the
window and the cluster filter first.
Limits
- 1000 rows unless the query carries its own . Aggregate in SQL rather than
pulling rows back to count them.
- Execution is read-only. / / , multiple statements, and functions
that reach outside the table (, , , , , ) are
rejected.
- The cluster filter is applied at the table scan and cannot be bypassed by the query.
[!WARNING]
Both tools are unavailable to scope-restricted roles. If the API key or role is
limited to specific clusters, namespaces, or workloads, the call is refused — the SQL
layer applies a cluster filter only and cannot enforce namespace or workload rules. Use
/
, which do. The refusal message says so; do not retry, and do
not try to express the scope in the SQL yourself.
Worked Examples
Compare this hour against the same hour yesterday, in one result
sql
SELECT service,
countIf(timestamp >= $__toTime - INTERVAL 1 HOUR) AS now_errors,
countIf(timestamp < $__fromTime + INTERVAL 1 HOUR) AS then_errors
FROM traces
WHERE $__timeFilter(timestamp) AND $__clusters AND status = 'error'
GROUP BY service HAVING now_errors > then_errors ORDER BY now_errors DESC
Slowest endpoint per service (window function)
sql
SELECT service, resource, p95_ms FROM (
SELECT service, resource, quantile(0.95)(duration_ms) AS p95_ms,
row_number() OVER (PARTITION BY service ORDER BY quantile(0.95)(duration_ms) DESC) AS rn
FROM traces
WHERE $__timeFilter(timestamp) AND $__clusters AND role = 'server'
GROUP BY service, resource
) WHERE rn = 1 ORDER BY p95_ms DESC
Workloads that logged an error but served no failing span (CTE join)
sql
WITH bad_logs AS (
SELECT DISTINCT workload FROM logs
WHERE $__timeFilter(timestamp) AND $__clusters AND type = 'ERROR'
)
SELECT workload FROM bad_logs
then a second
query — the two signals are
separate tables and cannot be joined
in one statement. Correlate the results yourself, or use
, which
combines signals in one call.
More patterns: references/recipes.md.
Rules
- Try first. Use SQL only for joins, CTEs, window functions, or arithmetic
across aggregates.
- Call for the window before writing the query.
- before for anything non-trivial, and read the
it returns.
- / only, matching . Never a database-qualified name.
- Catalog labels, never storage columns: not , not ,
not , not .
- (milliseconds) on traces. is rejected — do not carry a
nanosecond threshold over from .
- is valid on both signals even though discovery does not list it.
- Single quotes for string values — SQL, not the mini-language.
- Attribute keys are never validated. All-empty column = wrong key, not missing data.
- An empty result set is an answer. Check the window before rewriting the query.
- Aggregate in SQL; never paginate rows to compute a total. 1000-row cap.
- Logs and traces cannot be joined in one statement — they are different tables.
- If the call is refused for a scoped role, switch to / .