如何获取孤立Key Vault列表？KQL查询优化求助

原查询存在的问题 #

1. 操作范围过窄：仅筛选了Microsoft.KeyVault/vaults/write和delete操作，遗漏了密钥读取、证书访问等核心访问行为，无法准确判断Vault是否被访问。
2. 未覆盖无活动Vault：AzureActivity仅记录有操作的Vault，从未产生过任何操作的Vault不会出现在该表中，因此isnull(LatestActivityTime)条件永远无法触发。
3. 缺失空Vault检测逻辑：原查询未处理“无密钥、证书、机密”的Vault检测需求。

一、检测30天内未被访问的Key Vault #

通过AzureResources获取所有Key Vault列表，左连接操作日志与审计日志的最新活动记录，覆盖从未有过活动的Vault：

// 获取所有Azure Key Vault
let allKeyVaults = AzureResources
| where type == "microsoft.keyvault/vaults"
| project KeyVaultName = name, ResourceId = id, SubscriptionId, ResourceGroup;

// 获取所有Key Vault的最新访问/操作时间（合并操作日志与审计日志）
let latestActivity = union isfuzzy=true
    // 操作日志中的Vault相关操作
    (AzureActivity
    | where ResourceProvider == "Microsoft.KeyVault"
    | where OperationName startswith "Microsoft.KeyVault/"
    | extend KeyVaultName = tostring(split(Resource, '/')[8])
    | summarize LatestActivity = max(TimeGenerated) by KeyVaultName),
    // 审计日志中的访问记录（覆盖密钥读取、列表等场景）
    (KeyVaultAuditLogs
    | extend KeyVaultName = tostring(split(ResourceId, '/')[8])
    | summarize LatestActivity = max(TimeGenerated) by KeyVaultName);

// 筛选无活动或最后活动超过30天的Vault
allKeyVaults
| left join latestActivity on KeyVaultName
| where isnull(LatestActivity) or LatestActivity <= ago(30d)
| project KeyVaultName, ResourceGroup, SubscriptionId, LatestActivity

二、检测空的Key Vault（无密钥、证书、机密） #

方法1：使用KeyVaultResources表（推荐，数据更准确） #

KeyVaultResources
| where Type in ("Microsoft.KeyVault/vaults/keys", "Microsoft.KeyVault/vaults/secrets", "Microsoft.KeyVault/vaults/certificates")
| extend KeyVaultName = tostring(split(ResourceId, '/')[8])
| summarize ObjectCount = count() by KeyVaultName
// 左连接所有Key Vault，找出无对象的条目
| union kind=leftouter (
    AzureResources
    | where type == "microsoft.keyvault/vaults"
    | project KeyVaultName = name
)
| where isnull(ObjectCount) or ObjectCount == 0
| project KeyVaultName

方法2：通过操作日志判断（当KeyVaultResources不可用时） #

// 获取所有Key Vault
let allKeyVaults = AzureResources
| where type == "microsoft.keyvault/vaults"
| project KeyVaultName = name;

// 获取有创建密钥/证书/机密操作的Vault
let vaultsWithObjects = AzureActivity
| where ResourceProvider == "Microsoft.KeyVault"
| where OperationName in (
    "Microsoft.KeyVault/vaults/keys/write",
    "Microsoft.KeyVault/vaults/secrets/write",
    "Microsoft.KeyVault/vaults/certificates/write"
)
| extend KeyVaultName = tostring(split(Resource, '/')[8])
| distinct KeyVaultName;

// 筛选从未创建过任何对象的Vault
allKeyVaults
| where KeyVaultName !in (vaultsWithObjects)
| project KeyVaultName

三、合并两类检测结果（可选） #

如果需要一次性返回符合任一条件的Vault，可合并两个查询结果并去重：

// 30天未访问的Vault
let inactiveVaults = (
    let allKeyVaults = AzureResources
    | where type == "microsoft.keyvault/vaults"
    | project KeyVaultName = name, ResourceGroup, SubscriptionId;
    let latestActivity = union isfuzzy=true
        (AzureActivity
        | where ResourceProvider == "Microsoft.KeyVault"
        | where OperationName startswith "Microsoft.KeyVault/"
        | extend KeyVaultName = tostring(split(Resource, '/')[8])
        | summarize LatestActivity = max(TimeGenerated) by KeyVaultName),
        (KeyVaultAuditLogs
        | extend KeyVaultName = tostring(split(ResourceId, '/')[8])
        | summarize LatestActivity = max(TimeGenerated) by KeyVaultName);
    allKeyVaults
    | left join latestActivity on KeyVaultName
    | where isnull(LatestActivity) or LatestActivity <= ago(30d)
    | project KeyVaultName, ResourceGroup, SubscriptionId, Reason = "30天内未被访问"
);

// 空Vault
let emptyVaults = (
    KeyVaultResources
    | where Type in ("Microsoft.KeyVault/vaults/keys", "Microsoft.KeyVault/vaults/secrets", "Microsoft.KeyVault/vaults/certificates")
    | extend KeyVaultName = tostring(split(ResourceId, '/')[8])
    | summarize ObjectCount = count() by KeyVaultName
    | union kind=leftouter (
        AzureResources
        | where type == "microsoft.keyvault/vaults"
        | project KeyVaultName = name
    )
    | where isnull(ObjectCount) or ObjectCount == 0
    | join kind=inner (AzureResources | where type == "microsoft.keyvault/vaults" | project KeyVaultName = name, ResourceGroup, SubscriptionId) on KeyVaultName
    | project KeyVaultName, ResourceGroup, SubscriptionId, Reason = "无密钥/证书/机密"
);

// 合并并去重
union inactiveVaults, emptyVaults
| distinct KeyVaultName, ResourceGroup, SubscriptionId, Reason

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