SQL Query Guide

CtrlB stores logs and traces in its Lake and queries them with SQL. The Lake runs an SQL dialect plus a few CtrlB functions, such as histogram(). The same SQL works in the Logs/Traces Explorer, in the search_logs MCP tool, in dashboard panels, and in custom SQL alerts. Custom SQL alerts support a smaller set of syntax, and the compatibility cheat sheet shows what works where.

This guide covers:

1. Query basics
2. Filtering
3. Time bucketing
4. Aggregate functions
5. String, regex, and hashing functions
6. Advanced SQL: CTEs, subqueries, window functions
7. Common observability queries
8. Common security queries
9. Writing SQL for alerts and dashboards
10. Compatibility cheat sheet
11. MCP search_logs blocklist
12. Troubleshooting

Column names

Column names differ for every dataset. Before you write a query, run get_dataset_attributes (MCP) or open the Attributes panel in the Explorer to see the exact field names and types. In the examples below, fields such as client_ip, user_name, status_code, or http_url are placeholders. Replace them with the names from your schema.

Some number-like fields are stored as text, such as http_status_code in OpenTelemetry traces. Wrap them in TRY_CAST(col AS BIGINT) before comparing them to numbers.

---

Query basics

SELECT <columns | aggregates>
FROM <dataset_name>
[WHERE <conditions>]
[GROUP BY <columns>]
[HAVING <aggregate condition>]
[ORDER BY <column> [ASC|DESC]]
[LIMIT n [OFFSET m]]

• The table name is the dataset name. Dataset names can contain letters, digits, _, ., and -. Wrap names that contain - in double quotes: FROM "my-logs". Backticks don't work.
• The time range is applied for you. The Explorer time picker and MCP's start_time / end_time set the time range, so don't add _timestamp filters to your SQL.
• _timestamp is an Int64 in microseconds since the epoch. To use it with date functions, convert it first: to_timestamp_micros(_timestamp). histogram() accepts it directly.
• Always add a LIMIT to raw-row queries. Without one, the Explorer returns 250 rows and MCP returns 20.
• Paginate with LIMIT n OFFSET m.
• Read nested fields with brackets: httprequest['clientip'].
• Reuse SELECT aliases in HAVING. Write COUNT(*) AS failures ... HAVING failures > 20 instead of repeating the aggregate. Aliases don't work in WHERE, which runs before SELECT.
• NULL columns are dropped from JSON results. If an aggregate such as AVG(x) is missing from a row, every input value was NULL.

-- The 50 most recent errors
SELECT * FROM app_logs
WHERE severity_text = 'ERROR'
ORDER BY _timestamp DESC
LIMIT 50

---

Filtering

These are the SQL operators behind the Explorer's filter builder:

Explorer operator	SQL generated
= / !=	col = 'v' / col != 'v' (numbers unquoted, booleans use IS true)
>, >=, <, <=	Numeric fields only
IN / NOT IN	col IN ('a','b') / col IN (1,2,3)
CONTAINS / NOT CONTAINS	col LIKE '%v%' / col NOT LIKE '%v%' (case-sensitive)
CONTAINS IGNORE CASE	col ILIKE '%v%'
LIKE / NOT LIKE	col LIKE 'pattern'
REGEX / NREGEX	col ~ 'pattern' / col !~ 'pattern'
IS NULL / IS NOT NULL	col IS NULL / col IS NOT NULL

You can also use BETWEEN a AND b, starts_with(col, 'x'), ends_with(col, 'x'), contains(col, 'x'), and strpos(col, 'x') > 0.

LIKE and ILIKE

% matches any run of characters and _ matches exactly one character. To match a literal % or _, escape it with a backslash: LIKE '%50\%%'. Prefer ILIKE over lower(col) LIKE .... It's simpler, and MCP blocks LOWER(.

SELECT * FROM app_logs
WHERE status_code >= 500
  AND (body ILIKE '%timeout%' OR body ILIKE '%connection refused%')
