Messaging statistics for a MailScanner / Exchange 2007 environment

[Ehle, Roland]

Hi all,

I wrote a Powershell Script to have  the daily overall messaging statistics in a MailScanner / Exchange 2007 environment.  The script collects the information from the Message Tracking Logs of all Transport Servers and by doing some selects on the MailWatch MySQL database.

Prerequisites:
Windows computer with Powershell and Exchange 2007 Administration Tools installed. Powershell version can either be 1.0 or 2.0, depending on your environment.
For the MySQL Part of the script you need to download the .Net MySQL Connector from http://dev.mysql.com/downloads/connector/net/5.1.html and place the file MySQLData.dll into the directory, where the script is located at.

Results are sent by E-Mail and stored into c:\daily.csv (semicolon is used as delimiter) for further usage.

The script has been successfully tested on Exchange 2010 too. Script is herewith contributed as is. If you find the script useful, a link to my homepage http://www.roland-ehle.de is highly appreciated.

Script has no comments yet, sorry.

Regards,
Roland

$hubs = Get-TransportServer

# Get the start date for the tracking log search
$Start = (Get-Date -Hour 00 -Minute 00 -Second 00).AddDays(-1)

# Get the end date for the tracking log search
$End = (Get-Date -Hour 23 -Minute 59 -Second 59).AddDays(-1)

$Datum = $Start.ToShortDateString()

$receive = $hubs | get-messagetrackinglog -Start $Start -End $End -EventID "RECEIVE" -ResultSize Unlimited | select Sender,RecipientCount,TotalBytes,Recipients
$send = $hubs | get-messagetrackinglog -Start $Start -End $End -EventID "SEND" -ResultSize Unlimited | select Sender,RecipientCount,TotalBytes
$mreceive = $receive | Measure-Object TotalBytes -maximum -minimum -average -sum
$msend = $send | Measure-Object TotalBytes -maximum -minimum -average -sum

$anzahl = $mreceive.count + $msend.count
$volumen = ($mreceive.sum + $msend.sum) / (1024 * 1024)

$volumen = "{0:N2}" -f $volumen + " MB"

$msendmb = $msend.sum / (1024 * 1024)
$vsend = "{0:N2}" -f $msendmb + " MB"
$bigsend = $msend.maximum / (1024 * 1024)
$avsend = $msend.average / 1024

$bigsendmb = "{0:N2}" -f $bigsend + " MB"
$avsendkb = "{0:N2}" -f $avsend + " KB"


$mreceivemb = $mreceive.sum / (1024 * 1024)
$vreceive = "{0:N2}" -f $mreceivemb + " MB"
$bigreceive = $mreceive.maximum / (1024 * 1024)
$avreceive = $mreceive.average / 1024
#

$bigreceivemb = "{0:N2}" -f $bigreceive + " MB"
$avreceivekb = "{0:N2}" -f $avreceive + " KB"


#$senders = $send | Group-Object Sender | Sort-Object Count -Descending
#$topsender = $senders[0].Name
#$topsender += $senders[0].Count


#$receivers = $receive | Group-Object Recipients | Sort-Object Count -Descending
#$topreceiver = $receivers[0]
#$topreceiver
#$msend.Count


$Yesterday = (get-date -date ((get-date).AddDays(-1)) -uFormat "%Y-%m-%d");
# get the script's execution path
$myPath = Split-Path -Parent $MyInvocation.MyCommand.Path;
# load MySQL driver and query database
[void][system.reflection.Assembly]::LoadFrom($myPath + "\MySQL.Data.dll");


function getDatafromDb($q)
{

# the connection string used to connect to the database
$connString = "Server=mailwatchserver;Database=mailscanner;Uid=mailwatch;Pwd=password";
#

#

$conn = New-Object MySql.Data.MySqlClient.MySqlConnection;
$conn.ConnectionString = $connString;
$conn.Open();
$command = New-Object MySql.Data.MySqlClient.MySqlCommand;
$command.Connection = $conn;
$command.CommandText = $q;
$reader = $command.ExecuteReader();
while($reader.Read())
{
  $script:results = $reader.GetString(0);
}
$conn.Close();
}

