Wednesday, May 02, 2018

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.


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 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 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 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
   $ | % {
      $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

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

No comments: