Ppl Reference

PPL Language Reference

Piped Processing Language (PPL) for OpenSearch. Queries use pipe-delimited syntax starting with source=<index> and chaining commands with |. Grammar source: https://github.com/opensearch-project/sql

Field Name Escaping

Field names containing dots, @, or other special characters must be enclosed in backticks:

`attributes.gen_ai.operation.name`, `attributes.gen_ai.usage.input_tokens`, `status.code`, `events.attributes.exception.type`, `@timestamp`

Critical for OTel attribute fields which use dotted naming conventions.

API Endpoints

POST /_plugins/_ppl              # Execute query
POST /_plugins/_ppl/_explain     # Get execution plan

Body: {"query": "<ppl_query>"}

Commands

Core

source=<index-pattern> — Start a query. Alias: search source=.

source=otel-v1-apm-span-* | head 10

where <condition> — Filter results. Operators: =, !=, <, >, <=, >=, AND, OR, NOT, LIKE, IN, BETWEEN, IS NULL, IS NOT NULL.

source=otel-v1-apm-span-* | where `status.code` = 2 | head 10

fields [+|-] <field-list> — Select (+) or exclude (-) fields. Default is +.

source=otel-v1-apm-span-* | fields traceId, spanId, serviceName, durationInNanos | head 10

stats <aggregation>... [by <field-list>] — Aggregate. Functions: count(), sum(), avg(), max(), min(), var_samp(), var_pop(), stddev_samp(), stddev_pop(), distinct_count(), percentile(field, pct), earliest(), latest(), list(), values(), first(), last().

source=otel-v1-apm-span-* | stats count() as span_count, avg(durationInNanos) as avg_duration by serviceName

sort [+|-] <field> — + ascending (default), - descending.

source=otel-v1-apm-span-* | sort - durationInNanos | head 10

head [N] — Limit (default N=10).

source=otel-v1-apm-span-* | head 5

eval <field> = <expression> — Compute new fields.

source=otel-v1-apm-span-* | eval duration_ms = durationInNanos / 1000000 | fields traceId, serviceName, duration_ms | sort - duration_ms | head 10

dedup [N] <field-list> [keepempty=<bool>] [consecutive=<bool>] — Remove duplicates.

Caveat: dedup may throw a ClassCastException on fields with mixed types. Ensure consistent type.

source=otel-v1-apm-span-* | dedup serviceName | fields serviceName

rename <old> AS <new> — Rename fields.

source=otel-v1-apm-span-* | rename serviceName as service, durationInNanos as duration | fields traceId, service, duration | head 10

top [N] <field> [by <group>] — Most frequent values.

source=otel-v1-apm-span-* | top 5 serviceName

rare <field> [by <group>] — Least frequent values.

source=otel-v1-apm-span-* | rare `attributes.gen_ai.operation.name`

table <field-list> — Tabular display (alias for fields in some contexts).

source=otel-v1-apm-span-* | where `status.code` = 2 | table traceId, spanId, serviceName, name | head 10

reverse — Reverse the order of results.

source=otel-v1-apm-span-* | sort startTime | head 20 | reverse

Time-Series

timechart span=<interval> <aggregation>... [by <field>] — Time-bucketed aggregation. Rate functions: per_second(), per_minute(), per_hour(), per_day().

source=otel-v1-apm-span-* | timechart span=5m count() as span_count by serviceName
source=otel-v1-apm-span-* | timechart span=1h per_minute(count()) as spans_per_min by serviceName

chart <aggregation>... by <field> — General charting.

source=otel-v1-apm-span-* | chart avg(durationInNanos) by serviceName

bin / span(<field>, <interval>) — Bucket values. See Span Expression section below.

source=otel-v1-apm-span-* | stats count() by span(durationInNanos, 1000000000)

trendline [sort <field>] sma(<period>, <field>) [as <alias>] — Simple moving average.

source=otel-v1-apm-span-* | trendline sort startTime sma(10, durationInNanos) as avg_duration | fields startTime, durationInNanos, avg_duration | head 50

