Azure KQL反连接过滤异常：如何排除已更新的即将过期密钥日志

问题描述 #

需求是列出30天内即将过期的密钥日志，同时排除其中已通过SecretSet操作更新过的密钥条目。当前使用anti join实现排除逻辑，但出现异常：密钥test45存在于SecretNearExpiryLogs中，且未在SecretSetAudit中出现，却仍保留在最终结果里（与预期不符）。

问题原因分析 #

1. SecretSetAudit过滤条件冗余：原查询对SecretSetAudit添加了ExpiryDate <= now() + 30d的过滤，导致部分已更新但新过期时间超过30天的密钥无法被纳入排除列表，不符合“只要更新过就排除”的业务逻辑。
2. 密钥名称匹配隐患：未统一密钥名称的大小写，或正则提取逻辑可能因id_s格式变化导致提取失败，使得部分SecretSet操作的密钥无法被正确捕获。
3. 无效数据干扰：未过滤SecretSetAudit中提取失败的空值条目，可能影响join匹配逻辑。

优化后的KQL查询 #

// 定义30天内即将过期的密钥日志
let SecretNearExpiryLogs = AzureDiagnostics
| where OperationName contains "SecretNearExpiry"
| extend ExpiryDate = datetime(1970-01-01) + totimespan(eventGridEventProperties_data_EXP_d * 1s)
| where ExpiryDate between (now() .. now() + 30d)
| project
    SecretName = eventGridEventProperties_data_ObjectName_s,
    TenantId,
    TimeGenerated,
    ResourceId,
    Resource,
    OperationName,
    SecretId = eventGridEventProperties_data_Id_s,
    _ResourceId;

// 定义所有执行过SecretSet操作的密钥
let SecretSetAudit = AzureDiagnostics
| where Category == "AuditEvent"
| where OperationName == "SecretSet"
| extend SecretName = extract(@"https://[^/]+/secrets/([^/?]+)", 1, id_s)
| where isnotempty(SecretName)
| extend SecretName = tolower(SecretName)
| project SecretName;

// 排除已更新的密钥，保留未被更新的即将过期密钥
SecretNearExpiryLogs
| extend SecretName = tolower(SecretName)
| join kind=anti (SecretSetAudit) on SecretName

关键优化点 #

• 移除冗余过滤：删除SecretSetAudit中的过期时间限制，确保所有SecretSet操作的密钥都被纳入排除范围。
• 统一字段与格式：将长字段名重命名为SecretName提升可读性，通过tolower统一大小写，避免匹配失败。
• 过滤无效数据：添加isnotempty(SecretName)过滤提取失败的条目，避免无效数据干扰。
• 简化时间范围：用between替代原有的双重条件，逻辑更清晰。

验证步骤 #

若test45仍未按预期处理，可执行以下验证：

• 单独运行SecretSetAudit查询，确认是否包含test45的条目，检查密钥名称提取是否正确。
• 查看SecretNearExpiryLogs中test45的SecretName值，是否存在空格、特殊字符或大小写差异。
• 验证AzureDiagnostics中是否存在test45的SecretSet操作日志，确认Category和OperationName符合过滤条件。

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