SQL Query Language

KubeSense lets you query your logs directly with SQL. SQL mode is powered by ClickHouse, so you get the full expressive power of ClickHouse SQL — aggregations, window functions, string and date functions, CTEs, and subqueries — over your log data.

Open the Logs page and select the SQL tab above the search bar. Write a SELECT statement, hit Run, and visualize the result as a list, time series, bar chart, top list, or pie chart.

SELECT workload, count(*) AS cnt
FROM logs
WHERE $__timeFilter(timestamp)
GROUP BY workload
ORDER BY cnt DESC

info: Queries are read-only. Only SELECT statements are accepted — inserts, updates, and DDL are rejected.

Traces can be queried the same way — see SQL for Traces.

The `logs` Table

All log data lives in a single table named logs. Use it directly — no database prefix is needed (or allowed).

SELECT * FROM logs WHERE $__timeFilter(timestamp) LIMIT 100

SELECT * expands to all 14 catalog columns except the raw attribute maps (string_attributes, float_attributes). Use @attr syntax to access those.

Time Range and Clusters

Queries are automatically scoped to the cluster(s) selected in the UI — you never need to filter by cluster yourself, and you cannot query clusters outside your selection.

The time range picker is applied through the $__timeFilter macro. Include it in your WHERE clause to scope the query to the selected time window:

SELECT * FROM logs WHERE $__timeFilter(timestamp) LIMIT 100

warn: If you omit $__timeFilter, your query scans all data regardless of the time picker. Always include it unless you intentionally want an unbounded scan.

Macros

Macro	Meaning
`$__timeFilter(timestamp)`	Expands to `timestamp >= <from> AND timestamp < <to>` using the UI time range
`$__fromTime`	The start of the selected time range, as a scalar — usable in any expression
`$__toTime`	The end of the selected time range, as a scalar
`$__clusters`	The selected cluster filter (`cluster IN (...)`). Optional — cluster scoping is enforced automatically either way

-- Scalar macros in expressions
SELECT workload, dateDiff('second', $__fromTime, timestamp) AS age_s
FROM logs
WHERE $__timeFilter(timestamp)
LIMIT 100

Log Columns

Column	Description
`timestamp`	Log event time (UTC)
`body`	Raw log message text
`type`	Log severity: `'ERROR'`, `'WARN'`, `'INFO'`, `'DEBUG'`, `'TRACE'`, `'FATAL'`, `'PANIC'` — comparisons are case-sensitive
`cluster`	Kubernetes cluster name
`namespace`	Kubernetes namespace
`workload`	Workload name (Deployment, StatefulSet, etc.)
`instance`	Pod the log came from
`container`	Container name
`node`	Kubernetes node name
`source`	Log source identifier
`format`	Detected log format: `'json'`, `'klog'`, `'nginx'`
`region`	Region
`app_version`	Application version
`env_type`	Environment type

Any field not in this list — including everything parsed from structured logs — is available through @attribute syntax.

Dynamic Attributes (`@attr`)

Logs carry dynamic key-value attributes — fields parsed from structured logs, OpenTelemetry attributes, and pipeline-extracted values. Access any of them with the @ prefix:

SELECT @http.status_code, @http.method, workload
FROM logs
WHERE $__timeFilter(timestamp) AND @http.status_code = '500'
LIMIT 100

Dot notation is supported (@http.status_code, @k8s.pod.name).
A missing attribute returns NULL — use IS NOT NULL or coalesce(@field, '') if needed.
String attributes come from string_attributes; numeric attributes come from float_attributes. @attr checks both automatically. Use the result as a string unless you cast:

SELECT workload, toFloat64OrNull(@response_time_ms) AS latency_ms
FROM logs
WHERE $__timeFilter(timestamp)
  AND toFloat64OrNull(@response_time_ms) > 500
ORDER BY latency_ms DESC

info: Attributes named from_time, to_time, or clusters cannot be accessed via @ — these names are reserved internally.

Direct attribute map access

When you need map-level operations (checking if a key exists, listing all keys, using attribute values in array functions), query string_attributes and float_attributes directly:

