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

# Query telemetry

> Run SQL over a run's telemetry from the CLI, the SDKs, or the API.

Once you've [captured a run](/guides/capturing-telemetry), you query its telemetry with **SQL** over
a single `events` table. This guide covers the common shapes. For the concepts behind it, see
[the query engine](/concepts/query-engine); for the column list, see the
[event model](/concepts/event-model); for the exact request/response schemas, see the
[API reference](/api-reference).

Every query is a single `SELECT` over `events`. The gateway forces an organization predicate from your
verified identity, so you never write one — your SQL only ever sees your own rows. Only `SELECT`
runs; there's a row cap and a timeout.

**Annotations read per project.** Run-scoped events span your organization, but annotation rows are
project-scoped on read: selecting a project — the `--project` flag, then `HILOOP_PROJECT`, then
the context's project — returns only that project's annotations, both run-scoped and run-less
(cross-run knowledge written with `annotations add --project`). The SQL itself can also select a project
in-band with a `project_id = '<id>'` (or `project_id IN ('<id>', …)`) filter, which surfaces that
project's run-less annotations. Without any selection the query runs over run-scoped data and
returns no project-scoped annotations.

## The smallest query

Return the model calls in a run:

<CodeGroup>
  ```sh CLI theme={null}
  hiloop query --run-id 01K6Z… --signal llm
  ```

  ```sh API theme={null}
  hiloop api /v1/telemetry/sql -X post -d '{
    "sql": "SELECT * FROM events WHERE run_id = '\''01K6Z…'\'' AND signal = '\''llm'\''"
  }'
  ```
</CodeGroup>

The ad-hoc SQL endpoint is a single `POST /v1/telemetry/sql` taking `{"sql": "<SELECT …>"}`, plus
an optional `"project_id"` naming the project scope (the CLI fills it from your selected project),
and returning `{"rows": [ … ], "columns": [ … ]}` — a list of plain JSON object rows with `null`
columns omitted per row and 64-bit integer columns encoded as decimal strings, plus the declared
column names in projection order (so a column that is `NULL` in every row is still visible). From
the SDKs, `POST` to it directly; the typed view services ([data views](/observability/data-views))
wrap the saved-query path. The CLI's pragmatic scoping flags (`--run-id`, `--signal`, `--limit`,
`--since`, `--until`) build a `SELECT` over a compact default column
set — event id, time, signal, name, run identity, principal, and payload size; pass
`--fields <col,col,…>` to choose columns or `--fields '*'` for every column. For anything richer,
pass the SQL yourself with `--sql` (inline, `@file`, or `-` for stdin):

```sh theme={null}
hiloop query --sql "SELECT name, COUNT(*) AS n FROM events WHERE signal = 'exec' GROUP BY name ORDER BY n DESC"
```

## Filter, group, and aggregate

It's just SQL — `WHERE`, `GROUP BY`, aggregates (`COUNT`, `SUM`, `AVG`, `MIN`, `MAX`,
`approx_percentile_cont`), and `ORDER BY` all work. Count model calls grouped by lineage path:

```sh theme={null}
hiloop query --sql "
  SELECT lineage_path,
         COUNT(*) AS calls
  FROM events
  WHERE run_id = '01K6Z…' AND signal = 'llm'
  GROUP BY lineage_path
  ORDER BY calls DESC"
```

## Scope to one lineage path

Every event carries the `lineage_path` of the run that produced it, and logical child runs share
their parent's path as a prefix. A subtree is therefore an ordinary prefix predicate on that
column — the path itself, plus everything below it:

```sh theme={null}
hiloop query --sql "
  SELECT * FROM events
  WHERE run_id = '01K6Z…'
    AND signal = 'exec'
    AND (lineage_path = '01H8A.01H8B' OR lineage_path LIKE '01H8A.01H8B.%')"
```

Drop the `run_id` predicate to span the whole subtree rather than one run's slice of it. Logical
lineage does not imply runtime filesystem fork.

## Compare two runs

To ask what one run did that another did not, use an anti-join on signal, name, and attributes. The
events unique to run A relative to run B:

```sh theme={null}
hiloop query --sql "
  SELECT a.event_id, a.ts_wall_ns, a.signal, a.name
  FROM events a
  LEFT JOIN events b
    ON  b.run_id = '01K6ZB…'
    AND b.signal = a.signal
    AND b.name = a.name
    AND b.attributes_json = a.attributes_json
  WHERE a.run_id = '01K6ZA…' AND b.event_id IS NULL
  ORDER BY a.ts_wall_ns"
```

