Elasticsearch query log with user, duration and the DSL that ran

A cluster can tell you that a search took four seconds on shard 2. It cannot tell you that Priya ran it from the reporting connection, that it was a join over two indices, that the same shape has run 140 times this week, or that the engine, not the cluster, spent most of the time. FlatQL keeps that record, because it is the one place every statement passes through.

What one entry holds#

Each Run writes a history row before the result reaches the grid:

The history list shows the last of these as a duration bar scaled to the slowest query on the page, so the heavy ones stand out before you read a number. Filter by status, by user, or search the statement text. Click a row and the detail page shows the full statement, Copy SQL, Open in worksheet, and the query profile: which requests went to the cluster, whether the WHERE was pushed in full, how many rows each returned and how long each took. That is the same information as the Show as DSL tab on a live result, kept.

Grouping by shape#

Two runs of WHERE country = 'DEU' and WHERE country = 'FRA' are the same query. FlatQL computes a fingerprint by replacing string and numeric literals with ?, collapsing whitespace and lower-casing, then hashing. The Grouped queries tab uses it to show each distinct statement shape over the last day, week or month with runs, errors, average and maximum duration, total rows, the users who ran it and when it last ran, sorted by run count and total time. A detail page shows "Runs of the same shape" for the statement you are looking at.

This is where slow-query work starts. A statement with a 200 ms average and a 9 s maximum has a parameter problem, probably a date range that occasionally spans a year. A statement with 400 runs a day and 1.5 s each is a dashboard refresh worth caching. A statement with 30 percent errors is someone's broken saved query.

Usage by user and connection#

The Usage page turns the same rows into totals for the last 7, 30 or 90 days: queries, rows returned, query time, errors, active users and connections used, with a stacked daily chart and a table by user or by connection. Owners and admins see the whole workspace; members see their own share. It is built from the history, so every statement counts and no separate metric is collected. The query history docs describe both pages.

A heavy statement and its breakdown#

Here is the kind of statement that shows up at the top of a grouped list:

SELECT o.country, s.category, COUNT(*) AS suppliers, AVG(s.rating) AS avg_rating
FROM suppliers s
JOIN organizations o ON o.primary_domain = s.domain
WHERE o.status = 'active' AND s.rating >= 4
GROUP BY o.country, s.category
QUALIFY ROW_NUMBER() OVER (PARTITION BY o.country ORDER BY COUNT(*) DESC) <= 3;

The join, the grouping over joined rows and the QUALIFY all run in the engine. The history entry's plan shows two cluster requests. The first is a sliced scroll over organizations, POST /organizations/_search?scroll=2m, four slices in parallel, each with a body like this:

{
  "query": {
    "bool": {
      "filter": [
        { "term": { "status": "active" } }
      ]
    }
  },
  "_source": ["country", "primary_domain", "status"],
  "size": 5000,
  "sort": ["_doc"],
  "track_total_hits": true,
  "slice": { "id": 0, "max": 4 }
}

The second request fetches suppliers with {"range": {"rating": {"gte": 4}}} plus a terms filter on domain holding every primary_domain the first fetch returned, which the plan notes as "suppliers was filtered to the N join keys found in organizations". Under each block the entry records rows and milliseconds, so the breakdown reads something like: organizations 18,400 rows in 1.1 s, suppliers 6,200 rows in 0.6 s, engine 0.9 s for the join, grouping and window. If organizations had been scanned to the 50,000-row cap, a note says so and the fix is obvious: a tighter pushable WHERE. The pushdown page explains which predicates qualify, and limits lists the caps.

Retention by plan#

Free keeps one day of history, enough to look at what you just ran. Pro keeps every statement with its plan and timings for as long as the workspace exists; the history page says which applies, and an entry outside the window answers "Outside your history window" rather than disappearing silently. The pricing page has the plans.

Without FlatQL#

Slow log on the cluster. Elasticsearch and OpenSearch can write a search slow log per index once a query exceeds thresholds you set for each phase. It is the right tool for finding queries that hurt the cluster, and it costs nothing extra. The entries are per shard, hold the raw request body, and do not know who sent it or what SQL it came from; a join appears as two unrelated searches on two indices.

Audit logging in Elastic security. Elastic's security features can log authentication and request events to an audit file, including the cluster user that made each request. With a shared connection credential every entry shows the same user, and the events are request-level, so there is still no duration-by-statement view and no grouping by shape.

A proxy or an APM agent. A reverse proxy can log request lines with latency, and an application performance tool can trace calls from an application that owns its own Elasticsearch client. Both need building and maintaining, and neither sees interactive queries from analysts' tools. Grafana can chart those logs once they exist, and Kibana can search them; neither produces them.

FlatQL's history is narrower in one sense, since it only covers what runs through FlatQL, and broader in another: user, SQL, plan, DSL, duration and rows in one row, with no cluster setting touched. For a team that queries through shared connections with per-index access, it is also the companion to the audit log: one says who may query what, the other says who did.

See your own history today#

Create an account and connect a cluster; the first statement you run appears in history with its plan. Free keeps a day of it, Pro keeps everything, and the pricing page has the details.