Need to set up the e-mail notification for PowerShell runbook to send its output periodically/daily to the mail

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

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 command Send-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.

While there is no immediate replacement available in PowerShell, we recommend you do not use Send-MailMessage at this time.

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.

$query = @"
resources
| where type == "microsoft.keyvault/vaults"
| extend vaultUri = properties.vaultUri
| join kind=inner (
    resources
    | where type == "microsoft.keyvault/vaults/secrets"
    | extend vaultName = tostring(split(id, "/")[8]), resourceName = name
    | extend expired = properties.attributes.expiresOn
    | project vaultName,expired
) on $left.vaultName == $right.vaultName
| where expired < ago(1d)
| project vaultUri
"@
$result = Search-AzGraph -Query $query

$pwd = ConvertTo-SecureString '*****' -AsPlainText -Force
$CredSmtp = New-Object System.Management.Automation.PSCredential ('jahxxx@gmail', $password)$pwd = ConvertTo-SecureString 'Jahnavim@2727' -AsPlainText -Force
$CredSmtp = New-Object System.Management.Automation.PSCredential ('jxxxx@gmail', $pwd)
$FromMail = "jaxxxxgmail.com"
$MailTo = "xxxxx.com"
$Username = $CredSmtp.UserName
$Password = $CredSmtp.Password
$SmtpServer = "smtp.gmail.com"
$Port = 587
$Message = New-Object System.Net.Mail.MailMessage $FromMail, $MailTo
$MessageSubject = "Sending Automation results"
$Message.IsBodyHTML = $true
$Message.Subject = $MessageSubject
$Smtp = New-Object Net.Mail.SmtpClient($SmtpServer, $Port)
$Smtp.EnableSsl = $true
$Smtp.Credentials = New-Object System.Net.NetworkCredential($Username, $Password)
$Smtp.Send($Message)

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.