$qAnzahl = "SELECT Count(*) FROM maillog where date='$Yesterday'";
getDatafromDB $qAnzahl
$ganzahl = $results

$qinbound = "select COUNT(*) from maillog where date='$Yesterday' and clientip not REGEXP '^10.24.|^130.55.|^127.0.0.1';"
getDatafromDB $qinbound
$inbound = $results

$qoutbound = "select COUNT(*) from maillog where date='$Yesterday' and clientip REGEXP '^10.24.245.37|^10.24.20.21';"
getDatafromDB $qoutbound
$outbound = $results

$qdkbout = "select COUNT(*) from maillog where date='$Yesterday' and clientip REGEXP '^10.24.245.37|^10.24.20.21' and to_domain='dkb.de';"
getDatafromDB $qdkbout
$dkbout = $results

$qkopierer = "select COUNT(*) from maillog where date='$Yesterday' and clientip REGEXP '^10.24|^130.55' and clientip not REGEXP '^10.24.245.37|^10.24.20.21|^127.0.0.1';"
getDatafromDB $qkopierer
$kopierer = $results

$qvkopierer = "select SUM(size) from maillog where date='$Yesterday' and clientip REGEXP '^10.24|^130.55' and clientip not REGEXP '^10.24.245.37|^10.24.20.21|^127.0.0.1';"
getDatafromDB $qvkopierer
$vkopierer = $results / (1024*1024)
$vkopierer = "{0:N2}" -f $vkopierer + " MB"

$qmkopierer = "select MAX(size) from maillog where date='$Yesterday' and clientip REGEXP '^10.24|^130.55' and clientip not REGEXP '^10.24.245.37|^10.24.20.21|^127.0.0.1';"
getDatafromDB $qmkopierer
$mkopierer = $results / (1024*1024)
$mkopierer = "{0:N2}" -f $mkopierer + " MB"

$qspam = "select COUNT(*) from maillog where date='$Yesterday' and clientip not REGEXP '^10.24.|^130.55.' and isspam > 0;"
getDatafromDB $qspam
$spam = $results

$qvspam = "select SUM(size) from maillog where date='$Yesterday' and clientip not REGEXP '^10.24.|^130.55.' and isspam > 0;"
getDatafromDB $qvspam
$vspam = $results / (1024*1024)
$vspam = "{0:N2}" -f $vspam + " MB"

$qvirus = "select COUNT(*) from maillog where date='$Yesterday' and clientip not REGEXP '^10.24.|^130.55.' and virusinfected > 0;"
getDatafromDB $qvirus
$virus = $results

$internal = $msend.Count - $outbound

$in = [int]$inbound
$in2 = [int]$spam

$spampercent = ($in2 *100) / $in
$spampercent = "{0:N2}" -f $spampercent + "%"



$obj = new-object psObject

$obj |Add-Member -MemberType noteproperty -Name "Exchange" -value "Werte"
$obj |Add-Member -MemberType noteproperty -Name "Date" -Value $Yesterday
$obj |Add-Member -MemberType noteproperty -Name " E-Mails Out  Exchange" -Value $msend.Count
$obj |Add-Member -MemberType noteproperty -Name "Internal E-Mail traffic " -value $internal
$obj |Add-Member -MemberType noteproperty -Name "Volume of E-Mails Out" -Value $vsend
$obj |Add-Member -MemberType noteproperty -Name "Largest E-Mail Out" -value $bigsendmb
$obj |Add-Member -MemberType noteproperty -Name "Average size of E-Mails Out" -value $avsendkb
$obj |Add-Member -MemberType noteproperty -Name "E-Mails In Exchange" -Value $mreceive.Count
$obj |Add-Member -MemberType noteproperty -Name "Volume of E-Mails In Exchange" -Value $vreceive
$obj |Add-Member -MemberType noteproperty -Name "Largest E-Mail In" -value $bigreceivemb
$obj |Add-Member -MemberType noteproperty -Name "Average Size of E-Mails In" -value $avreceivekb
$obj |Add-Member -MemberType noteproperty -Name "Messages Total" -Value $anzahl
$obj |Add-Member -MemberType noteproperty -Name "Volume Total " -Value $volumen


