Simple Log Service:SPL instructions

.let

Defines a named dataset as the input for subsequent SPL expressions. For detailed information on SPL datasets, see SPL datasets.

Syntax

.let <dataset>=<spl-expr>

Parameter description

Examples

• Example 1: Filter and classify access logs by status codes before exporting them.

  • SPL statement

    -- Define the processing result of SPL as a named dataset src, which is used as the input of subsequent SPL expressions
    .let src = * 
    | where status=cast(status as BIGINT);
    
    -- Use the named dataset src as the input, filter the data whose status field is 5xx, and define the dataset err. The dataset is not exported
    .let err = $src
    | where status >= 500
    | extend msg='ERR';
    
    -- Use the named dataset src as the input, filter the data whose status field is 2xx, and define the dataset ok. The dataset is not exported
    .let ok = $src
    | where status >= 200 and status < 300
    | extend msg='OK';
    
    -- Export the named datasets err and ok
    $err;
    $ok;

• Input data

  # Entry 1
  status: '200'
  body: 'this is a test'
  
  # Entry 2
  status: '500'
  body: 'internal error'
  
  # Entry 3
  status: '404'
  body: 'not found'

• Output data

  # Entry 1: The dataset is err
  status: '500'
  body: 'internal error'
  msg: 'ERR'
  
  # Entry 2: The dataset is ok
  status: '200'
  body: 'this is a test'
  msg: 'OK'

project

Retains fields that match a specified pattern and renames specified fields. All expressions related to retaining fields are executed before those related to renaming during the execution of the instruction.

Syntax

| project -wildcard <field-pattern>, <output>=<field>, ...

Parameter description

Examples

• Example 1: Retain a field.

  * | project level, err_msg

• Example 2: Rename a field.

  * | project log_level=level, err_msg

• Example 3: Retain the field that exactly matches __tag__:*.

  * | project "__tag__:*"

project-away

Removes fields that match a specified pattern, retaining all other fields unchanged.

Syntax

| project-away -wildcard <field-pattern>, ...

Parameter description

project-rename

Renames specified fields while retaining all other fields unchanged.

Syntax

| project-rename <output>=<field>, ...

Parameter description

Example

Rename specified fields.

* | project-rename log_level=level, log_err_msg=err_msg

expand-values

Expands a first-level JSON object in a specified field, resulting in multiple output entries.

Syntax

| expand-values -path=<path> -limit=<limit> -keep <field> as <output>

Parameter description

Examples

• Example 1: Expand an array to return multiple result entries.

  • SPL statement

    * | expand-values y

  • Input data

    x: 'abc'
    y: '[0,1,2]'

  • Output data, including three entries

    # Entry 1
    x: 'abc'
    y: '0'
    
    # Entry 2
    x: 'abc'
    y: '1'
    
    # Entry 3
    x: 'abc'
    y: '2'

• Example 2: Expand a dictionary to return multiple result entries.

  • SPL statement

    * | expand-values y

  • Input data

    x: 'abc'
    y: '{"a": 1, "b": 2}'

  • Output data, including two entries

    # Entry 1
    x: 'abc'
    y: '{"a": 1}'
    
    # Entry 2
    x: 'abc'
    y: '{"b": 2}'

• Example 3: Expand content that matches a specified JSONPath expression and export to a new field.

  • SPL statement

    * | expand-values -keep content -path='$.body' as body

  • Input data

    content: '{"body": [0, {"a": 1, "b": 2}]}'

  • Output data, including two entries

    # Entry 1
    content: '{"body": [1, 2]}'
    body: '0'
    
    # Entry 2
    content: '{"body": [1, 2]}'
    body: '{"a": 1, "b": 2}'

extend

Generates fields based on SQL expression-based data calculations. For a list of supported SQL functions, see SQL functions supported by SPL.

Syntax

| extend <output>=<sql-expr>, ...

Parameter description

Examples

• Example 1: Apply a computation expression.

  * | extend Duration = EndTime - StartTime

• Example 2: Utilize a regular expression.

  * | extend server_protocol_version=regexp_extract(server_protocol, '\d+')

• Example 3: Extract JSONPath content and convert a field's data type.

  • SPL statement

    *
    | extend a=json_extract(content, '$.body.a'), b=json_extract(content, '$.body.b')
    | extend b=cast(b as BIGINT)

  • Input data

    content: '{"body": {"a": 1, "b": 2}}'

  • Output data

    content: '{"body": {"a": 1, "b": 2}}'
    a: '1'
    b: 2

where

Filters data based on SQL expression-based calculations. Data matching the specified SQL expression is retained. For a list of SQL functions supported by the where instruction, see SQL functions supported by SPL.

Syntax

| where <sql-expr>

Parameter description

Examples

• Example 1: Filter data based on field content.

  * | where userId='123'

• Example 2: Filter data using a regular expression that matches based on a field name.

  * | where regexp_like(server_protocol, '\d+')

• Example 3: Convert a field's data type to match all server error data.

  * | where cast(status as BIGINT) >= 500

parse-regexp

Extracts information matching groups in a specified regular expression from a field.

Syntax

| parse-regexp <field>, <pattern> as <output>, ...

Parameter description

Examples

