请求编写Sentinel KQL查询获取指定用户每日首次/末次活动

需求：编写KQL查询，获取指定用户每日的首次活动（含活动类型如Microsoft Teams及对应时间）、末次活动的类型与时间。

现有问题：以下查询返回该用户的所有活动，无法提取每日唯一的首次和末次活动：

OfficeActivity
| where UserId == 'UserId'
| summarize FirstActivity = min(TimeGenerated), LastActivity = max(TimeGenerated) by TimeGenerated, Application, Operation
| join 
(
    OfficeActivity
    | summarize FirstActivity2 = min(TimeGenerated), LastActivity2 = max(TimeGenerated) by TimeGenerated, Application, OfficeWorkload
)
on TimeGenerated
| project FirstActivity, LastActivity, Application, OfficeWorkload, Operation
| sort by FirstActivity asc

修正后的查询 #

// 第一步：筛选指定用户活动，按日期分组获取每日首次、末次活动时间
let daily_time_bounds = OfficeActivity
| where UserId == '替换为目标用户ID'
| summarize 
    FirstActivityTime = min(TimeGenerated),
    LastActivityTime = max(TimeGenerated)
    by bin(TimeGenerated, 1d) as ActivityDate;

// 第二步：关联原表获取首次活动详情
let first_activities = OfficeActivity
| where UserId == '替换为目标用户ID'
| join daily_time_bounds on $left.TimeGenerated == $right.FirstActivityTime
| project 
    ActivityDate,
    FirstActivityTime,
    FirstActivityApp = Application,
    FirstActivityWorkload = OfficeWorkload,
    FirstActivityOperation = Operation;

// 第三步：关联原表获取末次活动详情
let last_activities = OfficeActivity
| where UserId == '替换为目标用户ID'
| join daily_time_bounds on $left.TimeGenerated == $right.LastActivityTime
| project 
    ActivityDate,
    LastActivityTime,
    LastActivityApp = Application,
    LastActivityWorkload = OfficeWorkload,
    LastActivityOperation = Operation;

// 第四步：合并结果并按日期排序
first_activities
| join last_activities on ActivityDate
| project 
    ActivityDate,
    FirstActivityTime,
    FirstActivityApp,
    FirstActivityWorkload,
    FirstActivityOperation,
    LastActivityTime,
    LastActivityApp,
    LastActivityWorkload,
    LastActivityOperation
| sort by ActivityDate asc

关键逻辑说明 #

1. 按天聚合：用bin(TimeGenerated, 1d)将时间按天分组，确保每个日期仅生成一组时间边界（首次/末次时间）。
2. 提取时间边界：通过summarize计算每日的最早、最晚活动时间，作为匹配依据。
3. 关联获取详情：分别将首次、末次时间与原表关联，拿到对应时间点的活动类型、应用、Workload等信息。
4. 合并输出：将首次、末次活动信息按日期合并，输出清晰的每日活动概览。

注意事项 #

• 替换查询中的'替换为目标用户ID'为实际需要查询的用户ID。
• 若同一用户在同一时间点存在多条活动记录，可根据需求添加take 1或额外筛选条件去重。

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