---
title: "Trails Query"
description: "Run SQL over your organization's Trails events, asynchronously, and read or download the results."
---

> Documentation Index
> Fetch the complete documentation index at: https://docs.ezghcloud.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Trails Query

Trails Query runs a SQL `SELECT` over your organization's events. Use it for questions the
[lookup filters](/trails/lookup) 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.

| Operation | What it does | Needs |
| --- | --- | --- |
| [StartQuery](/trails-api/queries/StartQuery/) | Checks the SQL and queues the query | `audit.queries.run` |
| [GetQuery](/trails-api/queries/GetQuery/) | Returns the query's status and statistics | `audit.queries.get` for your own queries, `audit.queries.list` for anyone's |
| [GetQueryResults](/trails-api/queries/GetQueryResults/) | Returns a page of rows, or downloads every row | `audit.queries.get` for your own queries, `audit.queries.list` for anyone's |
| [CancelQuery](/trails-api/queries/CancelQuery/) | Cancels a queued or running query | `audit.queries.cancel`, and permission to see the query |
| [ListQueries](/trails-api/queries/ListQueries/) | Lists queries, newest first | `audit.queries.list` for everyone's; with only `audit.queries.get`, your own |
| [GetQuerySchema](/trails-api/queries/GetQuerySchema/) | Lists the `trails` columns and the functions you can use | Any of `audit.queries.run`, `.get` or `.list` |

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

## Run a query

### Console

