求KQL查询：列出密钥保管库中已过期及7/10天内过期的密钥与机密

原查询依赖SecretNearExpiry事件（默认提前30天触发），无法灵活设置预警天数。要实现自定义天数的过期排查，需直接获取机密和密钥的过期时间属性，再进行时间范围过滤，以下是两种可行方案：

方法1：Azure Resource Graph查询（推荐） #

直接从Azure资源元数据拉取信息，无需依赖日志事件，结果更准确灵活：

resources
| where type in ("Microsoft.KeyVault/vaults/secrets", "Microsoft.KeyVault/vaults/keys")
| extend expiryDate = todatetime(properties.expirationDate)
| extend daysUntilExpiry = datetime_diff('day', expiryDate, now())
| where daysUntilExpiry between (-1000, 10) // 覆盖已过期（负数）和10天内过期的对象，可按需调整范围
| project 
    KeyVaultName = split(id, '/')[8],
    ObjectType = case(type == "Microsoft.KeyVault/vaults/secrets", "Secret", "Key"),
    ObjectName = name,
    ExpiryDate = expiryDate,
    DaysUntilExpiry = daysUntilExpiry,
    Status = case(
        daysUntilExpiry < 0, "已过期",
        daysUntilExpiry <=7, "7天内过期",
        daysUntilExpiry <=10, "10天内过期",
        "正常"
    )
| order by DaysUntilExpiry asc

调整说明： #

• 若只需排查7天内过期和已过期的对象，将between (-1000, 10)改为between (-1000,7)即可
• Status字段自动标记状态，便于快速识别对象的过期阶段

方法2：Log Analytics的AzureKeyVault表查询 #

如果密钥保管库日志已收集到Log Analytics，可从审计日志中提取最新的过期信息：

AzureKeyVault
| where OperationName in ("Get Secret", "Get Key") // 获取对象的最新属性记录
| extend expiryDate = todatetime(properties.expiresOn)
| where isnotnull(expiryDate)
| summarize arg_max(TimeGenerated, *) by VaultName, ObjectName, ObjectType
| extend daysUntilExpiry = datetime_diff('day', expiryDate, now())
| where daysUntilExpiry between (-1000, 10)
| project 
    VaultName,
    ObjectType,
    ObjectName,
    ExpiryDate = expiryDate,
    DaysUntilExpiry = daysUntilExpiry,
    Status = case(
        daysUntilExpiry <0, "已过期",
        daysUntilExpiry <=7, "7天内过期",
        daysUntilExpiry <=10, "10天内过期",
        "正常"
    )
| order by DaysUntilExpiry asc

关键说明： #

• arg_max(TimeGenerated, *)确保获取每个对象的最新过期时间，避免旧数据干扰
• 同样可通过修改between的数值自定义预警时间范围

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