This post is also available in: Polish
Recently we wrote about how to find all the all recipients of the email:
Exchange MessageTrackingLogs get recipients list
Now we will present a powershell script that will help us generate statistics of sent emails.
However, we are not interested in the amount of all outgoing e-mails, their size, but we will focus on emails sent outside of our Exchange organization and get information about the addresses from which they were sent, with which the subject, how many of these emails and how many recipients receive those emails.
This script allows you to obtain information about the senders e-mail addresses that send many messages out.
This script is based on checking the Transport Logs located on Exchange servers with the Hub Transport roles.
After receiving the results of the script you can use the script in the previous post and find out to whom the message was sent, then you will get list of all recipients of this email.
Below we explain how this script work.:
First script part conatins:
- powershell object definitions
- paths to output files with emails statistic
- settings to send email with alert to admin
- $MAX_Recipients – number of recipients which define when to generate alert sent by email
Add-PSSnapin Microsoft.Exchange.Management.PowerShell.E2010
Set-AdServerSettings -ViewEntireForest $True
Function New-Array {,$args}
$Report = New-Array
$Report_SMTP = New-Array
$Rep_sum = New-Array
$Rep_SMTP_sum = New-Array
$data = $($((get-date).adddays(-1)).ToString('yyyy.MM.dd'))
#paths to output files
$Out_Rep_file = "d:\Scripts\Logs\Stats\msg_stat_out_$data.csv"
$Out_Rep_sum_file = "d:\Scripts\Logs\Stats\msg_stat_out_sum_$data.csv"
#files with emails with smtp traffic
$Out_Rep_SMTP_file = "d:\Scripts\Logs\Stats\msg_stat_smtp_out_$data.csv"
$Out_Rep_SMTP_sum_file = "d:\Scripts\Logs\Stats\msg_stat_smtp_out_sum_$data.csv"
#file with report attached to email (emails recipients grater than $MAX_Recipients)
$Email_HTML_File = "d:\Scripts\Logs\Stats\Report_outgoing_emails_$data.htm"
#settings for email with report
$mail_from = "exchangereport@domain.com"
$mail_to = "admin@domain.com"
$mail_smtp_host = "smtpserver.domain.local"
$mail_subject = "Report outgoing emails $data"
#variable that defines the threshold for recipients to write to email report
$MAX_Recipients = 100
You will get follwoing output files:
- out_$data.csv – file containing all emails sent from Exchange organization outside
- out_sum_$data.csv – file containing the emails sent out, grouped by subject and sender address
In addition, the files contain the name of the SMTP which means that files contained in the emails “passing” through our servers HT transferred to the Internet.
This applies in the case of extended Exchange organization and systems or scripts automatically sending emails.
In the next part of the script, we get all domains supported by our Exchange servers, and then search the Transaction Logs for the last 24 hours for those emails that:
- source equal SMTP and eventid equal Receive
- source equal STOREDRIVER and eventid equal Receive
$accepted_domains = Get-AcceptedDomain |% {$_.domainname.domain}
[regex]$dom_rgx = "`(?i)(?:" + (($accepted_domains |% {"@" + [regex]::escape($_)}) -join "|") + ")$"
Get-TransportServer | Get-MessageTrackingLog -resultsize unlimited -start (get-date).addhours(-24) -end (get-date) -EventId Receive | ? {$_.source -eq "SMTP" -or $_.source -eq "STOREDRIVER"} | % {
#get emails with evnetid Receive and source SMTP
if ($_.eventid -eq "RECEIVE" -and $_.source -eq "SMTP"){
if ($_.recipients -notmatch $dom_rgx){
$timestamp = ''
$ext_count = ''
$subject = ''
$sender = ''
$server = ''
$msgID = ''
$timestamp = $_.timestamp
$ext_count = ($_.recipients -notmatch $dom_rgx).count
$subject = $_.messagesubject
$sender = $_.sender
$server = $_.serverhostname
$msgID = $_.MessageId
$tmp = New-Object System.Object
$tmp | Add-Member -type NoteProperty -name Timestamp -value $timestamp
$tmp | Add-Member -type NoteProperty -name Sender -value $sender $tmp | Add-Member -type NoteProperty -name Subject -value $Subject
$tmp | Add-Member -type NoteProperty -name Recipients -value $ext_count
$tmp | Add-Member -type NoteProperty -name Server -value $server $tmp | Add-Member -type NoteProperty -name MsgID -value $msgID
$Report_SMTP += $tmp
}
}
#get emails with evnetid Receive and source StoreDriver
if ($_.eventid -eq "RECEIVE" -and $_.source -eq "STOREDRIVER"){
if ($_.recipients -notmatch $dom_rgx){
$timestamp = ''
$ext_count = ''
$subject = ''
$sender = ''
$server = ''
$msgID = ''
$timestamp = $_.timestamp
$ext_count = ($_.recipients -notmatch $dom_rgx).count
$subject = $_.messagesubject
$sender = $_.sender
$server = $_.serverhostname
$msgID = $_.MessageId
$tmp = New-Object System.Object
$tmp | Add-Member -type NoteProperty -name Timestamp -value $timestamp
$tmp | Add-Member -type NoteProperty -name Sender -value $sender
$tmp | Add-Member -type NoteProperty -name Subject -value $Subject
$tmp | Add-Member -type NoteProperty -name Recipients -value $ext_count
$tmp | Add-Member -type NoteProperty -name Server -value $server
$tmp | Add-Member -type NoteProperty -name MsgID -value $msgID
$Report += $tmp
}
}
}
The next step is to group the results by sender, and then by the subject of the message:
#group sender to get unique senders smtp addresses $emails = $Report | Group-Object sender | sort name $emails_smtp = $Report_SMTP | Group-Object sender | sort name
foreach ($sender in $emails){
$Report | ? {$_.sender -like $($sender.name)} | Group-Object subject | %{
$Rep_sum += New-Object psobject -property @{
Sender = $($sender.name)
Subject = $_.Name
Recipients_count = ($_.group | Measure-Object Recipients -Sum).sum
Emails = $_.count
}
}
}
foreach ($sender in $emails_smtp){
$Report_SMTP | ? {$_.sender -like $($sender.name)} | Group-Object subject | %{
$Rep_SMTP_sum += New-Object psobject -property @{
Sender = $($sender.name)
Subject = $_.Name
Recipients_count = ($_.group | Measure-Object Recipients -Sum).sum
Emails = $_.count
}
}
}
Now we will create output .csv reports files and generate a report file for sending e-mail alert if the number of recipients exceeds $MAX_RECIPIENTS
#save all emails as reports $Report | Export-Csv $Out_Rep_file $Report_SMTP | Export-Csv $Out_Rep_SMTP_file
#save grouped emailes to files $Rep_sum | Export-Csv $Out_Rep_sum_file $Rep_SMTP_sum |Export-Csv $Out_Rep_SMTP_sum_file
#create report for email alert where recipients_count greater than $MAX_Recipients
$E_body = $Rep_sum | ? {$_.Recipients_count -gt $MAX_Recipients} | sort Recipients_count -Descending
$E_body_smtp = $Rep_smtp_sum | ? {$_.Recipients_count -gt $MAX_Recipients} | sort Recipients_count -Descending
and finally generate a report file containing the entries sent by email containing emails where the number of recipients exceeds $MAX_RECIPIENTS
#Create HTML report to send by email
Function writeHtmlHeader
{
param($fileName)
Add-Content $fileName "<html>"
Add-Content $fileName "<head>"
Add-Content $fileName "<meta http-equiv='Content-Type' content='text/html; charset=iso-8859-1'>"
Add-Content $fileName '<title>Outgoing emails Report</title>'
Add-Content $fileName '<STYLE TYPE="text/css">'
Add-Content $fileName "<!--"
Add-Content $fileName "td {"
Add-Content $fileName "font-family: Tahoma;"
Add-Content $fileName "font-size: 13px;"
Add-Content $fileName "border-top: 1px solid #999999;"
Add-Content $fileName "border-right: 1px solid #999999;"
Add-Content $fileName "border-bottom: 1px solid #999999;"
Add-Content $fileName "border-left: 1px solid #999999;"
Add-Content $fileName "padding-top: 0px;"
Add-Content $fileName "padding-right: 0px;"
Add-Content $fileName "padding-bottom: 0px;"
Add-Content $fileName "padding-left: 0px;"
Add-Content $fileName "}"
Add-Content $fileName "body {"
Add-Content $fileName "margin-left: 5px;"
Add-Content $fileName "margin-top: 5px;"
Add-Content $fileName "margin-right: 0px;"
Add-Content $fileName "margin-bottom: 10px;"
Add-Content $fileName ""
Add-Content $fileName "table {"
Add-Content $fileName "border: thin solid #000000;"
Add-Content $fileName "}"
Add-Content $fileName "-->"
Add-Content $fileName "</style>"
Add-Content $fileName "</head>"
Add-Content $fileName "<body>"
Add-Content $fileName "<table width='100%'>"
Add-Content $fileName "<tr bgcolor='#CCCCCC'>"
Add-Content $fileName "<td height='25' align='center'>"
Add-Content $fileName "<font face='tahoma' color='#003399' size='4'><strong>Raport wyslanych emaili - $data</strong></font>"
Add-Content $fileName "</td>"
Add-Content $fileName "</tr>"
Add-Content $fileName "</table><BR>"
Add-Content $fileName "<table width='100%'>"
}
Function writeHtmlFooter
{
param($fileName)
Add-Content $fileName "</table>"
Add-Content $fileName "</body>"
Add-Content $fileName "</html>"
}
Function sendEmail
{ param($from,$to,$subject,$smtphost,$htmlFileName)
$body = Get-Content $htmlFileName
$smtp= New-Object System.Net.Mail.SmtpClient $smtphost
$msg = New-Object System.Net.Mail.MailMessage $from, $to, $subject, $body
$msg.cc.Add("secondadmin@domain.com")
$Attachment = New-Object Net.Mail.Attachment($htmlFileName)
$msg.Attachments.Add($Attachment)
$msg.isBodyhtml = $true
$smtp.send($msg)
}
if ($E_body_smtp -or $E_body){
writehtmlheader $Email_HTML_File
if($E_body){
Add-Content $Email_HTML_File "<tr><td align='left' colspan=4> <BR></td></tr>"
Add-Content $Email_HTML_File "<tr bgcolor=#CCCCCC><td width='10%' align='center' colspan=4><font face='tahoma' size='3'><strong>Outgoing Emails</strong></td></tr>"
Add-Content $Email_HTML_File "<tr><td width='10%' align='center' size='2'><strong>Recipients_count</strong></td><td width='50%' align='center' size='2'><strong>Subject</strong></td><td width='30%' align='center' size='2'><strong>Sender</strong></td><td width='10%' align='center' size='2'><strong>Emails</strong></td></tr>"
foreach ($e in $E_body){
Add-Content $Email_HTML_File "<tr><td align='center'>$($e.Recipients_count)</td><td align='center'> $($e.subject)</td><td align='center'>$($e.sender)</td><td align='center'>$($e.Emails)</td></tr>"
}
}
if($E_body_smtp){
Add-Content $Email_HTML_File "<tr><td align='left' colspan=4> <BR></td></tr>"
Add-Content $Email_HTML_File "<tr bgcolor=#CCCCCC><td width='10%' align='center' colspan=4><font face='tahoma' size='3'><strong>SMTP Outgoing Emails</strong></td></tr>"
Add-Content $Email_HTML_File "<tr><td width='10%' align='center' size='2'><strong>Recipients_count</strong></td><td width='50%' align='center' size='2'><strong>Subject</strong></td><td width='30%' align='center' size='2'><strong>Sender</strong></td><td width='10%' align='center' size='2'><strong>Emails</strong></td></tr>"
foreach ($e in $E_body_smtp){
Add-Content $Email_HTML_File "<tr><td align='center'>$($e.Recipients_count)</td><td align='center'> $($e.subject)</td><td align='center'>$($e.sender)</td><td align='center'>$($e.Emails)</td></tr>"
}
}
writehtmlfooter $Email_HTML_File sendEmail $mail_from $mail_to $mail_subject $mail_smtp_host $Email_HTML_File }
As a result of the script we will get four csv files:
- msg_stat_out_$data.csv – all emails searched for the query eventid = Receive and source = StoreDriver
- msg_stat_smtp_out_$data.csv – all emails searched for the query eventid = SMTP i source = StoreDriver
- msg_stat_out_sum_$data – all emails searched for the query eventid = Receive and source = StoreDriver group by sender and subject
- msg_stat_smtp_out_sum_$data.csv – all emails searched for the query eventid = SMTP i source = StoreDriver group by sender and subject
In addition, if any messages grouped by sender will have more than 100 recipients, script will create an additional file, $Report_outgoing_emails_$data.htm which will be sent by email.
You can download the entire script here:
English 
polski