Extract strings after specified characters on BigQuery

Question

I am trying to extract the data from one single column to multiple column. The original data is a long text, with parameters separated by spaces, as an example below: -

Query: -

SELECT * FROM `networkmanage.syslog`
WHERE DATE(msg_time) between DATE_SUB(current_date(), INTERVAL 1 DAY) AND current_date()
  and message like '%Rayong%'
  and message like '%urls%'
  and message like '%10.1.1.155%'
LIMIT 10

Output: -

[
  {
    "message": "1 1641525138.935169636 Rayong_1 urls src=10.1.1.155:57977 dst=23.1.1.2:443 mac=XX:XX:XX:XX:XX:XX request: UNKNOWN https://example.lan/...",
    "msg_time": "2022-01-07 03:12:18.993264 UTC",
    "rcv_time": "2022-01-07 03:12:19.050126 UTC",
    "client_addr": "10.158.81.1"
  },
  {
    "message": "1 1641525883.268370959 Rayong1 urls src=10.1.1.155:58199 dst=23.1.1.2:80 mac=XX:XX:XX:XX:XX:XX agent='Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/96.0.4664.110 Safari/537.36' request: POST http://example.lan/jsrpc.php?output=json-rpc",
    "msg_time": "2022-01-07 03:24:43.327320 UTC",
    "rcv_time": "2022-01-07 03:24:43.600830 UTC",
    "client_addr": "10.158.81.1"
  },
  {
    "message": "1 1641525892.720006714 Rayong_1 urls src=10.1.1.155:58207 dst=23.1.1.2:443 mac=XX:XX:XX:XX:XX:XX request: UNKNOWN https://acp-ss-an1.adobe.io/...",
    "msg_time": "2022-01-07 03:24:52.772515 UTC",
    "rcv_time": "2022-01-07 03:24:52.895756 UTC",
    "client_addr": "10.158.81.1"
  },
  {
    "message": "1 1641525894.263687469 Rayong_1 urls src=10.1.1.155:58199 dst=23.1.1.2:80 mac=XX:XX:XX:XX:XX:XX agent='Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/96.0.4664.110 Safari/537.36' request: POST http://example.lan/jsrpc.php?output=json-rpc",
    "msg_time": "2022-01-07 03:24:54.331499 UTC",
    "rcv_time": "2022-01-07 03:24:54.620822 UTC",
    "client_addr": "10.158.81.1"
  }, ...

I would like to achieve two things:

I would like to extract the string after characters "src=", "dst=", "mac=", and before the space.
I would like to extract the string and spaces after those parameters were extracted.

So the desirable output should be like: -

[
  {
    "src": "10.1.1.155:57977",
    "dst": "23.1.1.2:443",
    "info": "request: UNKNOWN https://example.lan/..."
    "msg_time": "2022-01-07 03:12:18.993264 UTC",
    "rcv_time": "2022-01-07 03:12:19.050126 UTC",
    "client_addr": "10.158.81.1"
  }, ...

Is it possible to do this using BigQuery syntax directly? Your helps and guidance are highly appreciated. Thank you very much in advance.

user_5221944 · Answer

Consider below approach

SELECT 
  REGEXP_EXTRACT(message, r' src=([^ ]+) ') src,
  REGEXP_EXTRACT(message, r' dst=([^ ]+) ') dst,
  REGEXP_EXTRACT(message, r' mac=([^ ]+) ') mac,
  REGEXP_EXTRACT(message, r' mac=[^ ]+ (.*)$') info,
  * EXCEPT(message) 
FROM `networkmanage.syslog`
WHERE DATE(msg_time) between DATE_SUB(current_date(), INTERVAL 1 DAY) AND current_date()
  and message like '%Rayong%'
  and message like '%urls%'
  and message like '%10.1.1.155%'
LIMIT 10

with output like below

Extract strings after specified characters on BigQuery

1 Answers