使用Datadog Grok规则从PostgreSQL日志提取指定dbuser数据

需要提取出如下格式的JSON数据：

{
   "dbuser": {
     "AROAXXXXXXXXXXXXXXXXX": "username"
   }
}

待处理的PostgreSQL日志示例 #

2022-11-11 09:09:15 UTC:10.116.0.244(57888):AROAXXXXXXXXXXXXXXXXX:username@database_name:[592]:LOG:  AUDIT: SESSION,3016,1,READ,SELECT,,,"/*pga4dash*/

2022-11-11 09:20:53 UTC:10.116.0.244(57946):AROAXXXXXXXXXXXXXXXXX:username@database_name:[7696]:LOG:  pam_authenticate failed: Permission denied 

2022-11-11 09:27:02 UTC:10.116.0.244(57984):AROAXXXXXXXXXXXXXXXXX:username@database_name:[8328]:LOG:  AUDIT: SESSION,1,1,ROLE,ALTER ROLE,,,ALTER USER app_user SET pgaudit.log TO 'NONE';,<not logged>

2022-11-11 09:21:57 UTC:10.117.0.98(44764):AROAXXXXXXXXXXXXXXXXX:username@database_name:[2873]:FATAL:  pg_hba.conf rejects connection for host "10.117.0.98", user "AROAXXXXXXXXXXXXXXXXX:username", database "database_name", SSL off
*   Trying 127.0.0.1:1108...
* Connected to rdsauthproxy (127.0.0.1) port 1108 (#0)
> POST /authenticateRequest HTTP/1.1
Host: rdsauthproxy:1108
Accept: */*
Content-Length: 1884
Content-Type: multipart/form-data; boundary=------------------------1b12ee5d61245d84
* We are completely uploaded and fine
* Mark bundle as not supporting multiuse
< HTTP/1.1 200 OK
< Content-Type: text/html;charset=utf-8
< Content-Length: 0
< 
* Connection #0 to host rdsauthproxy left intact

已尝试的方案及结果 #

• 方案1：通用模式，无输出结果

  %{date("yyyy-MM-dd HH:mm:ss z"):}\:%{ipv4:}\(%{number:}\)\:%{data:dbuser:keyvalue(":")}
  

• 方案2：仅适配第一条日志，能得到预期结果

  %{date("yyyy-MM-dd HH:mm:ss z"):}\:%{ipv4:}\(%{number:}\)\:%{data:dbuser:keyvalue(":")}:\[592\]\:LOG\:\s+AUDIT\:\s+SESSION,%{integer:},1,READ,SELECT,,,\"%{notSpace:}%{data}
  

适配所有日志的Grok解决方案 #

方案一：直接提取目标键值对 #

通过给keyvalue函数指定终止符@，精准捕获AROAXXXXXXXXXXXXXXXXX:username部分，避免后续字符干扰：

%{date("yyyy-MM-dd HH:mm:ss z"):log_timestamp}:%{ipv4:client_ip}\(%{number:client_port}\):%{data:dbuser:keyvalue(":", "@")}

该规则会直接将dbuser字段解析为{"AROAXXXXXXXXXXXXXXXXX": "username"}，完全匹配需求格式。

方案二：拆分提取后组合 #

如果需要单独获取ARN和用户名，可先拆分提取，再通过Datadog加工规则组合成目标JSON：

%{date("yyyy-MM-dd HH:mm:ss z"):log_timestamp}:%{ipv4:client_ip}\(%{number:client_port}\):%{word:aws_arn}:%{word:db_username}@%{data:database_name}:%{data:log_details}

之后用Datadog JSON模板生成目标格式：

{
  "dbuser": {
    "%{aws_arn}": "%{db_username}"
  }
}

内容的提问来源于stack exchange，提问作者Jay Shah