streamstats <aggregation>... [by <field>] — Running cumulative stats.

Caveat: Processes all rows in memory. Always add | head N before streamstats to limit data volume.

source=otel-v1-apm-span-* | sort startTime | head 50 | streamstats count() as running_count, sum(`attributes.gen_ai.usage.input_tokens`) as cumulative_tokens | fields startTime, running_count, cumulative_tokens

eventstats <aggregation>... [by <field>] — Add aggregation as new field without collapsing rows.

Caveat: Processes all rows in memory. Always add | head N before eventstats.

source=otel-v1-apm-span-* | head 100 | eventstats avg(durationInNanos) as avg_svc_duration by serviceName | eval deviation = durationInNanos - avg_svc_duration | fields traceId, serviceName, durationInNanos, avg_svc_duration, deviation | sort - deviation | head 20

Parse / Extract

parse <field> '<regex-with-named-groups>' — Extract fields via regex.

Caveat: May silently drop extracted fields on some OpenSearch versions. Use grok or rex if parse misbehaves.

source=logs-otel-v1-* | parse body '(?P<level>\w+): (?P<msg>.+)' | fields level, msg | head 10

grok <field> '<grok-pattern>' — Extract via named Grok patterns.

Caveat: Processes all rows in memory. Always add | head N before grok.

source=logs-otel-v1-* | head 100 | grok body '%{LOGLEVEL:level} %{GREEDYDATA:message}' | fields level, message | head 10

rex field=<field> '<regex>' — Splunk-compatible regex extract.

source=logs-otel-v1-* | rex field=body '(?<statuscode>\d{3})' | fields statuscode, body | head 10

regex — Filter results using a regular expression match on a field (used within where).

source=logs-otel-v1-* | where body like '%error%' | fields traceId, body, severityText | head 10

patterns <field> — Auto-cluster similar log messages.

source=logs-otel-v1-* | patterns body | fields body, patterns_field | head 20

spath input=<field> [path=<path>] [output=<field>] — Extract from structured data (JSON/XML).

Note: Verify the target field exists (describe <index>) before using spath.

source=otel-v1-apm-span-* | where isnotnull(`attributes.gen_ai.tool.name`) | spath input=`attributes.gen_ai.tool.name` | head 10

Join / Lookup / Subquery

join left=<alias> right=<alias> ON <condition> <right-source> — Types: inner, left, right, cross.

source=otel-v1-apm-span-* | join left=s right=l ON s.traceId = l.traceId logs-otel-v1-* | fields s.spanId, s.name, l.severityText, l.body | head 10

lookup <lookup-index> <match-field> [AS <alias>] [OUTPUT <field-list>] — Enrich from another index.

Note: The service map index (otel-v2-apm-service-map) uses sourceNode/targetNode, not serviceName.

source=otel-v1-apm-span-* | lookup otel-v2-apm-service-map serviceName AS `sourceNode` | fields serviceName, `targetNode`, durationInNanos | head 10

graphlookup <index> connectFromField=<f> connectToField=<f> [maxDepth=<N>] — Graph traversal.

Caveat: Limited support in OpenSearch 3.x PPL. Test before relying on this.

source=otel-v2-apm-service-map | graphlookup otel-v2-apm-service-map connectFromField=`destination.domain` connectToField=serviceName maxDepth=3 as dependencies | head 10

where <field> IN [ source=<index> | ... | fields <field> ] — Subquery filter.

source=otel-v1-apm-span-* | where traceId IN [ source=otel-v1-apm-span-* | where `status.code` = 2 | fields traceId ] | fields traceId, spanId, serviceName, name | head 20

append [ source=<index> | ... ] — Append rows from another query.

source=otel-v1-apm-span-* | stats count() as cnt by serviceName | append [ source=logs-otel-v1-* | stats count() as cnt by `resource.attributes.service.name` ] | head 20

appendcol [ <commands> ] — Append columns from a sub-pipeline.

Caveat: source= is NOT valid inside appendcol[] — it operates on the current result set. Use append if you need data from another index.

