logstash将json日志事件自动解析成KV字段

1 问题背景

logstash在接收来自filebeat采集的mysql审计日志（json格式）时，会将整个json事件作为message字段保存。
mysql审计日志如下示例：

{"msg-type":"activity","date":"1666405413511","thread-id":"3","query-id":"6","user":"root","priv_user":"root","ip":"","host":"localhost","_os":"linux-glibc2.12","_client_name":"libmysql","_pid":"14362","_client_version":"5.7.33","_platform":"x86_64","program_name":"mysql","pid":"14362","os_user":"root","appname":"mysql","rows":"1","status":"0","cmd":"select","query":"SELECT DATABASE()"}
{"msg-type":"activity","date":"1666405427863","thread-id":"3","query-id":"42","user":"root","priv_user":"root","ip":"","host":"localhost","_os":"linux-glibc2.12","_client_name":"libmysql","_pid":"14362","_client_version":"5.7.33","_platform":"x86_64","program_name":"mysql","pid":"14362","os_user":"root","appname":"mysql","rows":"4","status":"0","cmd":"select","objects":[{"db":"mysql","name":"user","obj_type":"TABLE"}],"query":"select * from user"}

logstash接收到处理后的事件如下图：

其中message中的json串为所需要的的日志信息，需要将json字符串解析出来的key作为字段展出出来。

2 解决方法

在logback的配置文件中filter向可加入过滤的配置

完整配置如下：

input {
    beats {
        port => "5044"
    }
}

filter {
    if [log_type] == "mysql" {
        json {
             source => "message"
             #target => "jsoncontent"  target表示解析出来的字段保存的位置，即添加一个jsoncontent的字段，然后将json字符串解析出来的key-value作为子属性，不配置则直接将解析出来的key作为字段放在原始数据里面
             #remove_field => "message" 删除原始message字段，不配置则保留
        }

       mutate {
            remove_field =>["host","ecs","input","log","@version","tags","agent"]
        }
    }
}

output {
    if [log_type] == "mysql" {
      stdout{}
    }
}

处理后事件如下：

{
                 "ip" => "",
               "rows" => "4",
           "msg-type" => "activity",
               "date" => "1666406079312",
           "log_type" => "mysql",
                "pid" => "14362",
            "message" => "{\"msg-type\":\"activity\",\"date\":\"1666406079312\",\"thread-id\":\"3\",\"query-id\":\"43\",\"user\":\"root\",\"priv_user\":\"root\",\"ip\":\"\",\"host\":\"localhost\",\"_os\":\"linux-glibc2.12\",\"_client_name\":\"libmysql\",\"_pid\":\"14362\",\"_client_version\":\"5.7.33\",\"_platform\":\"x86_64\",\"program_name\":\"mysql\",\"pid\":\"14362\",\"os_user\":\"root\",\"appname\":\"mysql\",\"rows\":\"4\",\"status\":\"0\",\"cmd\":\"select\",\"objects\":[{\"db\":\"mysql\",\"name\":\"user\",\"obj_type\":\"TABLE\"}],\"query\":\"select * from user\"}",
            "os_user" => "root",
            "appname" => "mysql",
               "user" => "root",
       "program_name" => "mysql",
           "hostipv6" => "fe80::a36f:f066:cdb2:8743",
               "_pid" => "14362",
           "hostipv4" => "192.168.149.152",
    "_client_version" => "5.7.33",
          "thread-id" => "3",
         "@timestamp" => 2022-10-22T02:34:45.039Z,
                "cmd" => "select",
            "objects" => [
        [0] {
            "obj_type" => "TABLE",
                  "db" => "mysql",
                "name" => "user"
        }
    ],
       "_client_name" => "libmysql",
             "status" => "0",
          "_platform" => "x86_64",
          "priv_user" => "root",
           "query-id" => "43",
              "query" => "select * from user",
                "_os" => "linux-glibc2.12"
}