Wednesday, May 09, 2018

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

No comments: