如何配置Filebeat采集Oracle审计日志的完整ACTION块

问题 #

需要采集Oracle审计日志（.aud格式）中从ACTION字段到ACTION NUMBER字段的完整日志块，避免长SQL被截断。

日志示例 #

Thu Nov  9 10:20:24 2023 +01:00
LENGTH : '373'
ACTION :[122] 'select 'export nls_nchar_characterset="'||value||'"' from nls_database_parameters where parameter='NLS_NCHAR_CHARACTERSET''
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[0] ''
STATUS:[1] '0'
DBID:[10] '3256053857'
SESSIONID:[10] '4294967295'
USERHOST:[12] 's01vl9926909'
CLIENT ADDRESS:[0] ''
ACTION NUMBER:[1] '3'

Thu Nov  9 10:20:24 2023 +01:00
LENGTH : '1575'
ACTION :[1323] 'select 'export db_patchset_new="'||max_ver||'"' from (
select length(regexp_replace(substr(description,0,decode(instr(description,'('),0,length(description),instr(description,'('))),'[^0.0-9]', '')) max_length,
       max(regexp_replace(substr(description,0,decode(instr(description,'('),0,length(description),instr(description,'('))),'[^0.0-9]', '')) max_ver
from   (select description from ( select da.description, da.action_time apply, nvl(dr.action_time, da.action_time-1) rollback
                                  from (select description, max(action_time) action_time from dba_registry_sqlpatch where action='APPLY'    group by description) da,
                                       (select description, max(action_time) action_time from dba_registry_sqlpatch where action='ROLLBACK' group by description) dr
                                  where da.description=dr.description(+)
                                )
        where apply > rollback
       )
where  (upper(description) like '%DATABASE%PATCH%' or upper(description) like '%DATABASE%RELEASE%') and upper(description) not like '%JAVA%' and upper(description) not like '%JVM%'
group by length(regexp_replace(substr(description,0,decode(instr(description,'('),0,length(description),instr(description,'('))),'[^0.0-9]', ''))
order by 1 desc
)
where rownum=1'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'oracle'
CLIENT TERMINAL:[0] ''
STATUS:[1] '0'
DBID:[10] '3256053857'
SESSIONID:[10] '4294967295'
USERHOST:[12] 's01vl9926909'
CLIENT ADDRESS:[0] ''
ACTION NUMBER:[1] '3'

原有问题 #

最初使用include_lines仅能采集单条匹配行，长SQL会被截断，比如仅保留到FROM关键字：

{"@timestamp":"2023-11-09T13:34:21.491Z","@metadata":{"beat":"filebeat","type":"_doc","version":"7.17.0"},"message":"ACTION:'select 'export db_patchset_new="'||max_ver||'"' from ('","fields":{"env":"staging"},"host":{"name":"s01vl9926909"},"event":{"timezone":"+01:00"},"log":{"offset":811,"file":{"path":"/apps/oracle/diag/rdbms/q08775kp1/Q08775KP10/audit/Q08775KP10_ora_29807_20231109143420100545459814.aud"}},"input":{"type":"log"},"ecs":{"version":"1.12.0"}

原有无效配置 #

仅include_lines的配置：

enabled: true
tags: ["linux-Oracle"]
#ignore_older: 4h
close_inactive: 30s

paths:
 /apps/oracle/diag/rdbms/*/*/audit/*.aud

include_lines: ['^LENGTH :', '^ACTION :', '^DATABASE USER:', '^PRIVILEGE :', '^CLIENT USER:', '^CLIENT TERMINAL:', '^STATUS:', '^DBID:', '^SESSIONID:', '^USERHOST:', '^CLIENT ADDRESS:', '^ACTION NUMBER:']

尝试无效的multiline配置：

type: log
enabled: true
tags: ["linux-Oracle"]
#ignore_older: 4h
close_inactive: 30s

paths:
  - /apps/oracle/diag/rdbms/*/*/audit/*.aud

multiline:
  pattern: '^ACTION :'
  negate: true
  match: after

include_lines: ['^LENGTH :', '^DATABASE USER:', '^PRIVILEGE :', '^CLIENT USER:', '^CLIENT TERMINAL:', '^STATUS:', '^DBID:', '^SESSIONID:', '^USERHOST:', '^CLIENT ADDRESS:', '^ACTION NUMBER:']

解决方案 #

需要先通过multiline合并完整的日志块（包括多行的ACTION内容），再过滤保留需要的字段行，或者调整multiline规则确保ACTION的多行内容被合并到同一事件中。

正确的Filebeat配置 #

type: log
enabled: true
tags: ["linux-Oracle"]
close_inactive: 30s

paths:
  - /apps/oracle/diag/rdbms/*/*/audit/*.aud

# 合并完整的日志条目：以日期行开头的是新的日志块
multiline:
  # 匹配日志块的起始行（Thu开头的日期行）
  pattern: '^Thu '
  negate: true
  match: after
  # 可选：设置超时，避免未完成的块一直占用内存
  timeout: 5s

# 过滤保留需要的字段行
include_lines: ['^LENGTH :', '^ACTION :', '^DATABASE USER:', '^PRIVILEGE :', '^CLIENT USER:', '^CLIENT TERMINAL:', '^STATUS:', '^DBID:', '^SESSIONID:', '^USERHOST:', '^CLIENT ADDRESS:', '^ACTION NUMBER:']

配置说明 #

1. Multiline规则逻辑：

   • 用^Thu 匹配每个日志块的起始行（Oracle审计日志的每个条目都以日期行开头）
   • negate: true + match: after 表示：所有不匹配^Thu 的行，都合并到前一行所在的日志块中
   • 这样就能把ACTION字段的多行SQL完整合并到同一个日志块里，不会被截断

2. Include_lines作用：

   • 在合并完完整日志块后，再过滤掉不需要的日期行，只保留需要的字段行

如果需要更精准地针对ACTION字段的多行合并，也可以调整multiline规则为：

multiline:
  # 匹配非字段起始行的内容（即ACTION的多行SQL部分）
  pattern: '^(LENGTH|ACTION|DATABASE USER|PRIVILEGE|CLIENT USER|CLIENT TERMINAL|STATUS|DBID|SESSIONID|USERHOST|CLIENT ADDRESS|ACTION NUMBER) :'
  negate: true
  match: after
  timeout: 5s

这个规则表示：所有不是以指定字段开头的行，都合并到前一行（也就是ACTION字段的行），确保SQL的多行内容都属于ACTION字段的一部分。

内容的提问来源于stack exchange，提问作者hakim arqib