Thursday, October 05, 2006

BYO Message Tracking Center with PowerShell

One of the things I blog and create a lot of scripts for is message tracking there is so much good information stored in the Message Tracking Logs once you can pull it out and start doing things beyond what the Message Tracking Center can do in ESM. With the ability to access and manipulate WinForms like proper .net applications and also access WMI information you can with a little effort build your own replacement to the normal Message Tracking Center in ESM with a powershell script that will give you a pretty GUI based front-end that even your average Admin should be able to get their head around. And the really cool thing is you can start doing a lot more with the data that you’re mining such as aggregation. By this I mean grouping the number of emails by size and number to alllow you to see trends such as who is sending the most email, who's recieving the most email, how much data is being send and recieved from each email domain and also how much email was sent on a particular date. You can also do a lot more filtering such as only looking at mail that was sent over a certain size , sent/receive or external/internal. These last two are a little experimental which I’ll try to explain later. Most of this stuff I’ve done in the past with other scripts and web apps but this is the first time I’ve pulled it all together using the live logs from a server and I’ve managed to patch up some of my screwy logic (and maybe I’ve created some more).And also you can do one of the things that has always frustrated me about the message tracking center which is export the results of your query to a CSV file.

What does this script do well basically it creates a Winform and adds a whole bunch of controls to that winform such as datapickers, textboxes, checkboxes, numericupanddown s,labels and buttons. Its then wires ups some functions to the button clicks so that when you click the search button in will query the Exchange Message Tracking logs via WMI. The data returned is then added to a ADO.NET data table which is then data bound to a datagridview to display back in the form. Clicking the export button will fire a save-file dialogue box and some code will then export the table results to a csv file. The WMI query looks for message tracking events of type 1020 and 1028 which from previous experience is the best way to capture all the messaging activity without having to worry about doing any address translations. I’ve included some textbox’s to do the normal filtering you would expect in the message tracking center such as from and sent address if these are left blank it will search on all addresses. Also I’ve added the ability to do add an extra filter such as showing only email over a certain size. The other two slightly experimental filters are the Direction of email which is meant to filter email that’s being sent or receive and the Type of Email which is meant to filter email that is Internal or External. Now these two filters work after the query has been made there is a section in the script that queries active directory for all the recipient policies in a domain and goes through each of the polices and adds each of the domains it finds into a hashtable. Then to work out if an email is Internal or External its basically checks the domain of the sender address to see if its contained within the hash table (meaning that its coming from a local sender) and then it also does the same check to see if the recipients domain is in the hash table if both match then the email is an Internal message if they don’t then its External. For sent and received this is much the e same process except a Sent message will have the logic that the from address of a message must be from a domain in the hash table and for a Received message the from address shouldn’t be in the hash table. From a logic standpoint this works okay on a single server but there will be some situations that maybe this wont work but hey..

But the filtering is really just the normal stuff the cool stuff is in the Extras Groups box on the right hand side of the form. When you select one of these boxes (and there is some code to make sure you don’t select more then one at a time) the byo message tracker now turns into an aggregate engine. The aggregation works by instead of just adding the results directly to a Datatable the results are instead feed into two hash tables. One hashtable tracks the number of messages for the value your aggregating and the other tracks the size of the messages for the value your aggregating. So for example if you wanted to find out how much email was being sent to fred.blogs@domain.com when the script was iterating though the WMI results collection it would first check to see if fred.blogs@domain.com was already in the hash table. If it was it would then for the “number of messages” hashtable increments it by one and for the “size” hash table adds the size of the message to the current value in the hash table. Once the WMI result iteration has finished there is some code that then loops though the hash table and adds the hash table results to the data-table and then bind this to the datagridview. This generally works the same for all the group by’s the domain group by just separates the domain from the email address and groups on that instead of the email address and the date again groups on the date instead of the address.

To run the script is basically straight forward when you run it the script will build the winform and should then present this as an active window. The only necessary text box you need to fill out is the servername box for the server you wish to query. From and To are optional leaving these blank means it will query for all address’s. Using the other features should be pretty much self explanatory. A word of warning depending on the size of you message tracking logs and the period of time you query for you may have to be patient waiting for the results.

The main section of the code look like this the full code is included in the download because it’s just to large to post. The code can be downloaded from here