source=otel-v1-apm-span-* | stats count() as span_count, avg(durationInNanos) as avg_dur | appendcol [ stats max(durationInNanos) as max_dur ]

appendpipe [ <commands> ] — Append results of sub-pipeline on current data.

source=otel-v1-apm-span-* | stats count() as cnt by serviceName | appendpipe [ stats sum(cnt) as total ]

Transform

fillnull [with <value>] [<field-list>] — Replace nulls.

Caveat: Backtick-quoted field names are NOT supported in fillnull field list. Use eval to rename dotted fields first, or apply without a field list.

source=otel-v1-apm-span-* | eval tokens = `attributes.gen_ai.usage.input_tokens` | fillnull with 0 tokens

flatten <field> — Flatten nested fields to top level.

source=otel-v1-apm-span-* | flatten events | head 10

expand <field> / mvexpand <field> — Expand array/multi-value fields into separate rows.

source=otel-v1-apm-span-* | expand events | fields traceId, spanId, events | head 20
source=otel-v1-apm-span-* | mvexpand events | fields traceId, spanId, events | head 20

transpose [<N>] — Pivot rows into columns.

source=otel-v1-apm-span-* | stats count() as cnt by serviceName | transpose

mvcombine <field> — Combine rows with same key into multi-value field.

source=otel-v1-apm-span-* | fields traceId, serviceName | mvcombine serviceName | head 10

nomv <field> — Multi-value → single-value.

Caveat: Only works on string arrays. Use flatten or expand for nested object arrays.

source=otel-v1-apm-span-* | nomv events | fields traceId, events | head 10

convert <function>(<field>) [as <alias>] — Type conversion. Functions: auto(), num(), ip(), ctime(), dur2sec(), mktime(), mstime(), rmcomma(), rmunit(), memk(), none().

source=otel-v1-apm-span-* | eval duration_str = CAST(durationInNanos AS STRING) | convert num(duration_str) as duration_num | fields traceId, duration_num | head 10

eval <field> = replace(<field>, '<old>', '<new>') — Replace values via replace() string function.

source=logs-otel-v1-* | eval severityText = replace(severityText, 'ERROR', 'ERR') | fields severityText, body | head 10

Totals

addcoltotals [<field-list>] — Add summary row with column totals.

source=otel-v1-apm-span-* | stats count() as cnt by serviceName | addcoltotals

addtotals [row=<bool>] [col=<bool>] [<field-list>] — Add row with sum of numeric fields.

source=otel-v1-apm-span-* | stats sum(`attributes.gen_ai.usage.input_tokens`) as input_tok, sum(`attributes.gen_ai.usage.output_tokens`) as output_tok by serviceName | addtotals

ML

ad [time_field=<field>] [number_of_trees=<N>] [shingle_size=<N>] [time_zone=<tz>] — Anomaly detection.

Note: ad takes no positional field argument; it auto-detects from preceding stats/eval output.

source=otel-v1-apm-span-* | where durationInNanos > 0 | ad time_field=startTime number_of_trees=100 time_zone="UTC" | head 50

kmeans [centroids=<N>] [iterations=<N>] [distance_type=<type>] — K-means clustering.

Note: No positional field args; operates on all numeric fields from preceding output. Use fields to control input.

source=otel-v1-apm-span-* | where durationInNanos > 0 | fields traceId, serviceName, durationInNanos | kmeans centroids=3 | fields traceId, serviceName, durationInNanos, ClusterID | head 30

ml action=<algorithm> — General ML command. Supported: kmeans, ad.

Note: ml action=rcf is NOT valid in OpenSearch 3.x. Use ad directly for Random Cut Forest.

source=otel-v1-apm-span-* | where durationInNanos > 0 | ml action=kmeans centroids=3 | head 50

System Commands

Caveat: describe and show datasources are standalone top-level commands, NOT pipe commands. They cannot appear after |.

• ✓ describe my-index-*
• ✗ source=my-index-* | describe

