Splunk跨日时段事件对比查询求助：8-18点数据按Journey分组

需求说明 #

需要对比当日8:00-18:00与前日同时段raw_iis索引中，ACOMPANHAMENTO、MelhorConversa、Reagendamento三类Journey的事件数据，统计请求总量、响应达标率（响应时间<6s且状态码为200/201/204的占比）、平均响应时间等指标，并按Journey分组展示。当前使用multisearch实现未得到正确结果。

原查询代码 #

| multisearch  
[search
index=raw_iis   
| search 
(
 (ACOMPANHAMENTO*)
)
| timechart  span=1d
count(eval(time_taken >0)) as TotalRequisições
count(eval(time_taken <6000 AND (sc_status=="200" OR sc_status=="201" OR sc_status=="204"))) as QtdRequisicoesMenor6seg 
eval(avg(time_taken)) as TempoMedioResp
| eval "% abaixo de 6s" =  (QtdRequisicoesMenor6seg/TotalRequisições)*100
| appendcols 
  [ search index=raw_iis 
    (earliest=-2d@d+8h latest=-2d@d+18h)  
  | search  
  (
    ACOMPANHAMENTO*
  ) 
  | timechart span=1d 
    count(eval(time_taken >0)) as DiaAntTotalRequisições
    count(eval(time_taken <6000 AND (sc_status=="200" OR sc_status=="201" OR sc_status=="204"))) as DiaAntQtdRequisicoesMenor6seg 
    eval(avg(time_taken)) as DiaAntTempoMedioResp
  | eval "Dia Ant % abaixo de 6s" =  (DiaAntQtdRequisicoesMenor6seg/DiaAntTotalRequisições)*100
  | head 1 ]
| eval "Diferença do dia Anterior" =  ('% abaixo de 6s') - ('Dia Ant % abaixo de 6s')
| eval Jornada = "Acompanhamento"]
| table Jornada, _time, TotalRequisições, QtdRequisicoesMenor6seg, TempoMedioResp, "% abaixo de 6s", "Diferença do dia Anterior"

[search 
index=raw_iis   
| search 
( 
  (MelhorConversa*) 
)
| timechart  span=1d
count(eval(time_taken >0)) as TotalRequisições
count(eval(time_taken <6000 AND (sc_status=="200" OR sc_status=="201" OR sc_status=="204"))) as QtdRequisicoesMenor6seg 
eval(avg(time_taken)) as TempoMedioResp
| eval "% abaixo de 6s" =  (QtdRequisicoesMenor6seg/TotalRequisições)*100
| appendcols 
  [ search index=raw_iis 
    (earliest=-2d@d+8h latest=-2d@d+18h)  
  | search  (MelhorConversa*) 
  | timechart span=1d 
    count(eval(time_taken >0)) as DiaAntTotalRequisições
    count(eval(time_taken <6000 AND (sc_status=="200" OR sc_status=="201" OR sc_status=="204"))) as DiaAntQtdRequisicoesMenor6seg 
  eval(avg(time_taken)) as DiaAntTempoMedioResp
  | eval "Dia Ant % abaixo de 6s" =  (DiaAntQtdRequisicoesMenor6seg/DiaAntTotalRequisições)*100
  | head 1 ]
| eval "Diferença do dia Anterior" =  ('% abaixo de 6s') - ('Dia Ant % abaixo de 6s')
| eval Jornada = "MelhorConversa"]
| table Jornada, _time, TotalRequisições, QtdRequisicoesMenor6seg, TempoMedioResp, "% abaixo de 6s", "Diferença do dia Anterior" 

