如何用Azure KQL查询安全组中60天未活跃的用户？

前提说明 #

要实现这个需求，需确保你的Log Analytics工作区已接入Azure AD审核日志（包含安全组成员变更记录）和Azure AD登录日志。

步骤1：提取目标安全组的成员列表 #

先通过审核日志获取指定安全组的所有有效成员，替换你的安全组名称为实际组名：

AuditLogs
| where OperationName in ("Add member to group", "Update group members", "Remove member from group")
| extend GroupName = tostring(TargetResources[0].displayName)
| where GroupName == "你的安全组名称"
| extend UserUPN = tostring(TargetResources[1].userPrincipalName)
| extend UserAction = iif(OperationName == "Remove member from group", "Removed", "Added")
| summarize LatestAction = max(TimeGenerated) by UserUPN, UserAction
| where UserAction != "Removed"
| distinct UserUPN, UserDisplayName = tostring(TargetResources[1].displayName)

这段逻辑会自动排除已从组内移除的用户，只保留当前有效成员。

步骤2：获取近60天有登录记录的活跃用户 #

基于你已掌握的登录日志查询，简化后得到活跃用户清单：

SigninLogs
| where TimeGenerated >= ago(60d)
| distinct UserPrincipalName

步骤3：关联数据筛选未登录用户 #

将前两个查询合并，用leftanti连接筛选出安全组内无登录记录的成员：

// 定义安全组有效成员表
let GroupValidMembers = AuditLogs
| where OperationName in ("Add member to group", "Update group members", "Remove member from group")
| extend GroupName = tostring(TargetResources[0].displayName)
| where GroupName == "你的安全组名称"
| extend UserUPN = tostring(TargetResources[1].userPrincipalName)
| extend UserAction = iif(OperationName == "Remove member from group", "Removed", "Added")
| summarize LatestAction = max(TimeGenerated) by UserUPN, UserAction
| where UserAction != "Removed"
| distinct UserUPN, UserDisplayName = tostring(TargetResources[1].displayName);

// 定义近60天活跃用户表
let ActiveUsers = SigninLogs
| where TimeGenerated >= ago(60d)
| distinct UserPrincipalName;

// 输出未登录用户清单
GroupValidMembers
| left join kind=leftanti ActiveUsers on $left.UserUPN == $right.UserPrincipalName
| project UserDisplayName, UserUPN
| sort by UserDisplayName asc

leftanti连接会返回仅存在于安全组成员表、但不在活跃用户表中的记录，也就是近60天未登录的目标组用户。

适配提示 #

• 如果你的环境中安全组日志的TargetResources结构不同，可通过查看单条AuditLogs记录的JSON结构，调整字段提取逻辑。
• 若无需排除已移除用户，可删除Remove member from group相关的筛选条件。

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