How to standardize data formats-Simple Log Service(SLS)-阿里云帮助中心

Scenario 1: Filter logs (where instruction)

The where SPL instruction filters log entries based on a boolean expression.

where <bool-expression>

Subscenario 1: Filter by field value.

Raw logs

#Log 1
__source__:  192.168.0.1
__tag__:__client_ip__:  192.168.0.2
__tag__:__receive_time__:  1597214851
__topic__: app
class:  test_case
id:  7992
test_string:  <function test1 at 0x1027401e0>
#Log 2
__source__:  192.168.0.1
__tag__:__client_ip__:  192.168.0.2
__tag__:__receive_time__:  1597214861
__topic__: web
class:  test_case
id:  7992
test_string:  <function test1 at 0x1027401e0>

SPL statement

Drops logs where the __topic__ field equals app.
```
* | where __topic__!='app'
```

Output

__source__:  192.168.0.1
__tag__:__client_ip__:  192.168.0.2
__tag__:__receive_time__:  1597214861
__topic__: web
class:  test_case
id:  7992
test_string:  <function test1 at 0x1027401e0>

Subscenario 2: Filter by regular expression match.

Raw logs

#Log 1
__source__:  192.168.0.1
__tag__:__client_ip__:  192.168.0.2
__tag__:__receive_time__:  1597214851
__topic__: app
class:  test_case
id:  7992
test_string:  <function test1 at 0x1027401e0>
server_protocol：test
#Log 2
__source__:  192.168.0.1
__tag__:__client_ip__:  192.168.0.2
__tag__:__receive_time__:  1597214861
__topic__: web
class:  test_case
id:  7992
test_string:  <function test1 at 0x1027401e0>
server_protocol: 14861

SPL statement

Keeps only logs where server_protocol is numeric.
```
* | where regexp_like(server_protocol, '^\d+$')
```

Output

__source__:  192.168.0.1
__tag__:__client_ip__:  192.168.0.2
__tag__:__receive_time__:  1597214861
__topic__: web
class:  test_case
id:  7992
test_string:  <function test1 at 0x1027401e0>
server_protocol: 14861

Scenario 2: Assign values to empty fields (extend and parse-regexp)

Use the extend and parse-regexp SPL instructions to add or update fields.

Subscenario 1: Assign a value to an empty or missing field.

* | extend <output>=<expression>, ...

Input data
```
name:
```
SPL statement: Assigns the value lily to the empty name field.
```
* | extend name='lily'
```
Output
```
name:lily
```

Subscenario 2: Extract structured fields with a regular expression.

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

Input data

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

SPL statement

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

Output

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

Subscenario 3: Assign values to multiple fields.

| extend <output>=<expression> | extend <output1>=<expression> | <output2>=<expression>

Input data

__source__:  192.168.0.1
__topic__:
__tag__:
__receive_time__:
id:  7990
test_string:  <function test1 at 0x1020401e0>

SPL statement

Populates the __topic__, __tag__, and __receive_time__ fields.

* | extend __topic__='app' | extend __tag__='stu' | extend __receive_time__='1597214851'

Output

__source__:  192.168.0.1
__topic__:  app
__tag__:  stu
__receive_time__:  1597214851
id:  7990
test_string:  <function test1 at 0x1020401e0>

Scenario 3: Delete and rename fields (project-away and project-rename)

The project-away instruction drops fields from log entries, and project-rename renames them.

Subscenario 1: Delete specific fields.

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

Input data
```
content：123
age：23
name：twiss
```
SPL statement: Drops the age and name fields.
```
* | project-away age, name
```
Output
```
content：123
```

Subscenario 2: Rename specific fields.

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

Input data
```
content：123
age：23
name：twiss
```
SPL statement: Renames age to new_age and name to new_name.
```
* | project-rename new_age=age, new_name=name
```

Output

content：123
new_age：23
new_name：twiss

Scenario 4: Convert data types

Subscenario 1: Use concat to concatenate strings or cast to add numbers.

Input data
```
x: 123
y: 100
```
SPL statement: Adds x and y as integers into field a, then concatenates them as strings into field b.
```
* | extend a=cast(x as bigint) + cast(y as bigint)| extend b=concat(x, y)
```
Output
```
x: 123
y: 100
a: 223
b: 123100
```

Subscenario 2: Use to_unixtime to convert a datetime to a UNIX timestamp.

Raw log
```
time1: 2020-09-17 9:00:00
```

Transformation rule

Converts the datetime value in time1 to a UNIX timestamp.

* | extend time1=cast(time1 as TIMESTAMP) | extend new_time=to_unixtime(time1)

Output

time1:  2020-09-17 9:00:00
new_time:  1600333200.0

Scenario 5: Set default values for fields (COALESCE)

Use the COALESCE expression to fall back to a default value when a field is missing or null.

Input data
```
server_protocol: 100
```
SPL statement

Assigns the value of server_protocol to y if the field exists. Falls back to 200 and assigns it to x because server_protocol1 does not exist.
```
* | extend x=COALESCE(server_protocol1, '200') | extend y=COALESCE(server_protocol, '200')
```
Output
```
server_protocol: 100
x: 200
y: 100
```

Scenario 6: Add fields conditionally (where and extend)

Chain where and extend to add a field only when a condition is met.

* | where <bool-expression> | extend <output>=<expression> |...

Input data
```
status1: 200
status2: 404
```

SPL statement: Adds status1_info when status1 is 200, and adds status2_info when status2 is 404.

* | where status1='200'| extend status1_info='normal' | where status2='404'| extend status2_info='error'

Output

status1: 200
status2: 404
status1_info: normal
status2_info: error

Scenario 7: Convert nanosecond-level UNIX timestamp

To convert a seconds-precision UNIX timestamp to nanoseconds, multiply by 1,000,000,000 and cast to BIGINT.

Input data

{
  "__source__": "1.2.3.4",
  "__time__": 1704983810,
  "__topic__": "test"
}

SPL statement: Converts the __time__ field from seconds to nanoseconds.
```
* | extend ts_nano = cast(__time__ * 1000000000 as BIGINT)
```

Output

 {
  "__source__": "1.2.3.4",
  "__time__": "1704983810",
  "__topic__": "test",
  "ts_nano": "1704983810000000000"
  
}

Scenario 8: Convert ISO 8601 to microsecond timestamp

To parse an ISO 8601 datetime string into a microsecond-precision UNIX timestamp, cast the field to TIMESTAMP, apply to_unixtime, multiply by 1,000,000, and cast to BIGINT.

Input data

{
  "__source__": "1.2.3.4",
  "__time__": 1704983810,
  "__topic__": "test",
  "log_time":"2024-01-11 23:10:43.992847200"
}

SPL statement: Converts log_time to a UNIX timestamp in microseconds.

* | extend ts_micro = cast(to_unixtime(cast(log_time as TIMESTAMP)) * 1000000 as BIGINT)

Output

 {
  "__time__": "1704983810",
  "__topic__": "test",
  "__source__": "1.2.3.4",
  "ts_micro": "1705014643992847",
  "log_time": "2024-01-11 23:10:43.992847200"
}