如何修改KQL查询，仅返回含Module1的OperationID及计时数据？

原查询用于计算OperationID的端到端时长及外部工作流时长，要仅返回Message字段包含"Module1"的OperationID数据，可按以下方式修改：

let x= datatable(timestamp:datetime,OperationID: string,Message: string)[
 "2022-06-30 04:10:00.460" ,"fbf759a0-d4be-4d07-adfb-7090a207e667","Work flow Started"
   ,"2022-06-30 04:12:00.091" ,"fbf759a0-d4be-4d07-adfb-7090a207e667","External Work Flow1 Started"
   ,"2022-06-30 04:10:00.460" ,"fbf759a0-d4be-4d07-adfb-7090a207e667","External Work Flow2 Started"
   ,"2022-06-30 04:10:00.460" ,"fbf759a0-d4be-4d07-adfb-7090a207e667","Workflow in progress"
   ,"2022-06-30 04:10:00.460" ,"fbf759a0-d4be-4d07-adfb-7090a207e667","Workflow in progress for Module1"
   ,"2022-06-30 04:14:10.791" ,"fbf759a0-d4be-4d07-adfb-7090a207e667","External Work Flow1 Ended"
   ,"2022-06-30 04:15:10.460" ,"fbf759a0-d4be-4d07-adfb-7090a207e667","External Work Flow2 Ended"
   ,"2022-06-30 04:20:04.343" ,"fbf759a0-d4be-4d07-adfb-7090a207e667","Work flow Ended"
];
// 提取所有Message包含Module1的OperationID并去重
let valid_ops = x 
| where Message contains "Module1" 
| distinct OperationID;
// 基于有效OperationID计算端到端时长
let t1=x
| where OperationID in (valid_ops)
| summarize EndtoEndTimings=round((max(timestamp) - min(timestamp)) / 1s) by OperationID;
// 基于有效OperationID计算External Work Flow1时长
let t2=x
| where OperationID in (valid_ops)
| where Message contains "External Work Flow1 Started" or Message contains "External Work Flow1 Ended"
| summarize ExternalWorkflow1Timings=round((max(timestamp) - min(timestamp)) / 1s) by OperationID;
// 基于有效OperationID计算External Work Flow2时长
let t3=x
| where OperationID in (valid_ops)
| where Message contains "External Work Flow2 Started" or Message contains "External Work Flow2 Ended"
| summarize ExternalWorkflow2Timings=round((max(timestamp) - min(timestamp)) / 1s) by OperationID;
let t4=t1
| join kind=inner t2 on OperationID;
t4
| join kind=inner t3 on OperationID
| project-away OperationID1,OperationID2

修改说明 #

• 新增valid_ops表：先筛选出所有Message包含"Module1"的OperationID并去重，得到需要保留的有效OperationID集合。
• 在t1、t2、t3的查询中添加where OperationID in (valid_ops)条件，确保只处理符合要求的OperationID数据，避免无效计算。
• 保持原有的时长计算逻辑不变，仅缩小数据处理范围到目标OperationID。

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