ExchangeBlog » PowerShell @en » Microsoft Exchange get sent emails list – messagetrackinglog

Microsoft Exchange get sent emails list – messagetrackinglog

  By Remigiusz Szatkowski | 14/03/2013 - 01:01 | , ,

This post is also available in: Polish

emailRecently 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:

emails_sent_outside.zip

 

 

Print Friendly


Related posts:

  1. Exam 70-417 Upgrading Your Skills To MCSA Windows Server 2012 – free online training
  2. Exchange 2010 remove mobile device Error The ActiveSyncDevice Cannot be Found
  3. Exchange Server 2013 Deployment Assistant Updated
  4. Microsoft Outlook Configuration Analyzer Tool 2.0 (OCAT)
  5. Microsoft Outlook Configuration Analyzer Tool 2.0 only in OffCAT
Tagged , , , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>