Commands

• ad
• bin
• dedup
• eval
• fields
• head
• join
• kmeans
• lookup
• parse
• rare
• regex
• rename
• rex
• sort
• spath
• stats
• timechart
• top
• where

The ad command applies the Random Cut Forest (RCF) algorithm in the ML Commons plugin on the search result returned by a PPL command. Based on the input, the plugin uses two types of RCF algorithms: fixed-in-time RCF for processing time-series data and batch RCF for processing non-time-series data.

Syntax: Fixed-in-time RCF for time-series data command

ad <shingle_size> <time_decay> <time_field>

The following table describes the parameters for the ad command when using fixed-in-time RCF for time-series data.

Syntax: Batch RCF for non-time-series data command

ad <shingle_size> <time_decay>

The following table describes the parameters for the ad command when using batch RCF for non-time-series data.

Example 1: Detecting events in New York City from taxi ridership data with time-series data

The following example trains an RCF model and uses the model to detect anomalies in the time-series ridership data:

source=nyc_taxi | fields value, timestamp | AD time_field='timestamp' | where value=10844.0

The command returns the following results.

Example 2: Detecting events in New York City from taxi ridership data with non-time-series data

The following example uses batch RCF to detect anomalies in non-time-series data:

source=nyc_taxi | fields value | AD | where value=10844.0

The command returns the following results.

The bin command groups numeric values into buckets of equal intervals, making it useful for creating histograms and analyzing data distribution. It takes a numeric or time-based field and generates a new field with values that represent the lower bound of each bucket.

Syntax

bin <field> [span=<interval>] [minspan=<interval>] [bins=<count>] [aligntime=(earliest | latest | <time-specifier>)] [start=<value>] [end=<value>]

The following table describes the parameters for the bin command.

Example 1: Basic numeric span

source=accounts | bin age span=10 | fields age, account_number | head 3;

The command returns the following results.

Example 2: Logarithmic span (log10)

source=accounts | bin balance span=log10 | fields balance | head 2;

The command returns the following results.

Example 3: Basic bins parameter

source=time_test | bin value bins=5 | fields value | head 3;

The command returns the following results.

Example 4: High bin count

source=accounts | bin age bins=21 | fields age, account_number | head 3;

The command returns the following results.

Example 5: Basic minspan

source=accounts | bin age minspan=5 | fields age, account_number | head 3;

The command returns the following results.

Example 6: Span with start/end

source=accounts | bin age span=1 start=25 end=35 | fields age | head 6;

The command returns the following results.

Example 7: Hour span

source=time_test | bin @timestamp span=1h | fields @timestamp, value | head 3;

The command returns the following results.

Example 8: Default behavior (no parameters)

source=accounts | bin age | fields age, account_number | head 3;

The command returns the following results.

Example 9: Using the bin command with string fields

source=accounts | eval age_str = CAST(age AS STRING) | bin age_str bins=3 | stats count() by age_str | sort age_str;

The command returns the following results.

The dedup (data deduplication) command removes duplicate documents defined by a field from the search result.

Syntax

dedup [int] <field-list> [keepempty=<bool>] [consecutive=<bool>]

The following table describes the parameters for the dedup command.

Example 1: Dedup by one field

To remove duplicate documents with the same gender, use the following command:

search source=accounts | dedup gender | fields account_number, gender;

The command returns the following results.

Example 2: Keep two duplicate documents

To keep two duplicate documents with the same gender, use the following command:

search source=accounts | dedup 2 gender | fields account_number, gender;

The command returns the following results.

Example 3: Keep or ignore an empty field by default

To keep two duplicate documents with a null field value, use the following command:

search source=accounts | dedup email keepempty=true | fields account_number, email;

The command returns the following results.

To remove duplicate documents with the null field value, use the following command:

search source=accounts | dedup email | fields account_number, email;

Example 4: Dedup of consecutive documents

To remove duplicates of consecutive documents, use the following command:

