# Query History

The query history pages (`/history-queries`, `/failed-queries`, `/expensive-queries`, `/slow-queries`) read from `system.query_log`. This table is built into ClickHouse and records every completed query.

---

## Requirements

`system.query_log` must be enabled in your ClickHouse configuration. It is enabled by default in most installations. If the table is empty or missing, history pages show no data.

The configured `CLICKHOUSE_USER` needs `SELECT` on `system.query_log`:

```sql
GRANT SELECT ON system.query_log TO monitoring_user;
```

---

## Retention

ClickHouse flushes query log entries every 7.5 seconds by default and retains them for 30 days. To change retention, set `flush_interval_milliseconds` and `partition_by` / `ttl` in `system.query_log` config:

```xml
<!-- config.xml or conf.d/query_log.xml -->
<query_log>
    <database>system</database>
    <table>query_log</table>
    <flush_interval_milliseconds>7500</flush_interval_milliseconds>
    <ttl>event_date + INTERVAL 14 DAY DELETE</ttl>
</query_log>
```

Shorter retention reduces disk usage but limits how far back history pages can look.

---

## Filtering out monitoring users

By default, history pages show queries from all users including the monitoring user itself. This creates noise. Filter out specific users using `CLICKHOUSE_EXCLUDE_USER_DEFAULT`:

```bash
CLICKHOUSE_EXCLUDE_USER_DEFAULT=monitoring,healthcheck
```

Set this to a comma-separated list of usernames to exclude from history page defaults. Users can still remove the filter in the UI.

**Docker:**

```bash
docker run -d \
  -e CLICKHOUSE_HOST='http://ch-1:8123' \
  -e CLICKHOUSE_USER='monitoring' \
  -e CLICKHOUSE_PASSWORD='' \
  -e CLICKHOUSE_EXCLUDE_USER_DEFAULT='monitoring,healthcheck' \
  -p 3000:3000 \
  ghcr.io/duyet/chmonitor:vX.Y.Z
```

**Kubernetes / Helm:**

```yaml
env:
  - name: CLICKHOUSE_HOST
    value: "http://ch-1:8123"
  - name: CLICKHOUSE_USER
    value: "monitoring"
  - name: CLICKHOUSE_PASSWORD
    value: ""
  - name: CLICKHOUSE_EXCLUDE_USER_DEFAULT
    value: "monitoring,healthcheck"
```

---

## What each history page shows

| Page | Route | Source | Description |
|---|---|---|---|
| History | `/history-queries` | `system.query_log` | All completed queries, filterable by user, database, and time. |
| Failed | `/failed-queries` | `system.query_log` | Queries that ended with an exception. |
| Expensive | `/expensive-queries` | `system.query_log` | Queries ranked by memory usage or read bytes. |
| Slow | `/slow-queries` | `system.query_log` | Queries ranked by elapsed time. |

---

## Notes

- History pages respect the global time range selector.
- If `system.query_log` is partitioned by day, queries crossing midnight may appear split across rows.
- The AI agent can analyze query history and surface patterns or anomalies — see [AI Agent](/ai-agent).

---

## Related

- [Features — Queries](/features/queries) — full overview of all query pages.
- [Environment Variables](/reference/environment-variables) — `CLICKHOUSE_EXCLUDE_USER_DEFAULT` and connection settings.
- [Self-Tracking](/advanced/self-tracking) — the dashboard's own event table.