<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type content="text/html; charset=iso-8859-1"><meta name=Generator content="Microsoft Word 14 (filtered medium)"><style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";
mso-fareast-language:EN-US;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
span.E-MailFormatvorlage17
{mso-style-type:personal-compose;
font-family:"Calibri","sans-serif";
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri","sans-serif";
mso-fareast-language:EN-US;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:70.85pt 70.85pt 2.0cm 70.85pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></head><body lang=DE link=blue vlink=purple><div class=WordSection1><p class=MsoNormal><span lang=EN-US>Hi all,<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US>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. <o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US>Prerequisites:<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>Windows computer with Powershell and Exchange 2007 Administration Tools installed. Powershell version can either be 1.0 or 2.0, depending on your environment.<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>For the MySQL Part of the script you need to download the .Net MySQL Connector from <a href="http://dev.mysql.com/downloads/connector/net/5.1.html">http://dev.mysql.com/downloads/connector/net/5.1.html</a> and place the file MySQLData.dll into the directory, where the script is located at.<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US>Results are sent by E-Mail and stored into c:\daily.csv (semicolon is used as delimiter) for further usage. <o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US>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 <a href="http://www.roland-ehle.de">http://www.roland-ehle.de</a> is highly appreciated.<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US>Script has no comments yet, sorry.<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US>Regards,<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>Roland<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US>$hubs = Get-TransportServer<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US># Get the start date for the tracking log search <o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>$Start = (Get-Date -Hour 00 -Minute 00 -Second 00).AddDays(-1)<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US># Get the end date for the tracking log search<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>$End = (Get-Date -Hour 23 -Minute 59 -Second 59).AddDays(-1)<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US>$Datum = $Start.ToShortDateString()<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US>$receive = $hubs | get-messagetrackinglog -Start $Start -End $End -EventID "RECEIVE" -ResultSize Unlimited | select Sender,RecipientCount,TotalBytes,Recipients<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>$send = $hubs | get-messagetrackinglog -Start $Start -End $End -EventID "SEND" -ResultSize Unlimited | select Sender,RecipientCount,TotalBytes<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>$mreceive = $receive | Measure-Object TotalBytes -maximum -minimum -average -sum<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>$msend = $send | Measure-Object TotalBytes -maximum -minimum -average -sum<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US>$anzahl = $mreceive.count + $msend.count<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>$volumen = ($mreceive.sum + $msend.sum) / (1024 * 1024)<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US>$volumen = "{0:N2}" -f $volumen + " MB"<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US>$msendmb = $msend.sum / (1024 * 1024)<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>$vsend = "{0:N2}" -f $msendmb + " MB"<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>$bigsend = $msend.maximum / (1024 * 1024)<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>$avsend = $msend.average / 1024<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US>$bigsendmb = "{0:N2}" -f $bigsend + " MB"<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>$avsendkb = "{0:N2}" -f $avsend + " KB"<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US>$mreceivemb = $mreceive.sum / (1024 * 1024)<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>$vreceive = "{0:N2}" -f $mreceivemb + " MB"<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>$bigreceive = $mreceive.maximum / (1024 * 1024)<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>$avreceive = $mreceive.average / 1024<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>#<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US>$bigreceivemb = "{0:N2}" -f $bigreceive + " MB"<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>$avreceivekb = "{0:N2}" -f $avreceive + " KB"<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US>#$senders = $send | Group-Object Sender | Sort-Object Count -Descending<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>#$topsender = $senders[0].Name<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>#$topsender += $senders[0].Count<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US>#$receivers = $receive | Group-Object Recipients | Sort-Object Count -Descending<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>#$topreceiver = $receivers[0]<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>#$topreceiver<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>#$msend.Count<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US>$Yesterday = (get-date -date ((get-date).AddDays(-1)) -uFormat “%Y-%m-%d”);<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US># get the script’s execution path<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>$myPath = Split-Path -Parent $MyInvocation.MyCommand.Path;<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US># load MySQL driver and query database<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>[void][system.reflection.Assembly]::LoadFrom($myPath + "\MySQL.Data.dll");<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US>function getDatafromDb($q)<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>{<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US># the connection string used to connect to the database<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>$connString = "Server=mailwatchserver;Database=mailscanner;Uid=mailwatch;Pwd=password";<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>#<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US>#<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US>$conn = New-Object MySql.Data.MySqlClient.MySqlConnection;<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>$conn.ConnectionString = $connString;<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>$conn.Open();<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>$command = New-Object MySql.Data.MySqlClient.MySqlCommand;<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>$command.Connection = $conn;<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>$command.CommandText = $q;<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>$reader = $command.ExecuteReader();<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>while($reader.Read())<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>{<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US> $script:results = $reader.GetString(0);<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>}<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>$conn.Close();<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>}<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US>$qAnzahl = "SELECT Count(*) FROM maillog where date='$Yesterday'";<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>getDatafromDB $qAnzahl<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>$ganzahl = $results<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US>$qinbound = "select COUNT(*) from maillog where date='$Yesterday' and clientip not REGEXP '^10.24.|^130.55.|^127.0.0.1';"<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>getDatafromDB $qinbound<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>$inbound = $results<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US>$qoutbound = "select COUNT(*) from maillog where date='$Yesterday' and clientip REGEXP '^10.24.245.37|^10.24.20.21';"<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>getDatafromDB $qoutbound<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>$outbound = $results<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US>$qdkbout = "select COUNT(*) from maillog where date='$Yesterday' and clientip REGEXP '^10.24.245.37|^10.24.20.21' and to_domain='dkb.de';"<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>getDatafromDB $qdkbout<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>$dkbout = $results<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US>$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';"<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>getDatafromDB $qkopierer<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>$kopierer = $results<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US>$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';"<o:p></o:p></span></p><p class=MsoNormal>getDatafromDB $qvkopierer<o:p></o:p></p><p class=MsoNormal>$vkopierer = $results / (1024*1024)<o:p></o:p></p><p class=MsoNormal>$vkopierer = "{0:N2}" -f $vkopierer + " MB"<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal><span lang=EN-US>$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';"<o:p></o:p></span></p><p class=MsoNormal>getDatafromDB $qmkopierer<o:p></o:p></p><p class=MsoNormal>$mkopierer = $results / (1024*1024)<o:p></o:p></p><p class=MsoNormal>$mkopierer = "{0:N2}" -f $mkopierer + " MB"<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal><span lang=EN-US>$qspam = "select COUNT(*) from maillog where date='$Yesterday' and clientip not REGEXP '^10.24.|^130.55.' and isspam > 0;"<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>getDatafromDB $qspam<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>$spam = $results<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US>$qvspam = "select SUM(size) from maillog where date='$Yesterday' and clientip not REGEXP '^10.24.|^130.55.' and isspam > 0;"<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>getDatafromDB $qvspam<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>$vspam = $results / (1024*1024)<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>$vspam = "{0:N2}" -f $vspam + " MB"<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US>$qvirus = "select COUNT(*) from maillog where date='$Yesterday' and clientip not REGEXP '^10.24.|^130.55.' and virusinfected > 0;"<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>getDatafromDB $qvirus<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>$virus = $results<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US>$internal = $msend.Count - $outbound<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US>$in = [int]$inbound<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>$in2 = [int]$spam<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US>$spampercent = ($in2 *100) / $in<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>$spampercent = "{0:N2}" -f $spampercent + "%"<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US>$obj = new-object psObject<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US>$obj |Add-Member -MemberType noteproperty -Name "Exchange" -value "Werte"<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>$obj |Add-Member -MemberType noteproperty -Name "Date" -Value $Yesterday<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>$obj |Add-Member -MemberType noteproperty -Name " E-Mails Out Exchange" -Value $msend.Count<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>$obj |Add-Member -MemberType noteproperty -Name "Internal E-Mail traffic " -value $internal<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>$obj |Add-Member -MemberType noteproperty -Name "Volume of E-Mails Out" -Value $vsend<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>$obj |Add-Member -MemberType noteproperty -Name "Largest E-Mail Out" -value $bigsendmb<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>$obj |Add-Member -MemberType noteproperty -Name "Average size of E-Mails Out" -value $avsendkb<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>$obj |Add-Member -MemberType noteproperty -Name "E-Mails In Exchange" -Value $mreceive.Count<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>$obj |Add-Member -MemberType noteproperty -Name "Volume of E-Mails In Exchange" -Value $vreceive<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>$obj |Add-Member -MemberType noteproperty -Name "Largest E-Mail In" -value $bigreceivemb<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>$obj |Add-Member -MemberType noteproperty -Name "Average Size of E-Mails In" -value $avreceivekb<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>$obj |Add-Member -MemberType noteproperty -Name "Messages Total" -Value $anzahl<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>$obj |Add-Member -MemberType noteproperty -Name "Volume Total " -Value $volumen<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US>$objm = new-object psObject<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>$objm |Add-Member -MemberType noteproperty -Name "Mailgateways" -Value "Werte"<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>$objm |Add-Member -MemberType noteproperty -Name "E-Mails Out Mailgateways" -Value $outbound<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>$objm |Add-Member -MemberType noteproperty -Name "E-Mails to company 2" -Value $dkbout<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>$objm |Add-Member -MemberType noteproperty -Name "E-Mails In Mailgateways" -Value $inbound<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>$objm |Add-Member -MemberType noteproperty -Name " Spam E-Mails" -value $spam<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>$objm |Add-Member -MemberType noteproperty -Name "Spam in %" -value $spampercent<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>$objm |Add-Member -MemberType noteproperty -Name "Volume of Spam" -value $vspam<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>$objm |Add-Member -MemberType noteproperty -Name "Viruses found" -value $virus<o:p></o:p></span></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal><span lang=EN-US>$out = $Datum + ";" + $msend.count + ";" + $vsend + ";" + $avsendkb + ";" + $mreceive.count + ";" + $vreceive + ";" + $avreceivekb + ";" + $anzahl + ";" + $volumen + ";" + $outbound + ";" + $dkbout + ";" + $inbound + ";" + $spam + ";" + $spampercent + ";" + $vspam + ";" + $virus<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US>$out | out-file c:\daily.csv -append -encoding default<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US>#function sendmail($body)<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>function sendmail<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>{<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US> $SmtpClient = new-object system.net.mail.smtpClient <o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US> $MailMessage = New-Object system.net.mail.mailmessage <o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US> $SmtpClient.Host = "mailserver" <o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US> $mailmessage.from = "admexchange@domain.tld" <o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US> $mailmessage.To.add("recipient@domain.tld")<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US> $mailmessage.CC.add("recipient@domain.tld")<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US> $mailmessage.Subject = “Messaging infrastructure daily report for $Datum” <o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US> $MailMessage.IsBodyHtml = $true<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US> #$mailmessage.Body = $body<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US> $mailmessage.Body = Get-Content status2.html<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US> $smtpclient.Send($mailmessage)<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US>}<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US>$obj |out-file test.csv -encoding default<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>(gc test.csv | where {$_ -ne ""} ) > test2.csv<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>(gc test2.csv) -replace ' {2,}','' > test3.csv<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>(gc test3.csv) -replace '\.','' > test4.csv<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>(gc test4.csv) -replace ',','.' > test5.csv<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>(gc test5.csv) -replace ': ',',' > test6.csv<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US>$objm |out-file testm.csv -encoding default<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>(gc testm.csv | where {$_ -ne ""} ) > testm2.csv<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>(gc testm2.csv) -replace ' {2,}','' > testm3.csv<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>(gc testm3.csv) -replace '\.','' > testm4.csv<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>(gc testm4.csv) -replace ',','.' > testm5.csv<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>(gc testm5.csv) -replace ': ',',' > testm6.csv<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US>$obj2 = import-csv test6.csv<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>$objm2 = import-csv testm6.csv<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US>#Remove-Item test.csv<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>#Remove-Item test2.csv<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>#Remove-Item test3.csv<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US>$a = "<style>"<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>$a = $a + "BODY{background-color:white;}"<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>$a = $a + "TABLE{border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;}"<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>$a = $a + "TH{width: 300px;border-width: 1px;padding: 5px;border-style: solid;border-color: black;background-color:#0099FF;text-align: left}"<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>$a = $a + "TD{border-width: 1px;padding: 5px;border-style: solid;border-color: black;background-color:palegoldenrod;}"<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>$a = $a + "</style>"<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US>$obj2 | ConvertTo-HTML -head $a | Out-File status.html<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>$objm2 | ConvertTo-Html | Out-File status.html -append<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-US>(gc status.html) -replace '\.',',' > status2.html<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US>sendmail<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-US><o:p> </o:p></span></p><p class=MsoNormal><span style='mso-fareast-language:DE'>Mit freundlichen Grüßen,<o:p></o:p></span></p><p class=MsoNormal><span style='mso-fareast-language:DE'><o:p> </o:p></span></p><p class=MsoNormal><span style='mso-fareast-language:DE'>Roland Ehle<o:p></o:p></span></p><p class=MsoNormal><span style='mso-fareast-language:DE'>Mail: <a href="mailto:roland@inbox4u.de">roland@inbox4u.de</a><o:p></o:p></span></p><p class=MsoNormal><span style='mso-fareast-language:DE'>Phone: +491702174446<o:p></o:p></span></p><p class=MsoNormal><span style='mso-fareast-language:DE'>BB Pin: </span><span style='font-size:10.0pt;font-family:"Arial","sans-serif";color:navy;mso-fareast-language:DE'>215B8A18</span><span style='mso-fareast-language:DE'><o:p></o:p></span></p><p class=MsoNormal><o:p> </o:p></p></div></body></html>