Trails Query

Trails Query runs a SQL SELECT over your organization’s events. Use it for questions the lookup filters don’t answer, such as counts per day, per caller or per error.

Queries are asynchronous. Starting a query queues it and returns its ID. You then poll the query until it finishes and read its results, which are kept for 7 days.

StartQuery, GetQueryResults, CancelQuery and ListQueries are recorded in Trails. GetQuery and GetQuerySchema are not.

Run a query

ezgh trails query run "SELECT event_name, count() AS n FROM trails GROUP BY event_name ORDER BY n DESC" --from 30d

ezgh trails query run "SELECT event_time, principal_id FROM trails WHERE error_code = 'access_denied'" --async
ezgh trails query get 0199a3e2-7c1d-7a4b-9e2f-1b3c4d5e6f70 --wait
ezgh trails query results 0199a3e2-7c1d-7a4b-9e2f-1b3c4d5e6f70 --all
ezgh trails query results 0199a3e2-7c1d-7a4b-9e2f-1b3c4d5e6f70 --download csv > refused.csv

The time window

Every query runs over a window of events: from is inclusive and to is exclusive (RFC 3339).

• With neither, the window is the last 7 days.
• With only from, it ends now. With only to, it starts 7 days before to.
• The window can be at most 90 days, and from must be before to.

The window applies to every use of trails in the query. A WHERE condition on event_time can narrow it but can’t widen it.

The trails table

Queries read one table, trails, with one row per event in your organization. Its columns follow the event format. GetQuerySchema returns each column’s exact type.

In the JSON columns, a key that was null or missing in the event reads as NULL.

SQL

A query is one SELECT statement of at most 16 KiB. You can use:

• WITH (common table expressions), UNION ALL, and subqueries in FROM, IN (SELECT …), EXISTS and as scalar values.
• JOIN (inner, LEFT, RIGHT, FULL and CROSS, with ON or USING) between trails and your own CTEs.
• DISTINCT, WHERE, GROUP BY (including GROUP BY ALL), HAVING, ORDER BY, LIMIT and OFFSET, and window functions with OVER and named WINDOWs.
• SELECT * and t.*.
• Arithmetic, comparison, AND, OR, NOT, ||, LIKE, ILIKE, IN (…), BETWEEN, IS [NOT] NULL, CASE, INTERVAL n SECOND|MINUTE|HOUR|DAY|WEEK|MONTH|QUARTER|YEAR, substring, trim, floor and ceil.
• Array and JSON access: resources[1], user_identity.userName, and lambdas as the first argument of array functions: arrayExists(r -> r.type = 'Bot', resources).
• The functions that GetQuerySchema lists: aggregates (count, countIf, uniq, sum, avg, min, max, argMax, quantile, topK, groupArray, …), window functions (row_number, rank, lagInFrame, …), and time, string, conditional, JSON, IP address, array, conversion and math functions. Function names are case-insensitive.

Write strings in single quotes. Names use letters, digits and _, up to 128 characters. Comments are allowed.

These are refused with 400 invalid_query: more than one statement, any table other than trails and your CTEs, table functions, functions not in the schema, CAST (use a conversion function such as toString, toInt64 or toDateTime), SETTINGS, FORMAT, FINAL, SAMPLE, PREWHERE, QUALIFY, SELECT … INTO, WITH RECURSIVE, UNION without ALL, DISTINCT ON, ORDER BY ALL, WITH FILL, LIMIT … BY, NATURAL and GLOBAL joins, * EXCEPT and other * modifiers, query parameters, and double-quoted strings.

topK, groupArray and groupUniqArray collect at most 1,000 values per group.

Examples

Who changed IAM, newest first:

SELECT
  event_time,
  toString(user_identity.userName) AS changed_by,
  event_name,
  arrayStringConcat(resources.resource_name, ', ') AS resources,
  error_code
FROM trails
WHERE event_source = 'iam.ezghcloud.com' AND NOT read_only
ORDER BY event_time DESC
LIMIT 100

Refused requests per API key per day:

SELECT
  toDate(event_time) AS day,
  access_key_id,
  count() AS refused
FROM trails
WHERE error_code = 'access_denied' AND access_key_id IS NOT NULL
GROUP BY day, access_key_id
ORDER BY day DESC, refused DESC

Events that touched one resource:

SELECT event_time, event_name, principal_id, error_code
FROM trails
WHERE has(resources.resource_name, 'ezgh::org_k3f9a0x2m7qp:prj_g7h8i9j0k1l2')
ORDER BY event_time DESC

Query status

GetQuery returns the query:

{
  "id": "0199a3e2-7c1d-7a4b-9e2f-1b3c4d5e6f70",
  "sql": "SELECT event_name, count() AS n FROM trails GROUP BY event_name ORDER BY n DESC",
  "status": "succeeded",
  "from": "2026-09-01T00:00:00.000Z",
  "to": "2026-09-29T00:00:00.000Z",
  "error": null,
  "stats": { "bytesScanned": "5242880", "rowsScanned": 48210, "elapsedMs": 312, "resultRows": 27 },
  "resultRows": 27,
  "truncated": false,
  "cancelRequested": false,
  "createdBy": "jtMJXwL0R8aB3cD4eF5gH6iJ",
  "createdByIdentity": {
    "type": "User",
    "principalId": "jtMJXwL0R8aB3cD4eF5gH6iJ",
    "userName": "ada@example.com",
    "isRoot": false,
    "accessKeyId": null,
    "sessionContext": { "credential": "session", "sessionIssuedAt": "2026-09-29T08:00:00.000Z", "ssoProviderId": null, "mfaAuthenticated": false, "clientId": null },
    "invokedBy": null
  },
  "createdAt": "2026-09-29T09:14:03.201Z",
  "startedAt": "2026-09-29T09:14:03.410Z",
  "finishedAt": "2026-09-29T09:14:03.839Z"
}

A failed query’s error.code is one of:

Results

GetQueryResults returns a page of a succeeded query’s rows:

{
  "columns": [
    { "name": "event_name", "type": "LowCardinality(String)" },
    { "name": "n", "type": "UInt64" }
  ],
  "rows": [
    ["LookupEvents", "1840"],
    ["CreateApiKey", "12"]
  ],
  "nextCursor": "eyJ2IjoxLCJvcmciOiIwMTk5YTNjMi01YjFlLTdkNDAtOWYzYS0yYzhlNmIxZDRmNzAifQ.c2ln",
  "totalRows": 27,
  "truncated": false
}

• Each row is an array of values in the order of columns.
• 64-bit and wider integers are JSON strings, so no value is rounded (count() is "1840"). Other numbers are JSON numbers; NaN and infinities are null. Dates and times are strings. JSON columns are objects.
• limit is 1 to 1,000 rows (default 100). A page also stops at 4 MiB, and always has at least one row. Pass nextCursor as cursor for the next page; it’s null on the last page.
• With Accept: text/csv or Accept: application/x-ndjson, the response streams every stored row as a file download. CSV has a header row, empty fields for nulls, and arrays and objects as JSON. NDJSON has one JSON object per row, keyed by column name.

Limits

The hourly allowances reset on the hour (UTC).

Errors

A 400 invalid_query looks like this:

{
  "error": {
    "code": "invalid_query",
    "message": "CAST isn't supported; use a conversion function such as toString, toInt64 or toDateTime",
    "line": 1,
    "column": 13
  }
}

CancelQuery answers 200 with the query when a queued query is cancelled, and 202 with the query when a running query has been asked to stop; poll it until its status is cancelled.