---
name: esql
description: "Query Elasticsearch/Kibana using ES|QL via the Kibana async search API. Requires an initial curl command from the user to extract session credentials. Triggers: /esql, 'query elastic', 'search logs', 'check elastic logs', 'run esql', 'elasticsearch query', 'check kibana'"
user_invocable: true
---

# ES|QL Query Skill

Query Elasticsearch via the Kibana ES|QL async search API. Run ES|QL queries directly against Kibana clusters without the user needing to copy-paste results.

## Setup

### Staging / Production — `,es-web` CLI

The `,es-web` CLI at `/Users/meain/.local/bin/utils/,es-web` handles Kibana ES|QL queries with automatic async polling, session management, and table output.

**First-time setup** for a cluster: open Kibana's ES|QL tab (use the URL with `dataSource:(type:esql)` so an `esql_async` request fires on load), open DevTools → Network, filter by `esql_async`, right-click the request → **Copy as cURL**, then run:
```bash
pbpaste | ,es-web init stg-us
pbpaste | ,es-web init prd-eu
```

**Check configured clusters:**
```bash
,es-web list
```

Session cookies expire after a few hours. If you get a 401/session expired error, use `reinit` — it reopens Kibana and waits for a fresh curl on the clipboard itself, then tests & saves:
```bash
,es-web reinit prd-us
```
Run this with `dangerouslyDisableSandbox: true` since it needs to open the browser. It blocks until a valid curl shows up on the clipboard, so tell the user to copy one (DevTools → Network → filter `esql_async` → right-click → Copy as cURL) while it's waiting.

## How to query with `,es-web`

```bash
# Simple: service + time window
,es-web -c stg-us -s earn -t 1h -n 20

# With additional WHERE clause
,es-web -c stg-us -s earn -t 1h -q 'message LIKE "*validation*"'

# Custom fields
,es-web -c stg-us -s earn -t 1h -f '@timestamp, message, labels.error'

# Raw ES|QL query
,es-web -c stg-us 'FROM logs-* | WHERE service.name == "earn" | STATS count = COUNT(*) BY labels.event_type | SORT count DESC | LIMIT 10'

# JSON output for further processing
,es-web -c stg-us -s earn -t 30m --json

# Debug: print generated query
,es-web -c stg-us -s earn -t 1h --debug
```

### `,es-web` options

- `-c` — Cluster alias (e.g. `stg-us`, `prd-eu`)
- `-s` — `service.name` filter
- `-t` — Time window (e.g. `30m`, `1h`, `2d`). Defaults to `4h` if omitted (a warning is printed to stderr)
- `-n` — Max rows (default: 20)
- `-q` — Additional WHERE clause
- `-f` — Comma-separated fields for KEEP clause
- `-i` — Index pattern (default: `logs-*`)
- `--json` — Raw JSON output
- `--debug` — Print generated query to stderr

### Important notes

- `,es-web` swallows unknown-column errors — a `KEEP`/`STATS BY` on a **non-existent field returns an empty "No results"**, not an error. Before concluding "no data", confirm the field exists (e.g. `... | WHERE <field> IS NOT NULL | STATS COUNT(*)`). Field names vary by dataset (e.g. `apm.app.earn` has no HTTP path field; `apm.app.nginx` uses `labels.path`).
- `labels.status` is a **keyword** field — compare with strings, not integers (e.g., `labels.status >= "500"`)
- When no `-f` is specified, default fields are: `@timestamp, service.name, message, labels.error, labels.error_message, labels.path, labels.status, trace.id`
- For raw ES|QL queries, pass the full query as a positional argument — no default KEEP is added
- LIKE queries on `message` across many services can be slow — scope with service and tight time windows

## EARN-specific reference

For EARN log fields, structured labels, message strings, query patterns, and metrics, read:
`.mdocs/reference/earn-kibana-esql-reference.md` (relative to the `control-plane-backend` repo root).

That doc is authoritative for EARN. The quick summary of EARN-specific field names (these differ from other services):
- `labels.workload_tenant_id` — workload tenant UUID (not `labels.workload_tenant.id`)
- `labels.event_type` — EARN event type (not `labels.earn.event.type`)
- `labels.organization_id` — org UUID in EARN context
- `labels.operation` — EARN operation name (e.g. `get_workload_tenant_metadata`)
- `labels.clog_labels` — structured log category (e.g. `[earn_event_dropped]`)
- `log.level` — log level in EARN APM logs (not `level`)

