Got a request if we could deliver how much mail is sent and received for a certain group of users in a specific organization unit over time. For the received mail they wanted to see what came from external or internal. Created a script for this that either can get daily statistics so it can be automated/scheduled or by entering certain dates generate a one time only version. It uses the transactionlogs in Exchange and in a default deployment that means you can go back for only 30 days. This is used against an Exchange 2013 Organization, there has been no testing against older versions of Exchange Server. A scripting language isn’t the greatest tool to start crunching numbers as it tends to be slow. As such would not suggest to use this in a larger deployment but rather use tooling that is build for this task. In a small deployment and for the task I created it for it works fine. It will run in automated fashion for a few months providing data for a specific project to reduce mail within the company and then shut down again (until the exercise might be repeated)…

#requires -version 3

<#
Program  : ExchangeMailStatistics.ps1 - Get Exchange Send and DELIVER statistics from scanning the transactionlog
Author   : Eugene Dullaard (https://eugene.dullaard.nl/?p=685)
Date     : 02-Mar-2015
           - Initial Script

Warning:
- Better not use in large deployments or run it per server otherwise completion of script might take a day or more

To Do's:
- You should modify the values under 'User needs to modify below variables' so they represent
  the requirements and environment

This script will retrieve transaction logs from designated exchange servers and will retrieve how many mails have been
send or delivered for a specific OU in AD.

It will create temp files (which you can optionally keep) and some fixed files which are configurable within the script.
#>

#Fixed variables
$Date            = [STRING]((get-date -Format s).split("T"))[0]
                                                        #Sortable date of today for filenames
#====================================
#User needs to modify below variables
#====================================

$StartDate       = "02/20/2015 00:00:00"                #Start Date of TransactionLog Scan, ignore if $Scripted is enabled
$EndDate         = "02/21/2015 00:00:00"                #End Date of TransactionLog Scan, ignore if $Scripted is enabled.

$Scheduled        = $false                              #If true the above $StartDate and $Enddate are not used, instead
                                                        #it will calculate today-2 as $StartDate and today-1 as $EndDate.
                                                        #Script should run daily at the same time.

$CASServerPS     = "https://<cas-server>/powershell"    #Host for implicit remoting Exchange.
$MailboxServers  = "<mailbox-server1>","<mailbox-server2>","<mailbox-server3>"
                                                        #Mailbox Servers to scan TransactionLogs on.
$SenderDomain    = "@domain.tld"                        #Sender domain in use can be a part of a name as well (-match).
$UserSearchBase  = "OU=Users,OU=Corp,DC=domain,DC=tld"  #LDAP SearchBase for user accounts.
$KeepTempFiles   = $true                                #Keep per server transactionlogfiles for other/later use if $true

#===============
#Start of Script
#===============

#Override certain variables in case $Scripted is enabled
if ($Scheduled -eq $true) {
  $StartDate = [DateTime]::Today.AddDays(-3)
  $EndDate = [DateTime]::Today.AddDays(-2)
  $Year = $StartDate.Year ; $Month = "{0:00}" -f $StartDate.Month ; $Day = "{0:00}" -f $StartDate.Day
  $Date = "$Year-$Month-$Day"
}

#Outputdata
$FileSubmitTotals          = ".\totals.$Date.submit.csv"
$FileDeliverInternalTotals = ".\totals.$Date.internal.deliver.csv"
$FileDeliverExternalTotals = ".\totals.$Date.external.deliver.csv"

#Get User objects from AD
$UserData = (Get-ADUser -Filter * -SearchBase $UserSearchBase -Properties ProxyAddresses | Select-Object Name,ProxyAddresses)

#Initialize remoting to Exchange Server
#Import-PSSession (New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri $CASServerPS)

#==========================================================================
#Data-Collection and storage in CSV format for Submitted and DELIVERd mails
#==========================================================================

Write-Host "Data-retrieval" -ForegroundColor Green
Write-Host "=============="

#Create Datafile(s) to be parsed later, capturing send and DELIVER data per server and for usage beyond this script
#Per Server Data, so to scan if a user belongs to that server to avoid duplicate entries
$MailboxServers | ForEach-Object {

  $MailboxServer = $_
  Write-Host "$_ by SUBMIT"
  Out-File -InputObject "Sender" -FilePath .\$MailboxServer.$Date.submit.csv
  Get-MessageTrackingLog -Start $StartDate -End $EndDate -ResultSize Unlimited -EventID SUBMIT -Server $MailboxServer | `
    Select-Object Sender | ForEach-Object {
      $Sender = $_.Sender
      Out-File -InputObject "$Sender" -FilePath .\$MailboxServer.$Date.submit.csv -Append
    }
  Write-Host "$_ by DELIVER"
  Out-File -InputObject "Sender,Recipient" -FilePath .\$MailboxServer.$Date.deliver.csv
  Get-MessageTrackingLog -Start $StartDate -End $EndDate -ResultSize Unlimited -EventID DELIVER -Server $MailboxServer | `
    Select-Object Sender,Recipients | ForEach-Object {
      $Sender = $_.Sender
      $_.Recipients | ForEach-Object {
        Out-File -InputObject "$Sender,$_" -FilePath .\$MailboxServer.$Date.deliver.csv -Append
      }
    }
}

#===========================================================
#Data-Analysis SUBMIT for Name and amount of submitted mails
#===========================================================

Write-Host ""
Write-Host "Data-Analysis on SUBMIT" -ForegroundColor Green
Write-Host "======================="

$HashData = @{}         # Holds the name and amount of mails

$MailboxServers | ForEach-Object {
  Write-Host $_

  #Retrieve data for server from export file
  $Data = Import-Csv .\$_.$Date.submit.csv | Group-Object Sender | Select-Object Count,Name

  #Sanitize data so it discards non-user data and add to Output File
  $Data | ForEach-Object {
    $MailAddress = $_.Name
    #Get corresponding AD account
    $ADUser = ($UserData | Where-Object {$_.ProxyAddresses -match $MailAddress})
    #Check if account exists and if so add it to hash table using name and count
    if ($ADUser -ne $null) {
      if ($HashData[$ADUser.Name] -eq $null) {
        $HashData[$ADUser.Name] = $_.Count
      }
      else {
        $HashData[$ADuser.Name] = $HashData[$ADUser.Name] + $_.Count
      }
    }
  }
}

#Output collected data to CSV file
Write-Host ""
Write-Host "Writing SUBMIT data to $FileSubmitTotals"
$HashData.GetEnumerator() | ForEach-Object {New-Object -TypeName PSObject -Property @{Count=$_.Value;Name=$_.Name}} | `
  Export-CSV -Path $FileSubmitTotals -NoTypeInformation

#===========================================================
#Data-Analysis DELIVER for Name and amount of DELIVERd mails
#===========================================================

Write-Host ""
Write-Host "Data-Analysis on DELIVER" -ForegroundColor Green
Write-Host "========================"

$HashDataInternal = @{}       # Holds the Name and amount of internal sourced mails
$HashDataExternal = @{}       # Holds the Name and amount of external sourced mails

$MailboxServers | ForEach-Object {
  Write-Host $_

  #Retrieve MailboxUsers for Server
  $MailBoxUsers = (Get-Mailbox -Server $_ | Select-Object Name)

  #Retrieve data for server from export file
  $Data = Import-Csv .\$_.$Date.deliver.csv

  #Sanatize data to discard any non-user data and add to Output File
  $Data | ForEach-Object {
    $MailAddress = $_.Recipient

    #Get corresponding AD account
    $ADUser = ($UserData | Where-Object {$_.ProxyAddresses -match $MailAddress})
    if ($ADUser -ne $null) {

      #Check if user has a mailbox on this server
      $MailBoxUser = ($MailboxUsers | Where-Object {$_.Name -match $ADUser.Name})

      #After above checks add it to the internal or external hash table depending on sender
      if ($MailBoxUser -ne $null) {

        #Check if Sender is internal or external and add 1 to the appropiate hashtable
        if ($_.Sender -match $SenderDomain) {
          if ($HashDataInternal[$ADUser.Name] -eq $null) {
            $HashDataInternal[$ADUser.Name] = 1
          }
          else {
            $HashDataInternal[$ADuser.Name]++
          }
        }
        else {
          if ($HashDataExternal[$ADUser.Name] -eq $null) {
            $HashDataExternal[$ADUser.Name] = 1
          }
          else {
            $HashDataExternal[$ADuser.Name]++
          }
        }
      }
    }
  }
}

#Output collected data to CSV file
Write-Host ""
Write-Host "Writing internal DELIVER data to $FileDeliverInternalTotals"
$HashDataInternal.GetEnumerator() | ForEach-Object {New-Object -TypeName PSObject -Property @{Count=$_.Value;Name=$_.Name}} | `
  Export-CSV -Path $FileDeliverInternalTotals -NoTypeInformation
Write-Host ""
Write-Host "Writing external DELIVER data to $FileDeliverExternalTotals"
$HashDataExternal.GetEnumerator() | ForEach-Object {New-Object -TypeName PSObject -Property @{Count=$_.Value;Name=$_.Name}} | `
  Export-CSV -Path $FileDeliverExternalTotals -NoTypeInformation

#=================================================================
#Cleanup or rename of temp files depending on $KeepTempFiles value
#=================================================================
Write-Host ""
Write-Host "Cleanup" -ForegroundColor Green
Write-Host "======="
if ($KeepTempFiles -eq $false) {Write-Host "Deleting Temp Files"}
$MailboxServers | ForEach-Object {
  if ($KeepTempFiles -eq $false) {
    Remove-Item .\$_.$Date.submit.csv
    Remove-Item .\$_.$Date.deliver.csv
  }
}
Write-Host ""
Write-Host "End" -ForegroundColor Red
Write-Host "==="