Skip to main content

PPL Search

Piped Processing Language (PPL) is a query language that focuses on processing data in a sequential, step-by-step manner. PPL uses the pipe (|) operator to combine commands to find and retrieve data. It is the primary language used with observability and supports multi-data queries.

The following example shows the basic PPL syntax:

search source=<index-name> | <command_1> | <command_2> | ... | <command_n>
info
  • Although SQL and PPL use the same endpoint, the data returned in PPL is not returned in JSON as in SQL . Json return is only valid for SQL searches. Even if “format= json” is selected as query paramater, it will not return in this way.
  • In ppl search pagination is done like this. The head mean how many to display data and ”from” mean what point to start.

pagination example:

search source =`c71c5cd51f_obsnew` | head 5 from 4

Basic PPL Query​

warning
  • All the outputs below consist of examples for the purpose of informing the user.

This returns schema and datarows in a similar way to sql. In the returned answer, the parts called 'schema' represent fields, in other words columns. 'Datarow' are the values corresponding to these fields.

note
  • Alias name or index name must be specified in the back tick.
search source=`sample-index1`| head 5 from 4

Using Where and Fields​

Uses fields and where commands for the conditions.

search source=`sample-index1` | where gender='M' and age > 35| fields firstname, lastname, city, employer

Using Time and Stats​

Use the stats command to aggregate from search results.

search source=`security-auditlog-2024.05.28` | where @timestamp >= '2024-05-29 04:16:28.165000' and @timestamp <= '2024-05-29 04:16:35.432000' | stats count() by span(@timestamp, 1s) as timestamp, patterns_field | AD time_field='timestamp' category_field='patterns_field'

Common Functions​

PPL supports all SQL common functions, but also introduces few more functions (called commands) which are available in PPL only.

Example avg,sort:

search source=`sample-index1` | stats avg(balance) as average_balance by state| sort -average_balance| head 5| fields state, average_balance

Example Dedup:

Info Dedup is used to remove duplicate documents.

Alias, which is normally a document at 6, had 3 repetitive data. Using dedup, we reduced the data in the search to 4.

search source=`sample-index1`| dedup account_number

Example Eval:

We can use it to compare two different columns, it can add a new column to the search result. Normally doublebalance was not in the data as a field but it was added.

search source=`sample-index1` | eval interest_amount = account_balance * account_interest_rate | fields account_number, account_balance, account_interest_rate, interest_amount
search source=`sample-index1`| eval doublebalance = balance * 2 | fields balance, doublebalance

Example Ascending Order:

search source=`sample-index1` | sort +account_number

Example Desc Order:

search source=`c71c5cd51f_ozgenew` | sort -account_number

Example Fields:

The fields command determines which fields are displayed or excluded in a search result.

  • Sign +: Allows the specified field to be included in the search results. In fact, by default, the fields command works this way without the need for the + sign. Therefore, the + sign is usually considered optional and does the same thing even if it is not typed.

  • Sign -: Excludes the specified field from the search results.

search source=`sample-index1` | fields + @timestamp

Example Head:

To return the first N number of results in a specified search order, use the head command.

search source=`sample-index1` | fields firstname, age | head 10

example of time:

search source=`sample-index1` | where @timestamp >= '2023-01-01' AND @timestamp <= '2023-12-31' | fields firstname, age | head 10

Example Rare:

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.

search source=`sample-index1` | rare gender

Example Parse:

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

search source=`sample-index1` | parse message \"* Date: %{date}\"

Example Rename:

The ‘rename’ command is used to rename one or more fields in the search result.

search source=`sample-index1` | rename account_number as an | fields an

Example Top:

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

search source=`sample-index1`|
   rename `sample-index1` AS samp_i|
   top limit=5 samp_i