powershellautomationemail-attachmentsazure-resource-graph

Send email with Attachment from Azure Automation Account RunBook

Can any body guide me with this issue.

i'm trying to create a runbook inside an Azure Automation Account, that runbook is a Azure Resource explorer query, i want to sent the result in a .CSV file to my email but i'm getting the following error

    System.Management.Automation.RuntimeException: You cannot call a method on a null-valued expression.
   at CallSite.Target(Closure , CallSite , Object , String )
   at System.Dynamic.UpdateDelegates.UpdateAndExecute2[T0,T1,TRet](CallSite site, T0 arg0, T1 arg1)
   at System.Management.Automation.Interpreter.DynamicInstruction`3.Run(InterpretedFrame frame)
   at System.Management.Automation.Interpreter.EnterTryCatchFinallyInstruction.Run(InterpretedFrame frame)

this is the runbook i'm trying to create. did anyone has the same problem that can give me some guide to solve it?

$IdentityAccountID = Get-AutomationVariable  -Name "IdentityName"
Connect-AzAccount -Identity -AccountId $IdentityAccountID -Tenant "<tenantName>" -SubscriptionId "1234-1234-1234-1234-1234"



$query = @"
resources
| where type in (
    'microsoft.sql/servers/databases',
    'microsoft.web/serverfarms'
)
| join kind=inner (
    resourcecontainers
    | where type =~ 'microsoft.resources/subscriptions'
    | project subscriptionId, subscriptionName=name
) on subscriptionId
| where subscriptionId in (
  '1234-1234-1234-1234-1234',
    '1234-1234-1234-1234-1234',
    '1234-1234-1234-1234-1234'
)
| where name != 'master'
| project subscriptionName, resourceType=type, resourceName=name, sku, resourceGroup
| order by subscriptionName, resourceType, resourceName
"@


#$csvpath = "$env:TEMP\ResourcesInventory.csv"
# Execute the query
$result = Search-AzGraph -Query $query
$result.Data | Export-Csv -Path "$env:TEMP\ResourcesInventory.csv" -Notype



$CredSmtp = Get-AutomationPSCredential -Name "smtpCred" 

# Sender and Recipient Info
$MailFrom = "no-reply@email.com"
$MailTo = "reciever@email.com"

# Sender Credentials
$Username = $CredSmtp.UserName
$Password = $CredSmtp.GetNetworkCredential().Password

$SmtpServer = "smtp.sendgrid.net"
$SmtpPort = "587"

# configure message

$Message.Attachments.Add("$env:TEMP\ResourcesInventory.csv")
$MessageSubject = "Azure resources Inventory Report" 
$Message = New-Object System.Net.Mail.MailMessage $MailFrom, $MailTo
$Message.IsBodyHTML = $true
$Message.Subject = $MessageSubject
$Message.Body = @'
<!DOCTYPE html>
<html>
<head>
</head>
<body>
Hello, 
<p>Please Find attached the resources inventory report regarding the subscriptions assigned to you </p>

Regards
</body>
</html>
'@


# Construct the SMTP client object, credentials, and send
$Smtp = New-Object Net.Mail.SmtpClient($SmtpServer,$SmtpPort)
$Smtp.EnableSsl = $true
$Smtp.Credentials = New-Object System.Net.NetworkCredential($Username,$Password)
$Smtp.Send($Message)
Solution

  • As mentioned in comments, the error occurs if you are getting null results from the query. Initially, I too got same error when I ran the script with query having null results:

    enter image description here

    Before running the script, make sure to run the query locally in Resource Graph Explorer and confirm results are not null:

    resources
| where type in (
    'microsoft.sql/servers/databases',
    'microsoft.web/serverfarms'
)
| join kind=inner (
    resourcecontainers
    | where type =~ 'microsoft.resources/subscriptions'
    | project subscriptionId, subscriptionName=name
) on subscriptionId
| where subscriptionId in (
  'xxxxxxxx'
)
| where name != 'master'
| project subscriptionName, resourceType=type, resourceName=name, sku, resourceGroup
| order by subscriptionName, resourceType, resourceName

    Response:

    enter image description here

    In my case, I ran below modified script by adding null result check condition and sent mail from Outlook account like this:

    $IdentityAccountID = Get-AutomationVariable -Name "IdentityName"
Connect-AzAccount -Identity -AccountId $IdentityAccountID -Tenant "tenantId" -SubscriptionId "subId"

$query = @"
resources
| where type in (
    'microsoft.sql/servers/databases',
    'microsoft.web/serverfarms'
)
| join kind=inner (
    resourcecontainers
    | where type =~ 'microsoft.resources/subscriptions'
    | project subscriptionId, subscriptionName=name
) on subscriptionId
| where subscriptionId in (
  'xxxxxxxxxx'
)
| where name != 'master'
| project subscriptionName, resourceType=type, resourceName=name, sku, resourceGroup
| order by subscriptionName, resourceType, resourceName
"@

$result = Search-AzGraph -Query $query

# Check if the result is null
if ($result -eq $null -or $result.Data -eq $null) {
    throw "No data returned from the query. Please check the query and try again."
}

# Export the result to CSV
$result.Data | Export-Csv -Path "$env:TEMP\ResourcesInventory.csv" -NoTypeInformation

# Get the SMTP credentials
$CredSmtp = Get-AutomationPSCredential -Name "smtpCred"

# Sender and Recipient Info
$MailFrom = "sridevixxxxxxxx@outlook.com"
$MailTo = "sridxxxxxxx@gmail.com"

# Sender Credentials
$Username = $CredSmtp.UserName
$Password = $CredSmtp.GetNetworkCredential().Password

# SMTP Server details
$SmtpServer = "smtp.office365.com" #Changed to send mail from Outlook account
$SmtpPort = 587

# Configure the message
$Message = New-Object System.Net.Mail.MailMessage $MailFrom, $MailTo

# Ensure the attachment path is correct
$csvFilePath = "$env:TEMP\ResourcesInventory.csv"
if (-Not (Test-Path $csvFilePath)) {
    throw "CSV file not found: $csvFilePath"
}

$Message.Attachments.Add($csvFilePath)
$MessageSubject = "Azure resources Inventory Report"
$Message.IsBodyHTML = $true
$Message.Subject = $MessageSubject
$Message.Body = @'
<!DOCTYPE html>
<html>
<head>
</head>
<body>
Hello,
<p>Please find attached the resources inventory report regarding the subscriptions assigned to you.</p>
Regards
</body>
</html>
'@

# Construct the SMTP client object, credentials, and send
$Smtp = New-Object Net.Mail.SmtpClient($SmtpServer, $SmtpPort)
$Smtp.EnableSsl = $true
$Smtp.Credentials = New-Object System.Net.NetworkCredential($Username, $Password)

try {
    $Smtp.Send($Message)
    Write-Output "Email sent successfully."
} catch {
    Write-Output "Failed to send email. $_"
}

    Response:

    enter image description here

    To confirm that, I checked Sent Items of Sender where mail sent successfully with attachment as below:

    enter image description here