RousselTMLEARNING

Observability

Advanced aggregations, PPL and SQL in OpenSearch

Analyzing data in OpenSearch: bucket, metric and pipeline aggregations, date histograms, approximate cardinality, composite pagination, PPL commands to explore logs and SQL through the Query Workbench.

1. Aggregation families #

Group, compute, chain.

Three families #

Aggregation families
Good to know : Requests are written in the OpenSearch Dashboards Dev Tools console format (GET _cluster/health). On the command line, the same request is curl -sk -u 'admin:Str0ng!Passw0rd-2026' https://localhost:9200/_cluster/health, with -H 'Content-Type: application/json' -d '…' when there is a body.

Error rate per service and per hour #

GET logs-app-*/_search
{
  "size": 0,
  "query": {"range": {"@timestamp": {"gte": "now-24h"}}},
  "aggs": {
    "par_heure": {
      "date_histogram": {"field": "@timestamp", "fixed_interval": "1h", "time_zone": "Europe/Paris"},
      "aggs": {
        "par_service": {
          "terms": {"field": "service", "size": 10},
          "aggs": {
            "erreurs": {"filter": {"term": {"level": "ERROR"}}},
            "p95": {"percentiles": {"field": "duree_ms", "percents": [95]}}
          }
        }
      }
    }
  }
}

size: 0 avoids returning documents: only aggregations matter. fixed_interval takes a fixed duration (1h, 5m); calendar_interval follows the calendar (1M for a real month).

2. Aggregations to know #

And their pitfalls.

Selection #

AggregationUsePitfall
termstop N valuesapproximate counts over several shards (doc_count_error_upper_bound)
cardinalitydistinct value count (unique users)approximate (HyperLogLog++), exact below precision_threshold
percentilesp50, p95, p99 latencyapproximate (t-digest)
compositewalk all combinations, page by pageno sort by count
top_hitssample documents per groupcostly with many groups
significant_termswhat is unusually frequent in a subsetneeds enough data

Pipeline aggregations #

GET logs-app-*/_search
{
  "size": 0,
  "aggs": {
    "par_minute": {
      "date_histogram": {"field": "@timestamp", "fixed_interval": "1m"},
      "aggs": {
        "volume": {"value_count": {"field": "@timestamp"}},
        "variation": {"derivative": {"buckets_path": "volume"}},
        "pics": {"bucket_selector": {"buckets_path": {"v": "volume"}, "script": "params.v > 1000"}}
      }
    }
  }
}

derivative computes the minute-to-minute change; bucket_selector keeps only minutes above 1,000 events.

Walking everything with composite #

GET logs-app-*/_search
{
  "size": 0,
  "aggs": {
    "couples": {
      "composite": {
        "size": 500,
        "sources": [{"service": {"terms": {"field": "service"}}}, {"niveau": {"terms": {"field": "level"}}}]
      }
    }
  }
}

The response contains after_key: send it back in "after" to get the next page, until an empty response.

3. Exploring with PPL #

The shell-like language.

Principle #

A PPL query
CommandRoleExample
wherefilterwhere level = 'ERROR' and duree_ms > 1000
fieldspick fieldsfields @timestamp, service, message
statsaggregatestats avg(duree_ms), count() by service
evalcompute a fieldeval secondes = duree_ms / 1000
sort / headsort, limitsort - duree_ms | head 20
dedupdeduplicatededup utilisateur
top / rarefrequent or rare valuestop 5 service
parseextract with a regexparse message 'utilisateur=(?\w+)'
patternsgroup similar messagespatterns message | stats count() by patterns_field

Examples #

source=logs-app-* | where level = 'ERROR' | stats count() as erreurs by span(@timestamp, 1h), service | sort - erreurs

source=logs-app-* | parse message 'utilisateur=(?<utilisateur>\w+)' | where isnotnull(utilisateur) | top 5 utilisateur

source=web-logs | eval lent = if(duree_ms > 1000, 1, 0) | stats sum(lent) as lentes, count() as total by request | eval taux = lentes * 100 / total | sort - taux | head 10
Good to know : Versions 3.x extend PPL (new query engine, joins and subqueries depending on the version). Check your version's command list in the documentation.

4. SQL and Query Workbench #

For those who think in tables.

SQL #

SELECT service, COUNT(*) AS n, AVG(duree_ms) AS moyenne
FROM logs-app-*
WHERE level = 'ERROR' AND @timestamp > NOW() - INTERVAL 1 DAY
GROUP BY service
ORDER BY n DESC
LIMIT 10

The console's Query Workbench runs SQL and PPL, shows the plan (_explain) and exports to CSV or JSON. Via API: POST _plugins/_sql?format=csv.

LanguageStrengths
DSLanything is possible, used by applications
PPLinteractive log exploration, readable chaining
SQLreports, BI tools (JDBC and ODBC drivers)

Commands to know #

Commands to know
  • POST _plugins/_ppl : run a PPL query
  • POST _plugins/_sql?format=csv : run SQL, CSV response
  • POST _plugins/_sql/_explain : query translated to DSL
  • "size": 0, "aggs": {…} : aggregate without returning documents

Sources #

Checked in November 2026 (OpenSearch 3.x, Data Prepper 2.x): OpenSearch ships a minor release about every six weeks, check the release notes before relying on a detail.

Related articles

The OpenSearch cluster in depth: nodes, shards and replication

Understanding how an OpenSearch cluster works inside: node roles, cluster manager election, shard allocation,…

Full-text search in OpenSearch: analyzers and relevance

Mastering full-text search: inverted index, analyzers (including French), the _analyze API, match, multi_matc…

Advanced Data Prepper: routing, OpenTelemetry and reliability

Going further with Data Prepper: architecture, buffers and workers, conditional processors, routing to severa…

Keep learning

Support us

♥ SUPPORT US

Support RousselTM Learning

Courses, articles, labs and certificates stay free with no intrusive ads. A voluntary contribution, of any amount you choose, pays for hosting, lab environments and new content.

Contribute with PayPal Any amount · card or PayPal accountLearn more
QR code PayPal
Or scan this QR code with your phone

Thank you, every contribution counts!