Skip to main content

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

Popular posts from this blog

Testing and Sending email via SMTP using Opportunistic TLS and oAuth in Office365 with PowerShell

As well as EWS and Remote PowerShell (RPS) other mail protocols POP3, IMAP and SMTP have had OAuth authentication enabled in Exchange Online (Official announcement here ). A while ago I created  this script that used Opportunistic TLS to perform a Telnet style test against a SMTP server using SMTP AUTH. Now that oAuth authentication has been enabled in office365 I've updated this script to be able to use oAuth instead of SMTP Auth to test against Office365. I've also included a function to actually send a Message. Token Acquisition  To Send a Mail using oAuth you first need to get an Access token from Azure AD there are plenty of ways of doing this in PowerShell. You could use a library like MSAL or ADAL (just google your favoured method) or use a library less approach which I've included with this script . Whatever way you do this you need to make sure that your application registration  https://docs.microsoft.com/en-us/azure/active-directory/develop/quickstart-register-

How to test SMTP using Opportunistic TLS with Powershell and grab the public certificate a SMTP server is using

Most email services these day employ Opportunistic TLS when trying to send Messages which means that wherever possible the Messages will be encrypted rather then the plain text legacy of SMTP.  This method was defined in RFC 3207 "SMTP Service Extension for Secure SMTP over Transport Layer Security" and  there's a quite a good explanation of Opportunistic TLS on Wikipedia  https://en.wikipedia.org/wiki/Opportunistic_TLS .  This is used for both Server to Server (eg MTA to MTA) and Client to server (Eg a Message client like Outlook which acts as a MSA) the later being generally Authenticated. Basically it allows you to have a normal plain text SMTP conversation that is then upgraded to TLS using the STARTTLS verb. Not all servers will support this verb so if its not supported then a message is just sent as Plain text. TLS relies on PKI certificates and the administrative issue s that come around certificate management like expired certificates which is why I wrote th

The MailboxConcurrency limit and using Batching in the Microsoft Graph API

If your getting an error such as Application is over its MailboxConcurrency limit while using the Microsoft Graph API this post may help you understand why. Background   The Mailbox  concurrency limit when your using the Graph API is 4 as per https://docs.microsoft.com/en-us/graph/throttling#outlook-service-limits . This is evaluated for each app ID and mailbox combination so this means you can have different apps running under the same credentials and the poor behavior of one won't cause the other to be throttled. If you compared that to EWS you could have up to 27 concurrent connections but they are shared across all apps on a first come first served basis. Batching Batching in the Graph API is a way of combining multiple requests into a single HTTP request. Batching in the Exchange Mail API's EWS and MAPI has been around for a long time and its common, for email Apps to process large numbers of smaller items for a variety of reasons.  Batching in the Graph is limited to a m
All sample scripts and source code is provided by for illustrative purposes only. All examples are untested in different environments and therefore, I cannot guarantee or imply reliability, serviceability, or function of these programs.

All code contained herein is provided to you "AS IS" without any warranties of any kind. The implied warranties of non-infringement, merchantability and fitness for a particular purpose are expressly disclaimed.