search source=accounts | dedup gender consecutive=true | fields account_number, gender;

The command returns the following results.

Limitations

The dedup command is not rewritten to OpenSearch query domain-specific language (DSL); it is only executed on the coordinating node.

The eval command evaluates an expression and appends its result to the search result.

Syntax

eval <field>=<expression> ["," <field>=<expression> ]...

The following table describes the parameters for the eval command.

Example 1: Create a new field

To create a new doubleAge field for each document where doubleAge is the result of age multiplied by 2, use the following command:

search source=accounts | eval doubleAge = age * 2 | fields age, doubleAge;

The command returns the following results.

Example 2: Overwrite the existing field

To overwrite the age field with age plus 1, use the following command:

search source=accounts | eval age = age + 1 | fields age;

The command returns the following results.

Example 3: Create a new field with a field defined with the eval command

To create a new field ddAge where ddAge is the result of doubleAge multiplied by 2 and doubleAge is defined in the eval command, use the following command:

search source=accounts | eval doubleAge = age * 2, ddAge = doubleAge * 2 | fields age, doubleAge, ddAge;

The command returns the following results.

Limitations

The eval command is not rewritten to OpenSearch query DSL; it is only executed on the coordinating node.

Use the fields command to keep or remove fields from a search result.

Syntax

fields [+|-] <field-list>

The following table describes the parameters for the fields command.

Example 1: Select specified fields from result

To get account_number, firstname, and lastname fields from a search result, use the following command:

search source=accounts | fields account_number, firstname, lastname;

The command returns the following results.

Example 2: Remove specified fields from a search result

To remove the account_number field from the search results, use the following command:

search source=accounts | fields account_number, firstname, lastname | fields - account_number;

The command returns the following results.

Use the head command to return the first N number of results in a specified search order.

Syntax

head [N]

The following table describes the parameters for the head command.

Example 1: Get the first 10 results

To get the first 10 results, use the following command:

search source=accounts | fields firstname, age | head;

The command returns the following results.

Example 2: Get the first N results

To get the first two results, use the following command:

search source=accounts | fields firstname, age | head 2;

The command returns the following results.

Limitations

The head command is not rewritten to OpenSearch query DSL; it is only executed on the coordinating node.

You can combine two datasets using the join command. The left side can be an index or results from piped commands, while the right side can be either an index or a subquery.

This is an experimental feature and is not recommended for use in a production environment. For updates on the progress of the feature or if you want to leave feedback, join the discussion on the OpenSearch forum.

Syntax

[join-type] join [left-alias] [right-alias] on <join-criteria> <right-dataset>

The following table describes additional requirements for the join command.

The following table describes the parameters for the join command.

The following examples use the state_country and occupation indexes.

The state_country index contains the following data.

The occupation index contains the following data.

Example 1: Join two indexes

The following example performs an inner join between two indexes:

search source = state_country
| inner join left=a right=b ON a.name = b.name occupation
| stats avg(salary) by span(age, 10) as age_span, b.country

The command returns the following results.

Example 2: Join with a subsearch

The following example performs a left join with a subsearch:

search source = state_country as a
| where country = 'USA' OR country = 'England'
| left join on a.name = b.name [
    source = occupation
    | where salary > 0
    | fields name, country, salary
    | sort salary
    | head 3
  ] as b
| stats avg(salary) by span(age, 10) as age_span, b.country

The command returns the following results.

Limitations

The join command works only when plugins.calcite.enabled is set to true.

The kmeans command applies the ML Commons plugin’s k-means algorithm to the provided PPL command’s search results.

Syntax

kmeans <cluster-number>

The following table describes the parameters for the kmeans command.

Example: Group Iris data

This example shows how to classify three Iris species (Iris setosa, Iris virginica, and Iris versicolor) based on the combination of four features measured from each sample: the length and the width of the sepals and petals:

source=iris_data | fields sepal_length_in_cm, sepal_width_in_cm, petal_length_in_cm, petal_width_in_cm | kmeans 3