[search 
index=raw_iis   
| search 
( 
  (Reagendamento*) 
)
| timechart  span=1d
count(eval(time_taken >0)) as TotalRequisições
count(eval(time_taken <6000 AND (sc_status=="200" OR sc_status=="201" OR sc_status=="204"))) as QtdRequisicoesMenor6seg 
eval(avg(time_taken)) as TempoMedioResp
| eval "% abaixo de 6s" =  (QtdRequisicoesMenor6seg/TotalRequisições)*100
| appendcols 
  [ search index=raw_iis 
    (earliest=-2d@d+8h latest=-2d@d+18h)  
  | search  (Reagendamento*) 
  | timechart span=1d 
    count(eval(time_taken >0)) as DiaAntTotalRequisições
    count(eval(time_taken <6000 AND (sc_status=="200" OR sc_status=="201" OR sc_status=="204"))) as DiaAntQtdRequisicoesMenor6seg 
  eval(avg(time_taken)) as DiaAntTempoMedioResp
  | eval "Dia Ant % abaixo de 6s" =  (DiaAntQtdRequisicoesMenor6seg/DiaAntTotalRequisições)*100
  | head 1 ]
| eval "Diferença do dia Anterior" =  ('% abaixo de 6s') - ('Dia Ant % abaixo de 6s')
| eval Jornada = "Reagendamento"]
| table Jornada, _time, TotalRequisições, QtdRequisicoesMenor6seg, TempoMedioResp, "% abaixo de 6s", "Diferença do dia Anterior"

核心问题分析 #

1. 时间范围缺失：原查询中当日数据未添加earliest=@d+8h latest=@d+18h限制，导致统计的是全天数据，而非目标8-18点时段。
2. 冗余重复逻辑：三次重复相同的搜索聚合逻辑，效率极低且易出错。
3. 数据关联风险：使用appendcols按行合并当日与前日数据，若两个子查询的_time不匹配，会直接导致数据错位。
4. 模糊搜索隐患：ACOMPANHAMENTO*这种写法会搜索所有包含该字符串的字段，而非指定Journey字段的值，可能引入大量无关数据。
5. 聚合逻辑不合理：timechart span=1d针对8-18小时的时段完全冗余，反而可能干扰数据聚合结果。

优化后的查询代码 #

// 1. 统一搜索当日和前日的目标时段数据，过滤三类Journey
index=raw_iis 
(
  earliest=@d+8h latest=@d+18h 
  OR earliest=-2d@d+8h latest=-2d@d+18h
)
// 假设Journey字段名为journey，根据实际字段调整，避免模糊搜索
(journey=ACOMPANHAMENTO* OR journey=MelhorConversa* OR journey=Reagendamento*)

// 2. 标记数据所属时段：当日/前日
| eval periodo=if(_time>=relative_time(now(), "@d+8h") AND _time<=relative_time(now(), "@d+18h"), "当日", "前日")

// 3. 按Journey和时段聚合统计指标
| stats 
  count(eval(time_taken>0)) as 总请求量
  count(eval(time_taken<6000 AND (sc_status=="200" OR sc_status=="201" OR sc_status=="204"))) as 达标请求量
  avg(time_taken) as 平均响应时间
  by journey, periodo

// 4. 将当日和前日数据转置为列，方便对比
| xyseries journey periodo 总请求量,达标请求量,平均响应时间

// 5. 计算达标率和差值
| eval "当日达标率(%)"=round(('当日.达标请求量'/'当日.总请求量')*100,2)
| eval "前日达标率(%)"=round(('前日.达标请求量'/'前日.总请求量')*100,2)
| eval "达标率差值(%)"=round('当日达标率(%)'-'前日达标率(%)',2)

// 6. 重命名Journey列，展示最终字段
| rename journey as 旅程
| table 旅程, 当日.总请求量, 当日.达标请求量, 当日.平均响应时间, "当日达标率(%)", 前日.总请求量, 前日.达标请求量, 前日.平均响应时间, "前日达标率(%)", "达标率差值(%)"

排查思路 #

• 验证单时段数据：单独执行index=raw_iis earliest=@d+8h latest=@d+18h journey=ACOMPANHAMENTO* | stats count，确认目标时段有数据，且数量符合预期。
• 检查时间范围语法：添加| eval 时间=strftime(_time,"%Y-%m-%d %H:%M:%S") | table 时间，查看数据是否准确落在8-18点区间。
• 确认字段匹配：检查raw_iis索引中Journey对应的字段名（比如是否是journey、Journey或其他），避免模糊搜索导致的误匹配。
• 验证聚合逻辑：单独计算某一类Journey的达标率，比如index=raw_iis earliest=@d+8h latest=@d+18h journey=ACOMPANHAMENTO* | stats count as 总请求, count(eval(time_taken<6000 AND sc_status IN ("200","201","204"))) as 达标 | eval 达标率=达标/总请求*100，确认计算结果正确。
• 排查数据错位：若使用原appendcols，检查两个子查询的_time是否一致，可添加| table _time对比，确保行对齐。

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