-- Check if an attribute key exists
SELECT workload, body
FROM logs
WHERE $__timeFilter(timestamp)
  AND mapContains(string_attributes, 'http.url')
LIMIT 100

-- Get a string attribute value directly
SELECT string_attributes['http.url'] AS url, count(*) AS hits
FROM logs
WHERE $__timeFilter(timestamp) AND type = 'ERROR'
GROUP BY url
ORDER BY hits DESC
LIMIT 25

-- Get a numeric attribute value directly
SELECT float_attributes['response_time_ms'] AS latency
FROM logs
WHERE $__timeFilter(timestamp)
  AND float_attributes['response_time_ms'] > 1000
ORDER BY latency DESC
LIMIT 50

-- Find the most common attribute keys
SELECT arrayJoin(mapKeys(string_attributes)) AS attr_key, count() AS cnt
FROM logs
WHERE $__timeFilter(timestamp)
GROUP BY attr_key
ORDER BY cnt DESC
LIMIT 30

Functions

Nearly all ClickHouse functions are supported, including aggregate functions, window functions, and combinators. The most useful ones for log analysis:

Aggregations

Function	Description
`count(*)` / `count(x)`	Row count / non-null count
`sum(x)`, `avg(x)`, `min(x)`, `max(x)`	Basic aggregates
`uniq(x)`	Approximate distinct count (fast)
`uniqExact(x)`	Exact distinct count
`quantile(0.95)(x)`	Percentile (p50, p95, p99, ...)
`topK(10)(x)`	Most frequent values
`countIf(cond)`, `sumIf(x, cond)`, `avgIf(x, cond)`	Conditional aggregates
`groupArray(x)`	Collect values into an array
`argMax(a, b)` / `argMin(a, b)`	Value of `a` at max/min of `b`

Date and Time

Function	Description
`toStartOfMinute(ts)`, `toStartOfHour(ts)`, `toStartOfDay(ts)`	Truncate to time bucket
`toStartOfInterval(ts, INTERVAL 5 MINUTE)`	Arbitrary time buckets — the building block for time series
`dateDiff('second', a, b)`	Difference between timestamps
`now()`	Current time
`formatDateTime(ts, '%Y-%m-%d %H:%M')`	Format a timestamp

Strings

Function	Description
`like(s, '%pattern%')` / `s LIKE '%pattern%'`	Substring pattern match
`ilike(s, '%pattern%')`	Case-insensitive LIKE
`position(s, 'needle')`	Substring position (0 = not found)
`match(s, 'regex')`	Regular expression match
`extract(s, 'regex')`	Extract first regex match
`extractAll(s, 'regex')`	Extract all matches as array
`lower(s)` / `upper(s)`	Case conversion
`splitByChar(',', s)`	Split into array
`replaceRegexpAll(s, 'regex', 'repl')`	Regex replace
`substring(s, start, len)`	Substring (1-indexed)
`length(s)`	String length

JSON

Function	Description
`JSONExtractString(body, 'key')`	Extract a string field from JSON
`JSONExtractFloat(body, 'key')`	Extract a numeric field
`JSONExtractString(body, 'a', 'b')`	Nested extraction
`JSONHas(body, 'key')`	Check if key exists
`isValidJSON(body)`	Check if a string is valid JSON

Type Conversion & Conditionals

Function	Description
`toFloat64OrNull(s)`, `toInt64OrNull(s)`	Safe numeric casts (NULL on failure)
`toString(x)`	Convert to string
`if(cond, then, else)`	Conditional
`multiIf(c1, v1, c2, v2, ..., default)`	Multi-branch conditional
`coalesce(a, b, ...)`	First non-NULL value

Blocked Functions

For security, functions that access external systems or affect server behavior are blocked: sleep, sleepEachRow, file, url, s3, hdfs, mysql, postgresql, sqlite, odbc, jdbc, remote, remoteSecure, cluster, clusterAllReplicas, dictGet variants, executable, and random-data generators. Using one returns an error like function "sleep" is not permitted in SQL queries.

