Azure Data Explorer中基于时间范围关联两数据表的Kusto查询

首先先修正你原查询里的两处语法错误：

• 事件列表查询中，GUID == 'eventNotification' 前缺少 | where 运算符
• 时间范围列表查询中，project 里的 StarTime 是拼写错误，应为 StartTime

修正后的基础查询如下：

let events = NOTIFICATIONTABLE
| where TimeStamp > ago(365d) 
| where GUID == 'eventNotification' 
| project TimeStamp, Notification;
let timeslots = ACTIVITYTABLE
| where StartTime > ago(365d)
| where GUID == "machineActive"
| project StartTime, EndTime;

接下来提供两种可行的方法来筛选出落在任意活跃时间范围内的事件：

方法一：使用范围Join #

通过join kind=inner结合时间范围匹配条件，直接关联两张表，同时可保留活跃时间段的信息：

let events = NOTIFICATIONTABLE
| where TimeStamp > ago(365d) 
| where GUID == 'eventNotification' 
| project TimeStamp, Notification;
let timeslots = ACTIVITYTABLE
| where StartTime > ago(365d)
| where GUID == "machineActive"
| project StartTime, EndTime;
events
| join kind=inner timeslots on $left.TimeStamp between ($right.StartTime .. $right.EndTime)
| project TimeStamp, Notification, StartTime, EndTime // 可选保留活跃时间段信息

方法二：使用Exists子查询 #

如果仅需要筛选符合条件的事件、不需要保留活跃时间段信息，用exists子查询更简洁，性能表现也更优：

let timeslots = ACTIVITYTABLE
| where StartTime > ago(365d)
| where GUID == "machineActive"
| project StartTime, EndTime;
NOTIFICATIONTABLE
| where TimeStamp > ago(365d) 
| where GUID == 'eventNotification'
| where exists (
    timeslots
    | where TimeStamp between (StartTime .. EndTime)
)
| project TimeStamp, Notification

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