KQL query to list the secrets and keys in keyvault that are expired or going to expire in 7 days

Please provide the KQL query to list all the secrets and keys in the keyvaults that are going to expire within 7 or 10 days and secrets and keys which are already expired.

Tried below KQL query it works but as it's using "SecretNearExpiry" events are logged 30 days before expiration, and I don't have the flexibility to log the secrets or keys 5 days or 7 days before expiration

let lastIndexof = (input:string, lookup: string) {
strlen(input) - indexof(reverse(input), reverse(lookup)) - strlen(lookup)
};
AzureDiagnostics
| where OperationName contains "SecretNearExpiry" or OperationName contains "SecretExpired"
| extend KeyVaultName = column_ifexists('eventGridEventProperties_data_VaultName_s', 'N/A')
| extend KeyVaultType = column_ifexists('eventGridEventProperties_data_ObjectType_s', 'N/A')
| extend Name = column_ifexists('eventGridEventProperties_data_ObjectName_s', 'N/A')
| extend EventType =  column_ifexists('eventGridEventProperties_eventType_s', 'N/A')
| extend EventType =  substring(EventType, (lastIndexof(EventType, '.') + 1))
| extend Expiry = column_ifexists('eventGridEventProperties_data_EXP_d', 0)
| extend Expiry = format_datetime(unixtime_seconds_todatetime(Expiry), 'yyyy/MM/dd HH:mm:ss')
| where Name != '' or EventType != ''
| where TimeGenerated > datetime_add('year', -10, now())
| project KeyVaultName, KeyVaultType, Name, EventType, Expiry
| order by Expiry

fetching keys using KQL logs is not possible

In general keyvault is meant to store the info securly. Which means a monitor Kusto Query Language (KQL) are primarily used for monitoring and querying logs and metrics cant be provided the direct access to fetch the key or secret info.

Instead, we can use powershell for this to achieve the requirement.

Powershell.ps1

$keyVaultName = "sakkbvksv"
$daysThreshold = 7  

$expirationThresholdDate = (Get-Date).AddDays($daysThreshold)

$allSecrets = Get-AzKeyVaultSecret -VaultName $keyVaultName
$allSecrets | ForEach-Object {
    Write-Output "Secret Name: $($_.Name), Expires On: $($_.Expires)"
}

$allKeys = Get-AzKeyVaultKey -VaultName $keyVaultName
$allKeys | ForEach-Object {
    Write-Output "Key Name: $($_.Name), Expires On: $($_.Attributes.Expires)"
}

$secrets = $allSecrets | Where-Object {
    $_.Expires -ne $null -and ($_.Expires -lt $expirationThresholdDate -or $_.Expires -lt (Get-Date))
}

$keys = $allKeys | Where-Object {
    $_.Attributes.Expires -ne $null -and ($_.Attributes.Expires -lt $expirationThresholdDate -or $_.Attributes.Expires -lt (Get-Date))
}

Write-Output "`nExpiring or Expired Secrets:"
$secrets | ForEach-Object {
    [PSCustomObject]@{
        Name       = $_.Name
        ExpiresOn  = $_.Expires
        Status     = if ($_.Expires -lt (Get-Date)) { "Expired" } else { "Expiring Soon" }
    }
}

Write-Output "`nExpiring or Expired Keys:"
$keys | ForEach-Object {
    [PSCustomObject]@{
        Name       = $_.Name
        ExpiresOn  = $_.Attributes.Expires
        Status     = if ($_.Attributes.Expires -lt (Get-Date)) { "Expired" } else { "Expiring Soon" }
    }
}

Output:

Refer:

https://learn.microsoft.com/en-us/azure/key-vault/general/monitor-key-vault-reference

Quickstart - Set and retrieve a secret from Azure Key Vault | Microsoft Learn