Swap the two run ids for the reverse difference, and add `AND a.signal = 'llm'` to compare one
signal. To compare logical subtrees, replace each `run_id` equality with the `lineage_path` prefix
match from [Scope to one lineage path](#scope-to-one-lineage-path).

## Query custom attributes

Arbitrary attributes an agent emits live in `attributes_json`. Reach into them with
`hiloop_json_get(attributes_json, '$.path.to.key')`:

```sh theme={null}
hiloop query --sql "
  SELECT * FROM events
  WHERE run_id = '01K6Z…'
    AND hiloop_json_get(attributes_json, '$.tool.name') = 'web_search'"
```

## Filter failed requests

```sh theme={null}
hiloop query --sql "
  SELECT ts_wall_ns, http_method, http_host, http_target, http_status_code
  FROM events
  WHERE run_id = '01K6Z…' AND http_status_code >= 400
  ORDER BY ts_wall_ns"
```

## Ask the platform what happened

Work in a [sandbox](/sandboxes/overview) also records
[`runtime` lifecycle events](/observability/event-model#runtime-platform-lifecycle) — platform
metadata that flows independently of workload capture. "Why did my sandbox take so long to start"
is a query over `operation.started`'s `queue_wait_ms`:

```sh theme={null}
hiloop query --sql "
  SELECT hiloop_json_get(attributes_json, '$.operation.kind') AS kind,
         hiloop_json_get(attributes_json, '$.queue_wait_ms')  AS queue_wait_ms
  FROM events
  WHERE run_id = '01K6Z…' AND signal = 'runtime' AND name = 'operation.started'
  ORDER BY ts_wall_ns"
```

And "did my command succeed" is the
[`exec.start`/`exec.end` pair](/observability/event-model#exec-command-lifecycle) a buffered
sandbox execution records in its ambient run — every `exec.start` gets exactly one
`exec.end`, so a failed command never reads as still-running:

```sh theme={null}
hiloop query --sql "
  SELECT name,
         hiloop_json_get(attributes_json, '$.execution.id')      AS execution_id,
         hiloop_json_get(attributes_json, '$.process.exit_code') AS exit_code,
         hiloop_json_get(attributes_json, '$.exec.error')        AS error
  FROM events
  WHERE run_id = '01K6Z…' AND signal = 'exec'
  ORDER BY ts_wall_ns"
```

## Tokens per model

Model, token counts, and message content live in the **captured request/response bodies**, exactly
as they crossed the wire. Rather than re-parsing provider JSON in every query, create the
`otel_genai_calls` [data view](/observability/data-views) once — one OpenTelemetry-GenAI-shaped row
per LLM exchange, derived entirely from the raw events — and query it like a table:

```sh theme={null}
hiloop data-views create otel_genai_calls --sql @otel_genai_calls.sql
```

The standard "tokens per model" summary is then a `GROUP BY` over it:

```sh theme={null}
hiloop query --sql "
  SELECT gen_ai_request_model,
         SUM(input_tokens)      AS sum_input_tokens,
         SUM(output_tokens)     AS sum_output_tokens,
         SUM(cache_read_tokens) AS sum_cache_read_tokens,
         COUNT(*)               AS exchanges
  FROM otel_genai_calls
  GROUP BY gen_ai_request_model
  ORDER BY sum_input_tokens DESC"
```

Add a `WHERE run_id = '…'` to scope it to one run. The token counts come from each provider
response's own usage block — streamed responses are reassembled at query time — so they match what
the provider actually reported. hiloop stores what crossed the wire and providers report tokens,
not prices; to turn token sums into dollars, multiply by your own rates in the `SELECT`.

The same derivation functions (`payload_text`, `hiloop_sse_reassemble`, `hiloop_json_get`, …) are
available in any query or view, so anything else in a captured body — messages, stop reasons, tool
definitions — is one expression away. See
[deriving views from captured payloads](/observability/data-views#derive-views-from-captured-payloads).

## Output

The CLI prints a table by default; pass `--output json` for the raw response body. Wide values are
truncated in table mode (tune with `--max-cell-width`, or `0` to disable); JSON is always full.

## Next

* Save a query you run often as a reusable [data view](/observability/data-views).
* Compare two runs with the [SQL recipe](#compare-two-runs) above.
* Run the same queries from your code with the [SDKs](/guides/using-the-sdks).