How To Correctly Convert Invoke-Restmethod get objects into a table then parse the table in email body and send using powershell

Question

How To Correctly Convert Invoke-Restmethod get objects into a table then parse the table in email body and send using powershell

Benard Mwanza 1,006

Hello,

Have created a custom PowerShell script that gets unread emails count, unflagged email count and total inbox email count for all users in office 365 tenant. I'm using Microsoft graph API to achieve this. The script is currently able to get that information and save well in a csv file.

However, i need to do something else, i need to send the response data through email right after i get the itterable response objects from invoke-restmethod. The results should be emailed in format of a table, just like the way the csv output look like.

I need to have the data formatted into a table then emailed in the email body(Just the table to resemble the csv output):

I'm facing two problems so far,
1) I'm unable to format the data into a table then parse in the email body.
Have tried to create a table using PowerShell, then parse the hashed table in email body, but i get this response in the email.

2) Depending on the number of users in the tenant, the script sends email for each user in the loop block.
I understand this behavior is being caused by the email code inside the loop block.
How can i email the results just in a single table, without having the email being sent for each user. Which is the best spot in the script to place the email code.

In summary.
I need assistance on how to format the data i get from invoke-restmethod into a table then parse the table in the email body.

This is my entire script

$connectiondetails = @{  
  
        # This ids and secret are present in the overview and certificate & secret page of our application in azure AD  
      
        # Tenant ID here  
        'tenantid' = "tenantidhere"  
      
        # Application (client) ID here  
        'clientid' = "appclientidhere"  
      
        # Secret id here  
        'ClientSecret' = "appsecrethere" | ConvertTo-SecureString -AsPlainText -Force  
      
    }  
      
  
$token = Get-MsalToken @connectiondetails  
$tokenid_ = $token.AccessToken  
  
# Access token from our azure ad oauth app  
$accessToken = $tokenid_  
  
  
# $mailUsers = get-content "C:\temp\usersfile.txt"  
# Reporting csv file | we can pass the results to an email  
# $reportfile = "C:\temp\new_users.csv"  
# Add your administrator password  
$password = ConvertTo-SecureString "passwordhere" -AsPlainText -Force  
  
# Add your administrator login UPN  
$upn = "UPNaddresshere"  
  
# Automated login to azure AD  
$AppCredential = New-Object System.Management.Automation.PSCredential($upn, $password)  
connect-azuread -Credential $AppCredential  
  
# grab all users in azure AD  
$mailUsers = Get-AzureADUser   
  
foreach($mailUser in $mailUsers){  
  
# grab all users UPNs      
$mail_user = $mailUser.UserPrincipalName  
  
# grab unread emails count  
$graphuri = "https://graph.microsoft.com/v1.0/users/$mail_user/mailfolders/Inbox/messages?filter=isRead ne true&count=true"  
  
# Grab unflagged emails count  
$graphuri_1 = "https://graph.microsoft.com/v1.0/users/$mail_user/mailfolders/Inbox/messages?$filter=flag/flagStatus ne 'flagged' & flag/flagStatus ne 'complete'&count=true"  
  
# grab total inbox emails count  
$graphuri_2 = "https://graph.microsoft.com/v1.0/users/$mail_user/mailfolders/Inbox/messages?$skip=0&count=true"  
  
$resp = Invoke-RestMethod -Method get -Uri $graphuri -ContentType "application/json" -Headers @{Authorization=("bearer {0}" -f $accessToken)}  
  
$resp_1 = Invoke-RestMethod -Method get -Uri $graphuri_1 -ContentType "application/json" -Headers @{Authorization=("bearer {0}" -f $accessToken)}  
  
$resp_2 = Invoke-RestMethod -Method get -Uri $graphuri_2 -ContentType "application/json" -Headers @{Authorization=("bearer {0}" -f $accessToken)}  
  
  
# Creating a new ps object to host our response information to a csv file  
# We can send the response data to an email directly  
# $record=New-Object PSObject  
  
# $record | Add-Member NoteProperty -Name "UPN" -Value $mail_user  
  
# $record | Add-Member NoteProperty -Name "Unread email Count" -Value $resp.'@odata.count'  
  
# $record | Add-Member NoteProperty -Name "Emails not flagged count" -Value $resp_1.'@odata.count'  
# $record | Add-Member NoteProperty -Name "Total Inbox emails" -Value $resp_2.'@odata.count'  
# $record | export-csv $reportfile -NoTypeInformation -Append  
  
# # system pause  
# Start-Sleep -Seconds 1.5  
# Create a powershell table  
$hash = @{UPN = $mail_user; Total_Unread_Email_Count=$resp.'@odata.count'; Total_Unflagged_Emails_Count=$resp_1.'@odata.count'; Total_Inbox_Emails_Count=$resp_2.'@odata.count'}  
  
# $newhash = ConvertFrom-StringData -StringData $hash -Delimiter '|'  
  
  
  
# send email here  
$SMTP = "smtp.office365.com"  
$From = "fromemailhere"  
$To = "toemailhere"  
$Subject = "OFFICE 365 USER MAILBOXES AUDIT"  
$Body = "The report for your users emails actions is: `n$hash"   
$Email = New-Object Net.Mail.SmtpClient($SMTP, 587)  
$Email.EnableSsl = $true  
$Email.UseDefaultCredentials = $false;  
$Email.Credentials = New-Object System.Net.NetworkCredential("emailaddress", "passwordhere");  
$Email.Send($From, $To, $Subject, $Body)  
  
  
}

