Simple Log Service:SPL instructions

Filter and categorize access logs based on status codes, and then export the logs.

• SPL statement

  -- Define the SPL processing result as a named dataset src, which is used as the input for subsequent SPL expressions
  .let src = * 
  | where status=cast(status as BIGINT);
  
  -- Use the named dataset src as the input for an SPL expression to obtain the data whose status field is 5xx and define the SPL processing result as a dataset named err. Do not export the dataset.
  .let err = $src
  | where status >= 500
  | extend msg='ERR';
  
  -- Use the named dataset src as the input for an SPL expression to obtain the data whose status field is 2xx and define the SPL processing result as a dataset named ok. Do not export the dataset.
  .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'

• Result

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

Syntax

| parse-kv -mode=<mode> -prefix=<prefix> -greedy <field>, <delim>, <kv-sep>

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

Parameters

Examples

• Example 1: Extract multiple characters from SLS time series data in Labels as data fields

  • SPL statement

    * | parse-kv -prefix='__labels__.' __labels__, '|', '#$#'

  • Input data

    __name__: 'net_in'
    __value__: '231461.57374215033'
    __time_nano__: '1717378679274117026'
    __labels__: 'cluster#$#sls-etl|hostname#$#iZbp17raa25u0xi4wifopeZ|interface#$#veth02cc91d2|ip#$#192.168.22.238'

  • Output data

    __name__: 'net_in'
    __value__: '231461.57374215033'
    __time_nano__: '1717378679274117026'
    __labels__: 'cluster#$#sls-etl|hostname#$#iZbp17raa25u0xi4wifopeZ|interface#$#veth02cc91d2|ip#$#192.168.22.238'
    __labels__.cluster: 'sls-etl'
    __labels__.hostname: 'iZbp17raa25u0xi4wifopeZ'
    __labels__.interface: 'veth02cc91d2'
    __labels__.ip: '192.168.22.238'

• Example 2: Enable greedy matching mode to extract key-value information from access logs.

  • SPL statement

    * | parse-kv -greedy content, ' ', '='

  • Input data

    content: 'src=127.0.0.1 dst=192.168.0.0 bytes=125 msg=connection refused body=this is test time=2024-05-21T00:00:00'

  • Output data

    content: 'src=127.0.0.1 dst=192.168.0.0 bytes=125 msg=connection refused body=this is test time=2024-05-21T00:00:00'
    src: '127.0.0.1'
    dst: '192.168.0.0'
    bytes: '125'
    msg: 'connection refused'
    body: 'this is test'
    time: '2024-05-21T00:00:00'

• Example 3: Enable regular extraction mode to process complex delimiters between key-value pairs and separators between keys and values.

  • 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 4: Extract information in preserve mode. For an existing field, retain the original value.

  • SPL statement

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

  • Input data

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

  • Result

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

China (Shanghai), China (Heyuan)

Syntax

| pack-fields -keep -ltrim -include=<include> -exclude=<exclude> as <output>

Parameters

Examples

• Example 1: Pack all fields in a log to the test field. By default, the original fields that are packed are deleted.

  • SPL statement

    * | pack-fields -include='\w+' as test

  • Input data

    test1:123
    test2:456
    test3:789

  • Result

    test:{"test1": "123", "test2": "456", "test3": "789"}

• Example 2: Pack all fields in a log to the test field. The original fields that are packed are not deleted.

  • SPL statement

    * | pack-fields -keep -include='\w+' as test

  • Input data

    test1:123
    test2:456
    test3:789

  • Result

    test:{"test1": "123", "test2": "456", "test3": "789"}
    test1:123
    test2:456
    test3:789

• Example 3: Pack the test and abcd fields to the content field. The original fields that are packed are not deleted.

  • SPL statement

    * | pack-fields -keep -include='\w+' as content

  • Input data

    abcd@#%:123
    test:456
    abcd:789

  • Result

    abcd:789
    abcd@#%:123
    content:{"test": "456", "abcd": "789"}
    test:456

• Example 4: Do not pack the test and abcd fields. Pack the remaining fields to the content field. Delete the original fields that are packed.

  • SPL statement

    * | pack-fields -exclude='\w+' as content

  • Input data

    abcd@#%:123
    test:456
    abcd:789

  • Result

    abcd:789
    content:{"abcd@#%": "123"}
    test:456

• Example 5: Extract all key-value pairs that match the regular expression from the field value, and pack the key-value pairs to the name field.

  • SPL statement

    * | parse-kv -prefix='k_' -regexp dict, '(\w+):(\w+)' | pack-fields -include='k_.*' -ltrim = 'k_' as name

  • Input data

    dict: x:123, y:456, z:789

  • Result

    dict:x:123, y:456, z:789
    name:{"x": "123", "y": "456", "z": "789"}

Syntax

| log-to-metric -wildcard -format -names=<names> -labels=<labels> -time_field=<time_field>

Parameters

Examples

• Example 1: Convert the log that contains the rt field to the time series data format.

  • SPL statement

    * | log-to-metric -names='["rt"]'

  • Input data

    __time__: 1614739608
    rt: 123

  • Result

    __labels__:
    __name__:rt
    __time_nano__:1614739608
    __value__:123

• Example 2: Convert the log that contains the rt field to the time series data format and add the host field as a label field.

  • SPL statement

    * | log-to-metric -names='["rt"]' -labels='["host"]'

  • Input data

    __time__: 1614739608
    rt: 123
    host: myhost

  • Result

    __labels__:host#$#myhost
    __name__:rt
    __time_nano__:1614739608
    __value__:123

• Example 3: Convert the log that contains the rt and qps fields to the time series data format and add the host field as a label field.

  • SPL statement

    * | log-to-metric -names='["rt", "qps"]' -labels='["host"]'

  • Input data

    __time__: 1614739608
    rt: 123
    qps: 10
    host: myhost

  • Result

    __labels__:host#$#myhost
    __name__:rt
    __time_nano__:1614739608
    __value__:123
    
    __labels__:host#$#myhost
    __name__:qps
    __time_nano__:1614739608
    __value__:10

• Example 4: Use fuzzy matching to convert the log that contains the rt1 and rt2 fields to the time series data format and add the host field as a label field.

  • SPL statement

    * | log-to-metric -wildcard -names='["rt*"]' -labels='["host"]'

  • Input data

    __time__: 1614739608
    rt1: 123
    rt2: 10
    host: myhost

  • Result

    __labels__:host#$#myhost
    __name__:rt1
    __time_nano__:1614739608
    __value__:123
    
    __labels__:host#$#myhost
    __name__:rt2
    __time_nano__:1614739608
    __value__:10

• Example 5: Convert the log that contains the rt and qps fields to the time series data format, add the host field as a label field, and automatically format the label value.

  • SPL statement

    * | log-to-metric -format -names='["rt", "qps"]' -labels='["host"]'

  • Input data

    __time__: 1614739608
    rt: 123
    qps: 10
    host: myhost1|myhost2

  • Result

    __labels__:host#$#myhost1_myhost2
    __name__:rt
    __time_nano__:1614739608
    __value__:123
    
    __labels__:host#$#myhost1_myhost2
    __name__:qps
    __time_nano__:1614739608
    __value__:10

• Example 6: Convert the log that contains the rt and qps fields to the time series data format, rename the fields to max_rt and total_qps, and add the host field as a label field.

  • SPL statement

    * | project-rename max_rt = rt, total_qps = qps| log-to-metric -names='["max_rt", "total_qps"]' -labels='["host"]'

  • Input data

    __time__: 1614739608
    rt: 123
    qps: 10
    host: myhost

  • Result

    __labels__:host#$#myhost
    __name__:max_rt
    __time_nano__:1614739608
    __value__:123
    
    __labels__:host#$#myhost
    __name__:total_qps
    __time_nano__:1614739608
    __value__:10

• Example 7: Convert the log that contains the rt and qps fields to the time series data format, rename the fields to max_rt and total_qps, rename the host field to hostname, and add the hostname field as a label field.

  • SPL statement

    * | project-rename max_rt = rt, total_qps = qps, hostname=host| log-to-metric -names='["max_rt", "total_qps"]' -labels='["hostname"]'

  • Input data

    __time__: 1614739608
    rt: 123
    qps: 10
    host: myhost

  • Result

    __labels__:hostname#$#myhost
    __name__:max_rt
    __time_nano__:1614739608
    __value__:123
    
    __labels__:hostname#$#myhost
    __name__:total_qps
    __time_nano__:1614739608
    __value__:10

• Example 8: Convert the log that contains the remote_user field to the time series data format, add the status field as a label field, use the time field as the time field of the time series data, and specify the time unit of the original log data as nanoseconds.

  • SPL statement

    * | log-to-metric -names='["remote_user"]' -labels='["status"]' -time_field='time'

  • Input data

    time:1652943594
    remote_user:89
    request_length:4264
    request_method:GET
    status:200

  • Result

    __labels__:status#$#200
    __name__:remote_user
    __time_nano__:1652943594
    __value__:89

Syntax

| metric-to-metric -format -add_labels=<add_labels> -del_labels=<del_labels> -rename_labels=<rename_labels>

Parameters

Examples

• Example 1: Add a label

  • SPL statement

    * | extend qps = '10'|metric-to-metric -add_labels='["qps"]'

  • Input data

    __labels__:host#$#myhost
    __name__:rt
    __time_nano__:1614739608
    __value__:123

  • Result

    __labels__:host#$#myhost|qps#$#10
    __name__:rt
    __time_nano__:1614739608
    __value__:123

• Example 2: Remove a label

  • SPL statement

    * | metric-to-metric -del_labels='["qps"]'

  • Input data

    __labels__:host#$#myhost|qps#$#10
    __name__:rt
    __time_nano__:1614739608
    __value__:123

  • Result

    __labels__:host#$#myhost
    __name__:rt
    __time_nano__:1614739608
    __value__:123

• Example 3: Rename a label

  • SPL statement

    * | metric-to-metric -rename_labels='{"host":"etl_host"}'

  • Input data

    __labels__:host#$#myhost|qps#$#10
    __name__:rt
    __time_nano__:1614739608
    __value__:123

  • Result

    __labels__:etl_host#$#myhost|qps#$#10
    __name__:rt
    __time_nano__:1614739608
    __value__:123

• Example 4: Format invalid data with one click

  • SPL statement

    * | metric-to-metric -format

  • Input data

    __labels__:host#$#myhost|qps#$#10|asda$cc#$#j|ob|schema#$#|#$#|#$#xxxx
    __name__:rt
    __time_nano__:1614739608
    __value__:123

  • Result

    __labels__:asda_cc#$#j|host#$#myhost|qps#$#10
    __name__:rt
    __time_nano__:1614739608
    __value__:123

Syntax

stats <output>=<aggOperator> by <group>,[<group>...]

Parameters

Examples

• Example 1: Count pv by ip for access logs

  • SPL statement

    * | stats pv=count(*) by ip

  • Input data

    ip: 192.168.1.1
    latencyMs: 10
    
    ip: 192.168.1.1
    latencyMs: 20
    
    ip: 192.168.1.2
    latencyMs: 10

  • Output data

    ip: 192.168.1.2
    pv: 1
    
    ip: 192.168.1.1
    pv: 2

• Example 2: Statistics of latency min/max for all ip in accesslog

  • SPL statement

    * 
    | extend latencyMs=cast(latencyMs as bigint)
    | stats minLatencyMs=min(latencyMs), maxLatencyMs=max(latencyMs) by ip

  • Input data

    ip: 192.168.1.1
    latencyMs: 10
    
    ip: 192.168.1.1
    latencyMs: 20
    
    ip: 192.168.1.2
    latencyMs: 10

  • Output data

    ip: 192.168.1.2
    minLatencyMs: 10
    maxLatencyMs: 20
    
    ip: 192.168.1.1
    minLatencyMs: 10
    maxLatencyMs: 10

• Example 3: Count all pv for access logs

  • SPL statement

    * | stats pv=count(*)

  • Input data

    ip: 192.168.1.1
    latencyMs: 10
    
    ip: 192.168.1.1
    latencyMs: 20
    
    ip: 192.168.1.2
    latencyMs: 10

  • Output data

    pv: 3

Syntax

sort <field> [asc/desc] ,(<field> [asc/desc])

Parameters

Example

Sort accesslog by latencyMs.

• SPL statement

  * 
  | extend latencyMs=cast(latencyMs as bigint) 
  | sort latencyMs desc

• Input data

  ip: 192.168.1.1
  latencyMs: 10
  
  ip: 192.168.1.1
  latencyMs: 20
  
  ip: 192.168.1.2
  latencyMs: 15

• Output data

  ip: 192.168.1.1
  latencyMs: 20
  
  ip: 192.168.1.2
  latencyMs: 15
  
  ip: 192.168.1.1
  latencyMs: 10

Syntax

limit (<offset>,) <size>

Parameters

Example

Sort access logs by latencyMs and return the first entry.

• SPL statement

  * 
  | extend latencyMs=cast(latencyMs as bigint) 
  | sort latencyMs
  | limit 1

• Input data

  ip: 192.168.1.1
  latencyMs: 10
  
  ip: 192.168.1.1
  latencyMs: 20
  
  ip: 192.168.1.2
  latencyMs: 15

• Output data

  ip: 192.168.1.1
  latencyMs: 20