describe otel-v1-apm-span-*       # Inspect index mapping/field types
show datasources                   # List PPL data sources

Display

fieldformat <field> = <format-expr> — Format display without changing data.

source=otel-v1-apm-span-* | eval duration_ms = durationInNanos / 1000000 | fieldformat duration_ms = CONCAT(CAST(duration_ms AS STRING), ' ms') | fields traceId, serviceName, duration_ms | head 10

Span Expression

Buckets numeric or datetime values into intervals. Used with stats, timechart, chart.

Syntax: span(<field>, <interval>)

Time units: ms, s, m (minutes), h, d, w, M (months), q (quarters), y.

source=otel-v1-apm-span-* | stats count() as span_count, avg(durationInNanos) as avg_duration by span(startTime, 1h)
source=otel-v1-apm-span-* | stats count() by span(durationInNanos, 1000000000)   # numeric, plain number

Functions

Aggregation (for stats, eventstats, streamstats, timechart, chart)

source=otel-v1-apm-span-* | stats count() as total, avg(durationInNanos) as avg_ns, percentile(durationInNanos, 95) as p95_ns, distinct_count(serviceName) as services

Note: corr() is NOT supported in OpenSearch 3.x PPL. Use covar_samp with separate stddev calls to approximate Pearson correlation.

source=otel-v1-apm-span-* | where `attributes.gen_ai.usage.input_tokens` > 0 | stats covar_samp(`attributes.gen_ai.usage.input_tokens`, durationInNanos) as token_duration_covar

Condition

source=otel-v1-apm-span-* | eval status_label = case(`status.code` = 0, 'UNSET', `status.code` = 1, 'OK', `status.code` = 2, 'ERROR') | stats count() by status_label

Conversion

source=otel-v1-apm-span-* | eval duration_ms = CAST(durationInNanos AS DOUBLE) / 1000000.0 | fields traceId, serviceName, duration_ms | sort - duration_ms | head 10

Datetime

source=otel-v1-apm-span-* | where startTime > DATE_SUB(NOW(), INTERVAL 1 HOUR) | stats count() as recent_spans by serviceName

String

source=logs-otel-v1-* | eval body_lower = lower(body) | where body_lower like '%exception%' | eval short_body = left(body, 200) | fields traceId, severityText, short_body | head 10

Math

source=otel-v1-apm-span-* | eval duration_ms = round(durationInNanos / 1000000.0, 2) | where duration_ms > 0 | fields traceId, serviceName, duration_ms | sort - duration_ms | head 10

Collection / Multi-Value

source=otel-v1-apm-span-* | eval tokens = array(`attributes.gen_ai.usage.input_tokens`, `attributes.gen_ai.usage.output_tokens`) | fields traceId, tokens | head 10

JSON

source=otel-v1-apm-span-* | where json_valid(`attributes.gen_ai.tool.call.arguments`) | eval tool_args = json_extract(`attributes.gen_ai.tool.call.arguments`, '$') | fields traceId, `attributes.gen_ai.tool.name`, tool_args | head 10

Crypto / IP / System

source=otel-v1-apm-span-* | eval trace_hash = md5(traceId) | fields traceId, trace_hash | head 5
source=otel-v1-apm-span-* | eval type_of_duration = typeof(durationInNanos) | fields traceId, durationInNanos, type_of_duration | head 5

Relevance (Full-Text Search)

source=logs-otel-v1-* | where match(body, 'timeout error') | fields traceId, severityText, body | head 10

Expressions / Operators

• Arithmetic: +, -, *, /
• Comparison: =, != or <>, <, >, <=, >=
• Logical: AND, OR, NOT, XOR

source=otel-v1-apm-span-* | eval duration_ms = durationInNanos / 1000000, total_tokens = `attributes.gen_ai.usage.input_tokens` + `attributes.gen_ai.usage.output_tokens` | where duration_ms > 1000 AND total_tokens > 0 | fields traceId, serviceName, duration_ms, total_tokens | head 10

References

• Official PPL docs — Fetch if queries fail due to OpenSearch version differences or new syntax.