skip to Main Content

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


  1. Chosen as BEST ANSWER

    If I want to integrate the mail is this the correct way or if it needs modification, can you please guide me

    Connect-AzAccount
    
    # Initialize an empty array to store the results
    $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, ObjectCategory, ExpirationDate, ExpiresIn
    
    project vaultUri
    "@
    $result = Search-AzGraph -Query $query
    
    $pwd = ConvertTo-SecureString 'mailtopassword' -AsPlainText -Force
    $CredSmtp = New-Object System.Management.Automation.PSCredential ('mail from', $password)$pwd = ConvertTo-SecureString 'mailfrompassword' -AsPlainText -Force
    $CredSmtp = New-Object System.Management.Automation.PSCredential ('mail to', $pwd)
    $FromMail = "@gmail.com"
    $MailTo = "@outlook.com"
    $Username = $CredSmtp.UserName
    $Password = $CredSmtp.Password
    $SmtpServer = "smtp.office365.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)
    

  2. 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:

    enter image description here

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search