Itamar Syn-Hershko
Hands-on OpenSearch PPL examples covering search, stats, eval, span, join, timechart, and more - ready for logs, metrics, and traces.
OpenSearch PPL (Piped Processing Language) is a pipe-chained query syntax for OpenSearch, modelled after Splunk SPL and Unix pipes. A PPL query starts with source=<index> and chains commands with |, like source=logs | where status=500 | stats count() by host | sort -count. PPL ships with the SQL/PPL plugin in every OpenSearch distribution and runs through Dashboards' Query Workbench or the POST _plugins/_ppl REST endpoint.
This guide is a copy-paste catalog of the 30+ PPL queries you will actually use in production - filtering, aggregation, time-bucketing, regex extraction, joins, subsearches, and the new commands shipped in OpenSearch 3.3-3.5. Every example assumes the Apache Calcite engine is enabled (the default since OpenSearch 3.3, October 2025).
What Is OpenSearch PPL and Why Use It?
PPL is a sequential, left-to-right query language designed for semi-structured data: logs, metrics, and traces. A pipeline reads as: read source, then apply each command in order, then return rows. It maps naturally onto how engineers reason about logs, especially anyone arriving from Splunk SPL or Unix |-style tools. PPL ships in the SQL plugin bundled with the standard OpenSearch distribution, and AWS OpenSearch Service has it enabled by default.
PPL is one of three first-class query interfaces in OpenSearch, alongside the JSON-based DSL and SQL. Each has a different sweet spot:
| Dimension | PPL | DSL | SQL |
|---|---|---|---|
| Syntax style | Pipe-chained, Splunk-like | JSON, nested | SQL standard |
| Best for | Ad-hoc investigation, observability | Programmatic search, scoring control | Reporting, joins on structured data |
| Aggregations | stats, top, rare, timechart |
aggregations DSL | GROUP BY |
| Joins (3.3+) | Yes (join, lookup, subquery) |
Limited | Yes |
| Score control | Limited | Full BM25 / function_score | None |
| Learning curve | Lowest for SPL/Splunk users | Steepest | Familiar to analysts |
PPL is the right tool when you are iterating on a question - reading rows, filtering, grouping, then reading again to spot what matters. DSL still wins when you need fine-grained scoring control or programmatic search. SQL is the natural fit for reports and BI tools. None of these is exclusive: you can mix them in the same cluster.
Prerequisites
The SQL plugin ships with the standard OpenSearch distribution, so most clusters have PPL out of the box. On the minimal distribution, install with bin/opensearch-plugin install opensearch-sql-plugin. On Amazon OpenSearch Service, PPL is enabled by default; AWS also exposes PPL in CloudWatch Logs, with a subset of commands available there.
Authorization requires the cluster:admin/opensearch/ppl permission. Subsearches and a handful of other advanced commands additionally need plugins.calcite.enabled set to true - the default since 3.3, but worth verifying on upgraded clusters.
PPL Syntax Fundamentals
A PPL query is one or more commands separated by |. The first command always defines the data source:
source=<index> | <command_1> | <command_2> | ... | <command_n>
Two facts that bite newcomers: order matters (filter before aggregating to cut data volume), and PPL drops fields you have not referenced after stats collapses rows. If you need a column in the final output, project it explicitly with fields or include it in the by clause.
You run queries from Dashboards' Query Workbench (Plugins menu, switch to the PPL tab) or via the REST API:
POST _plugins/_ppl
{
"query": "source=logs | head 10"
}
Add ?format=jdbc for typed schema metadata, ?format=csv to pull results as CSV. To inspect the execution plan, post to _plugins/_ppl/_explain and you will get the underlying Calcite tree - useful for tuning joins and filter placement.
Basic PPL Query Examples
These are the everyday building blocks: filtering rows, picking columns, computing fields, sorting, deduplicating.
Filtering and selecting fields
source=accounts
| where age > 18 AND state = 'CA'
| fields firstname, lastname, age
Comparison operators (=, !=, <, >, <=, >=, IN, LIKE) and Boolean logic (AND, OR, NOT) work the way you expect. IN takes a literal list; pair it with a subsearch for set-membership against another index (covered later).
Sorting and limiting
source=web_logs
| sort -timestamp
| head 20
A - prefix sorts descending; default is ascending. head N is the PPL equivalent of LIMIT N and is the standard way to cap result size during interactive use.
Deduplication
source=events
| dedup hostname
| fields hostname, status
Useful when you want one row per host. dedup accepts modifiers like keepempty=true (preserve null values as a distinct group) and consecutive=true (only collapse adjacent duplicates - handy in time-ordered streams).
Computing new fields with eval
source=orders
| eval total = price * quantity, discount_pct = (list_price - price) / list_price * 100
| fields order_id, total, discount_pct
eval is the workhorse for derived columns: arithmetic, string functions, type casts, and a conditional if(condition, then, else). Multiple assignments in one eval are evaluated left-to-right, so later expressions can reference earlier ones.
Renaming fields
source=metrics
| rename cpu_user_pct as cpu, mem_used_bytes as mem
Trivial but useful when joining indices that use different naming conventions or when you want shorter labels in dashboards.
Aggregation and Stats Examples
stats is where PPL earns its keep. The pattern is the same in every example: aggregate functions on the left, by clause on the right.
Counts, averages, sums
source=web_logs
| stats count() as total_requests by status_code, host
count() with no argument counts rows; count(field) counts non-null values; dc(field) (distinct count) counts unique values. Group by multiple dimensions by listing them in by.
Percentile aggregations
source=traces
| stats percentile(duration_ms, 50) as p50,
percentile(duration_ms, 95) as p95,
percentile(duration_ms, 99) as p99
by service_name
percentile(field, n) takes a percentile from 1 to 100. For a single-call multi-percentile result, use percentiles(duration_ms, 50, 95, 99) - it returns an array, which is convenient for compact dashboards.
Conditional counts
source=auth_logs
| stats count_if(action='failed_login') as failures,
count_if(action='success') as successes
by user
count_if lets you compute multiple conditional aggregates in one pass without subqueries. Pair it with eval afterward to compute ratios (failure rate, conversion rate) without re-scanning the data.
Top and rare
source=errors | top 5 error_type
source=access_logs | rare user_agent
top N field and rare field are shortcuts for the most and least common values - the kind of query you write twenty times a day during incident response.
Time-bucketed aggregations with span()
source=metrics
| stats avg(cpu_usage) as avg_cpu by span(@timestamp, 1m), host
span(field, interval) accepts intervals like 1s, 1m, 5m, 1h, 1d. This is the canonical pattern for time-series queries, but in 3.3+ the dedicated timechart command (below) is usually clearer.
timechart (added in 3.3)
source=metrics
| timechart span=5m avg(cpu_usage) by host
timechart is a dashboard-first time-series command. Its key advantage over stats ... by span(...) is that it auto-fills missing buckets - so a host that went silent shows up as null rather than disappearing from the chart. That is what you want in nearly every observability dashboard.
eventstats and streamstats
source=requests
| eventstats avg(duration_ms) as avg_dur by service
| where duration_ms > avg_dur * 2
eventstats computes the same aggregations as stats but appends them as new fields on every original row, preserving row count. The query above flags every request more than 2x slower than the per-service average.
streamstats, added in OpenSearch 3.5, performs cumulative statistical calculations as events are processed - running counts, rolling sums, and similar sliding-window aggregates without the row-collapse of stats.
Extracting Structured Data from Logs
Most production logs are semi-structured: a timestamp, a level, a hostname, and a free-text message. PPL has four extraction commands with different trade-offs.
parse - regex with named capture groups
source=raw_logs
| parse log_line "(?<ip>\\S+) - - \\[(?<ts>[^\\]]+)\\] \"(?<method>\\S+) (?<path>\\S+)"
| stats count() by ip, method
parse is the simplest option when you have a stable line format. Named capture groups become field names you can use downstream.
rex - regex extraction (added in 3.3)
source=raw_logs
| rex field=message "user=(?<user>\\w+) ip=(?<ip>\\S+)"
rex targets a specific field rather than the full row. It is the right call when extracting a few fields from a structured message column.
grok - pattern-library extraction
source=apache_logs | grok message "%{COMMONAPACHELOG}"
grok plugs in the same pattern library Logstash uses, so common log formats (Apache, Nginx, syslog) work out of the box without writing regex by hand.
spath - JSON path extraction (added in 3.3)
source=app_logs
| spath input=raw_message path=request.headers.user-agent output=ua
If your raw_message is JSON-encoded, spath pulls a nested field by path without an explicit regex. Combine with parse upstream when the JSON is wrapped in a wider envelope.
Advanced PPL Examples (OpenSearch 3.3+)
The 3.3 release substantially expanded PPL's reach with 9 new commands and 15 new functions. The headline additions are joins and subsearches.
Joining datasets with join
source=web_logs
| join type=inner left=client_ip right=ip [
source=ip_geodata | fields ip, country, city
]
| stats count() as requests by country
type= accepts inner, left, right, and cross. The right side can be an index reference or a full subsearch (as above). Filter both sides before joining - large unfiltered joins blow up memory budgets.
Appending results from multiple queries
source=logs-2026-04-*
| stats count() as errors by service
| append [
source=logs-2026-03-*
| stats count() as errors by service
]
append is the row-wise concatenation - useful for week-over-week comparisons or merging data from different index patterns. appendcol glues columns side-by-side; appendpipe re-runs the same pipeline with a different filter, which is the most common pattern for "compare to total" reports.
Subsearches
source=alerts
| where service IN [
source=critical_services | fields service_name
]
A subsearch in the IN clause turns the right-hand side into a dynamic list - here, "alerts for any service in the critical_services index". This requires plugins.calcite.enabled=true, which is the default on 3.3 and later.
Lookup tables for enrichment
source=requests
| lookup ip_geodata ip OUTPUT country, city
lookup does fast in-memory enrichment from a small reference index. It is far cheaper than a join for static data (geo databases, asset inventories, employee directories) and avoids reshaping the row.
Multivalue Field Operations (OpenSearch 3.5)
OpenSearch 3.5 (February 2026) shipped six commands and functions specifically for multivalue field manipulation. These matter for any data model with array fields: tags, headers, tracing spans, OpenTelemetry attributes.
mvcombine
source=log_events
| mvcombine values=tag delim=","
| stats count() by tag
mvcombine consolidates multivalue fields - useful for collapsing arrays when you want one tag-string per row rather than per-element exploding.
mvzip, mvfind, mvmap
source=traces
| eval pairs = mvzip(span_names, span_durations, "=")
| where mvfind(span_names, "checkout") >= 0
| eval squared = mvmap(span_durations, value * value)
mvzip stitches two parallel arrays into one. mvfind returns the index of the first match in an array (useful inside where filters). mvmap applies an expression to every element and returns a transformed array.
addtotals
source=daily_revenue
| stats sum(amount) as total by region, span(@timestamp, 1d)
| addtotals row=true col=true
addtotals adds a row that totals every column and a column that totals every row - the kind of thing analysts expect from a pivot table. New in 3.5.
Real-World Use Cases
The examples below combine the building blocks above into the queries you would actually keep around in a runbook.
Troubleshooting application latency
source=traces
| where service_name='checkout' AND duration_ms > 1000
| stats percentile(duration_ms, 99) as p99,
count() as slow_requests
by endpoint
| sort -p99
| head 10
Top 10 slowest endpoints in the checkout service, by p99 latency, filtered to traces over a second. Standard incident-response query.
Detecting anomalous logins
source=auth_logs
| where action='failed_login'
| stats count() as failures by src_ip, span(@timestamp, 5m)
| where failures > 50
| sort -failures
Source IPs with more than 50 failed logins in any 5-minute window - the kind of signal you want feeding into an alert rule.
Error spike investigation
source=app_logs
| where level='ERROR'
| timechart span=1m count() by service
A per-service error count by minute, with auto-filled gaps. Drop into Dashboards or Grafana and you have a tactical incident view in seconds.
Capacity planning
source=node_metrics
| timechart span=1h avg(cpu_pct) as cpu, avg(mem_pct) as mem by host
Hourly resource trends across hosts, suitable for the kind of "are we close to capacity" review that should happen weekly, not when the page goes off.
Using PPL with Grafana
Grafana shipped first-class PPL support in its OpenSearch data source in November 2025. Pick PPL as the query type in the panel editor; the same syntax above runs unchanged. Visualizations that benefit most are time-series panels (timechart), tables (stats with multi-dimension by), and stat panels (single-row stats).
PPL on AWS CloudWatch Logs
AWS exposes PPL in CloudWatch Logs Insights. The supported subset is smaller than OpenSearch's, and joins/subsearches are not available, but for cross-account log analysis the syntax portability is real. Build the query in OpenSearch, then port it to CloudWatch with minor edits.
Performance Tips and Best Practices
A few rules cover most of what you need to know to keep PPL queries fast.
The Calcite engine is doing real work
Apache Calcite became the default PPL engine in OpenSearch 3.3 (October 14, 2025). It does predicate pushdown, projection pruning, and join reordering - which is to say, you can write queries in the natural order and the optimizer will rearrange them. _plugins/_ppl/_explain shows the rewritten plan; check it when a query is slower than you expect.
Filter early, project narrow
Even with a smart optimizer, putting where immediately after source= and fields right after that gives Calcite less work and often hits the index in fewer round-trips. The cost is a few seconds of writing; the benefit is a query that scales.
Index pattern hygiene
Narrow source= to specific indices when you can. Wildcards like source=logs-* work but expand to every matching shard. ISM-rolled indices with date suffixes (logs-2026-05-*) let you write naturally time-bounded queries without a where on @timestamp.
Cross-cluster search
source=cluster_two:my_index | head 10
Cross-cluster PPL works when the remote cluster is registered. Authorization, latency, and version compatibility are the three things to verify; mismatched OpenSearch versions across clusters can produce surprising errors at the planner stage.
Common pitfalls
Forgetting the SQL plugin on the minimal distribution is the most common newcomer issue - PPL queries return a 404. stats ... by span(...) is technically equivalent to timechart but does not auto-fill missing buckets, so use timechart for dashboards. Joins without filtering both sides first will run, but they will run slowly. And dedup operates on rows; for multivalue field deduplication, you want mvcombine or dedup_splitvalues.
PPL Command Reference Cheat Sheet
A grouped reference for the commands you will reach for most often:
- Data retrieval:
source=,where,fields,head,dedup - Transformation:
eval,rename,parse,rex,grok,spath,convert,fillnull,flatten - Aggregation and analysis:
stats,eventstats,streamstats,top,rare,sort,chart,timechart,bin,addtotals - Multi-source (3.3+):
join,append,appendcol,appendpipe,subquery,lookup,multisearch - Multivalue (3.5+):
mvcombine,mvzip,mvfind,mvmap - Pattern matching and ML:
regex,patterns,trendline,kmeans,ad,ml
Full reference at docs.opensearch.org/latest/sql-and-ppl/ppl/commands/syntax/.
Frequently Asked Questions
Is PPL faster than SQL or DSL in OpenSearch?
With the Apache Calcite engine in 3.3+, PPL performance is competitive with SQL for analytical workloads. For pure search-relevance scoring, DSL still has the most expressive power. For ad-hoc investigation and observability, PPL is generally the fastest path from question to answer.
Does PPL work on AWS OpenSearch Service?
Yes - PPL is enabled by default on Amazon OpenSearch Service for clusters running OpenSearch 1.0+. AWS CloudWatch Logs Insights also supports a subset of PPL commands.
Can I write data with PPL?
PPL is read-only. Use the Index API or DSL bulk operations for writes.
What is the difference between stats and eventstats?
stats collapses rows into aggregated buckets - one row per group. eventstats computes the same aggregations but appends them as new fields on every original row, preserving the row count. Use eventstats when you need to compare each row to a per-group statistic (e.g., flag rows above the per-service average).
How do I save and reuse a PPL query?
Save it as a Dashboards search, schedule via Anomaly Detection, or wrap it in an alert monitor. There is no native "saved PPL" object distinct from saved searches.
Key Takeaways
- PPL is OpenSearch's pipe-chained query language, ideal for log analytics, observability, and ad-hoc investigation.
- Apache Calcite became the default engine in OpenSearch 3.3 (October 2025) and powers predicate pushdown, projection pruning, and join reordering.
- Joins, subsearches,
timechart,rex, andspatharrived in 3.3; multivalue commands (mvcombine,mvzip,mvfind,mvmap,addtotals,streamstats) shipped in 3.5 (February 2026). - Filter early, project narrow, and prefer
timechartoverstats ... by span(...)for dashboards. - AWS OpenSearch Service has PPL on by default; CloudWatch Logs supports a subset; Grafana added native PPL support in November 2025.
If you are operating an OpenSearch cluster at scale and want help tuning PPL workloads, designing log analytics pipelines, or migrating from Splunk, our OpenSearch consulting team works on these problems daily.
