Office 365 PowerShell Daily Email Report Log

12 Sep

I needed to generate an automated daily email to report on the email usage per user along with a more detailed log of all Non Delivery Reports (NDR).

The code basically performs the following:

  1. Connect to EOL
  2. Remove old log files
  3. Loop through all mailboxes and perform Get-MessageTrace on each for the previous day to get the stats and output these to a CSV
  4. Loop through all NDR for the previous day and output these to a CSV
  5. Send these 2 files via email.
$myusername = "username"
$mypassword = "password"

$securepassword = ConvertTo-SecureString -string $mypassword -AsPlainText -Force 
$cred = new-object System.Management.Automation.PSCredential ($myusername, $securepassword)
 
$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://ps.outlook.com/PowerShell-LiveID?PSVersion=4.0/ -Credential $cred -Authentication Basic -AllowRedirection 
Import-PSSession $Session -AllowClobber

#variables
$starttime = (get-date (get-date).addDays(-1) -UFormat "%m/%d/%Y 00:00:00")
$endtime = (get-date (get-date).addDays(-1) -UFormat "%m/%d/%Y 23:59:59")
#use MDY format: https://technet.microsoft.com/en-us/library/jj200704%28v=exchg.160%29.aspx?f=255&MSPPError=-2147217396
$yesterdaydate = (get-date (get-date).addDays(-1) -UFormat "%Y-%m-%d")
$datafilename = "C:\temp\DailyEmailReport.csv"
$ndrfilename = "C:\temp\NDR.csv"

#remove old files
if (Test-Path $datafilename){
    remove-item $datafilename
}

if (Test-Path $ndrfilename){
    remove-item $ndrfilename
}

#None: The message has no delivery status because it was rejected or redirected to a different recipient.
#Failed: Message delivery was attempted and it failed or the message was filtered as spam or malware, or by transport rules.
#Pending: Message delivery is underway or was deferred and is being retried.
#Delivered: The message was delivered to its destination.
#Expanded: There was no message delivery because the message was addressed to a distribution group, and the membership of the distribution was expanded.
 
$mbxs = Get-Mailbox -ResultSize Unlimited -RecipientTypeDetails UserMailbox | where{$_.PrimarySmtpAddress -like "*@domain.co.uk"} | select PrimarySmtpAddress | sort PrimarySmtpAddress

Add-Content -Path $datafilename -Value 'User,Received,Sent,Pending,Failed,None'

foreach ($mbx in $mbxs) 
{
    $failedcount = Get-Messagetrace -PageSize 5000 -SenderAddress $mbx.PrimarySmtpAddress -Start $starttime -End $endtime  | Where-Object { $_.Status -eq 'Failed' -and $_.Subject -notlike 'Automatic reply:*' }|  Group-Object -Property status | Select count
    $sentcount = Get-Messagetrace -PageSize 5000 -SenderAddress $mbx.PrimarySmtpAddress -Start $starttime -End $endtime  | Where-Object { $_.Status -eq 'Delivered' -and $_.Subject -notlike 'Automatic reply:*' -and $_.ToIP -ne $null} |  Group-Object -Property status | Select count
    $receivedcount = Get-Messagetrace -PageSize 5000 -RecipientAddress $mbx.PrimarySmtpAddress -Start $starttime -End $endtime  | Where-Object { $_.Status -eq 'Delivered' -and $_.ToIP -ne $null} |  Group-Object -Property RecipientAddress | Select count
    $pendingcount = Get-Messagetrace -PageSize 5000 -SenderAddress $mbx.PrimarySmtpAddress -Start $starttime -End $endtime  | Where-Object { $_.Status -eq 'Pending' -and $_.Subject -notlike 'Automatic reply:* '} |  Group-Object -Property status | Select count
    $nonecount = Get-Messagetrace -PageSize 5000 -SenderAddress $mbx.PrimarySmtpAddress -Start $starttime -End $endtime  | Where-Object { $_.Status -eq 'None' -and $_.Subject -notlike 'Automatic reply:*' } |  Group-Object -Property status | Select count
    #write-host "--------------------------------------"
    #write-host "User:" $mbx.PrimarySmtpAddres
    #write-host "Received:" $receivedcount.Count
    #write-host "Sent:" $sentcount.Count
    #write-host "Pending:" $pendingcount.Count
    #write-host "Failed:" $failedcount.Count
    #write-host "None:" $nonecount.Coun
    #write-host "--------------------------------------"
    $csvline = $mbx.PrimarySmtpAddress + ","+$receivedcount.Count+","+$sentcount.Count+","+$pendingcount.Count+","+$failedcount.Count+","+$nonecount.Count
    Add-Content -Path  $datafilename -Value $csvline
}

$failedmessages = Get-Messagetrace -PageSize 5000 -Start $starttime -End $endtime  | Where-Object { $_.Status -eq 'Failed' -and $_.Subject -notlike 'Automatic reply:*' -and $_.SenderAddress -like '*@domain.co.uk' -and $_.SenderAddress -notlike '[email protected]' } | Sort-Object $_.SenderAddress
Add-Content -Path  $ndrfilename -Value "Sender,Recipient,Subject"
    
foreach($msg in $failedmessages){     
    $csvline = $msg.SenderAddress + "," + $msg.RecipientAddress + "," + $msg.Subject
    Add-Content -Path  $ndrfilename -Value $csvline
}

$emailbodytext = "Please find attached the email log and NDR report for yesterday, $yesterdaydate."
$emailbodytext += ""
$emailbodytext += "Delivery Status Types:"
$emailbodytext += "None: The message has no delivery status because it was rejected or redirected to a different recipient."
$emailbodytext += "Failed: Message delivery was attempted and it failed or the message was filtered as spam or malware, or by transport rules."
$emailbodytext += "Pending: Message delivery is underway or was deferred and is being retried."
$emailbodytext += "Sent: The message was delivered to its destination (Excludes OOF Replies). Each recipient counts as one email."
$emailbodytext += "Received: The message was received to the users inbox (Excludes quarantine items)."

Send-MailMessage -To "[email protected]" -bcc "[email protected]" -From "SENDEREMAILADDRESS" -Subject "Email Log & NDR Report for $yesterdaydate" -smtpserver SERVERIPADDRESS -body $emailbodytext -BodyAsHtml -Attachments $datafilename, $ndrfilename  

Remove-PSSession $Session