Skip to main content

Junk Email reporting with PowerShell in Office365 Part 1

Because of some recent changes Microsoft have been making to the way Messages are evaluated as spam especially around sending domains having SPF and DKIM records (and how aggressively SPAM/Fraud is evaluated) you may have noticed a spike in legitimate email ending up in the Junk Email folder. As a email user in general on all platforms I've gotten pretty lazy in checking Junk email and Spam folders because I find the software is pretty good at doing its job and I only venture in there occasionally or when I know something should have arrived but didn't. However in the last few months enough email is going into these folders on a constant basis that its starting to become a pain point. So I'd thought I put together a few posts on the different ways of reporting on items in a Junk Email folder using both the reporting cmdlets in the Exchange Online PowerShell and also the Mailbox API's and some of the new stuff in the Graph (which I'll cover in part 2).

Many ways to skin a cat

As with many things in IT there are many ways of going about the same thing, mostly they get you same results, some faster, some slower and some more or less scalable as your Mail Traffic increases.

Get-MailDetailSpamReport

The fastest method of looking at what's being  junked as SPAM in your tenant is to make use of this cmdlet or the REST reporting endpoint that returns the same report. This gives you around 7 days (but don't quote me on this) of results. If you want to drill down to work out what and why a message was marked as spam then you use the Get-MessageTraceDetail cmdlet which pulls the details from the Message Tracking logs. If you want to pipeline the objects that  Get-MailDetailSpamReport returns you do need give some extra parameters for StartDate and EndDate which you didn't need to do when you pipeline this cmdlet with the results from Get-MessageTrace eg


$SPAMMessage | Get-MessageTraceDetail -StartDate (Get-Date).AddDays(-14) -EndDate (Get-Date)

Using Message Traces (or Message Tracking)

In Office365 if you want to know what happened to a Message that was sent or received the your first place to go  is to the Portal in the EMC and  https://technet.microsoft.com/en-us/library/jj200712%28v=exchg.150%29.aspx from a scripting point of view your Entry point into this data is the Get-MessageTrace and Get-MessageTraceDetail EMS Cmdlet. If you wanted to use REST you could use the Office365 Reporting Web Service https://msdn.microsoft.com/en-us/library/office/jj984325.aspx while this endpoint is mostly depreciated in favour of the Graph Endpoints the MessageTrace and MessageTraceDetail reports are still available to use. To use any of these cmdlet's and endpoints you need to have been granted the correct RBAC roles that will allow you to assert you delegated Exchange Admin rights . Note with the REST endpoint this doesn't support oAuth (like the Graph endpoint does) so you need to use Basic Auth so the PowerShell endpoint is a better option from a security point of view (eg MFA and the like) if you have a dim view of Basic Auth.

Restrictions in Message Traces

In Office365 only the last 7 days of logs are available live, while up to 90 days are available via an extended search. In these post I'm just going to look at what you can do with the live log data.

With Trace Logs you have access to lots of information available so if your going to digest in a meaning way you need to make what you looking at as meaningful as possible. For basic reporting such as looking at what is ending up in people's junk email folder the Get-MessageTrace cmdlet can be used, to filter down the results to just the SPAM then you can make use of the status parameter/field.  This property tell you want the overall status of routing a message was which for mail that ends in your junk email folder is FilteredAsSpam . Eg


If you want to get more information on the why Messages where marked as spam then that's where the MessageTraceDetail cmdlet (or REST endpoint) will come in handy. The easiest way of using Get-MessageTraceDetail is just to pipeline the result.

MessageTraceDetail and Custom_Data field

The MessageTraceDetail cmdlet and endpoint gives a lot more details of what happen to a message as it went through the Transport Pipeline which will give you details of what Transport Agent or Rules ran on a Message and what Agents (Mailware,DLP etc) took actions etc. Most of the detailed information is held in the Data property, this can contain a variety of different data depending on the type of entry. For the SPAM entry the data field holds a XML value of different actions taken in the host. A good explanation of this can be found is https://technet.microsoft.com/en-us/library/jj200712(v=exchg.150).aspx although there is more information in this field then is documented. But if your going to look at why a message got junked this is where to look for example if I look at the SPAM event of one message that was phishing attempt


The standout here for me would be MEP CTRY = MN which means that country for origin was Mongolia along with the SCL score etc.  It's not always that you will be able to work out why a particular action was taken but if your looking at this from a reporting point of view you can work out what is the most important parameters to you and promote those into your reports. Eg here is a sample script that will do a Get-MailDetailSpamReport then do a Get-MessageTraceDetail on the those messages and then promote the MEP properties from the SPAM event onto a report object that is exported to a CSV. Therefore you have a more useful spam report you can view for the last 24 hours. The script is relatively simple approach and looks like this