$objm = new-object psObject
$objm |Add-Member -MemberType noteproperty -Name "Mailgateways" -Value "Werte"
$objm |Add-Member -MemberType noteproperty -Name "E-Mails Out Mailgateways" -Value $outbound
$objm |Add-Member -MemberType noteproperty -Name "E-Mails to company 2" -Value $dkbout
$objm |Add-Member -MemberType noteproperty -Name "E-Mails In Mailgateways" -Value $inbound
$objm |Add-Member -MemberType noteproperty -Name " Spam E-Mails" -value $spam
$objm |Add-Member -MemberType noteproperty -Name "Spam in %" -value $spampercent
$objm |Add-Member -MemberType noteproperty -Name "Volume of Spam" -value $vspam
$objm |Add-Member -MemberType noteproperty -Name "Viruses found" -value $virus


$out = $Datum + ";" + $msend.count + ";" + $vsend + ";" + $avsendkb + ";" + $mreceive.count + ";" + $vreceive + ";" + $avreceivekb + ";" + $anzahl + ";" + $volumen + ";" + $outbound + ";" + $dkbout + ";" + $inbound + ";" + $spam + ";" + $spampercent + ";" + $vspam + ";" + $virus

$out | out-file c:\daily.csv -append -encoding default

#function sendmail($body)
function sendmail
{
    $SmtpClient = new-object system.net.mail.smtpClient
    $MailMessage = New-Object system.net.mail.mailmessage
    $SmtpClient.Host = "mailserver"
    $mailmessage.from = "admexchange at domain.tld"
    $mailmessage.To.add("recipient at domain.tld")
    $mailmessage.CC.add("recipient at domain.tld")
    $mailmessage.Subject = "Messaging infrastructure daily report for $Datum"
    $MailMessage.IsBodyHtml = $true
    #$mailmessage.Body = $body
    $mailmessage.Body = Get-Content status2.html
    $smtpclient.Send($mailmessage)

}

$obj |out-file test.csv -encoding default
(gc test.csv | where {$_ -ne ""} ) > test2.csv
(gc test2.csv) -replace ' {2,}','' > test3.csv
(gc test3.csv) -replace '\.','' > test4.csv
(gc test4.csv) -replace ',','.' > test5.csv
(gc test5.csv) -replace ': ',',' > test6.csv

$objm |out-file testm.csv -encoding default
(gc testm.csv | where {$_ -ne ""} ) > testm2.csv
(gc testm2.csv) -replace ' {2,}','' > testm3.csv
(gc testm3.csv) -replace '\.','' > testm4.csv
(gc testm4.csv) -replace ',','.' > testm5.csv
(gc testm5.csv) -replace ': ',',' > testm6.csv


$obj2 = import-csv test6.csv
$objm2 = import-csv testm6.csv

#Remove-Item test.csv
#Remove-Item test2.csv
#Remove-Item test3.csv

$a = "<style>"
$a = $a + "BODY{background-color:white;}"
$a = $a + "TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}"
$a = $a + "TH{width: 300px;border-width: 1px;padding: 5px;border-style: solid;border-color: black;background-color:#0099FF;text-align: left}"
$a = $a + "TD{border-width: 1px;padding: 5px;border-style: solid;border-color: black;background-color:palegoldenrod;}"
$a = $a + "</style>"

$obj2 | ConvertTo-HTML -head $a | Out-File status.html
$objm2 | ConvertTo-Html | Out-File status.html -append

(gc status.html) -replace '\.',',' > status2.html
sendmail

Mit freundlichen Grüßen,

Roland Ehle
Mail: roland at inbox4u.de<mailto:roland at inbox4u.de>
Phone: +491702174446
BB Pin: 215B8A18

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.mailscanner.info/pipermail/mailscanner/attachments/20100606/7c9006d9/attachment.html