Azure Alerts: How to check Key Vault secrets/certificates expirations across multiple timeframes

Question

I need to monitor Azure Key Vault secrets and certificates expiration dates and send alerts at specific intervals (30 days, 15 days, 5 days, etc. before expiration).

I'm using Azure Monitor Alerts with a Kusto query to detect near-expiry events:

let keyvault = "mykeyvault";
AzureDiagnostics
| where TimeGenerated > ago(30d)
| where OperationName in ("SecretNearExpiryEventGridNotification", "CertificateNearExpiryEventGridNotification")
| where column_ifexists("eventGridEventProperties_data_VaultName_s", "") == keyvault
| extend
    ObjectName = column_ifexists("eventGridEventProperties_data_ObjectName_s", ""),
    ExpirationTimestamp = column_ifexists("eventGridEventProperties_data_EXP_d", 0)
| where ExpirationTimestamp > 0
| extend
    DaysUntilExpiration = datetime_diff('day', unixtime_seconds_todatetime(ExpirationTimestamp), now()),
    ObjectType = iff(OperationName contains "Secret", "Secret", "Certificate"),
    ExpirationDateString = format_datetime(unixtime_seconds_todatetime(ExpirationTimestamp), 'yyyy-MM-dd')
| where DaysUntilExpiration in (30, 15, 5, 3, 1) or DaysUntilExpiration < 0    
| extend
    VaultName = keyvault,
    ExpiredStatus = iff(DaysUntilExpiration <= 0, "Expired", "Active")
| project
    TimeGenerated,
    VaultName,
    ObjectName,
    ObjectType,
    DaysUntilExpiration,
    ExpirationDateString,
    ExpiredStatus
| order by DaysUntilExpiration asc

I configured the alert with these settings:

evaluation_frequency    = "P1D"
window_duration         = "P1D"
time_aggregation_method = "Count"
operator                = "GreaterThanOrEqual"
threshold               = 1

My issue: Even though my query looks back 30 days (TimeGenerated > ago(30d)), the alert evaluation only considers events from the last day due to the window_duration setting.

The fundamental problem is that Key Vault only generates a single near-expiry event (at 30 days before expiration), but I need to trigger alerts at multiple intervals (30, 15, 5, 3, 1 day(s)).

What I've tried:

• Increasing window_duration (limited to 1 day for log alerts)
• Forcing a longer timeframe in the Kusto query (gets overridden by window_duration)

Is there a way to make Azure Monitor Alerts consider historical near-expiry events to trigger alerts at different intervals? Or do I need to switch to an alternative approach ?

Thanks for your assistance.

Answer 1

Azure Alerts: How to check Key Vault secrets/certificates expirations across multiple timeframes

AFAIK, there is no way to check the output in Log Analytics with specific intervals using a single query. Instead, you can use PowerShell along with the Logic App method approach.

Alternatively, you can create an alert for Key Vault certificates by following the approach below.

Email Configuration

Below is the PowerShell code to fetch Key Vault secrets and certificate expiry dates.

# Set the Azure Subscription Context (Ensure You Are Logged In)
$subscriptionId = (Get-AzSubscription).Id  # Automatically fetches the active subscription
Set-AzContext -SubscriptionId $subscriptionId

$todaydate = Get-Date
$alertIntervals = @(30, 15, 5, 3, 1)  # Days before expiry to trigger alerts

# Get all Key Vaults in the subscription
$allkeyvaults = Get-AzKeyVault

foreach ($keyVault in $allkeyvaults) {
    Write-Output " Checking Key Vault: $($keyVault.VaultName)"
    
  
    $keyvaultsecrets = Get-AzKeyVaultSecret -VaultName $keyVault.VaultName | Select-Object VaultName, Name, Expires

    foreach ($secret in $keyvaultsecrets) {
        if ($secret.Expires) {
            try {
                $secretexpirydate = [datetime]$secret.Expires
                $pendingdays = ($secretexpirydate - $todaydate).Days
                
                if ($pendingdays -in $alertIntervals) {
                    Write-Output " ALERT: Secret '$($secret.Name)' in Vault '$($keyVault.VaultName)' is expiring in $pendingdays days! Expiry Date: $secretexpirydate."
                } elseif ($pendingdays -lt 0) {
                    Write-Output " EXPIRED: Secret '$($secret.Name)' in Vault '$($keyVault.VaultName)' expired on $secretexpirydate."
                } else {
                    Write-Output " OK: Secret '$($secret.Name)' in Vault '$($keyVault.VaultName)' is safe. Expiry Date: $secretexpirydate."
                }
            } catch {
                Write-Output " ERROR: Invalid expiry date format for secret '$($secret.Name)' in Vault '$($keyVault.VaultName)'."
            }
        } else {
            Write-Output " No Expiry Date: Secret '$($secret.Name)' in Vault '$($keyVault.VaultName)' does not have an expiration date set."
        }
    }

   
    $keyvaultcertificates = Get-AzKeyVaultCertificate -VaultName $keyVault.VaultName | Select-Object VaultName, Name, Expires

    foreach ($certificate in $keyvaultcertificates) {
        if ($certificate.Expires) {
            try {
                $certificateexpirydate = [datetime]$certificate.Expires
                $pendingdays = ($certificateexpirydate - $todaydate).Days
                
                if ($pendingdays -in $alertIntervals) {
                    Write-Output " ALERT: Certificate '$($certificate.Name)' in Vault '$($keyVault.VaultName)' is expiring in $pendingdays days! Expiry Date: $certificateexpirydate."
                } elseif ($pendingdays -lt 0) {
                    Write-Output " EXPIRED: Certificate '$($certificate.Name)' in Vault '$($keyVault.VaultName)' expired on $certificateexpirydate."
                } else {
                    Write-Output " OK: Certificate '$($certificate.Name)' in Vault '$($keyVault.VaultName)' is safe. Expiry Date: $certificateexpirydate."
                }
            } catch {
                Write-Output " ERROR: Invalid expiry date format for certificate '$($certificate.Name)' in Vault '$($keyVault.VaultName)'."
            }
        } else {
            Write-Output "No Expiry Date: Certificate '$($certificate.Name)' in Vault '$($keyVault.VaultName)' does not have an expiration date set."
        }
    }
}

Output:

You can follow the links below related to email alerts and the Logic App process.

Follow the Link and Logic app trigger and Link2