Splunk appendpipe子管道异常：无法为过热记录计算平均温度

背景信息 #

Lookup表查询及数据 #

Lookup表bsm_string_new_overheat_records.csv的查询语句：

| inputlookup bsm_string_new_overheat_records.csv
| rename CCU_location AS overheat_location
| table _time overheat_location start_CCU_AMBI_TEMP start_time_secs end_CCU_AMBI_TEMP end_time_secs overheat_duration_minutes

查询结果：

_time                   overheat_location   start_CCU_AMBI_TEMP start_time_secs end_CCU_AMBI_TEMP   end_time_secs   overheat_duration_minutes
2023-06-10 11:41:44 A10             29                      1686421604          23                  1686422504      15
2023-06-10 11:42:44 A10             29                      1686413444          23                  1686422564      152
2023-06-10 11:43:44 A10             29                      1686412844          23                  1686422624      163
2023-06-10 11:44:44 A10             29                      1686413984          23                  1686422684      145
2023-06-10 11:45:44 A10             29                      1686420584          23                  1686422744      36

单独计算平均温度的查询及结果 #

针对指定位置和时间范围，从index="battery_data" sourcetype="battery_field_data"计算平均温度的查询：

index="battery_data" sourcetype="battery_field_data"
    | rex field=Tag "^(?P<CCU_location>\w+)_BQMS\.\1\.((BMS_\1_(?P<tag_suffix>.*))|(MU(?P<MU_number>\d+)\.BMS_\1_MU\6_UNIT(?P<UNIT_number>\d+)_(?P<tag_type>.*)))"
    | where CCU_location=="A10"
    | eval start_time_secs=1686421604, end_time_secs=1686422504
    | where (tag_suffix == "CCU_AMBI_TEMP") AND (start_time_secs <= _time) AND (_time <= end_time_secs)
    | eval Value=round(Value*0.1, 2)
    | stats avg(Value) AS average_temperature_in_overheat latest(_time) AS _time latest(*) AS * BY CCU_location start_time_secs end_time_secs
    | table _time CCU_location start_time_secs end_time_secs average_temperature_in_overheat

查询结果：

_time                   CCU_location    start_time_secs     end_time_secs   average_temperature_in_overheat
2023-06-10 11:39:54.971 A10             1686421604          1686422504      17.45

故障查询语句 #

尝试为Lookup表每条记录计算对应平均温度的查询：

| inputlookup bsm_string_new_overheat_records.csv
| rename CCU_location AS overheat_location
| appendpipe [ search
index="battery_data" sourcetype="battery_field_data"
    | rex field=Tag "^(?P<CCU_location>\w+)_BQMS\.\1\.((BMS_\1_(?P<tag_suffix>.*))|(MU(?P<MU_number>\d+)\.BMS_\1_MU\6_UNIT(?P<UNIT_number>\d+)_(?P<tag_type>.*)))"
    | where CCU_location==overheat_location
    | where (tag_suffix == "CCU_AMBI_TEMP") AND (start_time_secs <= _time) AND (_time <= end_time_secs)
    | eval Value=round(Value*0.1, 2)
    | stats avg(Value) AS average_temperature_in_overheat latest(_time) AS _time latest(*) AS * BY CCU_location start_time_secs end_time_secs
]
| table _time overheat_location CCU_location start_time_secs end_time_secs average_temperature_in_overheat start_CCU_AMBI_TEMP end_CCU_AMBI_TEMP overheat_duration_minutes

实际仅返回Lookup表的5条原记录，子管道未生成带平均温度的记录。

故障点分析 #

• appendpipe用法错误：appendpipe仅对当前管道已生成的结果集做二次处理，无法在子管道中发起全新的search index=...查询来关联外部数据集。子管道内的search会忽略主管道的Lookup数据，从头查询索引数据，自然无法匹配主管道的字段条件。
• 字段作用域限制：子管道无法直接引用主管道的overheat_location、start_time_secs、end_time_secs字段，因为appendpipe的子管道是独立处理现有结果集，而非遍历每条Lookup记录去关联索引数据。

修正后的查询 #

改用map命令遍历Lookup表的每条记录，针对每条记录的位置和时间范围查询并计算平均温度，最后合并结果：

| inputlookup bsm_string_new_overheat_records.csv
| rename CCU_location AS overheat_location
| map maxsearches=0 [ search index="battery_data" sourcetype="battery_field_data"
    | rex field=Tag "^(?P<CCU_location>\w+)_BQMS\.\1\.((BMS_\1_(?P<tag_suffix>.*))|(MU(?P<MU_number>\d+)\.BMS_\1_MU\6_UNIT(?P<UNIT_number>\d+)_(?P<tag_type>.*)))"
    | where CCU_location="$overheat_location$"
    | where (tag_suffix == "CCU_AMBI_TEMP") AND ($start_time_secs$ <= _time) AND (_time <= $end_time_secs$)
    | eval Value=round(Value*0.1, 2)
    | stats avg(Value) AS average_temperature_in_overheat latest(_time) AS _time latest(*) AS * BY CCU_location start_time_secs end_time_secs
    | eval overheat_location="$overheat_location$", start_CCU_AMBI_TEMP="$start_CCU_AMBI_TEMP$", end_CCU_AMBI_TEMP="$end_CCU_AMBI_TEMP$", overheat_duration_minutes="$overheat_duration_minutes$"
]
| table _time overheat_location CCU_location start_time_secs end_time_secs average_temperature_in_overheat start_CCU_AMBI_TEMP end_CCU_AMBI_TEMP overheat_duration_minutes

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