Answer accepted by question author

0 additional answers

Your answer

Answer 1

Rich Matheisen 48,026

You'd want to build an array of PSCustomObjects, that looks something like this:

$rows = [PSCustomObject]@{UPN = 'bernard@bernarc'; "Unread email Count" = 21;"Emails not flagged count" = 21;"Total Inbox emails" = 21},  
        [PSCustomObject]@{UPN = 'BernardMwanza@'; "Unread email Count" = 69;"Emails not flagged count" = 99;"Total Inbox emails" = 99},  
        [PSCustomObject]@{UPN = 'Test_Userberna@'; "Unread email Count" = 4;"Emails not flagged count" = 4;"Total Inbox emails" = 4}

I haven't run this, but add your code to it in the appropriate places:

$body = '<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">  
<html xmlns="http://www.w3.org/1999/xhtml">  
<head>  
<title>MY HTML MESSAGE BODY</title>  
</head><body>  
The report for your users emails actions is:\b\b  
'  
  
$body = "The report for your users emails actions is:\b\b"  
$props = @{ SMTPServer = "smtp.office365.com"  
            From = "fromemailhere"  
            To = "toemailhere"  
            Subject = "OFFICE 365 USER MAILBOXES AUDIT"  
            EnableSsl = $true  
            Port = 587  
            BodyAsHtml = $true  
            Credentials = New-Object System.Net.NetworkCredential("emailaddress", "passwordhere");  
}  
[array]$rows = @()  
  
# YOUR code to get the list of users, connections, tokens, etc. goes here  
  
###### process the users here  
foreach ($mailUser in $mailUsers) {  
      
    # grab all users UPNs      
    $mail_user = $mailUser.UserPrincipalName  
         
    # grab unread emails count  
    $graphuri = "https://graph.microsoft.com/v1.0/users/$mail_user/mailfolders/Inbox/messages?filter=isRead ne true&count=true"  
         
    # Grab unflagged emails count  
    $graphuri_1 = "https://graph.microsoft.com/v1.0/users/$mail_user/mailfolders/Inbox/messages?$filter=flag/flagStatus ne 'flagged' & flag/flagStatus ne 'complete'&count=true"  
         
    # grab total inbox emails count  
    $graphuri_2 = "https://graph.microsoft.com/v1.0/users/$mail_user/mailfolders/Inbox/messages?$skip=0&count=true"  
         
    $resp = Invoke-RestMethod -Method get -Uri $graphuri -ContentType "application/json" -Headers @{Authorization = ("bearer {0}" -f $accessToken) }  
         
    $resp_1 = Invoke-RestMethod -Method get -Uri $graphuri_1 -ContentType "application/json" -Headers @{Authorization = ("bearer {0}" -f $accessToken) }  
         
    $resp_2 = Invoke-RestMethod -Method get -Uri $graphuri_2 -ContentType "application/json" -Headers @{Authorization = ("bearer {0}" -f $accessToken) }  
         
  
    $rows += [PSCustomObject]@{  
        UPN                        = $mail_user  
        "Unread email Count"       = $resp.'@odata.count'  
        "Emails not flagged count" = $resp_1.'@odata.count'  
        "Total Inbox emails"       = $resp_2.'@odata.count'  
    }  
}  
  