• Example 1: Use exploratory match mode.

  • SPL statement

    *
    | parse-regexp content, '(\S+)' as ip -- Generate the ip: 10.0.0.0 field.
    | parse-regexp content, '\S+\s+(\w+)' as method -- Generate the method: GET field.

  • Input data

    content: '10.0.0.0 GET /index.html 15824 0.043'

  • Output data

    content: '10.0.0.0 GET /index.html 15824 0.043'
    ip: '10.0.0.0'
    method: 'GET'

• Example 2: Use full pattern match mode with unnamed capturing groups in the regular expression.

  • SPL statement

    * | parse-regexp content, '(\S+)\s+(\w+)' as ip, method

  • Input data

    content: '10.0.0.0 GET /index.html 15824 0.043'

  • Output data

    content: '10.0.0.0 GET /index.html 15824 0.043'
    ip: '10.0.0.0'
    method: 'GET'

parse-csv

Extracts CSV-formatted information from a specified field.

Syntax

| parse-csv -delim=<delim> -quote=<quote> -strict <field> as <output>, ...

Parameter description

Examples

• Example 1: Match data in simple mode.

  • SPL statement

    * | parse-csv content as x, y, z

  • Input data

    content: 'a,b,c'

  • Output data

    content: 'a,b,c'
    x: 'a'
    y: 'b'
    z: 'c'

• Example 2: Use double quotes as the quote character to match data containing special characters.

  • SPL statement

    * | parse-csv content as ip, time, host

  • Input data

    content: '192.168.0.100,"10/Jun/2019:11:32:16,127 +0800",example.aliyundoc.com'

  • Output data

    content: '192.168.0.100,"10/Jun/2019:11:32:16,127 +0800",example.aliyundoc.com'
    ip: '192.168.0.100'
    time: '10/Jun/2019:11:32:16,127 +0800'
    host: 'example.aliyundoc.com'

• Example 3: Use a combination of multiple characters as separators.

  • SPL statement

    * | parse-csv -delim='||' content as time, ip, req

  • Input data

    content: '05/May/2022:13:30:28||127.0.0.1||POST /put?a=1&b=2'

  • Output data

    content: '05/May/2022:13:30:28||127.0.0.1||POST /put?a=1&b=2'
    time: '05/May/2022:13:30:28'
    ip: '127.0.0.1'
    req: 'POST /put?a=1&b=2'

parse-json

Extracts first-layer JSON information from a specified field.

Syntax

| parse-json -mode=<mode> -path=<path> -prefix=<prefix> <field>

Parameter description

Examples

• Example 1: Extract all keys and values from the y field.

  • SPL statement

    * | parse-json y

  • Input data

    x: '0'
    y: '{"a": 1, "b": 2}'

  • Output data

    x: '0'
    y: '{"a": 1, "b": 2}'
    a: '1'
    b: '2'

• Example 2: Extract the value of the body key from the content field as different fields.

  • SPL statement

    * | parse-json -path='$.body' content

  • Input data

    content: '{"body": {"a": 1, "b": 2}}'

  • Output data

    content: '{"body": {"a": 1, "b": 2}}'
    a: '1'
    b: '2'

• Example 3: Extract information in preserve mode, retaining the original value for existing fields.

  • SPL statement

    * | parse-json -mode='preserve' y

  • Input data

    a: 'xyz'
    x: '0'
    y: '{"a": 1, "b": 2}'

  • Output data

    x: '0'
    y: '{"a": 1, "b": 2}'
    a: 'xyz'
    b: '2'

parse-kv

Extracts key-value pair information from a specified field.

Syntax

| parse-kv -mode=<mode> -prefix=<prefix> -regexp <field>, <pattern>

Parameter

Examples

• Example 1: Extract key-value pairs in regular extraction mode when delimiters between pairs and keys and values vary.

  • SPL statement

    * | parse-kv -regexp content, '([^&?]+)(?:=|:)([^&?]+)'

  • Input data

    content: 'k1=v1&k2=v2?k3:v3'
    k1: 'xyz'

  • Output data

    content: 'k1=v1&k2=v2?k3:v3'
    k1: 'v1'
    k2: 'v2'
    k3: 'v3'

• Example 2: Extract information in preserve mode, retaining the original value for existing fields.

  • SPL statement

    * | parse-kv -regexp -mode='preserve' content, '([^&?]+)(?:=|:)([^&?]+)'

  • Input data

    content: 'k1=v1&k2=v2?k3:v3'
    k1: 'xyz'

  • Output data

    content: 'k1=v1&k2=v2?k3:v3'
    k1: 'xyz'
    k2: 'v2'
    k3: 'v3'

• Example 3: Extract information from complex unstructured data in regular extraction mode, where values are digits or strings enclosed in double quotes.

  • SPL statement

    * | parse-kv -regexp content, '([^&?]+)(?:=|:)([^&?]+)'

  • Input data

    content: 'verb="GET" URI="/healthz" latency="45.911µs" userAgent="kube-probe/1.30+" audit-ID="" srcIP="192.168.123.45:40092" contentType="text/plain; charset=utf-8" resp=200'

  • Output data

    content: 'verb="GET" URI="/healthz" latency="45.911µs" userAgent="kube-probe/1.30+" audit-ID="" srcIP="192.168.123.45:40092" contentType="text/plain; charset=utf-8" resp=200'
    verb: 'GET'
    URI: '/healthz'
    latency: '45.911µs'
    userAgent: 'kube-probe/1.30+'
    audit-ID: ''
    srcIP: '192.168.123.45:40092'
    contentType: 'text/plain; charset=utf-8'
    resp: '200'