如何在Splunk中基于RequestId合并两个查询的结果？

现有查询说明 #

查询1：返回请求核心字段 #

该查询用于提取RequestId、responseBody、Time、responseCode、requestBody字段：

index=docee_logs sourcetype="kube:container:xyz" 
    [ search index=docee_logs host=sl***** source="/var/log/containers/**.log" sourcetype="kube:container:*xyz*" "callType\\\" :[\\\"12\\\"" AND  "Activity"  AND "Time=" 
    | rex field=_raw "Time=(?<proctime>.*), apiVersion" 
    | where proctime > 3000 
    | eval RequestId=replace(RequestId, "\\'", "")
    | fields RequestId 
    | rename RequestId as search 
    | format] 
| search "Activity" AND requestUrl="\\'/Request\\'"
| eval RequestId=replace(RequestId, "\\'", "")
| eval requestBody=replace(requestBody, "\\'", "") 
| eval requestBody=replace(requestBody, "\\", "")
| table RequestId,responseBody, Time, responseCode,requestBody

查询2：返回关联的request_url #

该查询通过公共字段RequestId（原查询中字段名为vrolRequestId，需统一为RequestId）提取对应的request_url：

index=docee_logs sourcetype="kube:container:xyz" 
    [ search index=docee_logs host=sl******* source="/var/log/containers/**.log" sourcetype="kube:container:*xyz*" "callType\\\" :[\\\"12\\\"" AND  "Activity"  AND "Time=" 
    | rex field=_raw "Time=(?<proctime>.*), apiVersion" 
    | where proctime > 3000 
    | eval RequestId=replace(RequestId, "\\'", "")
    | fields RequestId 
    | rename RequestId as search 
    | format]
| search "Sending merchant notification, Url" 
| rex "Sending merchant notification, Url=(?P<request_url>[^,]*)"
| eval RequestId=replace(vrolRequestId, "\\'", "")  # 统一字段名
| table request_url,RequestId

尝试的查询存在的问题 #

你尝试的查询有以下几个关键问题：

• 子查询嵌套结构错误：外层搜索的过滤条件（如"Activity" AND requestUrl="\\'/Request\\'"）位置错误，导致无法正确过滤目标事件
• 使用append未关联数据：仅拼接结果集，未通过RequestId关联两个查询的数据，导致字段无法对应
• 全局执行rex提取：对所有事件执行request_url匹配，污染非目标事件的字段
• 字段名未统一：第二个查询的vrolRequestId未转为RequestId，无法实现关联

修正后的查询方案 #

方案1：使用stats合并（推荐，性能更稳定） #

通过UNION整合两个查询的结果，再按RequestId合并所有关联字段：

// 获取查询1的核心结果集
(index=docee_logs sourcetype="kube:container:xyz" 
    [ search index=docee_logs host=sl***** source="/var/log/containers/**.log" sourcetype="kube:container:*xyz*" "callType\\\" :[\\\"12\\\"" AND  "Activity"  AND "Time=" 
    | rex field=_raw "Time=(?<proctime>.*), apiVersion" 
    | where proctime > 3000 
    | eval RequestId=replace(RequestId, "\\'", "")
    | fields RequestId 
    | rename RequestId as search 
    | format] 
| search "Activity" AND requestUrl="\\'/Request\\'"
| eval RequestId=replace(RequestId, "\\'", "")
| eval requestBody=replace(requestBody, "\\'", "") 
| eval requestBody=replace(requestBody, "\\", "")
| fields RequestId,responseBody, Time, responseCode,requestBody)

UNION

// 获取查询2的关联结果集，统一字段名
(index=docee_logs sourcetype="kube:container:xyz" 
    [ search index=docee_logs host=sl******* source="/var/log/containers/**.log" sourcetype="kube:container:*xyz*" "callType\\\" :[\\\"12\\\"" AND  "Activity"  AND "Time=" 
    | rex field=_raw "Time=(?<proctime>.*), apiVersion" 
    | where proctime > 3000 
    | eval RequestId=replace(RequestId, "\\'", "")
    | fields RequestId 
    | rename RequestId as search 
    | format]
| search "Sending merchant notification, Url" 
| rex "Sending merchant notification, Url=(?P<request_url>[^,]*)"
| eval RequestId=replace(vrolRequestId, "\\'", "")
| fields RequestId,request_url)

// 按RequestId合并所有关联字段
| stats values(responseBody) as responseBody values(Time) as Time values(responseCode) as responseCode values(requestBody) as requestBody values(request_url) as request_url by RequestId
| table RequestId,responseBody, Time, responseCode,requestBody,request_url

方案2：使用join关联（适合小数据集） #

先执行查询1获取核心数据，再通过RequestId关联查询2的request_url：

// 先获取查询1的核心数据
index=docee_logs sourcetype="kube:container:xyz" 
    [ search index=docee_logs host=sl***** source="/var/log/containers/**.log" sourcetype="kube:container:*xyz*" "callType\\\" :[\\\"12\\\"" AND  "Activity"  AND "Time=" 
    | rex field=_raw "Time=(?<proctime>.*), apiVersion" 
    | where proctime > 3000 
    | eval RequestId=replace(RequestId, "\\'", "")
    | fields RequestId 
    | rename RequestId as search 
    | format] 
| search "Activity" AND requestUrl="\\'/Request\\'"
| eval RequestId=replace(RequestId, "\\'", "")
| eval requestBody=replace(requestBody, "\\'", "") 
| eval requestBody=replace(requestBody, "\\", "")
// 通过RequestId关联查询2的request_url
| join type=left RequestId [
    search index=docee_logs sourcetype="kube:container:xyz" 
        [ search index=docee_logs host=sl******* source="/var/log/containers/**.log" sourcetype="kube:container:*xyz*" "callType\\\" :[\\\"12\\\"" AND  "Activity"  AND "Time=" 
        | rex field=_raw "Time=(?<proctime>.*), apiVersion" 
        | where proctime > 3000 
        | eval RequestId=replace(RequestId, "\\'", "")
        | fields RequestId 
        | rename RequestId as search 
        | format]
    | search "Sending merchant notification, Url" 
    | rex "Sending merchant notification, Url=(?P<request_url>[^,]*)"
    | eval RequestId=replace(vrolRequestId, "\\'", "")
    | fields RequestId,request_url
]
| table RequestId,responseBody, Time, responseCode,requestBody,request_url

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