Getting statistics from Exchange Online about inbound mails from external domains

I've been asked to see if we can get an idea of how many external mails we receive, and what external domains are the most "active".

Basically what's wanted is a report detailing how many mails we receive from external domains under a specified time period: I'm thinking something along these lines is what's being asked.

*@gmail.com; 450
*@yahoo.com; 200

I'm comfortable using Powershell and have started looking there, but I'm really not finding anything giving me the results I'm looking for. Get-MailFlowStatusReport, Get-MailTrafficReport and Get-MailTrafficSummaryReport give me raw numbers for in and outgoing mail, but as far as I can tell they don't give the more detailed reports being asked for.

Theoretically I should be able to write something which iterates over all mailboxes with a Get-MessageTrace and extracting all external addresses. But that's major overkill and would probably take forever to run. Not to mention that would only give me the statistics for the latest ten days.

MS Graph may have better tools for this type of report, but I've barely used Graph, and in my googling I haven't found anything there or in EAC which matches what we're after.

How long do you want to search messages? If you want to view message trace results for messages less than 7 days old, you could run the following commands to search emails from all external senders:

Get-MessageTrace -StartDate "2021/10/1" -EndDate "2021/10/8" | where{$_.SenderAddress -notlike "*@company.onmicrosoft.com"} | Select-Object -Property SenderAddress | Export-Csv "C:\external.csv"

After running the commands, you will get a .csv file and then you could use the feature of Excel(PivotTable) to count the number of external senders:

If you want to view message trace results for messages more than 7 days old, you could try message trace report and compliance search, but they can't be specified external senders in the trace or search.

Exchange Online does hold ready-to-use statistics per sender domain. So you will have to download trace log and do the counting yourself.

One option is to use Get-MessageTrace command, for example:

Get-MessageTrace -StartDate "2021-09-28" -EndDate "2021-10-07" -SenderAddress "*@gmail.com" -PageSize 5000

You have to keep in mind several limitations:

• The StartDate parameter can't be older than 10 days from today. If you need bigger date range, I'd suggest you download data daily and keep it in on the disk or OneDrive. You can then do the counting using a script, or visualize using Excel/Power BI
• Maximum record count returned by the command is 5000. So you probably would have to implement paging logic in your PowerShell script

Another option is to use Exchange Admin portal Message Trace > Downloadable reports

It is a manual approach and I am not sure it can be automated, but it might be enough for one-time analysis. It can return data for the last 90 days

Hope this helps