Splunk子搜索未返回预期数据，如何实现用户最新登录统计？

环境与数据源 #

我的Splunk环境包含索引myIndex，以及两个数据源：

mySource1示例数据 #

2023-02-01 17:00:01 - Naam van gebruiker: hank - Rol van gebruiker: operator

2023-02-02 17:00:01 - Naam van gebruiker: skylar - Rol van gebruiker: operator

2023-02-03 17:00:01 - Naam van gebruiker: walt - Rol van gebruiker: operator

2023-02-02 17:00:01 - Naam van gebruiker: skylar - Rol van gebruiker: administrator

2023-02-03 17:00:01 - Naam van gebruiker: walt - Rol van gebruiker: administrator

mySource2示例数据 #

2023-02-06 13:49:57,654 User:hank The user is authenticated and logged in.

2023-02-07 13:49:57,654 User:skylar The user is authenticated and logged in.

2023-02-08 13:49:57,654 User:walt The user is authenticated and logged in.

2023-02-03 13:49:57,654 User:hank The user is authenticated and logged in.

2023-02-02 13:49:57,654 User:skylar The user is authenticated and logged in.

2023-02-01 13:49:57,654 User:walt The user is authenticated and logged in

需求目标 #

生成统计表格，展示mySource1中的所有用户（包括从未在mySource2产生登录事件的用户）及其最新的登录事件，预期格式如下：

USER, LATEST
hank, 2023-02-03 13:49:57,654 User:hank The user is authenticated and logged in.
skylar, 2023-02-02 13:49:57,654 User:skylar The user is authenticated and logged in.
walt, 2023-02-01 13:49:57,654 User:walt The user is authenticated and logged in

尝试过的查询及问题 #

初始尝试 #

index="myIndex" 
source="mySource1"
| fields _time, _raw
| rex "Naam van gebruiker: (?<USER>.+) -"
| dedup USER
| table USER
| sort USER
| join type=left 
[ search
    index="myIndex"
    source="mySource2"
    "User:myUserID The user is authenticated and logged in." 
    | stats latest(_raw)
]

问题：子搜索未正确传递用户参数，无法关联到具体用户的登录事件。

后续尝试 #

index="myIndex" source="mySource2"
    "The user is authenticated and logged in."
| rex "User:(?<USER>\w+) The user is authenticated and logged in."
| search [search index="myIndex" 
    source="mySource1"
    | rex "Naam van gebruiker: (?<USER>.+) -"
    | dedup USER
    | table USER
    | sort USER
    | format] 
| stats latest(_raw) by USER

问题：单独运行子搜索和主搜索均能返回数据，但组合后无结果。原因是主搜索的正则表达式末尾的.限制过严，无法匹配mySource2中部分无句点的事件，导致用户字段提取失败，无法与子搜索的用户列表匹配。

修正后的查询方案 #

方案1：使用左连接（Left Join） #

该方案先提取mySource1的所有唯一用户，再左连接mySource2中每个用户的最新登录记录，确保保留所有mySource1用户：

// 提取mySource1中的所有唯一用户
index="myIndex" source="mySource1"
| rex "Naam van gebruiker: (?<USER>.+) -"
| dedup USER
| fields USER
// 左连接mySource2中每个用户的最新登录事件
| join type=left USER 
    [ search index="myIndex" source="mySource2" "The user is authenticated and logged in."
      | rex "User:(?<USER>\w+) The user is authenticated and logged in.*"  // 修正正则，兼容有无句点的情况
      | stats latest(_raw) as LATEST by USER ]
| table USER LATEST
| sort USER

方案2：使用Stats高效聚合（推荐） #

该方案无需使用join，通过一次扫描所有数据并聚合，性能更优：

// 获取两个数据源的目标事件
index="myIndex" (source="mySource1" OR (source="mySource2" "The user is authenticated and logged in."))
// 针对不同数据源提取USER字段
| rex field=_raw "Naam van gebruiker: (?<USER>.+) -"
| rex field=_raw "User:(?<USER>\w+) The user is authenticated and logged in.*"
// 过滤出仅mySource1中存在的用户
| search [ search index="myIndex" source="mySource1"
           | rex "Naam van gebruiker: (?<USER>.+) -"
           | dedup USER
           | table USER
           | format ]
// 标记登录事件的原始内容，非登录事件置空
| eval login_raw = if(source="mySource2", _raw, null())
// 按用户分组，取最新的登录事件内容
| stats latest(login_raw) as LATEST by USER
| table USER LATEST
| sort USER

关键修正点 #

1. 调整正则表达式：将User:(?<USER>\w+) The user is authenticated and logged in.改为User:(?<USER>\w+) The user is authenticated and logged in.*，兼容mySource2中结尾有无句点的事件，确保用户字段能正确提取。
2. 确保保留所有mySource1用户：使用type=left的join，或通过stats结合子搜索过滤，保证无登录事件的用户也会出现在结果中。

内容的提问来源于stack exchange，提问作者Niek Jonkman