Report Email Traffic By The Hour

It’s a well known fact that reporting is the sexiest topic in IT. To that end, I thought I’d post a quick one liner about email flow reporting in your organisation. This came about following a request from one of my favourite customers, who needed a way to report on how much email was being sent and received out of hours.

Get-MailTrafficReport -StartDate 01/14/2018 -EndDate 01/22/2018 -AggregateBy Hour -EventType GoodMail | select Date,Direction,MessageCount | Export-csv C:\users\emily\Desktop\mailflowreport.csv

This PS command is run in Exchange Online Powershell and will result in a CSV which shows an hourly breakdown of email sent / received in a given time period. It’s possible to add specific times to the dates (eg “01/14/2018 05:00”). I used the -EventType GoodMail variable to only report on Accepted mail in this example. You can also filter on -Direction (Inbound or Outbound). Below is a snapshot of the results:

Date Event Type Direction Action Message Count
------ ---- ---------- --------- ------ -------------
 15/01/2018 14:00:00 GoodMail Inbound 430
 15/01/2018 15:00:00 GoodMail Inbound 230
 15/01/2018 16:00:00 GoodMail Inbound 187
 15/01/2018 18:00:00 GoodMail Inbound 57
 15/01/2018 18:00:00 GoodMail Outbound 124
 15/01/2018 19:00:00 GoodMail Inbound 34
 15/01/2018 19:00:00 GoodMail Outbound 87

The TechNet article on the Get-MailTrafficReport cmdlet is here

This is a very versatile reporting function which can yield interesting data. This data can then be fed into PowerBI or a.n.other reporting tool to add some visual showmanship to the results!

Office 365 Hybrid – On Premise Room Mailboxes not available in OWA

I came across an issue today wherein a user whose mailbox was hosted on Office 365 attempted to use OWA to book a meeting On Premise, and was told that there were no rooms available. The list simply didn’t contain any rooms. The Room Mailboxes were synchronised to Office 365 using the AADSync tool, however OWA knew nothing about them. When using Outlook, the Rooms were shown correctly, so this was just an issue with OWA, not with Room Mailboxes per se.

After a little digging, I found the following KB article: which explains that a Room List should be created and synchronised to Office 365 to get this working in OWA.

However the PowerShell cmdlet in the KB article fell a little short as I had over 100 Rooms to add into the Distribution Group. This was the PowerShell I ended up using:

$members=Get-Mailbox -RecipientTypeDetails RoomMailbox
New-DistributionGroup -Name "RoomList" -RoomList -Members $Members

I then moved the newly created Distribution Group into the correct OU and performed a Directory Sync. The RoomList showed up instantly, but it took 5-10 minutes for it to become populated. Once this was all done, the Rooms were available!


Exchange Online – Lock down mail flow

By default, Office 365/Exchange Online allows mail to be received from any external source. This is done using a ‘hidden’ default inbound connector. The properties of this connector cannot be viewed or modified, even in Exchange Online Powershell.

This is all well and good and allows you to be able to send/receive mail out of the box in Office 365, however is does cause a problem if you are using a 3rd party mail solution such as Mimecast or Websense. If you do happen to be using a 3rd party mail filter and you leave the default inbound connector alone, somebody could bypass your filter by sending you mail directly to your Office 365 hostname. From a best practices and security point of view, this is most definitely a bad thing.

To combat this and limit Office 365 from receiving mail only from your mail filter, go into your Exchange Admin centre and create a new Inbound Connector under Mail Flow>Connectors.

New Inbound Connector

The settings of your Inbound Connector should be as follows:

Type: Partner
Connection Security: Force TLS (only if your mail filter supports forced TLS. This will add an extra layer of security. Otherwise, use Opportunistic TLS)
Sender Domains: *
Sender IP Addresses: (enter your mail filters IP addresses here)

This example states that Office 365 will only receive mail from the IP address and nothing else. The * wildcard under Sender Domains applies the connector to all mail. If I were to use Exchange Online Powershell to perform the same task, my command would look like this:

New-InboundConnector -Name Lockdown -ConnectorType Partner -RequireTls $true -SenderIPAddresses -SenderDomains *

This simple configuration change will ensure that nobody can bypass your mail filter and spam you with invitations to enlarge something or other 🙂