$body += $rows | ConvertTo-Html -Fragment  
  
Send-MailMessage @props -body $body  
  
# you can export the $rows array to a CSV here (no need to append):  
$rows | Export-CSV  $reportfile -NoTypeInformation

Benard Mwanza 1,006

Hello @Rich Matheisen Upon executing the script, i get the following error message:

Send-MailMessage : Cannot process argument transformation on parameter 'Credential'. userName  
At line:1 char:18  
+ Send-MailMessage @props -body $body  
+                  ~~~~~~  
    + CategoryInfo          : InvalidData: (:) [Send-MailMessage], ParameterBindingArgumentTransformationException  
    + FullyQualifiedErrorId : ParameterArgumentTransformationError,Microsoft.PowerShell.Commands.SendMailMessage

Benard Mwanza 1,006 Reputation points

2022-05-26T07:14:20.933+00:00

Kindly note, have added the correct credentials three times, still i'm getting the same error.
Benard Mwanza 1,006 Reputation points

2022-05-26T07:40:53.98+00:00

Hello @Rich Matheisen I managed to resolve the credentials issue by replacing New-Object System.Net.NetworkCredential("emailaddress", "passwordhere"); with New-Object System.Management.Automation.PSCredential("emailhere", "securepasswordhere"), and was able to get the email delivered with the results as expected. However the results are not aligned well, is there a way to edit the html to make the table properly aligned, if yes kindly can you assist. Thank you.

Below is the current results:

Is there a way i can add border between the columns and rows just to make the table look at least readable to non-IT staff.
Rich Matheisen 48,026 Reputation points

2022-05-26T14:43:28.73+00:00

You can modify the formatting of the table by manipulating the HTML in the $body variable. Add borders, change alignment, how column width is handled, fonts, bolding, etc.

As to the creation of the credential, I assumed the code you posted was already working correctly. That code had nothing to do with the question you asked and, so, wasn't examined at all.
Benard Mwanza 1,006 Reputation points

2022-05-26T17:05:20.837+00:00

@Rich Matheisen thank you for response, i will greatly appreciate your contribution in modifying the html as instructed in the code you shared. I'm completely new in binding html in PowerShell.

Thank you. Perhaps the snippet of adding the styles. Thank you.
Benard Mwanza 1,006 Reputation points

2022-05-26T17:20:19.153+00:00
Have tried the below trick but its failing

$head = @"

<style> th, td{ border: 1px solid; } </style> "@ then in $body += $rows | ConvertTo-Html -Fragment -Head $head

Benard Mwanza 1,006

Have managed to resolve the styling issue,

by doing this:

$body = '<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>MY HTML MESSAGE BODY</title>
<style>

table{
    text-align: center;
    width: 850px;
    padding-bottom: 10px;
    border: 1px solid black;
    margin: 0 auto; 
    font-family: Georgia;
  }

  th {
    border: 1px solid black;
    padding: 5px; 
    background-color: grey;
    color: #FFDB4D;
  }

  td {
    border: 1px solid black;
    padding: 2px;
    background-color: grey;
    color: white;
  }</style>
</head><body>
The report for your users emails actions is:\b\b
'

Rich Matheisen 48,026 Reputation points

2022-05-26T18:36:43.43+00:00

You need to add "</body></html>" to $body after you add the result of ConvertTo-HTML cmdlet to $body.

HTML is pretty forgiving when it comes to missing end tags, but some web browsers are not so forgiving.

FWIW, I neglected to add it to the code I posted, too.
Rich Matheisen 48,026 Reputation points

2022-05-26T18:40:22.693+00:00

Using the -Head parameter AND the -Fragment parameter usually doesn't make sense, as -Fragment doesn't add <html> tag to the output and $body already contains <head></head>.

Share via

How To Correctly Convert Invoke-Restmethod get objects into a table then parse the table in email body and send using powershell

0 additional answers

Your answer