I’m trying to get the list of all secrets and keys in the key vault that are expired and are going to expire in 7 days to my mail.
I have created the automation account and published the runbook with PowerShell script which provide the output for my above query
Please help me on how I can receive that PowerShell script output from runbook to my mail daily or periodically?
$expirationDetails = @()
# Get all subscriptions
$subscriptions = Get-AzSubscription
# Loop through each subscription
foreach ($subscription in $subscriptions) {
# Set the context to the current subscription
Set-AzContext -SubscriptionId $subscription.Id
# Get all Key Vaults in the current subscription
$kvnames = Get-AzKeyVault
foreach ($kvitem in $kvnames) {
# Get Key Vault secrets, keys, and certificates
$secrets = Get-AzKeyVaultSecret -VaultName $kvitem.VaultName
$keys = Get-AzKeyVaultKey -VaultName $kvitem.VaultName
$certificates = Get-AzKeyVaultCertificate -VaultName $kvitem.VaultName
# Function to check expiration date and return the expiration DateTime or null for missing values
function Check-Expiration($expiryDate) {
if ($expiryDate) {
return [datetime]$expiryDate # Return the DateTime object if expiration date exists
}
return $null # Return null if expiration date is missing
}
# Function to calculate remaining days
function Get-RemainingDays($expiryDate) {
if ($expiryDate -ne $null) {
$remainingDays = ($expiryDate - (Get-Date)).Days
return $remainingDays
}
return $null # Return null if no expiration date
}
# Process secrets
foreach ($secret in $secrets) {
$expirationDate = Check-Expiration $secret.Expires
$remainingDays = Get-RemainingDays $expirationDate
if ($expirationDate -ne $null) {
$formattedExpirationDate = $expirationDate.ToString("MM/dd/yyyy HH:mm:ss")
} else {
$formattedExpirationDate = "" # Empty string for null expiration dates
}
# Only include items expiring within the next 7 days
if ($remainingDays -le 7 -and $remainingDays -ge 0) {
$expirationDetails += [PSCustomObject]@{
SubscriptionName = $subscription.Name
ResourceGroupName = $kvitem.ResourceGroupName
ResourceName = $kvitem.VaultName # Key Vault name
ObjectName = $secret.Name # Name of the secret
ObjectCategory = "Secret" # Category for KeyVault secret
ExpirationDate = $formattedExpirationDate # Formatted expiration date
ExpiresIn = $remainingDays # Remaining days until expiration
}
}
}
# Process keys
foreach ($key in $keys) {
$expirationDate = Check-Expiration $key.Attributes.Expires
$remainingDays = Get-RemainingDays $expirationDate
if ($expirationDate -ne $null) {
$formattedExpirationDate = $expirationDate.ToString("MM/dd/yyyy HH:mm:ss")
} else {
$formattedExpirationDate = "" # Empty string for null expiration dates
}
# Only include items expiring within the next 7 days
if ($remainingDays -le 7 -and $remainingDays -ge 0) {
$expirationDetails += [PSCustomObject]@{
SubscriptionName = $subscription.Name
ResourceGroupName = $kvitem.ResourceGroupName
ResourceName = $kvitem.VaultName # Key Vault name
ObjectName = $key.Name # Name of the key
ObjectCategory = "Key" # Category for KeyVault key
ExpirationDate = $formattedExpirationDate # Formatted expiration date
ExpiresIn = $remainingDays # Remaining days until expiration
}
}
}
# Process certificates
foreach ($certificate in $certificates) {
$expirationDate = Check-Expiration $certificate.Attributes.Expires
$remainingDays = Get-RemainingDays $expirationDate
if ($expirationDate -ne $null) {
$formattedExpirationDate = $expirationDate.ToString("MM/dd/yyyy HH:mm:ss")
} else {
$formattedExpirationDate = "" # Empty string for null expiration dates
}
# Only include items expiring within the next 7 days
if ($remainingDays -le 7 -and $remainingDays -ge 0) {
$expirationDetails += [PSCustomObject]@{
SubscriptionName = $subscription.Name
ResourceGroupName = $kvitem.ResourceGroupName
ResourceName = $kvitem.VaultName # Key Vault name
ObjectName = $certificate.Name # Name of the certificate
ObjectCategory = "Certificate" # Category for KeyVault certificate
ExpirationDate = $formattedExpirationDate # Formatted expiration date
ExpiresIn = $remainingDays # Remaining days until expiratio
}
}
}
}
}
# Optionally, display the results on the screen
$expirationDetails | Format-Table -Property SubscriptionName, ResourceGroupName, ResourceName, ObjectName, ObjectC
2
Answers
If I want to integrate the mail is this the correct way or if it needs modification, can you please guide me
To send an email from Azure Automation runbook, I have found a way using PowerShell command called
Send-MailMessage
. But as I mentioned in the comments, the commandSend-MailMessage
is obsolete and this cmdlet does not guarantee secure connections to SMTP servers.I have tried to execute it along with the PowerShell script and the warning has occurred in below format.
Refer this Microsoft Q&A for the relevant issue.
As a workaround, I have tried below PowerShell script by writing a query for retrieving the list of secrets that are expired and established a smtp server connection.
Message formatted in the below way:
Reference SO by @Sridevi for the relevant information on sending an email.
Alternatively, you can also use alert rules available in Azure Monitoring for creating and triggering alerts daily or periodically by adding the above KQL query in Azure Monitor workspace.
Detailed here in MSDoc.