CTEs and Subqueries

Standard WITH clauses and subqueries work as expected:

WITH error_logs AS (
  SELECT workload, namespace
  FROM logs
  WHERE $__timeFilter(timestamp) AND type = 'ERROR'
)
SELECT workload, count(*) AS err_cnt
FROM error_logs
GROUP BY workload
ORDER BY err_cnt DESC

Practical Examples

Latest error logs

SELECT timestamp, workload, instance, body
FROM logs
WHERE $__timeFilter(timestamp) AND type = 'ERROR'
ORDER BY timestamp DESC
LIMIT 50

Error rate per workload

SELECT
  workload,
  count(*) AS total,
  countIf(type = 'ERROR') AS errors,
  round(errors / total, 4) AS error_rate
FROM logs
WHERE $__timeFilter(timestamp)
GROUP BY workload
HAVING total > 100
ORDER BY error_rate DESC

Log volume over time (time series)

SELECT
  toStartOfInterval(timestamp, INTERVAL 5 MINUTE) AS t,
  type,
  count(*) AS cnt
FROM logs
WHERE $__timeFilter(timestamp)
GROUP BY t, type
ORDER BY t

Visualize this with Time Series to see log volume trends by severity.

Log volume spike detection

SELECT
  toStartOfInterval(timestamp, INTERVAL 1 MINUTE) AS t,
  count(*) AS cnt,
  countIf(type IN ('ERROR', 'FATAL', 'PANIC')) AS errors
FROM logs
WHERE $__timeFilter(timestamp)
GROUP BY t
ORDER BY cnt DESC
LIMIT 20

Extract and aggregate a field from JSON logs

SELECT
  JSONExtractString(body, 'url') AS url,
  count(*) AS hits,
  avg(JSONExtractFloat(body, 'duration')) AS avg_dur
FROM logs
WHERE $__timeFilter(timestamp) AND isValidJSON(body)
GROUP BY url
HAVING url != ''
ORDER BY hits DESC
LIMIT 25

Slow requests via attributes

SELECT timestamp, workload, @http.method, @http.url,
       toFloat64OrNull(@response_time_ms) AS latency_ms
FROM logs
WHERE $__timeFilter(timestamp)
  AND toFloat64OrNull(@response_time_ms) > 1000
ORDER BY latency_ms DESC
LIMIT 50

Most common attribute keys

SELECT arrayJoin(mapKeys(string_attributes)) AS attr_key, count() AS cnt
FROM logs
WHERE $__timeFilter(timestamp) AND type = 'ERROR'
GROUP BY attr_key
ORDER BY cnt DESC
LIMIT 30

Logs matching a regex pattern

SELECT timestamp, workload, body
FROM logs
WHERE $__timeFilter(timestamp)
  AND match(body, 'OOMKilled|out of memory|memory limit exceeded')
ORDER BY timestamp DESC
LIMIT 100

Noisiest namespaces

SELECT namespace, count(*) AS cnt
FROM logs
WHERE $__timeFilter(timestamp)
GROUP BY namespace
ORDER BY cnt DESC
LIMIT 20

Unique pods logging errors

SELECT workload, uniqExact(instance) AS error_pods, count(*) AS errors
FROM logs
WHERE $__timeFilter(timestamp) AND type = 'ERROR'
GROUP BY workload
ORDER BY error_pods DESC
LIMIT 20

Limits and Behavior

Default row limit: 1000. Add an explicit LIMIT N to return more (or fewer) rows.
Read-only. Only SELECT queries run; the underlying connection is read-only, so mutations are impossible.
Cluster scoping is enforced. Queries can only see data from clusters you have selected and are authorized for.
Timeouts. Very expensive queries are cancelled — narrow the time range or aggregate instead of scanning raw rows.
type values are case-sensitive. Use uppercase: type = 'ERROR', not type = 'error'.

Querying via API

The same SQL can be executed programmatically against the KubeSense API — see API Access for authentication and the /api/logs/sql/execute endpoint.

SQL Query Language

ON THIS PAGE