Supported PPL commands

 fields field1, field2

Filters the data based on the conditions that you specify.

 where field1="success"
| where field2 != "i -023fe0a90929d8822"
| fields field3, col4, col5, col6
| head 1000

Performs aggregations and calculations.

stats count(),
      count(`field1`),
      min(`field1`),
      max(`field1`),
      avg(`field1`)
by field2
| head 1000

Extracts a regular expression (regex) pattern from a string and displays the extracted pattern. The extracted pattern can be further used to create new fields or filter data.

parse `field1` ".*/(?<field2>[^/]+$)"
| where field2 = "requestId"
| fields field2, `field2`
| head 1000

Extracts log patterns from a text field and appends the results to the search result. Grouping logs by their patterns makes it easier to aggregate stats from large volumes of log data for analysis and troubleshooting.

patterns new_field='no_numbers' pattern='[0-9]' message
| fields message, no_numbers

Sort the displayed results by a field name. Use sort -FieldName to sort in descending order.

stats count(),
      count(`field1`),
      min(`field1`) as field1Alias,
      max(`field1`),
      avg(`field1`)
by field2
| sort -field1Alias
| head 1000

Modifies or processes the value of a field and stores it in a different field. This is useful to mathematically modify a column, apply string functions to a column, or apply date functions to a column.

eval field2 = `field1` * 2
| fields field1, field2
| head 20

Renames one or more fields in the search result.

rename field2 as field1
| fields field1

Limits the displayed query results to the frst N rows.

 fields `@message`
| head 20

Parses a text field with a grok pattern based on regular expression, and appends the results to the search result.

 grok email '.+@%{HOSTNAME:host}'
| fields email

Finds the most frequent values for a field.

 top 2 Field1 by Field2

Removes duplicate entries based on the fields that you specify.

dedup field1
| fields field1, field2, field3

Joins two datasets together.

source=customer
| join ON c_custkey = o_custkey orders
| head 10

Enriches your search data by adding or replacing data from a lookup index (dimension table). You can extend fields of an index with values from a dimension table, append or replace values when lookup condition is matched

where orderType = 'Cancelled'
| lookup account_list, mkt_id AS mkt_code
  replace amount, account_name as name
| stats count(mkt_code), avg(amount)
  by name

where id in [
  subquery source=users
  | where user in [
    subquery source=actions
    | where action="login"
    | fields user
  ]
  | fields uid
]

Finds the least frequent values of all fields in the field list.

 rare Field1 by Field2

 trendline sma(2, field1) as field1Alias

Supported (except count())

 eventstats sum(field1) by field2

Flattens a field, The field must be of this type: struct<?,?> or array<struct<?,?>>

 source=table | flatten field1

where field1 != 200
| fieldsummary includefields=field1 nulls=true

fields field1
| eval field2=field1
| fillnull value=0 field1

expand employee
| stats max(salary) as max
  by state, company

Gets detailed information about the structure and metadata of tables, schemas, and catalogs

 describe schema.table

PPL string functions

(CONCAT, CONCAT_WS, LENGTH, LOWER, LTRIM, POSITION, REVERSE, RIGHT, RTRIM, SUBSTRING, TRIM, UPPER)

Built-in functions in PPL that can manipulate and transform string and text data within PPL queries. For example, converting case, combining strings, extracting parts, and cleaning text.

eval col1Len = LENGTH(col1)
| fields col1Len

PPL date and time functions

(DAY, DAYOFMONTH, DAY_OF_MONTH,DAYOFWEEK, DAY_OF_WEEK, DAYOFYEAR, DAY_OF_YEAR, DAYNAME, FROM_UNIXTIME, HOUR, HOUR_OF_DAY, LAST_DAY, LOCALTIMESTAMP, LOCALTIME, MAKE_DATE, MINUTE, MINUTE_OF_HOUR, MONTH, MONTHNAME, MONTH_OF_YEAR, NOW, QUARTER, SECOND, SECOND_OF_MINUTE, SUBDATE, SYSDATE, TIMESTAMP, UNIX_TIMESTAMP, WEEK, WEEKDAY, WEEK_OF_YEAR, DATE_ADD, DATE_SUB, TIMESTAMPADD, TIMESTAMPDIFF, UTC_TIMESTAMP, CURRENT_TIMEZONE)

Built-in functions for handling and transforming date and timestamp data in PPL queries. For example, date_add, date_format, datediff, and current_date.

eval newDate = ADDDATE(DATE('2020-08-26'), 1)
| fields newDate

PPL condition functions

(EXISTS, IF, IFNULL, ISNOTNULL, ISNULL, NULLIF)

Built-in functions that perform calculations on multiple rows to produce a single summarized value. For example, sum, count, avg, max, and min.

eval field2 = isnull(col1)
| fields field2, col1, field3  

PPL mathematical functions

(ABS, ACOS, ASIN, ATAN, ATAN2, CEIL, CEILING, CONV, COS, COT, CRC32, DEGREES, E, EXP, FLOOR, LN, LOG, LOG2, LOG10, MOD, PI. POW, POWER, RADIANS, RAND, ROUND, SIGN, SIN, SQRT, CBRT)

Built-in functions for performing mathematical calculations and transformations in PPL queries. For example: abs (absolute value), round (rounds numbers), sqrt (square root), pow (power calculation), and ceil (rounds up to nearest integer).

eval field2 = ACOS(col1)
| fields col1

PPL expressions

(Arithmetic operators (+, -, *), Predicate operators (>. <, IN))

Built-in functions for expressions, particularly value expressions, return a scalar value. Expressions have different types and forms.

where age > (25 + 5)
| fields age  

PPL IP address functions

(CIDRMATCH)

Built-in functions for handling IP addresses such as CIDR.

where cidrmatch(ip, '***********/24')
| fields ip 

PPL JSON functions

(ARRAY_LENGTH, ARRAY_LENGTH, JSON, JSON_ARRAY, JSON_EXTRACT, JSON_KEYS, JSON_OBJECT, JSON_VALID, TO_JSON_STRING)

Built-in functions for handling JSON including arrays, extracting, and validation.

eval `json_extract('{"a":"b"}', '$.a')` = json_extract('{"a":"b"}', '$a')

PPL Lambda functions

(EXISTS, FILTER, REDUCE, TRANSFORM)

Built-in functions for handling JSON including arrays, extracting, and validation.

eval array = json_array(1, -1, 2),
     result = filter(array, x -> x > 0)
| fields result

PPL cryptographic hash functions

(MD5, SHA1, SHA2)

Built-in functions that allow you to generate unique fingerprints of data, which can be used for verification, comparison, or as part of more complex security protocols.

eval `MD5('hello')` = MD5('hello')
| fields `MD5('hello')`