LIMIT 100

Regular expressions

Function	Returns	Notes
re_match(col, 'pattern')	boolean	CtrlB function. Negate it with NOT re_match(...)
regexp_like(col, 'pattern'[, 'i'])	boolean	Pass 'i' for case-insensitive matching
regexp_count(col, 'pattern')	integer	Number of matches
regexp_match(col, 'pattern')	array of capture groups	Index with (regexp_match(...))[1] (not in alerts)
regexp_replace(col, 'pattern', 'repl', 'g')	string	'g' replaces every match

Regex is much slower than LIKE. When you can, narrow the rows with a cheap LIKE/ILIKE or equality filter first, and run the regex on what's left.

---

Time bucketing

Function	Example	Works on
histogram(_timestamp, 'N unit')	histogram(_timestamp, '5 minutes')	Everywhere. CtrlB's built-in bucketing, used by every chart and alert
date_bin('interval', ts)	date_bin('15 minutes', to_timestamp_micros(_timestamp))	Everywhere (quote the interval as a string)
date_trunc('unit', ts)	date_trunc('hour', to_timestamp_micros(_timestamp))	Everywhere
date_part('unit', ts)	date_part('hour', to_timestamp_micros(_timestamp))	Everywhere. Useful for hour-of-day and day-of-week analysis
now() - INTERVAL 1 HOUR	to_timestamp_micros(_timestamp) > now() - INTERVAL 1 HOUR	Everywhere

-- Error volume in 5-minute buckets
SELECT histogram(_timestamp, '5 minutes') AS bucket,
       COUNT(*) AS errors
FROM app_logs
WHERE severity_text = 'ERROR'
GROUP BY bucket
ORDER BY bucket

---

Aggregate functions

Used by CtrlB today

CtrlB's own charts, APM views, and alerts use these functions:

Function	Purpose
COUNT(*), COUNT(col), COUNT(DISTINCT col)	Volume and unique counts
SUM(col), AVG(col), MIN(col), MAX(col)	Basic metrics, and the operators offered in the alert builder
approx_percentile_cont(col, p)	P50/P90/P99 latency (t-digest approximation)
SUM(CASE WHEN <cond> THEN 1 ELSE 0 END)	Conditional counts, such as error counts
NULLIF(x, 0)	Prevents division by zero in rates
COALESCE(a, b)	Fallback values, such as COALESCE(NULLIF(peer_service,''), net_peer_name)

