Exporting Sent and received count for exchange online users through powershell

Aran Billen 866

Hi all,

is it possible to generate a report from Office 365 using PowerShell that details the total number of emails sent and received by each user over the past 30 days, specifically for users with a license containing "faculty"

Bruce Jing-MSFT 6,630 Reputation points Microsoft Vendor

2024-11-28T07:52:22.05+00:00

Hi，@Aran Billen

Vasil Michevde's answer is very good, please refer to it.
Bruce Jing-MSFT 6,630 Reputation points Microsoft Vendor

2024-12-03T08:22:52.54+00:00

Hello, @Aran Billen

It's been a long time since I've heard from you, have you solved your problem yet? Did my previous reply help you？ If it was useful, you can mark it as an answer. Thank you for your understanding and support.

1 answer

Vasil Michev 109.7K Reputation points MVP

2024-11-27T16:43:07.5933333+00:00

There's a built-in report for that: https://learn.microsoft.com/en-us/microsoft-365/admin/activity-reports/email-activity-ww?view=o365-worldwide

You can simply use the Export functionality in the portal, then filter the report in Excel. Or you can get the report data via PowerShell and the Get-MgReportEmailActivityUserDetail cmdlet. No server-side filtering is supported, so as with the portal, you will have to apply the filter client-side.
Please sign in to rate this answer.
Aran Billen 866 Reputation points

2024-11-28T09:35:30.28+00:00

Hi, thank you for the help.

I’m looking to automate a PowerShell script that generates a monthly report for email traffic in Office 365. The script should:

Include both "Sent" and "Received" emails within a specified month.

Filter the report for staff members only (excluding students, as we have over 2000 students).

Automatically export the report to a CSV file without the need for manual intervention.

Optionally, send the report via email once it's generated.

I found a script that generates daily reports, but I need it modified to run count monthly basis instead of daily count. Below is the original script for reference.

https://admindroid.com/how-to-get-user-email-activity-report-in-microsoft-365#:~:text=Option%203%20Using%20PowerShell%20Script&text=By%20default%2C%20the%20script%20retrieves,parameter%20while%20running%20the%20script.

Bruce Jing-MSFT 6,630 Reputation points Microsoft Vendor

2024-11-29T08:05:04.5466667+00:00

Hi，@Aran Billen

The forum does not support providing scripts at this time, so I hope you understand. Here are my personal modifications to the script, just for reference.

# Connect to Exchange Online Connect-ExchangeOnline # Define date range $endDate = Get-Date $startDate = $endDate.AddDays(-30) # Get all mailboxes $users = Get-EXOMailbox -ResultSize Unlimited # Loop through each mailbox foreach ($user in $users) { $emailAddress = $user.PrimarySmtpAddress $sentEmailsCount = (Get-MessageTrace -StartDate $startDate -EndDate $endDate -SenderAddress $emailAddress | Measure-Object).Count $receivedEmailsCount = (Get-MessageTrace -StartDate $startDate -EndDate $endDate -RecipientAddress $emailAddress | Measure-Object).Count Write-Output "User: $emailAddress" Write-Output "Sent Emails Count: $sentEmailsCount" Write-Output "Received Emails Count: $receivedEmailsCount" Write-Output "" }

If my answer is helpful to you, please mark it as the answer so that other users can refer to it. Thank you for your support and understanding.

Aran Billen 866 Reputation points

2024-12-03T12:35:45.3333333+00:00

Hi,

Thanks for this! However, the script isn’t working as expected—it won’t allow a date range longer than 10 days. I’d like to modify it so that it only includes users with a faculty license, rather than pulling in all students in the report.

Is that possible?

Bruce Jing-MSFT 6,630 Reputation points Microsoft Vendor

2024-12-04T07:00:47.8766667+00:00

Hi，@Aran Billen

Unfortunately, I consulted with Exchange engineers and there is no official script to generate monthly reports on email traffic at this time.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Exporting Sent and received count for exchange online users through powershell

1 answer

Your answer