The command returns the following results.

The lookup command enriches your search data by adding or replacing data from a lookup index (dimension table). You can extend index fields with values from a dimension table or append/replace values when a lookup condition is matched. As an alternative to the join command, the lookup command is more suitable for enriching the source data with a static dataset.

This is an experimental feature and is not recommended for use in a production environment. For updates on the progress of the feature or if you want to leave feedback, join the discussion on the OpenSearch forum.

Syntax

lookup <lookup-index> (<lookup-mapping-field> [as <source-mapping-field>])... [(replace | append) (<input-field> [AS <output-field>])...]

The following table describes the parameters for the lookup command.

The following examples use the workers and work_information indexes.

The workers index contains the following data.

The work_information index contains the following data.

Example 1: Look up workers and return the corresponding department

The following example looks up workers and returns the corresponding department:

source = workers | lookup work_information uid as id append department

The command returns the following results.

Example 2: Look up workers and replace their occupation and department

The following example looks up workers and replaces their occupation and department using their work_information:

source = workers | lookup work_information uid as id, name

The command returns the following results.

Example 3: Look up workers and create a new occupation field

The following example looks up workers and appends their occupation from work_information as a new field:

source = workers | lookup work_information name replace occupation as new_occupation

The command returns the following results.

Limitations

The lookup command works only when plugins.calcite.enabled is set to true.

Use the parse command to parse a text field using a regular expression and append the result to the search result.

Syntax

parse <field> <regular-expression>

The following table describes the parameters for the parse command.

The regular expression is used to match the whole text field of each document with the Java regex engine. Each named capture group in the expression becomes a new STRING field.

Example 1: Create a new field

The following example shows how to create a new field host for each document. host is the hostname after @ in the email field. Parsing a null field returns an empty string:

source=accounts | parse email '.+@(?<host>.+)' | fields email, host ;

The command returns the following results.

Example 2: Override the existing field

The following example shows how to override the existing address field with the street number removed:

source=accounts | parse address '\d+ (?<address>.+)' | fields address ;

The command returns the following results.

Example 3: Filter and sort by a cast-parsed field

The following example shows how to sort street numbers that are higher than 500 in the address field:

source=accounts | parse address '(?<streetNumber>\d+) (?<street>.+)' | where cast(streetNumber as int) > 500 | sort num(streetNumber) | fields streetNumber, street ;

The command returns the following results.

Limitations

A few limitations exist when using the parse command:

• Fields defined by parse cannot be parsed again. For example, source=accounts | parse address '\d+ (?<street>.+)' | parse street '\w+ (?<road>\w+)' ; fails to return any expressions.
• Fields defined by parse cannot be overridden with other commands. For example, when entering source=accounts | parse address '\d+ (?<street>.+)' | eval street='1' | where street='1' ; where does not match any documents since street cannot be overridden.
• The text field used by parse cannot be overridden. For example, when entering source=accounts | parse address '\d+ (?<street>.+)' | eval address='1' ;, street is not parsed since the address is overridden.
• Fields defined by parse cannot be filtered/sorted after using them in the stats command. For example, source=accounts | parse email '.+@(?<host>.+)' | stats avg(age) by host | where host=pyrami.com ; where does not match the domain listed.

Use the rare command to find the least common values of all fields in a field list. A maximum of 10 results are returned for each distinct set of values of the group-by fields.

Syntax

rare <field-list> [by-clause]

The following table describes the parameters for the rare command.

Example 1: Find the least common values in a field

To find the least common values of gender, use the following command:

search source=accounts | rare gender;

The command returns the following results.

Example 2: Find the least common values grouped by gender

To find the least common age grouped by gender, use the following command:

search source=accounts | rare age by gender;

The command returns the following results.

Limitations

The rare command is not rewritten to OpenSearch query DSL; it is only executed on the coordinating node.

The regex command filters search results by matching field values against a regular expression pattern. Only documents in which the specified field matches the pattern are included in the results.

Syntax

regex <field> = <pattern>
regex <field> != <pattern>

The following table describes the parameters for the regex command.

Example 1: Basic pattern matching

The following example shows how to filter documents where the lastname field matches names starting with uppercase letters:

source=accounts | regex lastname="^[A-Z][a-z]+$" | fields account_number, firstname, lastname;

The command returns the following results.

Example 2: Negative matching

The following example shows how to exclude documents where the lastname field ends with “son”:

source=accounts | regex lastname!=".*son$" | fields account_number, lastname;

The command returns the following results.

Example 3: Email domain matching

The following example shows how to filter documents by email domain patterns:

source=accounts | regex email="@pyrami\.com$" | fields account_number, email;

The command returns the following results.

Example 4: Complex patterns with character classes

The following example shows how to use complex regex patterns with character classes and quantifiers:

source=accounts | regex address="\d{3,4}\s+[A-Z][a-z]+\s+(Street|Lane|Court)" | fields account_number, address;

The command returns the following results.

Example 5: Case-sensitive matching

The following example demonstrates that regex matching is case-sensitive by default:

source=accounts | regex state="va" | fields account_number, state;

The command returns the following results.

| account_number | state | :— | :—

source=accounts | regex state="VA" | fields account_number, state;

The command returns the following results.

Limitations

• Field specification required: A field name must be specified in the regex command. Pattern-only syntax (for example, regex "pattern") is not currently supported.
• String fields only: The regex command currently only supports string fields. Using it on numeric or Boolean fields results in an error.

Use the rename command to rename one or more fields in the search result.

Syntax

rename <source-field> AS <target-field>["," <source-field> AS <target-field>]...

The following table describes the parameters for the rename command.

Example 1: Rename one field

To rename the account_number field as an, use the following command:

search source=accounts | rename account_number as an | fields an;

The command returns the following results.

Example 2: Rename multiple fields

To rename the account_number field as an and employer as emp, use the following command:

search source=accounts | rename account_number as an, employer as emp | fields an, emp;

The command returns the following results.

Limitations

The rename command is not rewritten to OpenSearch query DSL; it is only executed on the coordinating node.

The rex command extracts fields from a raw text field using regular expression named capture groups.

Syntax

rex [mode=<mode>] field=<field> <pattern> [max_match=<int>] [offset_field=<string>]

The following table describes the parameters for the rex command.

Example 1: Basic field extraction

The following example shows how to extract the username and domain from email addresses using named capture groups. Both extracted fields are returned as a string type:

source=accounts | rex field=email "(?<username>[^@]+)@(?<domain>[^.]+)" | fields email, username, domain | head 2;

The command returns the following results.

Example 2: Handling non-matching patterns

The following example shows the rex command returning all events, setting extracted fields to null for non-matching patterns. Extracted fields are of a string type when matches are found:

source=accounts | rex field=email "(?<user>[^@]+)@(?<domain>gmail\\.com)" | fields email, user, domain | head 2;

The command returns the following results.

Example 3: Multiple matches with max_match

The following example shows how to extract multiple words from the address field using the max_match parameter. The extracted field is returned as an array type containing string elements:

source=accounts | rex field=address "(?<words>[A-Za-z]+)" max_match=2 | fields address, words | head 3;

The command returns the following results.

Example 4: Text replacement with mode=sed

The following example shows how to replace email domains using sed mode for text substitution. The extracted field is returned as a string type:

source=accounts | rex field=email mode=sed "s/@.*/@company.com/" | fields email | head 2;

The command returns the following results.

Example 5: Using offset_field

The following example shows how to track the character positions where matches occur. Extracted fields are of a string type, and the offset_field is also of a string type:

source=accounts | rex field=email "(?<username>[^@]+)@(?<domain>[^.]+)" offset_field=matchpos | fields email, username, domain, matchpos | head 2;

The command returns the following results.

Limitations

Named Capture Group Naming:

• Group names must start with a letter and contain only letters and digits.
• For detailed Java regex pattern syntax and usage, refer to the official Java Pattern documentation.

Pattern requirements:

• The pattern must contain at least one named capture group.
• Regular capture groups (...) without names are not allowed.

Max match limit:

• The max_match parameter is subject to a configurable system limit to prevent memory exhaustion.
• When max_match=0 (unlimited) is specified, it is automatically capped at the configured limit (default: 10).
• User-specified values exceeding the configured limit result in an error.
• Users can adjust the limit using the plugins.ppl.rex.max_match.limit cluster setting. Setting this limit to a large value is not recommended because it can lead to excessive memory consumption, especially with patterns that match empty strings (for example, \d*, \w*).

Use the sort command to sort search results by a specified field.

Syntax

sort [count] <[+|-] sort-field>...

The following table describes the parameters for the sort command.

Example 1: Sort by one field

To sort all documents by the age field in ascending order, use the following command:

search source=accounts | sort age | fields account_number, age;

The command returns the following results.

Example 2: Sort by one field and return all results

To sort all documents by the age field in ascending order and specify count as 0 to return all results, use the following command:

search source=accounts | sort 0 age | fields account_number, age;

The command returns the following results.

Example 3: Sort by one field in descending order

To sort all documents by the age field in descending order, use the following command:

search source=accounts | sort - age | fields account_number, age;

Example 4: Specify the number of sorted documents to return

To sort all documents by the age field in ascending order and specify count as 2 to return two results, use the following command:

search source=accounts | sort 2 age | fields account_number, age;

The command returns the following results.

Example 5: Sort by multiple fields

To sort all documents by the gender field in ascending order and the age field in descending order, use the following command:

search source=accounts | sort + gender, - age | fields account_number, gender, age;

The command returns the following results.

The spath command allows you to extract fields from structured text data. It currently allows selecting from JSON data with JSON paths.

Syntax

spath input=<field> [output=<field>] [path=]<path>

The following table describes the parameters for the spath command.

Example 1: Simple field extraction

The simplest spath is to extract a single field. The following example extracts n from the doc field of type text:

source=structured | spath input=doc_n n | fields doc_n n;

The command returns the following results.

Example 2: Lists and nesting

The following example demonstrates additional JSON path use cases, such as traversing nested fields and extracting list elements:

source=structured | spath input=doc_list output=first_element list{0} | spath input=doc_list output=all_elements list{} | spath input=doc_list output=nested nest_out.nest_in | fields doc_list first_element all_elements nested;

Example 3: Sum of inner elements

The following example shows how to extract an inner field and generate statistics on it, using the documents from the first example. It also demonstrates that spath always returns strings for inner types:

source=structured | spath input=doc_n n | eval n=cast(n as int) | stats sum(n) | fields `sum(n)`;

The command returns the following results.

Example 4: Escaped paths

spath can escape paths with strings to accept any path that json_extract does. This includes escaping complex field names as array components:

source=structured | spath output=a input=doc_escape "['a fancy field name']" | spath output=b input=doc_escape "['a.b.c']" | fields a b;

The command returns the following results.

Use the stats command to aggregate data from search results.

The following table lists the aggregation functions and also indicates how each one handles null or missing values.

Syntax

stats <aggregation>... [by-clause]...

The following table describes the parameters for the stats command.

Example 1: Calculate the average value of a field

To calculate the average age of all documents, use the following command:

search source=accounts | stats avg(age);

The command returns the following results.

Example 2: Calculate the average value of a field by group

To calculate the average age grouped by gender, use the following command:

search source=accounts | stats avg(age) by gender;

The command returns the following results.

Example 3: Calculate the average and sum of a field by group

To calculate the average and sum of age grouped by gender, use the following command:

search source=accounts | stats avg(age), sum(age) by gender;

The command returns the following results.

Example 4: Calculate the maximum value of a field