-- APM-style latency and error rate per operation (traces dataset)
SELECT operation_name,
       COUNT(*) AS requests,
       approx_percentile_cont(duration, 0.5)  AS p50,
       approx_percentile_cont(duration, 0.99) AS p99,
       SUM(CASE WHEN status_code = 2 OR span_status = 'ERROR' THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS error_rate_pct
FROM traces
WHERE span_kind = '2'
GROUP BY operation_name
ORDER BY p99 DESC
LIMIT 20

Worth adding to your toolkit

Function	Why it's useful
approx_distinct(col)	Fast unique count (HyperLogLog), such as unique IPs or users. Much cheaper than COUNT(DISTINCT) on large windows
median(col) / approx_median(col)	Typical value without outlier skew
stddev(col), var(col)	Spread. Use with AVG to flag anomalies (value > avg + 3*stddev)
string_agg(DISTINCT col, ',')	Lists the distinct values in a group, such as every user an IP tried
array_agg(DISTINCT col)	Same as string_agg, but returns an array
bool_or(cond) / bool_and(cond)	Whether any (or every) row in the group matched, such as "did this IP ever succeed?"
first_value(col ORDER BY ts) / last_value(...)	First or last event in a group (not in alerts)
corr(x, y)	Correlation, such as between latency and payload size
COUNT(*) FILTER (WHERE cond)	Cleaner conditional counts (not in alerts; use CASE WHEN there)
MIN(_timestamp) / MAX(_timestamp)	First-seen and last-seen times per entity, in microseconds

---

String, regex, and hashing functions

Function	Example use
split_part(str, delim, n)	Strip a query string: split_part(http_url, '?', 1). Get the first octet of an IP: split_part(client_ip, '.', 1)
strpos(str, sub)	Position of a substring (0 if absent)
substr(str, start, len), left(str, n), right(str, n)	Truncate long messages for grouping
length(str)	Find oversized URLs or payloads
concat(a, b, ...), a || b	Build composite keys
replace(str, from, to), regexp_replace(...)	Normalize messages, such as replacing numbers with N
coalesce(a, 'default')	Label missing values
md5(str)	Hex digest. Useful for sampling: substr(md5(trace_id), 1, 8) < '20000000' keeps about 12.5% of traces
encode(sha256(str), 'hex')	Hex SHA-256 (sha256 returns binary, so wrap it in encode)
TRY_CAST(x AS BIGINT)	Safe type conversion. Returns NULL instead of an error

Normalizing log messages into patterns groups thousands of near-identical lines into a few rows:

SELECT regexp_replace(body, '[0-9]+', 'N', 'g') AS pattern,
       COUNT(*) AS occurrences
FROM app_logs
WHERE severity_text = 'ERROR'
GROUP BY pattern
ORDER BY occurrences DESC
LIMIT 20

---

Advanced SQL

Not in custom SQL alerts

The features in this section work in the Explorer, the SQL API, MCP search_logs, dashboard panels, and dashboard variable queries. Custom SQL alerts reject them.

CTEs (WITH)

WITH per_ip AS (
  SELECT client_ip, COUNT(*) AS failures
  FROM auth_logs
  WHERE outcome = 'failure'
  GROUP BY client_ip
)
SELECT * FROM per_ip WHERE failures > 50 ORDER BY failures DESC

Subqueries

You can use a subquery in WHERE ... IN (...), in FROM (...), as a scalar in SELECT, or in HAVING. EXISTS / NOT EXISTS work only when the subquery references the outer query (a correlated subquery).

-- Every event for IPs that had a failed login
SELECT * FROM auth_logs
WHERE client_ip IN (SELECT client_ip FROM auth_logs WHERE outcome = 'failure')
ORDER BY _timestamp
LIMIT 200

Window functions

row_number(), rank(), dense_rank(), ntile(n), percent_rank(), cume_dist(), lag(col, n), lead(col, n), first_value, last_value, and nth_value all take OVER (PARTITION BY ... ORDER BY ... [ROWS BETWEEN ...]).

-- Time between consecutive requests per user (useful for bot detection)
SELECT user_name, _timestamp,
       _timestamp - lag(_timestamp) OVER (PARTITION BY user_name ORDER BY _timestamp) AS gap_micros
FROM api_logs
LIMIT 500

Set operations and other extras

• UNION ALL, UNION, INTERSECT, and EXCEPT combine result sets, such as several summary rows in one response.
• SELECT DISTINCT ON (key) ... keeps one row per key. Your ORDER BY must start with the same key.
• ORDER BY col DESC NULLS LAST controls where NULLs sort.
• GROUP BY 1, 2 groups by column position.

---

Common observability queries

Every query below also works in custom SQL alerts unless it's marked (not in alerts). To use one as an alert, add a threshold with HAVING (see Custom SQL alerts).

Error rate over time

SELECT histogram(_timestamp, '5 minutes') AS bucket,
       SUM(CASE WHEN severity_text IN ('ERROR','FATAL') THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS error_pct
FROM app_logs
GROUP BY bucket
ORDER BY bucket

Noisiest services or loggers

SELECT service_name, COUNT(*) AS lines
FROM app_logs
GROUP BY service_name
ORDER BY lines DESC
LIMIT 10

Slowest endpoints, query strings stripped

SELECT split_part(http_url, '?', 1) AS path,
       COUNT(*) AS hits,
       approx_percentile_cont(duration, 0.95) AS p95
FROM traces
GROUP BY path
HAVING hits > 20
ORDER BY p95 DESC
LIMIT 20

HTTP status breakdown per minute

SELECT histogram(_timestamp, '1 minute') AS bucket,
       SUM(CASE WHEN status_code BETWEEN 200 AND 399 THEN 1 ELSE 0 END) AS ok,
       SUM(CASE WHEN status_code BETWEEN 400 AND 499 THEN 1 ELSE 0 END) AS client_err,
       SUM(CASE WHEN status_code >= 500 THEN 1 ELSE 0 END) AS server_err
FROM access_logs
GROUP BY bucket
ORDER BY bucket

Error spans in one trace

SELECT * FROM traces
WHERE trace_id = '<trace_id>' AND (status_code = 2 OR span_status = 'ERROR')
ORDER BY start_time

Latency outliers, more than 3σ above the operation mean (not in alerts)

WITH stats AS (
  SELECT operation_name, AVG(duration) AS mu, stddev(duration) AS sigma
  FROM traces GROUP BY operation_name
)
SELECT t.operation_name, t.trace_id, t.duration
FROM traces t JOIN stats s ON t.operation_name = s.operation_name
WHERE t.duration > s.mu + 3 * s.sigma
ORDER BY t.duration DESC
LIMIT 50

Pod restart and OOM signals

SELECT kubernetes_pod_name, COUNT(*) AS hits
FROM k8s_logs
WHERE body ILIKE '%OOMKilled%' OR body ILIKE '%Back-off restarting%' OR body ILIKE '%CrashLoopBackOff%'
GROUP BY kubernetes_pod_name
ORDER BY hits DESC
LIMIT 20

---

Common security queries

These queries cover common detection and threat-hunting patterns. Replace the placeholder columns with your auth, access, or audit log fields.

Brute force: failed logins per source IP

SELECT client_ip,
       COUNT(*) AS failures,
       approx_distinct(user_name) AS users_tried,
       MIN(_timestamp) AS first_seen,
       MAX(_timestamp) AS last_seen
FROM auth_logs
WHERE outcome = 'failure'
GROUP BY client_ip
HAVING failures > 20
ORDER BY failures DESC
LIMIT 50

Password spraying: one IP trying many accounts

SELECT client_ip,
       COUNT(DISTINCT user_name) AS distinct_users,
       string_agg(DISTINCT user_name, ',') AS users
FROM auth_logs
WHERE outcome = 'failure'
GROUP BY client_ip
HAVING distinct_users >= 10
ORDER BY distinct_users DESC

Repeated failures and a successful login from the same IP and user

SELECT client_ip, user_name,
       SUM(CASE WHEN outcome = 'failure' THEN 1 ELSE 0 END) AS failures,
       bool_or(outcome = 'success') AS eventually_succeeded
FROM auth_logs
GROUP BY client_ip, user_name
HAVING failures >= 5
   AND eventually_succeeded

Web attack signatures: SQLi, traversal, and probes in URLs

SELECT client_ip, http_url, status_code, user_agent
FROM access_logs
WHERE http_url ILIKE '%union%select%'
   OR http_url ILIKE '%../%'
   OR http_url ILIKE '%/etc/passwd%'
   OR http_url ILIKE '%<script%'
   OR http_url ILIKE '%/.env%'
   OR http_url ILIKE '%/wp-admin%'
LIMIT 200

Scanners: many 404s from one IP

SELECT client_ip,
       COUNT(*) AS not_found,
       COUNT(DISTINCT split_part(http_url, '?', 1)) AS distinct_paths
FROM access_logs
WHERE status_code = 404
GROUP BY client_ip
HAVING not_found > 100
ORDER BY not_found DESC

Suspicious or scripted user agents

SELECT user_agent, COUNT(*) AS requests, approx_distinct(client_ip) AS ips
FROM access_logs
WHERE user_agent ILIKE '%sqlmap%' OR user_agent ILIKE '%nikto%' OR user_agent ILIKE '%nmap%'
   OR user_agent ILIKE '%curl%'   OR user_agent ILIKE '%python-requests%' OR user_agent = ''
GROUP BY user_agent
ORDER BY requests DESC

Privilege and role changes

SELECT _timestamp, actor, email, oldrole, newrole
FROM audit_logs
WHERE newrole IS NOT NULL AND (oldrole IS NULL OR newrole != oldrole)
ORDER BY _timestamp DESC
LIMIT 100

Off-hours activity, UTC hour of day

SELECT user_name,
       date_part('hour', to_timestamp_micros(_timestamp)) AS hour_utc,
       COUNT(*) AS events
FROM audit_logs
WHERE date_part('hour', to_timestamp_micros(_timestamp)) NOT BETWEEN 6 AND 20
GROUP BY user_name, hour_utc
ORDER BY events DESC

Private versus external sources

SELECT CASE WHEN client_ip LIKE '10.%' OR client_ip LIKE '192.168.%'
                 OR (client_ip LIKE '172.%' AND TRY_CAST(split_part(client_ip, '.', 2) AS BIGINT) BETWEEN 16 AND 31)
            THEN 'internal' ELSE 'external' END AS origin,
       COUNT(*) AS requests
FROM access_logs
GROUP BY origin

Possible data exfiltration: large responses per user

SELECT user_name, SUM(response_bytes) AS total_bytes, COUNT(*) AS requests
FROM access_logs
GROUP BY user_name
ORDER BY total_bytes DESC
LIMIT 20

Secrets leaking into logs (not in alerts)

SELECT _timestamp, service_name, left(body, 200) AS snippet
FROM app_logs
WHERE (body LIKE '%AKIA%' OR body LIKE '%PRIVATE KEY-----%' OR body LIKE '%password=%')
  AND regexp_like(body, '(AKIA[0-9A-Z]{16}|-----BEGIN [A-Z ]*PRIVATE KEY-----|password=[^&\s]+)')
LIMIT 50

Account takeover: same user from many IPs

SELECT user_name,
       COUNT(DISTINCT client_ip) AS ips,
       string_agg(DISTINCT client_ip, ',') AS ip_list
FROM auth_logs
WHERE outcome = 'success'
GROUP BY user_name
HAVING ips >= 3
ORDER BY ips DESC

Burst detection: top 3 busiest minutes per IP (not in alerts)

WITH per_min AS (
  SELECT client_ip, histogram(_timestamp, '1 minute') AS minute, COUNT(*) AS c
  FROM access_logs GROUP BY client_ip, minute
  HAVING c > 300
)
SELECT * FROM (
  SELECT *, row_number() OVER (PARTITION BY client_ip ORDER BY c DESC) AS rn FROM per_min
) ranked
WHERE rn <= 3
ORDER BY c DESC

---

Writing SQL for alerts and dashboards

Custom SQL alerts

• The alert fires whenever your query returns at least one row. Put the threshold inside the query, usually in HAVING.
• Alias the value you're watching as ctrlb_agg_value so the rule preview can chart it.
• Query only one dataset, and it must match the dataset selected on the alert condition.
• Don't add your own time bucket or LIMIT.
• Use syntax from the Works everywhere column of the cheat sheet.

-- Fires when one IP has more than 20 failed logins in the window
SELECT client_ip, COUNT(*) AS ctrlb_agg_value
FROM auth_logs
WHERE outcome = 'failure'
GROUP BY client_ip
HAVING ctrlb_agg_value > 20

-- Fires when more than 5% of requests are 5xx
SELECT SUM(CASE WHEN status_code >= 500 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS ctrlb_agg_value
FROM access_logs
HAVING ctrlb_agg_value > 5

Dashboard panels

• Panel queries follow the same rules as the Explorer, including CTEs, subqueries, and window functions.
• Time-series panels: return histogram(_timestamp, '<interval>') AS <name> and GROUP BY and ORDER BY that column.
• Variable queries (the dropdown values) use the same syntax.

---

Compatibility cheat sheet

Each row below was tested on CtrlB.

You want	Works everywhere	Not in alerts	Avoid
Dataset name with -	—	FROM "my-logs"	FROM `my-logs`
Case-insensitive match	col ILIKE '%x%'	—	LOWER(col) LIKE (blocked by MCP)
Regex	re_match(col,'p'), regexp_like(col,'p','i')	(regexp_match(col,'(p)'))[1]	REGEXP, RLIKE, SIMILAR TO, ~
Negated regex	NOT re_match(col,'p')	—	—
Cast	TRY_CAST(x AS BIGINT), arrow_cast(x,'Int64')	CAST(x AS BIGINT), x::BIGINT	CAST(x AS SIGNED) (rejected by the Lake)
Compare a text field as a number	TRY_CAST(col AS BIGINT) >= 500	—	col >= 500 on a text field
Time bucket	histogram(_timestamp,'5 minutes'), date_bin('5 minutes', ts), date_trunc('hour', ts)	date_bin(INTERVAL '5 minutes', ts)	Filtering _timestamp yourself
Hour or day part	date_part('hour', ts)	—	EXTRACT(hour FROM ts)
Relative time	now() - INTERVAL 1 HOUR	now() - INTERVAL '1 hour'	—
Conditional count	SUM(CASE WHEN c THEN 1 ELSE 0 END)	COUNT(*) FILTER (WHERE c)	—
Percentile	approx_percentile_cont(col, 0.99)	approx_percentile_cont(0.99) WITHIN GROUP (ORDER BY col)	—
Unique count	approx_distinct(col), COUNT(DISTINCT col)	—	—
Ordered aggregate	—	first_value(col ORDER BY ts)	—
Multiple steps	—	WITH ..., subqueries, JOIN, UNION ALL	—
Ranking	ORDER BY ... LIMIT n	rank() OVER (...), row_number() OVER (...)	—
NULL ordering	—	ORDER BY c DESC NULLS LAST	—
SHA-256	encode(sha256(x), 'hex')	—	length(sha256(x))

ts stands for to_timestamp_micros(_timestamp).

---

MCP search_logs blocklist

search_logs rejects any query containing these, wherever they appear (including inside string literals):

Blocked	Use instead
LOWER(	ILIKE for case-insensitive matching
REGEXP, RLIKE, SIMILAR TO	regexp_like(col, 'pattern') or re_match(col, 'pattern')
The ~ character (including ~* and !~)	regexp_like(col, 'pattern', 'i')

The Explorer's REGEX filter writes col ~ 'pattern'. If you copy that SQL into MCP, change it to regexp_like(col, 'pattern') first.

---

Troubleshooting

Error	Cause	Fix
only plain ILIKE/LIKE is supported; remove LOWER() or regex operators	The query hit the MCP blocklist	Use ILIKE, regexp_like(), or re_match()
invalid SQL syntax: syntax error at position N	The alert uses syntax that custom SQL alerts don't support	See the cheat sheet
subqueries not allowed / multiple tables not allowed (no JOINs) / UNION operations are not allowed	Custom SQL alert	Rewrite it as a single-table query, or run it from the Explorer or MCP
query range too long	The MCP window is longer than allowed (24 hours by default)	Split the range into smaller calls, or use get_histogram for long-range volume
query too long	The query is longer than 5,000 characters	Simplify, or move repeated filters into a CTE
Incorrect query	The Lake couldn't parse the query, for example because a name is quoted with backticks	Use double quotes for names, and check the cheat sheet
field not found in dataset	The column doesn't exist in this dataset	Check field names with get_dataset_attributes
dataset not found	The dataset name is wrong	Check the name with list_datasets
A column is missing from the results	Every value was NULL	Wrap it in coalesce(col, ...), or check the field name