$rptCollection = @()
$Last24Junk = Get-MailDetailSpamReport -StartDate (Get-Date).AddDays(-1) -EndDate (Get-Date)
foreach($JunkMail in $Last24Junk){
   $RptObject = New-Object PsObject
   $JunkMail.psobject.properties | % {
      $RptObject | Add-Member -NotePropertyName $_.Name -NotePropertyValue $_.Value 
   }
   $Details = $JunkMail | Get-MessageTraceDetail -StartDate (Get-Date).AddDays(-2) -EndDate (Get-Date) | Where-Object {$_.Event -eq "Spam"}
   $XMLDoc = [XML]$Details.Data
   $MEPNodes = $XMLDoc.GetElementsByTagName("MEP")   
   for($nodeval=0;$nodeval -lt $MEPNodes.Count;$nodeval++){
      $Key = $MEPNodes[$nodeval].Attributes[0].Value.ToString()  
   $Value = $MEPNodes[$nodeval].Attributes[1].Value.ToString()  
   Add-Member -InputObject $RptObject -NotePropertyName ($Key) -NotePropertyValue ($Value)
     
   } 
   $rptCollection += $RptObject
     
}
$ScriptPath = split-path -parent $MyInvocation.MyCommand.Definition
$filename = $ScriptPath + "\SpamReport-" + $(get-date -f yyyy-MM-dd-hh-mm-ss) + ".csv"
$rptCollection | Export-csv -NoTypeInformation -Path $filename
Write-Host ("Report written to " + $filename)
A downloadable version of this script can be found https://github.com/gscales/Powershell-Scripts/blob/master/detailSpamReport.ps1

What about REST


PowerShell is great for administration but if your a service provider and you want to write a really scalable multi tenant reporting platform then from a developer perspective PowerShell is not a great API interface to be using (it wasn't really designed for this application). So using the REST endpoint for reporting will give you a lot better experience. I've added support for MessageTrace and MessageTraceDetail to my Exch-Rest  lib https://github.com/gscales/Exch-Rest/tree/master/functions/reports  but didn't emphasis them in this post as PowerShell offers a more practical and secure solution for most people.

In Part 2 I'll look at using the Mailbox API's to do some Digest reporting of the JunkEmail folder plus introduce how you can leverage the People API in graph to help with making your digests more relevant.

Popular posts from this blog

Downloading a shared file from Onedrive for business using Powershell

I thought I'd quickly share this script I came up with to download a file that was shared using One Drive for Business (which is SharePoint under the covers) with Powershell. The following script takes a OneDrive for business URL which would look like https://mydom-my.sharepoint.com/personal/gscales_domain_com/Documents/Email%20attachments/filename.txt This script is pretty simple it uses the SharePoint CSOM (Client side object Model) which it loads in the first line. It uses the URI object to separate the host and relative URL which the CSOM requires and also the SharePointOnlineCredentials object to handle the Office365 SharePoint online authentication. The following script is a function that take the OneDrive URL, Credentials for Office365 and path you want to download the file to and downloads the file. eg to run the script you would use something like ./spdownload.ps1 ' https://mydom-my.sharepoint.com/personal/gscales_domain_com/Documents/Email%20attachments/filena

A walk-though using the Graph API Mailbox reports in Powershell

Quite recently the Reporting side of the Graph API has moved in GA from beta, there are quite a number of reports that can be run across various Office365 surfaces but in this post I'm going to focus on the Mailbox related ones. Accessing Office365 Reports using Powershell is nothing new and has been available in the previous reporting endpoint  https://msdn.microsoft.com/en-us/library/office/jj984326.aspx however from the end of January many of these cmdlets are now being depreciated in favour of the Graph API  https://msdn.microsoft.com/en-us/library/office/dn387059.aspx . Prerequisites  In comparison to using the Remote PowerShell cmdlets where only the correct Office365 Admin permissions where needed, to use the new Graph API reports endpoint you need to use OAuth for authentication so this requires an Application Registration  https://developer.microsoft.com/en-us/graph/docs/concepts/auth_overview  that is then given the correct oAuth Grants to use the Reports EndPoin

How to test SMTP using Opportunistic TLS with Powershell and grab the public certificate a SMTP server is using

Most email services these day employ Opportunistic TLS when trying to send Messages which means that wherever possible the Messages will be encrypted rather then the plain text legacy of SMTP.  This method was defined in RFC 3207 "SMTP Service Extension for Secure SMTP over Transport Layer Security" and  there's a quite a good explanation of Opportunistic TLS on Wikipedia  https://en.wikipedia.org/wiki/Opportunistic_TLS .  This is used for both Server to Server (eg MTA to MTA) and Client to server (Eg a Message client like Outlook which acts as a MSA) the later being generally Authenticated. Basically it allows you to have a normal plain text SMTP conversation that is then upgraded to TLS using the STARTTLS verb. Not all servers will support this verb so if its not supported then a message is just sent as Plain text. TLS relies on PKI certificates and the administrative issue s that come around certificate management like expired certificates which is why I wrote th
All sample scripts and source code is provided by for illustrative purposes only. All examples are untested in different environments and therefore, I cannot guarantee or imply reliability, serviceability, or function of these programs.

All code contained herein is provided to you "AS IS" without any warranties of any kind. The implied warranties of non-infringement, merchantability and fitness for a particular purpose are expressly disclaimed.