1. In the [console](https://console.ezghcloud.com), open **Trails** and select the **Query**
tab.
2. Write a query in the editor, or select one of the **Sample queries**.
3. Choose a **Time range**: **Last hour**, **Last 24 hours**, **Last 7 days**,
**Last 30 days**, **Last 90 days**, or **Custom range…** with a **From** and **To**.
4. Select **Run**, or press Ctrl+Enter (⌘ Enter on a Mac). The query page opens and shows the
query's status until it finishes.
5. Page through the rows with **Rows per page**, **Previous** and **Next**, or download every
row with **CSV** or **JSON Lines**.

To stop a queued or running query, select **Cancel query** on its page. **History** lists
past queries: choose **My queries** or **Everyone’s queries** and a status, select a query to
open it, or select **Open in editor** to run it again.
### CLI

`ezgh trails query run` starts a query, waits for it, and prints its rows. Press Ctrl-C while
it waits to cancel the query.

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

With `--async`, it prints the query ID and returns:

```sh
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
```

| Command | Does |
| --- | --- |
| `ezgh trails query run "<SQL>"` | Runs a query. `--from` and `--to` set the window (default: the 7 days before `--to`, which defaults to now); `--async` returns at once; `--limit` sets rows per page read (1 to 1000). |
| `ezgh trails query get <query-id>` | Shows a query's status. `--wait` waits for it to finish. |
| `ezgh trails query results <query-id>` | Prints a succeeded query's rows (`--limit`, `--all`, `--cursor`), or streams every row with `--download csv` or `--download ndjson`. |
| `ezgh trails query cancel <query-id>` | Cancels a queued or running query. |
| `ezgh trails query list` | Lists queries, newest first. Filter with `--status` and `--created-by` (a principal ID or a user's email address). |
| `ezgh trails query schema` | Lists the `trails` columns. With `-o json`, also the functions. |

Times are RFC 3339, a date, or a duration ago (`2h`, `7d`).
### API

1. Start the query with [StartQuery](/trails-api/queries/StartQuery/):

```sh
curl -X POST -H "Authorization: Bearer $EZGH_API_KEY" \
  -H "Content-Type: application/json" \
  -d '{"sql": "SELECT event_name, count() AS n FROM trails GROUP BY event_name ORDER BY n DESC", "from": "2026-09-01T00:00:00Z", "to": "2026-09-29T00:00:00Z"}' \
  "https://audit.ezghcloud.com/v1/organizations/$ORG_ID/queries"
```

The response is `202 Accepted`, with the query's path in the `Location` header:

```json
{ "queryId": "0199a3e2-7c1d-7a4b-9e2f-1b3c4d5e6f70", "status": "queued" }
```

2. Poll [GetQuery](/trails-api/queries/GetQuery/) until `status` is `succeeded`, `failed` or
`cancelled`. While the query is queued or running, the response has a `Retry-After` header
with the seconds to wait before polling again.

```sh
curl -H "Authorization: Bearer $EZGH_API_KEY" \
  "https://audit.ezghcloud.com/v1/organizations/$ORG_ID/queries/$QUERY_ID"
```

3. Read the rows with [GetQueryResults](/trails-api/queries/GetQueryResults/):

```sh
curl -H "Authorization: Bearer $EZGH_API_KEY" \
  "https://audit.ezghcloud.com/v1/organizations/$ORG_ID/queries/$QUERY_ID/results?limit=100"
```

Or download every row as CSV (`Accept: text/csv`) or newline-delimited JSON
(`Accept: application/x-ndjson`):

```sh
curl -H "Authorization: Bearer $EZGH_API_KEY" -H "Accept: text/csv" \
  -o results.csv \
  "https://audit.ezghcloud.com/v1/organizations/$ORG_ID/queries/$QUERY_ID/results"
```

To cancel, send `DELETE` to the query's path ([CancelQuery](/trails-api/queries/CancelQuery/)).

## 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](/trails/events). [GetQuerySchema](/trails-api/queries/GetQuerySchema/)
returns each column's exact type.

| Column | Type | Holds |
| --- | --- | --- |
| `event_id` | UUID | `eventId` |
| `event_time` | Timestamp, UTC, milliseconds | `eventTime` |
| `event_type` | String | `ApiCall`, `SignIn` or `ServiceEvent` |
| `event_category` | String | `eventCategory` |
| `event_source` | String | `eventSource`, such as `iam.ezghcloud.com` |
| `event_name` | String | `eventName`, such as `CreateApiKey` |
| `read_only` | Boolean | `readOnly` |
| `project_id` | Nullable string | `projectId`, or `NULL` for organization-level events |
| `identity_type` | String | `userIdentity.type` |
| `principal_id` | Nullable string | `userIdentity.principalId` |
| `access_key_id` | Nullable string | `userIdentity.accessKeyId` |
| `user_identity` | JSON | `userIdentity`. Read its fields as subcolumns: `user_identity.userName` |
| `source_ip` | Nullable string | `sourceIpAddress` |
| `user_agent` | Nullable string | `userAgent` |
| `error_code` | Nullable string | `errorCode`, or `NULL` on success |
| `error_message` | Nullable string | `errorMessage` |
| `request_parameters` | JSON | `requestParameters`. Fields are subcolumns: `request_parameters.name` |
| `response_elements` | JSON | `responseElements`. Fields are subcolumns |
| `additional_event_data` | JSON | `additionalEventData` |
| `resources` | Array of (`resource_name`, `type`) | `resources`. `resources.resource_name` is the array of names |
| `request_id` | Nullable string | `requestId` |

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 `WINDOW`s.
- `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](/trails-api/queries/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:

```sql
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:

```sql
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:

```sql
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](/trails-api/queries/GetQuery/) returns the query:

```json
{
  "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"
}
```

| Field | Description |
| --- | --- |
| `status` | `queued`, `running`, `succeeded`, `failed` or `cancelled` |
| `queuePosition` | Present only while `queued`: the query's place among your organization's queued queries (1 is next) |
| `error` | `{ "code", "message" }` when the query failed, else `null` |
| `stats` | What a succeeded query read and returned: `bytesScanned`, `rowsScanned`, `elapsedMs`, `resultRows`. `null` otherwise. `bytesScanned` is a string of digits, since it can exceed what a JSON number holds exactly; the others are numbers |
| `resultRows` | Rows stored for the result |
| `truncated` | `true` if the result stopped at 100,000 rows |
| `cancelRequested` | `true` once a running query has been asked to stop |
| `createdBy`, `createdByIdentity` | Who started the query: their principal ID and their [`userIdentity`](/trails/events#useridentity) |
| `createdAt`, `startedAt`, `finishedAt` | Times, RFC 3339 with milliseconds; `startedAt` and `finishedAt` are `null` until they happen |

A failed query's `error.code` is one of:

| Code | Meaning |
| --- | --- |
| `invalid_query` | The query failed when it ran: an unknown column, a type mismatch, a failed conversion, division by zero |
| `query_too_large` | The query would read more than 500 million events. It fails before reading any data; narrow the window or add conditions on the time range |
| `too_much_data` | The query read more than the per-query limit |
| `timeout` | The query ran longer than 120 seconds |
| `memory_limit` | The query needed more than 4 GB of memory |
| `quota_exceeded` | Your organization's hourly allowance is used up |
| `cancelled` | The query was stopped before it finished |
| `worker_lost` | The query stopped unexpectedly; run it again |
| `query_failed` | Any other failure |

## Results

[GetQueryResults](/trails-api/queries/GetQueryResults/) returns a page of a succeeded query's
rows:

```json
{
  "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

| Limit | Value |
| --- | --- |
| Time window | Last 7 days by default; at most 90 days |
| SQL length | 16 KiB |
| Queued queries per organization | 5 |
| Running queries per organization | 2 at a time; the rest wait in the queue |
| Queries started per organization per hour | 200 |
| Data read per organization per hour | 500 GB |
| Events read per query | 500 million |
| Data read per query | 20 GB |
| Run time per query | 120 seconds |
| Memory per query | 4 GB |
| Result rows | 100,000; a bigger result is cut off and `truncated` is `true` |
| Result retention | 7 days after the query finishes |
| Result pages and downloads per organization | 20 per second |
| History page size | 1 to 100 queries (default 20) |

The hourly allowances reset on the hour (UTC).

## Errors

| Status | Code | Cause |
| --- | --- | --- |
| `400` | `invalid_request` | A body that isn't `{ "sql", "from", "to" }`, an empty `sql`, or a window that is invalid or longer than 90 days |
| `400` | `invalid_query` | The SQL isn't allowed or doesn't parse. The error has `line` and `column` |
| `400` | `invalid_cursor` | A cursor from another query or list, or edited |
| `403` | `access_denied` | The caller lacks the permission the operation needs |
| `404` | `not_found` | The query doesn't exist in this organization |
| `409` | `query_not_finished` | Results were read while the query is queued or running. Retry after `Retry-After` |
| `409` | `query_failed` | Results were read for a failed query. `reason` holds its `error.code` |
| `409` | `query_cancelled` | Results were read for a cancelled query |
| `409` | `query_finished` | A cancel was sent for a query that has already finished |
| `410` | `results_expired` | The results are more than 7 days old. Run the query again |
| `429` | `too_many_queries` | Your organization already has 5 queued queries, or the queue is full. Retry after `Retry-After` |
| `429` | `quota_exceeded` | An hourly allowance is used up. The error's `quota.id` is `audit.queries.queriesPerHour` or `audit.queries.bytesReadPerHour`. There is no `Retry-After`: try again after the hour |
| `429` | `too_many_requests` | Result pages or downloads were read too fast, or too many requests were sent. Retry after `Retry-After` |
| `503` | `unavailable` | A dependency is unavailable. Retry after `Retry-After` |

A `400 invalid_query` looks like this:

```json
{
  "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`.

Source: https://docs.ezghcloud.com/trails/query/index.mdx