[System.Reflection.Assembly]::LoadWithPartialName("System.Drawing")
[System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms")

function CheckExtraSettings{
$extrasettings = 0
if ($GroupbySender.Checked -eq $true) {$extrasettings = 1}
if ($GroupByReciever.Checked -eq $true) {$extrasettings = 1}
if ($GroupByRecieverDomain.Checked -eq $true) {$extrasettings = 1}
if ($GroupBySenderDomain.Checked -eq $true) {$extrasettings = 1}
if ($GroupByDate.Checked -eq $true) {$extrasettings = 1}
return $extrasettings
}

function AggResults([string]$saAddress){
if ($gbhash1.ContainsKey($saAddress)){
$tsize = [int]$gbhash2[$saAddress] + [int]$svSizeVal
$tnum = [int]$gbhash1[$saAddress] + 1
$gbhash1[$saAddress] = $tnum
$gbhash2[$saAddress] = $tsize
}
else{
$gbhash1.Add($saAddress,1)
$gbhash2.Add($saAddress,$svSizeVal)
}

}

function GetEmailDomains{

$reRootDse = New-Object System.DirectoryServices.DirectoryEntry("LDAP://rootDSE")

$cfConfigroot = New-Object directoryservices.directoryentry("LDAP://" + $reRootDse.configurationnamingcontext)

$dsSearcher = New-Object directoryservices.directorySearcher($cfConfigroot)
$dsSearcher.PropertiesToLoad.Add("gatewayProxy")
$dsSearcher.filter = "(objectCategory=msExchRecipientPolicy)"
$rsResults = $dsSearcher.findall()
foreach ($rsResult in $rsResults) {
$rpProps = $rsResult.properties
foreach ($eaEmailAddress in $rpProps.gatewayproxy){
if ($eaEmailAddress.ToLower().indexofany("smtp:") -eq 0){
$arEmailAddress = $eaEmailAddress.split("@")
if ($adhash.ContainsKey($arEmailAddress[1])){}
else {$adhash.Add($arEmailAddress[1],1)}
}
}
}
}

function adddata{

$adhash.Clear()
$ssTable.Clear()
$gsTable.Clear()
$dchash.Clear()
$gbhash1.Clear()
$gbhash2.Clear()
$servername = $snServerNameTextBox.text
$extrasettings = CheckExtraSettings
$inIncludeit = 0

$dtQueryDT = New-Object System.DateTime
$dpTimeFrom.value.year,$dpTimeFrom.value.month,
$dpTimeFrom.value.day,$dpTimeFrom2.value.hour,
$dpTimeFrom2.value.minute,$dpTimeFrom2.value.second
$dtQueryDTf = New-Object System.DateTime
$dpTimeFrom1.value.year,$dpTimeFrom1.value.month,
$dpTimeFrom1.value.day,$dpTimeFrom3.value.hour,
$dpTimeFrom3.value.minute,$dpTimeFrom3.value.second
$WmidtQueryDT =
[System.Management.ManagementDateTimeConverter]::ToDmtfDateTime($dtQueryDT.ToUniversalTime())
$WmidtQueryDTf =
[System.Management.ManagementDateTimeConverter]::ToDmtfDateTime($dtQueryDTf.ToUniversalTime())
$WmiNamespace = "ROOT\MicrosoftExchangev2"

$filterblock1 = "OriginationTime <= '" + $WmidtQueryDTf + "' and OriginationTime
>= '" + $WmidtQueryDT + "' and entrytype = '1028'"
$filterblock2 = "OriginationTime <= '" + $WmidtQueryDTf + "' and OriginationTime
>= '" + $WmidtQueryDT + "' and entrytype = '1020'"

if ($seSizeCheck.Checked -eq $true){
$schfor = $seSizeCheckNum.value*1024
$filterblock1 = $filterblock1 + " and Size > '" + $schfor.ToString(0.00) + "'"

$filterblock2 = $filterblock2 + " and Size > '" + $schfor.ToString(0.00) + "'"
}

if ($snSenderAddressTextBox.text -ne ""){
$filterblock1 = $filterblock1 + " and SenderAddress = '" +
$snSenderAddressTextBox.text + "'"
$filterblock2 = $filterblock2 + " and SenderAddress = '" +
$snSenderAddressTextBox.text + "'"
}

$filter = $filterblock1 + " or " + $filterblock2
if ($etTypeCheck.Checked -eq $true -bor $edTypeCheck.Checked -eq
$true){GetEmailDomains}
if ($snRecipientAddressTextBox.text -eq ""){
get-wmiobject -class Exchange_MessageTrackingEntry -Namespace $WmiNamespace
-ComputerName $servername -filter $filter | ForEach-Object{
if ($etTypeCheck.Checked -eq $true){
$inInternal = 0
$inIncludeit = 0
$rmRecpMatch = 1
$senddomarray1 = $_.SenderAddress.split("@")
foreach($recp in $_.RecipientAddress){
$recpdomarray1 =$recp.split("@")
if ($adhash.ContainsKey($recpdomarray1[1])){}
else {$rmRecpMatch = 0}
}
if ($senddomarray1.length -ne 1){
if ($adhash.ContainsKey($senddomarray1[1]) -band $rmRecpMatch -eq 1){$inInternal
= 1}}
if ($etTypeCheckDrop.SelectedItem -eq "Internal" -band $inInternal -eq 1){
$inIncludeit = 1
}
if ($etTypeCheckDrop.SelectedItem -eq "External" -band $inInternal -eq 0){
$inIncludeit = 1
}
}
else {$inIncludeit = 1}
if ($edTypeCheck.Checked -eq $true -band $inIncludeit -eq 1){
$issentex = 1
$senddomarray1 = $_.SenderAddress.split("@")
if ($senddomarray1.length -ne 1){
if ($adhash.ContainsKey($senddomarray1[1])){
$issentex = 0
}
}
if ($edTypeCheckDrop.SelectedItem -eq "Sent" -band $issentex -eq 1){$inIncludeit
= 0}
if ($edTypeCheckDrop.SelectedItem -eq "Recieved" -band $issentex -eq
0){$inIncludeit = 0}

}
if ($inIncludeit -eq 1){
$recpval = ""
foreach($recp in $_.RecipientAddress){
if ($recpval -eq ""){$recpval = $recp}
else {$recpval = $recpval + ";" + $recp}}

if($dchash.ContainsKey($_.MessageID)){}
else{
$dchash.Add($_.MessageID,1)
$svSizeVal = $_.size/1024
if ($extrasettings -eq 0){
$ssTable.Rows.Add([System.Management.ManagementDateTimeConverter]::ToDateTime($_.OriginationTime),$_.SenderAddress,$recpval,$_.subject,[int]$svSizeVal.ToString(0.00))
}
if ($GroupbySender.Checked -eq $true -bor $GroupBySenderDomain.Checked -eq $true
){
if ($GroupbySender.Checked -eq $true){
AggResults($_.SenderAddress)
}
else{
$senddomarray = $_.SenderAddress.split("@")
AggResults($senddomarray[1])
}

}
if ($GroupByReciever.Checked -eq $true -bor $GroupByRecieverDomain.Checked -eq
$true ){
foreach($recp in $_.RecipientAddress){
if ($GroupByReciever.Checked -eq $true){
AggResults($recp)
}
else{
$recpdomarray = $recp.split("@")
AggResults($recpdomarray[1])
}
}
}
if ($GroupByDate.Checked -eq $true){
$dateag =
[System.Management.ManagementDateTimeConverter]::ToDateTime($_.OriginationTime)
AggResults($dateag.ToShortDateString())
}
}
}
}
}
else{
get-wmiobject -class Exchange_MessageTrackingEntry -Namespace $WmiNamespace
-ComputerName $servername -filter $filter | where-object {$_.RecipientAddress
-eq $snRecipientAddressTextBox.text} | ForEach-Object{
if ($etTypeCheck.Checked -eq $true){
$inInternal = 0
$inIncludeit = 0
$rmRecpMatch = 1
$senddomarray1 = $_.SenderAddress.split("@")
foreach($recp in $_.RecipientAddress){
$recpdomarray1 =$recp.split("@")
if ($adhash.ContainsKey($recpdomarray1[1])){}
else {$rmRecpMatch = 0}
}
if ($senddomarray1.length -ne 1){
if ($adhash.ContainsKey($senddomarray1[1]) -band $rmRecpMatch -eq 1){$inInternal
= 1}}
if ($etTypeCheckDrop.SelectedItem -eq "Internal" -band $inInternal -eq 1){
$inIncludeit = 1
}
if ($etTypeCheckDrop.SelectedItem -eq "External" -band $inInternal -eq 0){
$inIncludeit = 1
}
}
else {$inIncludeit = 1}
if ($edTypeCheck.Checked -eq $true -band $inIncludeit -eq 1){
$issentex = 1
$senddomarray1 = $_.SenderAddress.split("@")
if ($senddomarray1.length -ne 1){
if ($adhash.ContainsKey($senddomarray1[1])){
$issentex = 0
}
}
if ($edTypeCheckDrop.SelectedItem -eq "Sent" -band $issentex -eq 1){$inIncludeit
= 0}
if ($edTypeCheckDrop.SelectedItem -eq "Recieved" -band $issentex -eq
0){$inIncludeit = 0}

}
if ($inIncludeit -eq 1){
$recpval = ""
foreach($recp in $_.RecipientAddress){if ($recpval -eq ""){$recpval = $recp}
else {$recpval = $recpval + ";" + $recp}}
if($dchash.ContainsKey($_.MessageID)){}
else{
$dchash.Add($_.MessageID,1)
if ($extrasettings -eq 0){
$svSizeVal = $_.size/1024
$ssTable.Rows.Add([System.Management.ManagementDateTimeConverter]::ToDateTime($_.OriginationTime),$_.SenderAddress,$recpval,$_.subject,[int]$svSizeVal.ToString(0.00))
}
if ($GroupbySender.Checked -eq $true -bor $GroupBySenderDomain.Checked -eq $true
){
if ($GroupbySender.Checked -eq $true){
AggResults($_.SenderAddress)
}
else{
$senddomarray = $_.SenderAddress.split("@")
AggResults($senddomarray[1])
}

}
if ($GroupByReciever.Checked -eq $true -bor $GroupByRecieverDomain.Checked -eq
$true ){
foreach($recp in $_.RecipientAddress){
if ($GroupByReciever.Checked -eq $true){
AggResults($recp)
}
else{
$recpdomarray = $recp.split("@")
AggResults($recpdomarray[1])
}
}
}
if ($GroupByDate.Checked -eq $true){
$dateag =
[System.Management.ManagementDateTimeConverter]::ToDateTime($_.OriginationTime)
AggResults($dateag.ToShortDateString())
}
}}}}

if ($extrasettings -eq 0){$dgDataGrid.DataSource = $ssTable

}
else { foreach ($htent in $gbhash1.keys){
$spemarray = $htent.split("@")
$gsTable.Rows.Add($htent,$spemarray[1],[int]$gbhash1[$htent],[int]$gbhash2[$htent])
}
$dgDataGrid.DataSource = $gsTable
}}

function Exportcsv{

$exFileName = new-object System.Windows.Forms.saveFileDialog
$exFileName.DefaultExt = "csv"
$exFileName.Filter = "csv files (*.csv)|*.csv"
$exFileName.InitialDirectory = "c:\temp"
$exFileName.ShowDialog()
if ($exFileName.FileName -ne ""){
$logfile = new-object IO.StreamWriter($exFileName.FileName,$true)
$logfile.WriteLine("OriginationTime,SenderAddress,RecipientAddress,Subject,Size")
foreach($row in $ssTable.Rows){
$logfile.WriteLine($row[0].ToString() + "," + $row[1].ToString() + "," +
$row[2].ToString() + ",`"" + $row[3].ToString() + "`"," + $row[4].ToString())
}
$logfile.Close()
}
}

$Dataset = New-Object System.Data.DataSet
$ssTable = New-Object System.Data.DataTable
$ssTable.TableName = "TrackingLogs"
$ssTable.Columns.Add("Origination Time",[DateTime])
$ssTable.Columns.Add("SenderAddress")
$ssTable.Columns.Add("RecipientAddress")
$ssTable.Columns.Add("Subject")
$ssTable.Columns.Add("Size (KB)",[int])
$Dataset.tables.add($ssTable)
$gsTable = New-Object System.Data.DataTable
$gsTable.TableName = "Grouped-TrackingLogs-Sender"
$gsTable.Columns.Add("EmailAddress")
$gsTable.Columns.Add("Domain")
$gsTable.Columns.Add("Number_Messages",[int])
$gsTable.Columns.Add("Size (KB)",[int])
$Dataset.tables.add($gsTable)
Note ** Code abreviated for size full code availible in the download http://msgdev.mvps.org/exdevblog/mtrackv1.zip