Skip to main content

Junk Email reporting with PowerShell in Office365 Part 2

This is part 2 of my Junk Email reporting series of posts for Part 1 which covers using Message Tracking please see . In this post I am going to look at using the Mailbox API's EWS and REST to actually read the contents of the Junk Email folder in Exchange and from there we can report on the various aspects of the Antispam information that is available in the Message Headers. Firstly if you are just looking for something to do single message analysis then I would check out Stephen Griffin's  Message Header Analyser Addin for Outlook  https://appsource.microsoft.com/en-us/product/office/WA104005406 this is a brilliant little tool for that. In this post I will focus on doing it in bulk using PowerShell and building some reports to allow you to see what's happening.

Mailbox Access

A big consideration if your going to be accessing Mailbox data is security, one of the benefits of using the REST api over EWS is that you can be very granular about the access that you give the App. At a very minimum you need the "Mail.Read.Shared" oauth Grant that give you API access into a Mailbox and then the underlying rights on the JunkEmail folder for the account your Authenticated as . EWS will still require convention access rights to work

Accessing the Data

Once you have your authentication sorted out you just need to access the MessageHeaders of the messages that you want to report on. Because of the size of this information you need to make a GetItem request in EWS for each item while REST can handle this for that property.

What Headers to look for and what your looking at 

Authentication-Results
This can be a little bit of a moving feast but currently if you want to look at the Authentication Results which include the SPF,DKIM and DMARC results then look at the Authentication-Results Header typically it will look like


The compauth part of this header is the Composite authentication result which is documented in https://support.office.com/en-us/article/anti-spoofing-protection-in-office-365-d24bb387-c65d-486e-93e7-06a4f1a436c0 . If a message has been forwarded through to you as part of a Mailing list your a member of another header you may see is the Authentication-Results-Original (or X-Original-Authentication-Results) https://sites.google.com/site/oauthgoog/mlistsdkim how this header is implemented by Office365 is not entirely clear but I find it a useful header to look at when trying to work out why something is junked when the source domain looks okay.

X-Microsoft-Antispam

This header should contain the PCL (Phishing Confidence Level) a good link for this and BCL (Bulk Complaint Level) which is the Bulk mailing list compliant level documented here

X-Forefront-Antispam-Report 

This header documented here contains the Country of origin value, SFV (Spam filter Verdict),SRV,IPV,PTR
X-MS-Exchange-Organization-SCL

SCL is the Spam confidence level which is where we all started back in 2003.

X-CustomSpam 

Is the ASF (Advanced spam filtering) Header for those using this feature

This isn't an extensive list just those that I've written code to process out.

Putting this all to work

All this information is only as good as how you can use it because Email Authentication is a hot topic at the moment lets see how we can put some script to work to help us look at this data. For EWS and REST I've created a ProcessAntiSPAMHeaders script that first indexes the Messages headers and then uses some REGEX to extract the relevant property data from the headers I've talked about above. For my examples I'd do it in two stage first is you need to get the ItemCollection that will be the basis for any reporting or investigation we do which involves enumerating the Items in the Mailbox and then processing those headers. Then you can use this ItemCollection is different way instead or re-enumerating messages each time you want to look at a different view of the data.

EWS 

For EWS I've created a simple script that lets you specify a FolderPath, MailboxName and how many emails you want to look at and it will then return a collection of those items with all the Properties from the headers parsed out and promoted as first Class properties. This script is located here https://github.com/gscales/Powershell-Scripts/blob/master/ExchangeASReport.ps1 to use it to generate the Message collection use the following to look at the last 50 email in the Junk Email folder (Note you may need to adjust the spelling of the Junk Email folder) 

$Messages = Get-EWSAntiSpamReport -MailboxName gscales@datarumble.com -Credentials $creds -FolderPath "\Junk E-mail" -MaxCount 50


REST-Graph API

For the Graph API I've included all the processing code in my Exch-REST library which is available from the PowerShell Gallery https://www.powershellgallery.com/packages/Exch-Rest and GitHub https://github.com/gscales/Exch-Rest to use this we can use either Get-EXRWellknowFolderItems for Well Known folders such as the Inbox and JunkEmail Folder or Get-EXRFolderItems for all other folders. eg to get the top 50 messages from the JunkEmail Folder and process those

$Messages = Get-EXRWellKnownFolderItems -Mailbox gscales@datarumble.com -WellKnownFolder JunkEmail -Top 50 -TopOnly:$true -ReturnInternetMessageHeaders -ProcessAntiSPAMHeaders

What you can do with this Messages Collection



Browse Around

If your in troubleshooting mode one the best things to do is just browse around the data in PowerShell to see what's happening eg showing the Sender,SPF,DKIM,DMARC to the shell for the last 50 emails 



Or if you want to look at the SCL,PCL,BCL,SFV and CTRY values for the last 50 emails


Or if you just wanted to look at Messages that have failed DMARC



Or came from a specific Country 




Or if you wanted to compare the DMARC result to the Original-DMARC results for mailing lists



And any other combination of property values you want to look at to work out more what's happening to email that is ending up in your Junk Email folder. This type of drill down analysis should be useful in building your knowledge of these Antispam markers or to help spot any new trends that you might not be aware of etc or just to look flashy in a technical meeting (and you can do it all for free!! just a little PowerShell knowledge is required).

Digesting 

I wrote this separate post on digesting emails as this was another lengthy but useful thing you can do with script. For JunkEmail here is a sample digest of the last 10 messages in the JunkEmail folder and digest the Authentication Results 


to produce this report in REST using Exch-REST


$Messages = Get-EXRWellKnownFolderItems -Mailbox gscales@datarumble.com -WellKnownFolder JunkEmail -Top 10 -TopOnly:$true -ReturnInternetMessageHeaders -ProcessAntiSPAMHeaders -SelectProperties "ReceivedDateTime,Sender,Subject,IsRead,inferenceClassification,parentFolderId,hasAttachments,webLink,BodyPreview"
Send-EXRMessage -MailboxName gscales@datarumble.com -To gscales@datarumble.com -Body (Get-EXRDigestEmailBody -MessageList $Messages -Detail -InfoField1Name SPF -InfoField2Name DKIM -InfoField3Name DMARC -InfoField4Name CompAuth -InfoField5Name SCL) -Subject "Junk Mail Auth digest"



$Messages = Get-EXRWellKnownFolderItems -Mailbox gscales@datarumble.com -WellKnownFolder JunkEmail -Top 50 -TopOnly:$true -ReturnInternetMessageHeaders -ProcessAntiSPAMHeaders
Send-EWSMessage -MailboxName gscales@datarumble.com -To gscales@datarumble.com -Body (Get-EWSDigestEmailBody -MessageList $Messages -Detail -InfoField1Name SPF -InfoField2Name DKIM -InfoField3Name DMARC -InfoField4Name CompAuth -InfoField5Name SCL) -Subject "Junk Mail Auth digest" -Credentials $creds

Complete AnitSpam property Report

One last sample for this post would be to take all the properties that we are extracting and creating a spreadsheet to allow you to view them at a glance eg all these


You can create one big CSV file that you can open up in Excel by taking the $Messages Collection we generated above and selecting all the related AS properties and using export-csv eg




 $Messages | Select-Object SenderEmailAddress,Subject,SCL,PCL,BCL,SFV,SRV,IPV,CIP,PTR,ASF,CTRY,SPF,DKIM,DMARC,COMPAuth,Original-SPF,Orignal-DMARC,Original-DKIM | Export-Csv -NoTypeInformation -Path c:\temp\AsReport.csv

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.