Answered by:
Script to report on incoming emailing Traffic

Question
-
Hi
I need a script that i could run monthly that reports back on all incoming emails to a few mailboxes for the last 30 days, and then sends an email automatically to a user with the report.
Any help would be appreciated.
Thanks
Wednesday, July 25, 2018 4:26 PM
Answers
-
Hi berketjun,
The script below will help you generate this report:
Before use script below, please have a check on the value of MessageTrackingLogMaxAge, if the value of it isn't 30 days, please modify it to 30 days.
Get-TransportService | fl Name,MessageTrackingLogMaxAge
1. If you want to count all mails sent to this user, you can use script below:
$Mailboxes = Import-Csv c:/temp/mailboxes.csv foreach($Mailbox in $Mailboxes) { $User = $Mailbox.Name Get-TransportService | Get-MessageTrackingLog -Recipients $User | Where{$_.EventId -eq "DELIVER"} | Select Sender, MessageSubject | Export-Csv c:/temp/$User.csv -NoTypeInformation }
2. If you only want to count mails from external domain, you can use script below, before using this script, you should modify the value of {$_.sender -notlike "*@yourdomain.com*"} in the script below:
$Mailboxes = Import-Csv c:/temp/mailboxes.csv foreach($Mailbox in $Mailboxes) { $User = $Mailbox.Name Get-TransportService | Get-MessageTrackingLog -Recipients $User | Where{$_.sender -notlike "*@yourdomain.com*" -and $_.EventId -eq "DELIVER"} | Select Sender, MessageSubject | Export-Csv c:/temp/$User.csv -NoTypeInformation }
By the way, here is the structure of my mailboxes.csv file for this script:
Regards,
Kyle Xu
Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.
Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.
- Proposed as answer by Kyle.XuMicrosoft contingent staff Friday, August 3, 2018 2:11 AM
- Marked as answer by berketjun Tuesday, August 14, 2018 2:21 PM
- Unmarked as answer by berketjun Tuesday, August 14, 2018 4:12 PM
- Marked as answer by berketjun Friday, August 17, 2018 3:26 PM
Thursday, July 26, 2018 7:50 AM
All replies
-
Hi berketjun,
The script below will help you generate this report:
Before use script below, please have a check on the value of MessageTrackingLogMaxAge, if the value of it isn't 30 days, please modify it to 30 days.
Get-TransportService | fl Name,MessageTrackingLogMaxAge
1. If you want to count all mails sent to this user, you can use script below:
$Mailboxes = Import-Csv c:/temp/mailboxes.csv foreach($Mailbox in $Mailboxes) { $User = $Mailbox.Name Get-TransportService | Get-MessageTrackingLog -Recipients $User | Where{$_.EventId -eq "DELIVER"} | Select Sender, MessageSubject | Export-Csv c:/temp/$User.csv -NoTypeInformation }
2. If you only want to count mails from external domain, you can use script below, before using this script, you should modify the value of {$_.sender -notlike "*@yourdomain.com*"} in the script below:
$Mailboxes = Import-Csv c:/temp/mailboxes.csv foreach($Mailbox in $Mailboxes) { $User = $Mailbox.Name Get-TransportService | Get-MessageTrackingLog -Recipients $User | Where{$_.sender -notlike "*@yourdomain.com*" -and $_.EventId -eq "DELIVER"} | Select Sender, MessageSubject | Export-Csv c:/temp/$User.csv -NoTypeInformation }
By the way, here is the structure of my mailboxes.csv file for this script:
Regards,
Kyle Xu
Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.
Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.
- Proposed as answer by Kyle.XuMicrosoft contingent staff Friday, August 3, 2018 2:11 AM
- Marked as answer by berketjun Tuesday, August 14, 2018 2:21 PM
- Unmarked as answer by berketjun Tuesday, August 14, 2018 4:12 PM
- Marked as answer by berketjun Friday, August 17, 2018 3:26 PM
Thursday, July 26, 2018 7:50 AM -
Thanks Kyle
Anyway to have the report emailed automatically or saved to a network share
Thursday, July 26, 2018 3:16 PM -
Hi berketjun,
You can modify the value of parameter below to save this report to a network share.
Export-Csv c:/temp/$User.csv -NoTypeInformation
Such as:
Export-Csv \\Computer\temp\test.csv -NoTypeInformation
Regards,
Kyle Xu
Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.
Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.
Friday, July 27, 2018 1:28 AM -
Hi berketjun,
Any update now?
If the above suggestion helps, please be free to mark it as answer for helping more people.Regards,
Kyle Xu
Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.
Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.
Friday, August 3, 2018 2:12 AM -
Hi Kyle
There seems to be an issue with scriptoutput file. It just generates one file. Can you double check this?
Also is there a way to count the totals rather than output all emails?
Thanks
- Edited by berketjun Tuesday, August 14, 2018 4:26 PM
Tuesday, August 14, 2018 4:13 PM -
Hi berketjun,
Use the command below, it will create a CSV file for each user:
Export-Csv \\Computer\temp\$User.csv -NoTypeInformation
No, we cannot count with command, you can open this csv file with Excel, you will know how many records in it.
Regards,
Kyle Xu
Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.
Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Teams.
Wednesday, August 15, 2018 3:07 AM