Note: EARN logs appear **twice** in `logs-*` — once as APM-parsed (`data_stream.dataset: apm.app.earn`, has structured fields) and once as raw filebeat (`data_stream.dataset: kubernetes.container_logs`, has JSON in message). Filter to `data_stream.dataset == "apm.app.earn"` to avoid double-counting in STATS.

## Known service names

These are the `service.name` values for control-plane-backend services:

| Service | `service.name` |
|---|---|
| Workload Tenants | `workload-tenants-svc` |
| Subscriptions | `subscriptions-svc` |
| User Management | `user-management` |
| Routing | `routing` |
| EARN | `earn` |
| EARN Telemetry | `earn-telemetry` |
| Usage Reports | `usage_reports` |
| Onboarding | `onboarding` |
| Scheduler | `scheduler` |

The nginx ingress controller logs under `service.name == "nginx"`.

## Common log fields

### Standard fields
- `@timestamp` — event timestamp (ISO 8601)
- `service.name` — service identifier (see table above)
- `service.environment` — `prod`, `prd`, `qa`, `dev`
- `message` — log message text
- `level` — log level (`info`, `warn`, `error`)
- `trace.id` — distributed trace ID for correlating across services

### Label fields (keyword/string type)
- `labels.status` — HTTP status code (string, not int)
- `labels.path` — request path
- `labels.method` — HTTP method
- `labels.error` / `labels.error_message` — error details
- `labels.organization_id` / `labels.org_id` — organization identifier
- `labels.organization.id` — alternative org ID field
- `labels.workload_tenant_id` — workload tenant identifier (EARN: same field, see EARN reference)
- `labels.user.id` / `labels.principal.id` — user/principal identifiers
- `labels.operation` — operation being performed
- `labels.handler` — handler name
- `labels.event_type` — EARN event type (use `labels.event_type`, not `labels.earn.event.type`)
- `labels.earn.event.dedupe_key` — EARN dedup key
- `labels.scheduler.event.id` — scheduler event ID
- `labels.request.url` — outgoing request URL
- `labels.http.router.request_id` — request ID

### Numeric label fields
- `numeric_labels.*` — numeric values (latency, counts, etc.)

### Overview labels (for filtering structured log categories)
These are set via `clog.Label()` and appear in a labels field:
- `http_server_overview`, `dispatcher_overview`, `queue_dispatcher_overview`
- `earn_event_delivery`, `events_processor`
- `panic_recovery`, `panic_recovered`
- `azure_sdk_logging`, `secrets_handling`
- `deprecated_behavior_overview`

## Common query patterns

### List services in a time window
```
FROM logs-*
| WHERE service.environment == "prod"
  AND @timestamp >= "2026-04-07T07:00:00.000Z"
  AND @timestamp <= "2026-04-07T08:00:00.000Z"
| STATS count = COUNT(*) BY service.name
| SORT count DESC
| LIMIT 30
```

### Find 5xx errors from nginx
```
FROM logs-*
| WHERE service.name == "nginx"
  AND service.environment == "prod"
  AND labels.status >= "500"
| SORT @timestamp DESC
| LIMIT 20
| KEEP @timestamp, labels.status, labels.path, labels.method, message
```

### Find errors for a specific service
```
FROM logs-*
| WHERE service.name == "earn"
  AND service.environment == "prod"
  AND level == "error"
  AND @timestamp >= "START"
  AND @timestamp <= "END"
| SORT @timestamp DESC
| LIMIT 30
| KEEP @timestamp, message, labels.error, labels.error_message, labels.operation, trace.id
```

### Search by org/tenant ID across all services
```
FROM logs-*
| WHERE service.environment == "prod"
  AND @timestamp >= "START"
  AND @timestamp <= "END"
  AND (labels.organization_id == "ORG_ID" OR labels.org_id == "ORG_ID" OR labels.organization.id == "ORG_ID")
| SORT @timestamp DESC
| LIMIT 20
| KEEP @timestamp, service.name, message, labels.error, trace.id
```

### Search by workload tenant ID
```
FROM logs-*
| WHERE service.environment == "prod"
  AND @timestamp >= "START"
  AND @timestamp <= "END"
  AND labels.workload_tenant_id == "TENANT_ID"
| SORT @timestamp DESC
| LIMIT 30
| KEEP @timestamp, service.name, message, labels.error, labels.operation
```

### Trace a request across services
```
FROM logs-*
| WHERE trace.id == "TRACE_ID"
| SORT @timestamp ASC
| LIMIT 100
| KEEP @timestamp, service.name, level, message, labels.error, labels.status, labels.path
```

### Check latency metrics
```
FROM logs-*
| WHERE service.name == "SERVICE"
  AND service.environment == "prod"
  AND message LIKE "*metric*latency*"
| SORT @timestamp DESC
| LIMIT 30
| KEEP @timestamp, message, numeric_labels.value_ms
```

### Error rate by service (last hour)
```
FROM logs-*
| WHERE service.environment == "prod"
  AND @timestamp >= "START"
  AND @timestamp <= "END"
  AND level == "error"
| STATS error_count = COUNT(*) BY service.name
| SORT error_count DESC
| LIMIT 20
```

### Find panics
```
FROM logs-*
| WHERE service.environment == "prod"
  AND @timestamp >= "START"
  AND @timestamp <= "END"
  AND (message LIKE "*panic*" OR labels.panic_recovered == "true" OR labels.panic_recovery == "true")
| SORT @timestamp DESC
| LIMIT 20
| KEEP @timestamp, service.name, message, labels.error, trace.id
```

## Dev / Personal Environments — `,es` CLI

The `,es` CLI at `/Users/meain/.local/bin/utils/,es` provides a simpler way to query Elasticsearch, but it **only works for the dev environment**. Do not use it for staging or production.

### Common queries

Search by trace ID:
```
,es -q 'trace.id:<TRACE_ID>' -n 50
```

Filter for errors:
```
,es -q 'trace.id:<TRACE_ID> AND event.type:error' -n 10
```

Broader service-level search (no trace ID):
```
,es -q 'service.name:<SERVICE_NAME> AND (level:error OR event.type:error)' -n 20
```

### `,es` CLI options

- `-q` — Lucene query string
- `-n` — Number of results
- `-f` — Comma-separated fields to display
- `-s` — Sort field (e.g. `@timestamp:desc`)

## Choosing the right approach

| Environment | Method |
|---|---|
| Dev / personal | Use `,es` CLI directly |
| Staging / production | Use `,es-web -c <cluster>` (requires session cookie from user) |
| No session available | Fall back to clipboard mode (see below) |

**Cluster naming convention:** `<env>-<region>`, e.g. `stg-us`, `stg-eu`, `prd-us`, `prd-eu`, `prd-apj` (APJ, not apac).

When no `,es-web` session is configured for the target cluster:
1. Look up the Kibana URL on the Confluence Speed Dial page (`confluence page 405504500`), open `<kibana-base-url>/app/discover#/` with `open <url>` (with `dangerouslyDisableSandbox: true`), then ask the user to copy a curl from the network tab and run `! pbpaste | ,es-web init <cluster>`
2. If that's not possible, fall back to clipboard mode:
   - Copy the ES|QL query to the user's clipboard with `pbcopy`
   - Tell the user to open Kibana, run the query in Discover (ES|QL mode)
   - Have them copy the response JSON from the network tab

## Investigation workflow tips

- **Start broad, narrow down.** First identify which services are involved, then drill into specific errors.
- **nginx → upstream.** nginx logs show external-facing status codes; upstream service logs show root cause.
- **Use trace.id** to correlate requests across services when available.
- **Error details** live in `labels.error` and `labels.error_message`.
- **Field types matter:** `numeric_labels.*` fields are numeric; `labels.*` fields are keywords (strings).
- **Time windows:** Default is 4h when `-t` is omitted. If a query times out, retry with a narrower window (e.g., `1h` → `30m`). For very broad searches (e.g., no service filter), start at `30m` and expand only if needed.
- **Overview labels** (like `http_server_overview`, `dispatcher_overview`) are useful for filtering to structured log categories without needing to match on message text.