To calculate the maximum age, use the following command:

search source=accounts | stats max(age);

Example 5: Calculate the maximum and minimum value of a field by group

To calculate the maximum and minimum age values grouped by gender, use the following command:

search source=accounts | stats max(age), min(age) by gender;

The command returns the following results.

The timechart command creates a time-based aggregation of data. It groups data by time intervals and optionally by a field, then applies an aggregation function to each group. The results are returned in an unpivoted format with separate rows for each time-field combination.

Syntax

timechart [timefield=<field_name>] [span=<time_interval>] [limit=<number>] [useother=<boolean>] <aggregation_function> [by <field>]

The following table describes the parameters for the timechart command.

Example 1: Count events by hour

The following example counts events for each hour and groups them by host:

source=events | timechart span=1h count() by host;

The command returns the following results.

Example 2: Calculate average number of packets by minute

The following example calculates the average packets for each minute without grouping by any field:

source=events | timechart span=1m avg(packets);

The command returns the following results.

Example 3: Calculate average number of packets by every 20 minutes and status

The following example calculates the average number of packets for every 20 minutes and groups them by status:

source=events | timechart span=20m avg(packets) by status;

The command returns the following results.

Example 4: Using the limit parameter with the count() function

When there are many distinct values in the “by” field, the timechart command displays the top values based on the limit parameter and groups the rest into an “OTHER” category. The following query displays the top 2 hosts with the highest count values and groups the remaining hosts into an “OTHER” category:

source=events | timechart span=1m limit=2 count() by host;

Example 5: Using limit=0 with count() to show all values

To display all distinct values without any limit, set limit=0 and use the following command:

source=events_many_hosts | timechart span=1h limit=0 count() by host;

The command returns the following results.

Example 6: Using useother=false with the count() function

The following example displays the top 10 hosts without the OTHER category (useother=false):

source=events_many_hosts | timechart span=1h useother=false count() by host;

The command returns the following results.

Example 7: Using the limit parameter with the useother parameter and the avg() function

The following example displays the top 3 hosts with the OTHER category (default is useother=true):

source=events_many_hosts | timechart span=1h limit=3 avg(cpu_usage) by host;

The command returns the following results.

Example 8: Handling null values in the “by” field

The following example shows how null values in the “by” field are treated as a separate category. The dataset events_null has 1 entry that does not have a host field. It is put into a separate “NULL” category because the defaults for usenull and nullstr are true and "NULL", respectively:

source=events_null | timechart span=1h count() by host;

The command returns the following results.

Example 9: Calculate packets per second rate

The following example calculates the per-second packet rate for network traffic data using the per_second() function:

source=events | timechart span=30m per_second(packets) by host;

The command returns the following results.

Limitations

• Only a single aggregation function is supported per timechart command.
• The bins parameter and other bin options are not supported in the timechart command. Use the span parameter to control time intervals.

Use the top command to find the most common values of all fields in the field list.

Syntax

top [N] <field-list> [by-clause]

The following table describes the parameters for the top command.

Example 1: Find the most common values in a field

To find the most common genders, use the following command:

search source=accounts | top gender;

The command returns the following results.

Example 2: Find the most common value in a field

To find the most common gender, use the following command:

search source=accounts | top 1 gender;

The command returns the following results.

Example 3: Find the most common values grouped by gender

To find the most common age grouped by gender, use the following command:

search source=accounts | top 1 age by gender;

The command returns the following results.

Limitations

The top command is not rewritten to OpenSearch query DSL; it is only executed on the coordinating node.

Use the where command with a Boolean expression to filter the search result. The where command only returns the result when the Boolean expression evaluates to true.

Syntax

where <boolean-expression>

The following table describes the parameters for the where command.

Example: Filter the result set with a condition

To get all documents from the accounts index where account_number is 1 or gender is F, use the following command:

search source=accounts | where account_number=1 or gender="F" | fields account_number, gender;

The command returns the following results.