locked
Script to report on incoming emailing Traffic RRS feed

  • 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.

    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.

    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