Thursday, December 14, 2006

Quick Create Mailbox Powershell Form for Exchange 2007

A lot of times when your evaluating or testing code you may want to create some temporary mailboxes very quickly to test this or that. In Exchange 2007 the Exchange Command Shell’s new-mailbox commandlet gives a quick way of doing this. But because of the complexities of actually creating a mailbox eg knowing the Conical name of the OU and the name of the database you want to put it into etc actually typing all these values isn’t a fast or easy task. If your scripting this its not such a big deal because when your creating the script you can just cut and paste the values into your script and then you can run the thing a number of times and not have to worry about it. The Exchange Management Console provides wizards to create a mailbox but like all wizards they are designed to be easy to use not fast. So what I decided to do was see if I could put together a small 1 page form that you could run from the Exchange Command shell that would allow me to quickly put in the information that I wanted and then call the New-Mailbox commandlet to create the account.

Using the example from get-help new-mailbox as a template I created a form and populated it with textboxes to allow input of

UserPrincipalName,Alias,FirstName,LastName,DisplayName

To remove the requirement of knowing the canonical name of the OU and exact servername and database name I used Comboboxes for these values and then wired these to some other code and cmdlets to fill the values in the drop downs.

For the OU Name box an ADSI query of all the OU’s in the domain is used to populate the OU Name dropdown . For the Servername box the get-mailboxserver cmdlet is used to populate the list of mailbox servers. The servername combobox has one wired event so when a servername is select the get-mailboxdatabase cmdlet is run to then populate the Mailstore combobox for the selected severname. If the servername is change the Mailstore dropdown values should also change (to be honest I didn’t test this because I don’t have more the one server)

The password box is just a textbox as well with the UseSystemPasswordChar property set true to provide the normal password masking.

The rest of the codes is relatively simple I used some hashtables to mask some of the values to make them more practical to use in a Winform such as trimming the domain from the conical name. But the rest of the code just builds the form and then builds the new-mailbox command. The result of the command is written back to the commandline so any error messages will be displayed there.

The script only runs in the Exchange Command Shell because it’s using the Exchange cmdlets to do the work. I’ve put a down-loadable copy of the code here the code itself looks like.

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

Function createmailbox{
$psSecurePasswordString = new-object System.Security.SecureString
foreach($char in $pwPassWordTextBox.Text.ToCharArray())
{
$psSecurePasswordString.AppendChar($char)
}
$result = New-mailbox -UserPrincipalName $unUserNameTextBox.text -alias $emAliasTextBox.text -database $MBhash1[$msMailStoreDrop.SelectedItem.ToString()] `
-Name $dsDisplayNameTextBox.text -OrganizationalUnit $OUhash1[$ouOuNameDrop.SelectedItem.ToString()] -password $psSecurePasswordString `
-FirstName $unFirstNameTextBox.text -LastName $lnLastNameTextBox.text -DisplayName $dsDisplayNameTextBox.text
$msgbox = new-object -comobject wscript.shell
if ($result -ne $null){write-host "Mailbox Created Sucessfully"}
else{write-host "Error Creating Mailbox check command Line for Details"}

}


$OUhash1 = @{ }
$MBhash1 = @{ }

$form = new-object System.Windows.Forms.form
$form.Text = "Exchange 2007 Quick User Create Form"
$form.size = new-object System.Drawing.Size(430,400)

# Add UserName Box
$unUserNameTextBox = new-object System.Windows.Forms.TextBox
$unUserNameTextBox.Location = new-object System.Drawing.Size(100,30)
$unUserNameTextBox.size = new-object System.Drawing.Size(130,20)
$form.Controls.Add($unUserNameTextBox)

# Add Username Lable
$unUserNamelableBox = new-object System.Windows.Forms.Label
$unUserNamelableBox.Location = new-object System.Drawing.Size(10,30)
$unUserNamelableBox.size = new-object System.Drawing.Size(100,20)
$unUserNamelableBox.Text = "Username UPN"
$form.Controls.Add($unUserNamelableBox)

# Add Alias Box
$emAliasTextBox = new-object System.Windows.Forms.TextBox
$emAliasTextBox.Location = new-object System.Drawing.Size(100,60)
$emAliasTextBox.size = new-object System.Drawing.Size(130,20)
$form.Controls.Add($emAliasTextBox)

# Add Alias Lable
$emAliaslableBox = new-object System.Windows.Forms.Label
$emAliaslableBox.Location = new-object System.Drawing.Size(10,60)
$emAliaslableBox.size = new-object System.Drawing.Size(100,20)
$emAliaslableBox.Text = "Alias"
$form.Controls.Add($emAliaslableBox)

# Add FirstName Box
$unFirstNameTextBox = new-object System.Windows.Forms.TextBox
$unFirstNameTextBox.Location = new-object System.Drawing.Size(100,90)
$unFirstNameTextBox.size = new-object System.Drawing.Size(130,20)
$form.Controls.Add($unFirstNameTextBox)

# Add FirstName Lable
$unFirstNamelableBox = new-object System.Windows.Forms.Label
$unFirstNamelableBox.Location = new-object System.Drawing.Size(10,90)
$unFirstNamelableBox.size = new-object System.Drawing.Size(60,20)
$unFirstNamelableBox.Text = "First Name"
$form.Controls.Add($unFirstNamelableBox)

# Add LastName Box
$lnLastNameTextBox = new-object System.Windows.Forms.TextBox
$lnLastNameTextBox.Location = new-object System.Drawing.Size(100,120)
$lnLastNameTextBox.size = new-object System.Drawing.Size(130,20)
$form.Controls.Add($lnLastNameTextBox)

# Add LastName Lable
$lnLastNamelableBox = new-object System.Windows.Forms.Label
$lnLastNamelableBox.Location = new-object System.Drawing.Size(10,120)
$lnLastNamelableBox.size = new-object System.Drawing.Size(60,20)
$lnLastNamelableBox.Text = "Last Name"
$form.Controls.Add($lnLastNamelableBox)

# Add DisplayName Box
$dsDisplayNameTextBox = new-object System.Windows.Forms.TextBox
$dsDisplayNameTextBox.Location = new-object System.Drawing.Size(100,150)
$dsDisplayNameTextBox.size = new-object System.Drawing.Size(130,20)
$form.Controls.Add($dsDisplayNameTextBox)

# Add DisplayName Lable
$dsDisplayNamelableBox = new-object System.Windows.Forms.Label
$dsDisplayNamelableBox.Location = new-object System.Drawing.Size(10,150)
$dsDisplayNamelableBox.size = new-object System.Drawing.Size(100,20)
$dsDisplayNamelableBox.Text = "Display Name"
$form.Controls.Add($dsDisplayNamelableBox)

# Add OU Drop Down
$ouOuNameDrop = new-object System.Windows.Forms.ComboBox
$ouOuNameDrop.Location = new-object System.Drawing.Size(100,180)
$ouOuNameDrop.Size = new-object System.Drawing.Size(230,30)
$ouOuNameDrop.Items.Add("/Users")
$OUhash1.Add("/Users","Users")
$root = [ADSI]''
$searcher = new-object System.DirectoryServices.DirectorySearcher($root)
$searcher.Filter = '(objectClass=organizationalUnit)'
$searcher.PropertiesToLoad.Add("canonicalName")
$searcher.PropertiesToLoad.Add("Name")
$searcher1 = $searcher.FindAll()
foreach ($person in $searcher1){
[string]$ent = $person.Properties.canonicalname
$OUhash1.Add($ent.substring($ent.indexof("/"),$ent.length-$ent.indexof("/")),$ent)
$ouOuNameDrop.Items.Add($ent.substring($ent.indexof("/"),$ent.length-$ent.indexof("/")))
}
$form.Controls.Add($ouOuNameDrop)

# Add OU DropLable
$ouOuNamelableBox = new-object System.Windows.Forms.Label
$ouOuNamelableBox.Location = new-object System.Drawing.Size(10,180)
$ouOuNamelableBox.size = new-object System.Drawing.Size(100,20)
$ouOuNamelableBox.Text = "OU Name"
$form.Controls.Add($ouOuNamelableBox)

# Add Server Drop Down
$snServerNameDrop = new-object System.Windows.Forms.ComboBox
$snServerNameDrop.Location = new-object System.Drawing.Size(100,210)
$snServerNameDrop.Size = new-object System.Drawing.Size(130,30)
get-mailboxserver | ForEach-Object{$snServerNameDrop.Items.Add($_.Name)}
$snServerNameDrop.Add_SelectedValueChanged({
$msMailStoreDrop.Items.Clear()
get-mailboxdatabase -Server $snServerNameDrop.SelectedItem.ToString()| ForEach-Object{$msMailStoreDrop.Items.Add($_.Name)
$MBhash1.add($_.Name,$_.ServerName + "\" + $_.StorageGroup.Name + "\" + $_.Name)
}
})
$form.Controls.Add($snServerNameDrop)

# Add Server DropLable
$snServerNamelableBox = new-object System.Windows.Forms.Label
$snServerNamelableBox.Location = new-object System.Drawing.Size(10,210)
$snServerNamelableBox.size = new-object System.Drawing.Size(100,20)
$snServerNamelableBox.Text = "ServerName"
$form.Controls.Add($snServerNamelableBox)

# Add MailStore Drop Down
$msMailStoreDrop = new-object System.Windows.Forms.ComboBox
$msMailStoreDrop.Location = new-object System.Drawing.Size(100,240)
$msMailStoreDrop.Size = new-object System.Drawing.Size(130,30)
$form.Controls.Add($msMailStoreDrop)

# Add MailStore DropLable
$msMailStorelableBox = new-object System.Windows.Forms.Label
$msMailStorelableBox.Location = new-object System.Drawing.Size(10,240)
$msMailStorelableBox.size = new-object System.Drawing.Size(100,20)
$msMailStorelableBox.Text = "Mail-Store"
$form.Controls.Add($msMailStorelableBox)

# Add Password Box
$pwPassWordTextBox = new-object System.Windows.Forms.TextBox
$pwPassWordTextBox.Location = new-object System.Drawing.Size(100,270)
$pwPassWordTextBox.size = new-object System.Drawing.Size(130,20)
$pwPasswordTextBox.UseSystemPasswordChar = $true
$form.Controls.Add($pwPassWordTextBox)

# Add Password Lable
$pwPassWordlableBox = new-object System.Windows.Forms.Label
$pwPassWordlableBox.Location = new-object System.Drawing.Size(10,270)
$pwPassWordlableBox.size = new-object System.Drawing.Size(60,20)
$pwPassWordlableBox.Text = "Password"
$form.Controls.Add($pwPassWordlableBox)

# Add Create Button

$crButton = new-object System.Windows.Forms.Button
$crButton.Location = new-object System.Drawing.Size(110,310)
$crButton.Size = new-object System.Drawing.Size(100,23)
$crButton.Text = "Create Mailbox"
$crButton.Add_Click({CreateMailbox})
$form.Controls.Add($crButton)

$form.topmost = $true
$form.Add_Shown({$form.Activate()})
$form.ShowDialog()

Friday, December 08, 2006

Scripting Exchange Web Services (2007) with VBS and Powershell

I’ve been playing around with the new Exchange 2007 Web Services and thought I would share a few scripts. With a lot of API’s being deemphasized or disappearing completely in Exchange 2007, Exchange Web Services are the brave new world that confronts people who want to develop applications that run against an Exchange 2007 server. Like any other WebService EWS allows you to invoke different methods which will perform specific tasks by sending SOAP messages that contain certain properties and then receiving specifically formatted responses. For a scripting point of view it’s pretty easy firstly you need to authenticate the default authentication is NTLM so there is no need to worry about FBA synthetic logons and then it’s just a matter of posting a XML formatted SOAP message. If your familiar with using WebDAV the underlying methods you use are the same but the requests and responses are very different. Lets start with a simple send email example.

smSoapMessage = "<?xml version='1.0' encoding='utf-8'?>" _
& "<soap:Envelope xmlns:soap=""http://schemas.xmlsoap.org/soap/envelope/"" " _
& " xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance""
xmlns:xsd=""http://www.w3.org/2001/XMLSchema"">" _
& "<soap:Body><CreateItem MessageDisposition=""SendAndSaveCopy"" " _
& " xmlns=""http://schemas.microsoft.com/exchange/services/2006/messages""> " _
& "<SavedItemFolderId><DistinguishedFolderId Id=""sentitems""
xmlns=""http://schemas.microsoft.com/exchange/services/2006/types""/>" _
& "</SavedItemFolderId><Items><Message
xmlns=""http://schemas.microsoft.com/exchange/services/2006/types"">" _
& "<ItemClass>IPM.Note</ItemClass><Subject>" & stSubjet & "</Subject><Body
BodyType=""Text"">" & ebEmailBody & "</Body><ToRecipients>" _
& "<Mailbox><EmailAddress>" & EmailAddress &
"</EmailAddress></Mailbox></ToRecipients></Message></Items>" _
& "</CreateItem></soap:Body></soap:Envelope>"
set req = createobject("microsoft.xmlhttp")


The first thing you need to do is build a SOAP message for scripting I prefer to just to build the message manually as a string. You could use the XMLDOM object but this requires a lot more lines of code and complexity. This soap message is a CreateItem request that creates a message and saves it in the SentItems folder. The MessageDisposition Attribute controls how the Exchange server goes about handling this message after its received in this instance it is sent and also a copy is saved to the folder listed in the DistinguishedFolderId element.


set req = createobject("microsoft.xmlhttp")
req.Open "post", "http://" & servername & "/ews/Exchange.asmx", False,"domain\user", "password"
req.setRequestHeader "Content-Type", "text/xml"
req.setRequestHeader "translate", "F"
req.send smSoapMessage
wscript.echo req.status
wscript.echo
wscript.echo req.responsetext

The IIS virtual directory you use to access the Exchange Webservice is http://Servername/EWS. If your send is successful you will see a Status of 200 returned if not you usually get a 50x error message. The response messages are usually pretty good for diagnosing what your doing wrong (its generally a formatting issue with the soap message).

Another method of interest is the GetFolder method which is roughly equivalent to a propfind in WebDAV in that it allows you to retrieve properties from a folder. Some properties of interest might be things like the foldersize or the unread message count on the inbox. The following is a SOAP request that can be used to get the unread message count on the inbox. (Switching from VBS to Powershell this time)

$smSoapMessage = "<?xml version='1.0' encoding='utf-8'?>" `
+ "<soap:Envelope xmlns:soap=`"http://schemas.xmlsoap.org/soap/envelope/`" " `
+ " xmlns:xsi=`"http://www.w3.org/2001/XMLSchema-instance`" xmlns:xsd=`"http://www.w3.org/2001/XMLSchema`"" `
+ " xmlns:t=`"http://schemas.microsoft.com/exchange/services/2006/types`" >" `
+ "<soap:Body>" `
+ "<GetFolder xmlns=`"http://schemas.microsoft.com/exchange/services/2006/messages`"
" `
+ " xmlns:t=`"http://schemas.microsoft.com/exchange/services/2006/types`"> " `
+ "<FolderShape> " `
+ "<t:BaseShape>Default</t:BaseShape> " `
+ "</FolderShape> " `
+ "<FolderIds> " `
+ "<t:DistinguishedFolderId Id=`"inbox`"/> " `
+ "</FolderIds> " `
+ "</GetFolder> " `
+ "</soap:Body></soap:Envelope>"


The BaseShape element controls which properties are returned by the query the default set generally contains all the properties you need to do basic tasks (including the unread count). But if you want to get all the properties or include additional properties there are other elements to do this job.

$servername = "ws03r2eeexchlcs"
$strRootURI = "http://" + $servername + "/ews/Exchange.asmx"
$WDRequest = [System.Net.WebRequest]::Create($strRootURI)
$WDRequest.ContentType = "text/xml"
$WDRequest.Headers.Add("Translate", "F")
$WDRequest.Method = "Post"
$WDRequest.Credentials = $cdUsrCredentials
$bytes = [System.Text.Encoding]::UTF8.GetBytes($smSoapMessage)
$WDRequest.ContentLength = $bytes.Length
$RequestStream = $WDRequest.GetRequestStream()
$RequestStream.Write($bytes, 0, $bytes.Length)
$RequestStream.Close()
$WDResponse = $WDRequest.GetResponse()
$ResponseStream = $WDResponse.GetResponseStream()
$ResponseXmlDoc = new-object System.Xml.XmlDocument
$ResponseXmlDoc.Load($ResponseStream)
$UreadNameNodes = @($ResponseXmlDoc.GetElementsByTagName("t:UnreadCount"))
"Number of Unread Email : " + $UreadNameNodes[0].'#text'

The one important thing here when you are looking at the results returned by the EWS and your using getElementsByTagName method in Powershell is make sure you use the @ array specifier In front of the method call or you wont be able to index into the collection and access the elements. Authentication in Powershell can be handled in two ways if you want to specify which credentials to use you can use a credential object with a line such as

$cdUsrCredentials = new-object System.Net.NetworkCredential("Administrator", "Evaluation1", "CONTOSO")

And then set the request object to use those credentials

$WDRequest.Credentials = $cdUsrCredentials

Or you can specify to use the credentials from the user context that is invoking the script by setting the request object like

$WDRequest.UseDefaultCredentials = $True

The Last thing to look at is the restriction element this allows you to specify which items you want to return. So if you where to use the FindItem method to list items in your inbox and you wanted to just return those items that where unread and only 24 hours old then you could use something like this in the restriction section of the SOAP message.


+ "<Restriction>" `
+ "<t:And>" `
+ "<t:IsEqualTo>" `
+ "<t:FieldURI FieldURI=`"message:IsRead`"/>"`
+ "<t:FieldURIOrConstant>" `
+ "<t:Constant Value=`"0`"/>" `
+ "</t:FieldURIOrConstant>" `
+ "</t:IsEqualTo>" `
+ "<t:IsGreaterThanOrEqualTo>" `
+ "<t:FieldURI FieldURI=`"item:DateTimeSent`"/>"`
+ "<t:FieldURIOrConstant>" `
+ "<t:Constant Value=`"" + $datetimetoquery.ToUniversalTime().AddDays(-1).ToString("yyyy-MM-ddThh:mm:ssZ")
+ "`"/>"`
+ "</t:FieldURIOrConstant>"`
+ "</t:IsGreaterThanOrEqualTo>"`
+ "</t:And>"`
+ "</Restriction>"`


Like Exchange 200x when you query the Exchange store with a Datetime range that datetime range should be converted to the ISO dateformat and also because Exchange stores date and times in UTC you should convert the date and time you want to query for into UTC before you use it (and also convert the returned times back into Local time so they make sense). Powershell lets you tap into the .NET time conversion functions which makes things a lot simpler then they where in VBS.

I’ve created 4 scripts all up both in VBS and Powershell the scripts do the following tasks

Sendews : Sends a message using EWS
Ureadews : Reads the UnreadCount property of the inbox
Shureadews : Shows the Time,From,Subject and size of all unread messages in the inbox
Shureadewstday Shows the Time,From,Subject and size of all unread messages in the inbox just for the past 24 hours

I’ve put a downloadable copy of the script here.

Friday, December 01, 2006

Exchange SMTP Log file DNS Test tool Powershell script

This powershell script is a combination of some of my past scripts (in particular the DNS util script) wrapped up in a nice little GUI with buttons to make it easy to use on a daily basis. What this script does is allows you to open a SMTP log file (or if you don’t want to read the whole log file just a certain number of lines from a file) and it will then parse that log file into a DataGrid on a Winform. You can then select a line in the datagrid and use one of the Buttons provided to perform different DNS test on that log file entry. The tests it can do are

Reverse DNS query on the Source Mail server IP Address (from the log file)

MX lookup of the parsed domain name from a RCPT or MAIL SMTP command

SPF lookup of the parsed domain name from a RCPT or MAIL SMTP command

A Record lookup of the parsed domain name from a RCPT or MAIL SMTP command

RBL lookup of Source Mail Server IP Address (Using SORB’s or any other RBL you like). To configure which RBL server to use you need to modify the following line in the RBLLookup() function.

$RBLService = "dnsbl.sorbs.net"

Helo Banner Check does a connection to the Mail server source IP address and reports on the banner. To do this it creates a connection on Port 25 of the source mail server IP address then reads the response and then issues a Quit to end the connection.

Whois Lookup of the Source Mail Server IP Address using whois.arin.net (or any other whois server you want to configure). This will only return results for IP’s and domains this whois server is authorative for or it will return a referral to the whois server you should be using.

I’ve created two versions of the script the second version does Geolocationing of the Source Mail server IP address and gives the option to resolve every IP address in the log file to a geographical location or just do single queries on the location of certain ip addresses. This is based on my previous post of Geolocationing Message tracking logs .The reason for creating two versions is that the second script is quire complex and blows out to the size to around 800 lines so I wanted to have a simple script for anyone who doesn’t care about geolocatiion. Personally I think it’s really useful if you’re trying to diagnose something with a SMTP log file.

The parsing section of the script uses the get-content commandlet to read the log file you select this commandlet allows you to pass in the number of lines you want it to read from a file or pass -1 and it will read everything. The one thing I wasn’t 100 % sure about is the file locking operation of this commandlet. If it does lock the file when its retrieving the content then this could be a bad thing if you where to open a live log file. If anyone knows the answer to this one please let me know I haven’t come across any issues thus far.

To use the script just run it from powershell and you should see it build and popup a Winfrom. Use the select file button which will popup a file browser to allow you to select the log file you want to use. To use the geolocation version please see my other post about where you need to obtain the download from the iptocountry.csv file and where to place it to make the script work correctly. If you’re using the Geolocation version and you want the ipaddress resolved to a county location make sure you select the Show Cnty check box.

I’ve put a download of this script here the whole script is a bit too large to post but here are some highlights.

function openLog{
$logTable.clear()
$exFileName = new-object System.Windows.Forms.openFileDialog
$exFileName.ShowDialog()
$fnFileNamelableBox.Text = $exFileName.FileName
$tcountline = -1
if ($rbVeiwAllOnlyRadioButton.Checked -eq $true){$tcountline = $lnLogfileLineNum.value}
get-content $exFileName.FileName -totalCount $tcountline | %{
$linarr = $_.split(" ")
$lfDate = ""
$lfTime = ""
$lfSourceIP = ""
$lfHostName = ""
$lfDestIP = ""
$lfSMTPVerb = ""
$lfCommandText = ""
if ($linarr[0].substring(0, 1) -ne "#"){
if ($linarr.Length -gt 0){$lfDate = $linarr[0]}
if ($linarr.Length -gt 1){$lfTime = $linarr[1]}
if ($linarr.Length -gt 2){$lfSourceIP= $linarr[2]}
if ($linarr.Length -gt 3){$lfHostName = $linarr[3]}
if ($linarr.Length -gt 6){$lfDestIP = $linarr[6]}
if ($linarr.Length -gt 8){$lfSMTPVerb = $linarr[8]}
if ($linarr.Length -gt 10){$lfCommandText = $linarr[10]}
$logTable.Rows.Add($lfDate,$lfTime,$lfSourceIP,$lfHostName,$lfDestIP,$lfSMTPVerb,$lfCommandText)
}
}

$dgDataGrid.DataSource = $logTable
}

Compile-Csharp $code
$form = new-object System.Windows.Forms.form
$form.Text = "SMTP Log Test Tool"
$Dataset = New-Object System.Data.DataSet
$logTable = New-Object System.Data.DataTable
$logTable.TableName = "SMTPLogs"
$logTable.Columns.Add("Date");
$logTable.Columns.Add("Time");
$logTable.Columns.Add("SourceIPAddress");
$logTable.Columns.Add("HostName");
$logTable.Columns.Add("DestIPAddress");
$logTable.Columns.Add("SMTPVerb");
$logTable.Columns.Add("CommandText");

# Content
$cmClickMenu = new-object System.Windows.Forms.ContextMenuStrip
$cmClickMenu.Items.add("test122")

# Add Open Log file Button

$olButton = new-object System.Windows.Forms.Button
$olButton.Location = new-object System.Drawing.Size(20,19)
$olButton.Size = new-object System.Drawing.Size(75,23)
$olButton.Text = "Select file"
$olButton.Add_Click({openLog})
$form.Controls.Add($olButton)

# Add Reverse DNS Lookup Button

$rdnsbutton = new-object System.Windows.Forms.Button
$rdnsbutton.Location = new-object System.Drawing.Size(500,19)
$rdnsbutton.Size = new-object System.Drawing.Size(85,23)
$rdnsbutton.Text = "Reverse DNS"
$rdnsbutton.Add_Click({ptrLookup})
$form.Controls.Add($rdnsbutton)

# Add MX Lookup Button

$mxbutton = new-object System.Windows.Forms.Button
$mxbutton.Location = new-object System.Drawing.Size(500,44)
$mxbutton.Size = new-object System.Drawing.Size(85,23)
$mxbutton.Text = "MX Lookup"
$mxbutton.Add_Click({mxLookup})
$form.Controls.Add($mxbutton)


# Add SPF Lookup Button

$spfbutton = new-object System.Windows.Forms.Button
$spfbutton.Location = new-object System.Drawing.Size(590,19)
$spfbutton.Size = new-object System.Drawing.Size(85,23)
$spfbutton.Text = "SPF Lookup"
$spfbutton.Add_Click({spfLookup})
$form.Controls.Add($spfbutton)

# Add A Lookup Button

$Abutton = new-object System.Windows.Forms.Button
$Abutton.Location = new-object System.Drawing.Size(590,44)
$Abutton.Size = new-object System.Drawing.Size(85,23)
$Abutton.Text = "A Rec Lookup"
$Abutton.Add_Click({ALookup})
$form.Controls.Add($Abutton)

# Add RBL Single Lookup Button

$RBLButton = new-object System.Windows.Forms.Button
$RBLButton.Location = new-object System.Drawing.Size(680,19)
$RBLButton.Size = new-object System.Drawing.Size(85,23)
$RBLButton.Text = "RBL Lookup"
$RBLButton.Add_Click({RBLLookup})
$form.Controls.Add($RBLButton)

# Add HELO chk Button

$HelochkButton = new-object System.Windows.Forms.Button
$HelochkButton.Location = new-object System.Drawing.Size(680,44)
$HelochkButton.Size = new-object System.Drawing.Size(85,23)
$HelochkButton.Text = "HELO Banner Check"
$HelochkButton.Add_Click({HELOChk})
$form.Controls.Add($HelochkButton)

# Add WhoIS chk Button

$Whois = new-object System.Windows.Forms.Button
$Whois.Location = new-object System.Drawing.Size(770,19)
$Whois.Size = new-object System.Drawing.Size(85,23)
$Whois.Text = "Whois Check"
$Whois.Add_Click({whoischk})
$form.Controls.Add($Whois)


# Add FileName Lable
$fnFileNamelableBox = new-object System.Windows.Forms.Label
$fnFileNamelableBox.Location = new-object System.Drawing.Size(110,25)
$fnFileNamelableBox.forecolor = "MenuHighlight"
$fnFileNamelableBox.size = new-object System.Drawing.Size(200,20)
$form.Controls.Add($fnFileNamelableBox)

# Add Veiw RadioButtons
$rbVeiwAllRadioButton = new-object System.Windows.Forms.RadioButton
$rbVeiwAllRadioButton.Location = new-object System.Drawing.Size(310,19)
$rbVeiwAllRadioButton.size = new-object System.Drawing.Size(69,17)
$rbVeiwAllRadioButton.Checked = $true
$rbVeiwAllRadioButton.Text = "View All"
$rbVeiwAllRadioButton.Add_Click({if ($rbVeiwAllRadioButton.Checked -eq $true){$lnLogfileLineNum.Enabled = $false}})
$form.Controls.Add($rbVeiwAllRadioButton)

$rbVeiwAllOnlyRadioButton = new-object System.Windows.Forms.RadioButton
$rbVeiwAllOnlyRadioButton.Location = new-object System.Drawing.Size(310,42)
$rbVeiwAllOnlyRadioButton.size = new-object System.Drawing.Size(89,17)
$rbVeiwAllOnlyRadioButton.Text = "View Only #"
$rbVeiwAllOnlyRadioButton.Add_Click({if ($rbVeiwAllOnlyRadioButton.Checked -eq $true){$lnLogfileLineNum.Enabled = $true}})
$form.Controls.Add($rbVeiwAllOnlyRadioButton)

# Add Numeric log line number
$lnLogfileLineNum = new-object System.Windows.Forms.numericUpDown
$lnLogfileLineNum.Location = new-object System.Drawing.Size(401,39)
$lnLogfileLineNum.Size = new-object System.Drawing.Size(69,20)
$lnLogfileLineNum.Enabled = $false
$lnLogfileLineNum.Maximum = 10000000000
$form.Controls.Add($lnLogfileLineNum)


# File setting Group Box

$OfGbox = new-object System.Windows.Forms.GroupBox
$OfGbox.Location = new-object System.Drawing.Size(12,0)
$OfGbox.Size = new-object System.Drawing.Size(464,75)
$OfGbox.Text = "Log File Settings"
$form.Controls.Add($OfGbox)

# Add DataGrid View

$dgDataGrid = new-object System.windows.forms.DataGrid
$dgDataGrid.AllowSorting = $True
#$dgDataGrid.Add_Click({MXlookup($logTable.DefaultView[$dgDataGrid.CurrentCell.RowNumber])})
$dgDataGrid.Location = new-object System.Drawing.Size(12,81)
$dgDataGrid.size = new-object System.Drawing.Size(1024,750)
$form.Controls.Add($dgDataGrid)

#
$form.topmost = $true
$form.Add_Shown({$form.Activate()})
$form.ShowDialog()

Friday, November 24, 2006

Reporting on the age of content in mailbox folders and across an Exchange Server via a script

Like many of us the age of the content in mailboxes is constantly aging the usefulness of aged content on expensive storage is an always a elemental question for those people who are grasping at trying to manage mail-store usage. If your still running Exchange 2000 then you maybe battling with a very finite amount of storage asking your service provider to please make it last another six months (why me!!). So if this is the case you need to arm your users (and yourself) with maybe a little more information about where the storage in your mailboxes is being used and where the growth curves have happened over the years they have been commanding the storage in their mailbox.

So enter this script, what this script does is scans every single item in a mailbox and looks at the creation date of this item. It then aggregates the size of the item and the item count into one of 3 categories 0-1 year old , 1-2 years old and over 2 years old. The script then produces a little html report that shows for each folder what this size of and total number of items are in this folder over these three time periods.

The time it takes to scan every item in a mailbox means that if you have large mailboxes this script will take a very long time to run. On a server with a large number of mailboxes it probably not practical. But it can be handy to get a farily detail report on a single mailbox and it can be good on a small mail server with not too many mailboxes if you have time to run it. The script itself uses WebDAV to search firstly the folder hierarchy and then search each individual folder. I’ve come up with two versions of the script the first Is a version that uses Form Based Authentication and just scans one configured mailbox. The other version takes the servername of a sever you want to run it against as a commandline parameter and then querys for all mailboxes that are visible in the GAL on this server. It connects to and scans every mailbox using the Admin virtual root which means the script can run with delegated Exchange admin rights.

The FBA version of the script creates a htm report for the user it’s configured to run against in the c:\temp directory of the server. The server version creates a separate report for each user it runs against and creates one report that shows the totals for every mailbox across each time period.

To use the FBA version you need to configure the authentication details to use for the synthetic form logon and the mailbox and server you want to connect to in the following line

snServername = "servername.com"
mnMailboxname = "mailboxname"
username = "username"
domain = "domain"
strpassword = "password"


To run the ageautserver.vbs script it takes one command line parameter which is the name of the server you want to run it against (eg cscript ageautserver.vbs servername). By default the script isn’t using SSL which may mean you need to adjust the following line if you are using SSL on the ExAdmin Directory

Eg change

falias = "http://" & servername & "/exadmin/admin/" & dpDefaultpolicy & "/mbx/"

to

falias = "https://" & servername & "/exadmin/admin/" & dpDefaultpolicy & "/mbx/"

The script is pretty verbose which could be cut down because it was such a long running script I kind of preferred that it at least told me what it was doing while it was taking so long.

I’ve put a downloadable version of the code here the server version looks like

on error resume next
Servername = wscript.arguments(0)
treport = "<table border=""1"" width=""100%"">" & vbcrlf
treport = treport & " <tr>" & vbcrlf
treport = treport & "<td align=""center"" bgcolor=""#000080""><b><font color=""#FFFFFF"">Mailbox
Name</font></b></td>" & vbcrlf
treport = treport & "<td align=""center"" bgcolor=""#000080"" colspan=""2""><b><font
color=""#FFFFFF"">Over 2 Years Old</font></b></td>" & vbcrlf
treport = treport & "<td align=""center"" bgcolor=""#000080"" colspan=""2""><b><font
color=""#FFFFFF"">1-2 Years Old</font></b></td>" & vbcrlf
treport = treport & "<td align=""center"" bgcolor=""#000080"" colspan=""2""><b><font
color=""#FFFFFF"">0-1 Years Old</font></b></td>" & vbcrlf
treport = treport & "</tr>" & vbcrlf
treport = treport & " <tr>" & vbcrlf
treport = treport & "<td align=""center"" bgcolor=""#000080""><b><font color=""#FFFFFF"">&nbsp;</font></b></td>"
& vbcrlf
treport = treport & "<td align=""center"" bgcolor=""#000080""><b><font color=""#FFFFFF"">#Messages</font></b></td>"
& vbcrlf
treport = treport & "<td align=""center"" bgcolor=""#000080""><b><font color=""#FFFFFF"">Size(MB)</font></b></td>"
& vbcrlf
treport = treport & "<td align=""center"" bgcolor=""#000080""><b><font color=""#FFFFFF"">#Messages</font></b></td>"
& vbcrlf
treport = treport & "<td align=""center"" bgcolor=""#000080""><b><font color=""#FFFFFF"">Size(MB)</font></b></td>"
& vbcrlf
treport = treport & "<td align=""center"" bgcolor=""#000080""><b><font color=""#FFFFFF"">#Messages</font></b></td>"
& vbcrlf
treport = treport & "<td align=""center"" bgcolor=""#000080""><b><font color=""#FFFFFF"">Size(MB)</font></b></td>"
& vbcrlf
treport = treport & "</tr>" & vbcrlf
set req = createobject("microsoft.xmlhttp")
set com = createobject("ADODB.Command")
set conn = createobject("ADODB.Connection")
Set iAdRootDSE = GetObject("LDAP://RootDSE")
strNameingContext = iAdRootDSE.Get("configurationNamingContext")
strDefaultNamingContext = iAdRootDSE.Get("defaultNamingContext")
Conn.Provider = "ADsDSOObject"
Conn.Open "ADs Provider"
polQuery = "<LDAP://" & strNameingContext & ">;(&(objectCategory=msExchRecipientPolicy)(cn=Default
Policy));distinguishedName,gatewayProxy;subtree"
svcQuery = "<LDAP://" & strNameingContext & ">;(&(objectCategory=msExchExchangeServer)(cn="
& Servername & "));cn,name,legacyExchangeDN;subtree"
Com.ActiveConnection = Conn
Com.CommandText = polQuery
Set plRs = Com.Execute
while not plRs.eof
for each adrobj in plrs.fields("gatewayProxy").value
if instr(adrobj,"SMTP:") then dpDefaultpolicy =
right(adrobj,(len(adrobj)-instr(adrobj,"@")))
next
plrs.movenext
wend
wscript.echo dpDefaultpolicy
Com.CommandText = svcQuery
Set Rs = Com.Execute
while not rs.eof
GALQueryFilter = "(&(&(&(& (mailnickname=*)(!msExchHideFromAddressLists=TRUE)(|
(&(objectCategory=person)(objectClass=user)(msExchHomeServerName=" &
rs.fields("legacyExchangeDN") & ")) )))))"
strQuery = "<LDAP://" & strDefaultNamingContext & ">;" & GALQueryFilter &
";displayname,mail,distinguishedName,mailnickname,proxyaddresses;subtree"
com.Properties("Page Size") = 100
Com.CommandText = strQuery
Set Rs1 = Com.Execute
while not Rs1.eof
report = "<table border=""1"" width=""100%"">" & vbcrlf
report = report & " <tr>" & vbcrlf
report = report & "<td align=""center"" bgcolor=""#000080""><b><font
color=""#FFFFFF"">Folder Name</font></b></td>" & vbcrlf
report = report & "<td align=""center"" bgcolor=""#000080""
colspan=""2""><b><font color=""#FFFFFF"">Over 2 Years Old</font></b></td>" &
vbcrlf
report = report & "<td align=""center"" bgcolor=""#000080""
colspan=""2""><b><font color=""#FFFFFF"">1-2 Years Old</font></b></td>" & vbcrlf
report = report & "<td align=""center"" bgcolor=""#000080""
colspan=""2""><b><font color=""#FFFFFF"">0-1 Years Old</font></b></td>" & vbcrlf
report = report & "</tr>" & vbcrlf
report = report & " <tr>" & vbcrlf
report = report & "<td align=""center"" bgcolor=""#000080""><b><font
color=""#FFFFFF"">&nbsp;</font></b></td>" & vbcrlf
report = report & "<td align=""center"" bgcolor=""#000080""><b><font
color=""#FFFFFF"">#Messages</font></b></td>" & vbcrlf
report = report & "<td align=""center"" bgcolor=""#000080""><b><font
color=""#FFFFFF"">Size(MB)</font></b></td>" & vbcrlf
report = report & "<td align=""center"" bgcolor=""#000080""><b><font
color=""#FFFFFF"">#Messages</font></b></td>" & vbcrlf
report = report & "<td align=""center"" bgcolor=""#000080""><b><font
color=""#FFFFFF"">Size(MB)</font></b></td>" & vbcrlf
report = report & "<td align=""center"" bgcolor=""#000080""><b><font
color=""#FFFFFF"">#Messages</font></b></td>" & vbcrlf
report = report & "<td align=""center"" bgcolor=""#000080""><b><font
color=""#FFFFFF"">Size(MB)</font></b></td>" & vbcrlf
report = report & "</tr>" & vbcrlf
falias = "http://" & servername & "/exadmin/admin/" & dpDefaultpolicy & "/mbx/"
for each paddress in rs1.fields("proxyaddresses").value
if instr(paddress,"SMTP:") then falias = falias & replace(paddress,"SMTP:","")

next
ReDim tresarray(1,6)
wscript.echo falias
call RecurseFolder(falias)
report = report & "</table>" & vbcrlf
Set fso = CreateObject("Scripting.FileSystemObject")
set wfile = fso.opentextfile("c:\temp\" & rs1.fields("mail").value &
".htm",2,true)
wfile.write report
wfile.close
set wfile = nothing
treport = treport & "<tr>" & vbcrlf
treport = treport & "<td align=""center"">" & rs1.fields("mail").value &
"&nbsp;</td>" & vbcrlf
treport = treport & "<td align=""center"">" & tresarray(0,1) & "&nbsp;</td>" &
vbcrlf
treport = treport & "<td align=""center"">" &
FormatNumber(tresarray(1,1)/1024/1024,2) & "&nbsp;</td>" & vbcrlf
treport = treport & "<td align=""center"">" & tresarray(0,2) & "&nbsp;</td>" &
vbcrlf
treport = treport & "<td align=""center"">" &
FormatNumber(tresarray(1,2)/1024/1024,2) & "&nbsp;</td>" & vbcrlf
treport = treport & "<td align=""center"">" & tresarray(0,3) & "&nbsp;</td>" &
vbcrlf
treport = treport & "<td align=""center"">" &
FormatNumber(tresarray(1,3)/1024/1024,2) & "&nbsp;</td>" & vbcrlf
treport = treport & "</tr>" & vbcrlf
rs1.movenext
wend
rs.movenext
wend
rs.close
set conn = nothing
set com = nothing
treport = treport & "</table>" & vbcrlf
Set fso = CreateObject("Scripting.FileSystemObject")
set wfile = fso.opentextfile("c:\temp\mailboxage.htm",2,true)
wfile.write treport
wfile.close
set wfile = nothing
set fso = nothing

Public Sub RecurseFolder(sUrl)

req.open "SEARCH", sUrl, False, "", ""
sQuery = "<?xml version=""1.0""?>"
sQuery = sQuery & "<g:searchrequest xmlns:g=""DAV:"">"
sQuery = sQuery & "<g:sql>SELECT ""http://schemas.microsoft.com/"
sQuery = sQuery & "mapi/proptag/x0e080003"", ""DAV:hassubs"" FROM SCOPE "
sQuery = sQuery & "('SHALLOW TRAVERSAL OF """ & sUrl & """') "
sQuery = sQuery & "WHERE ""DAV:isfolder"" = true and ""DAV:ishidden"" = false
and ""http://schemas.microsoft.com/mapi/proptag/x36010003"" = 1"
sQuery = sQuery & "</g:sql>"
sQuery = sQuery & "</g:searchrequest>"
req.setRequestHeader "Content-Type", "text/xml"
req.setRequestHeader "Translate", "f"
req.setRequestHeader "Depth", "0"
req.setRequestHeader "Content-Length", "" & Len(sQuery)
req.send sQuery
Set oXMLDoc = req.responseXML
Set oXMLSizeNodes = oXMLDoc.getElementsByTagName("d:x0e080003")
Set oXMLHREFNodes = oXMLDoc.getElementsByTagName("a:href")
Set oXMLHasSubsNodes = oXMLDoc.getElementsByTagName("a:hassubs")
For i = 0 to oXMLSizeNodes.length - 1
call procfolder(oXMLHREFNodes.Item(i).nodeTypedValue,sUrl)
wscript.echo oXMLHREFNodes.Item(i).nodeTypedValue
If oXMLHasSubsNodes.Item(i).nodeTypedValue = True Then
call RecurseFolder(oXMLHREFNodes.Item(i).nodeTypedValue)
End If
Next
End Sub

sub procfolder(strURL,pfname)
wscript.echo strURL
ReDim resarray(1,6)
strQuery = "<?xml version=""1.0""?><D:searchrequest xmlns:D = ""DAV:""
xmlns:b=""urn:uuid:c2f41010-65b3-11d1-a29f-00aa00c14882/"">"
strQuery = strQuery & "<D:sql>SELECT ""DAV:displayname"",
""urn:schemas:httpmail:subject"", "
strQuery = strQuery & """DAV:creationdate"", ""DAV:getcontentlength"", "
strQuery = strQuery & """urn:schemas:httpmail:fromemail"""
strQuery = strQuery & " FROM scope('shallow traversal of """
strQuery = strQuery & strURL & """') Where ""DAV:ishidden"" = False AND
""DAV:isfolder"" = False</D:sql></D:searchrequest>"
req.open "SEARCH", strURL, false
req.setrequestheader "Content-Type", "text/xml"
req.setRequestHeader "Translate","f"
req.send strQuery
If req.status >= 500 Then
ElseIf req.status = 207 Then
set oResponseDoc = req.responseXML
set oNodeList = oResponseDoc.getElementsByTagName("a:displayname")
set oNodeList1 = oResponseDoc.getElementsByTagName("a:href")
set oSize = oResponseDoc.getElementsByTagName("a:getcontentlength")
set odatereceived = oResponseDoc.getElementsByTagName("a:creationdate")
For i = 0 To (oNodeList.length -1)
set oNode = oNodeList.nextNode
set oNode1 = oNodeList1.nextNode
set oNode2 = oSize.nextNode
set oNode3 = odatereceived.nextNode
wscript.echo oNode3.text
If CDate(DateSerial(mid(oNode3.text,1,4),
mid(oNode3.text,6,2),mid(oNode3.text,9,2))) < dateadd("m",-24,now()) Then
resarray(0,1) = resarray(0,1) + 1
resarray(1,1) = resarray(1,1) + Int(oNode2.text)
End if
If CDate(DateSerial(mid(oNode3.text,1,4),
mid(oNode3.text,6,2),mid(oNode3.text,9,2))) > dateadd("m",-24,now()) And
CDate(DateSerial(mid(oNode3.text,1,4),
mid(oNode3.text,6,2),mid(oNode3.text,9,2))) < dateadd("m",-12,now()) Then
resarray(0,2) = resarray(0,2) + 1
resarray(1,2) = resarray(1,2) + Int(oNode2.text)
End if
If CDate(DateSerial(mid(oNode3.text,1,4),
mid(oNode3.text,6,2),mid(oNode3.text,9,2))) > dateadd("m",-12,now()) And
CDate(DateSerial(mid(oNode3.text,1,4),
mid(oNode3.text,6,2),mid(oNode3.text,9,2))) < now() Then
resarray(0,3) = resarray(0,3) + 1
resarray(1,3) = resarray(1,3) + Int(oNode2.text)
End if
Next
Else
End If
tresarray(0,1) = tresarray(0,1) + resarray(0,1)
tresarray(1,1) = tresarray(1,1) + resarray(1,1)
tresarray(0,2) = tresarray(0,2) + resarray(0,2)
tresarray(1,2) = tresarray(1,2) + resarray(1,2)
tresarray(0,3) = tresarray(0,3) + resarray(0,3)
tresarray(1,3) = tresarray(1,3) + resarray(1,3)
report = report & "<tr>" & vbcrlf
report = report & "<td align=""center"">" & unescape(Replace(strURL,falias,""))
& "&nbsp;</td>" & vbcrlf
report = report & "<td align=""center"">" & resarray(0,1) & "&nbsp;</td>" &
vbcrlf
report = report & "<td align=""center"">" &
FormatNumber(resarray(1,1)/1024/1024,2) & "&nbsp;</td>" & vbcrlf
report = report & "<td align=""center"">" & resarray(0,2) & "&nbsp;</td>" &
vbcrlf
report = report & "<td align=""center"">" &
FormatNumber(resarray(1,2)/1024/1024,2) & "&nbsp;</td>" & vbcrlf
report = report & "<td align=""center"">" & resarray(0,3) & "&nbsp;</td>" &
vbcrlf
report = report & "<td align=""center"">" &
FormatNumber(resarray(1,3)/1024/1024,2) & "&nbsp;</td>" & vbcrlf
report = report & "</tr>" & vbcrlf
end sub
 

Friday, November 10, 2006

Creating a Server Side rule to move suspect messages with inline gifs to another folder using Rule.dll

Recently there has been a large increase in the amount of image based spam being sent out and also an increase in the amount of SPAM making it though SPAM filters because of the methods being employed such as randomizing images and also modifying images so they are difficult for any OCR based spam filter to decode the text. Stopping this at Edge device is the ultimate goal of any decent Sys Admin but this continuing war between spammers and those that create the software that can fight spam (and the accountants that stop us buying said software) means that we have to deal with the stuff that makes it though any defenses we might have and the inevitable complaints from the end users that stem from this. I decided to see if I could make a rule that at least could move any of these image based emails into a separate folder mainly for my postmaster account which tends to get hammered.

Analyzing the basic image based spam message it consists of one inline gif image and a bunch of text. So to write a server rule with rule dll I needed to come up with a rule that would act on two basic logic constraints. Using the PR_TRANSPORT_MESSAGE_HEADERS property which stores the Entire header of message which includes information about the bodyparts of a message eg if it’s a html body part or a text bodypart or an attachment etc. So the rule looks at the message header and the first logic constraint looks to see if this message has any body parts that has a content type of image/gif then next logic condition which is a Bitwise NOT looks to see if this message has any body parts with have a content disposition set to attachment; which means they will actually be an attached file vs inline attachments. The two logic constraints get tied together via a Bitwise AND logic condition (eg the first condition is positive if there is a bodypart of image/gif and the second is positive is there are no attached files). The end result is a rule that will fire on any message that are received with one inline gif image.

BUT!!!! Before you go tearing off and wanting to look at using this on a users mailbox you should first consider this rule may generate a lot of false positives. For instance if someone has an inline gif in there signature this would cause the rule to fire. Some HTML based newsletters will also cause this to fire. For a postmaster account it seems to work pretty well it also seems to even be able to move image base spam messages that are located attached to NDR notification messages because the headers from the bounced message are still included in the NDR’s PR_TRANSPORT_MESSAGE_HEADERS prop.

The Code is setup to move these images into the Junk Email Folder you may however want to look at creating another folder like Potential Image Spam to hold messages that are moved by the rule.

This code requires the Rule.dll to be registered on the machine you’re running the script on if you have never used rule.dll you should read up about the restrictions around rules created this way.

I’ve put a download of this code here the code itself looks like.

const SUBSTRING = 1 ' Substring
const IGNORECASE = &H00010000 ' Ignore case
Const ACTION_MOVE = 1
const B_NEZ = 2
const L_OR = 3
Const REL_EQ = 7
const MSG_ATTACH = 2
Const PR_Transport_Headers = &H007D001E

servername = "servername"
mailboxname = "user"

Set objSession = CreateObject("MAPI.Session")

objSession.Logon "","",false,true,true,true,servername & vbLF & mailboxname
Set objRules = CreateObject("MSExchange.Rules")
objRules.Folder = objSession.Inbox
Set objInbox = objSession.Inbox

Set CdoInfoStore = objSession.GetInfoStore
Set CdoFolderRoot = CdoInfoStore.RootFolder
Set CdoFolders = CdoFolderRoot.Folders

bFound = False
Set CdoFolder = CdoFolders.GetFirst
Do While (Not bFound) And Not (CdoFolder Is Nothing)
If CdoFolder.Name = "Junk E-mail" Then
bFound = True
Else
Set CdoFolder = CdoFolders.GetNext
End If
Loop
Set ActionFolder = CdoFolder


Set importPropVal = CreateObject("MSExchange.PropertyValue")
importPropVal.Tag = PR_Transport_Headers
importPropVal.Value = " attachment;"

Set importPropCond = CreateObject("MSExchange.ContentCondition")
importPropCond.PropertyType = PR_Transport_Headers
importPropCond.Operator = SUBSTRING + IGNORECASE
importPropCond.Value = importPropVal


Set importPropVal1 = CreateObject("MSExchange.PropertyValue")
importPropVal1.Tag = PR_Transport_Headers
importPropVal1.Value = "image/gif"

Set importPropCond1 = CreateObject("MSExchange.ContentCondition")
importPropCond1.PropertyType = PR_Transport_Headers
importPropCond1.Operator = SUBSTRING + IGNORECASE
importPropCond1.Value = importPropVal1


Set logPropCond1 = CreateObject("MSExchange.LogicalCondition")
logPropCond1.Operator = 3
logPropCond1.Add importPropCond

Set logPropCond = CreateObject("MSExchange.LogicalCondition")
logPropCond.Operator = 1
logPropCond.Add importPropCond1
logPropCond.Add logPropCond1


' Create action
Set objAction = CreateObject("MSExchange.Action")
objAction.ActionType = ACTION_MOVE
objAction.Arg = ActionFolder

' Create new rule
Set objRule = CreateObject("MSExchange.Rule")
objRule.Name = "Gif Image Move Rule"

' Add action and assign condition
objRule.Actions.Add , objAction
objRule.Condition = logPropCond

' Add rule and update
objRules.Add , objRule
objRules.Update

' Log off and cleanup
objSession.Logoff

Set objRules = Nothing
Set objSession = Nothing
Set importProp = Nothing
Set importPropVal = Nothing
Set objAction = Nothing
Set objRule = Nothing

Thursday, November 02, 2006

Geolocationing Exchange Message Tracking with Powershell (Exchange 2000/2003) (Seeing what countries your messages/Spam are coming from)

Geolocation as wikipedia aptly puts is the real-world geographic location of a computer based on its IP address. Many people use this for web site statistics for displaying where users are coming from. This information can also be very useful to actually tell you where your mail (and SPAM) is coming from. There are a lot of people out there selling and open sourcing geoip services, for instance awstats uses one of the open source variants to get the country information it uses in its report. Following some of these trails I found references to the IpToCountry.csv file which is a list of IP ranges per country that a few people maintain and provide (in slightly differing formats) for free as a download. The most up to date version in the one provided by Webnet77 which is the one I choose to use and is what this script is based on. Using this CSV file in its native format while possible with a little manipulation using the Microsoft text driver I found was very slow when trying to resolve a large number of entries from the message tracking. So for this script I decided to go with creating a mdb database using the ADO and ADO.NET and importing the csv file into to the database.

The script itself is a cut down version of my BYO message tracker the main section of script remains the same. It produces a .NET form that allows you to enter a server-name and a time range for the message tracking logs you want to query. (There was a lot of more functionality in the BYO message tracker that I didn’t port over into this script).It still uses WMI behind the scenes to query the Exchange Message tracking logs. To manage the Geolocation process a number of functions and routines have been added. This script itself is self maintaining meaning that as long as you’re using it on a machine that has ADO on it the script will create the database it’s going to use itself using ADOX. And it also includes a maintenance routine that checks the import file to see if its been updated based on the file modified time of the iptocountry.csv file. If you have downloaded a new csv file when the script is run if will detect the new version and delete the old database and create a new one based on the new file. To insert the records into the database itself the System.Data.ole class is used as well as some powershell cmdlets to get the content of the IptoCountry file one line at a time and then parse it into the database. After the records are inserted into the database because this process tends to make the file size bloat a bit I used the ADO JRO objects to compress the mdb file back to smallest possible size which helps on the performance side of thing as well. To help out further with query performances two indexes are added to both the IPFrom and IPTo columns in the database.

Once the script had created and populated the databases you’ll have a database of numerical ranges which are representations of the IP Blocks that have been allocated to particular countries/IP’s . To find out where an IP address belongs to it’s a matter of first converting it into its numeric representation and then doing a SQL query to find the row in the database that this numeric representation falls within. Because you’ll find mail servers in your message tracking logs tend to repeat a lot to save querying the database for every occurrence of the same server I used a hash table the tracks the C class of a resolved IP and if the script detects it querying the same c class it will use the hashtable value instead of making a another database call. This speeds things up considerably when you making future queries because the database doesn’t need to be queried.

The WMI side of this script does differ from the BYO message tracker in that the message ID that is uses is different. Because the messageID I was using in the other script doesn’t have access to the sending servers IP address I had to use another Message Tracking ID. The one that I found worked the best was 1019 which is the first event ID in the tracking log see this kb. To cater for the possibility of multiple instances of a 1019 events for the same message a hash-table is used to prevent the same message ID being counted twice. The Email Type box allows you to select between what type of email this script will report on. Normal mail is as it indicates normal messages that are sent and received via the server. The Spam email type is messages that the IMF has taken gateway action on (either delete or achieve). When you select the Spam email type the type of tracking ID the WMI side of the script queries for changes from 1019 to 1040.

The extras side of the script allows the aggregation of the query results by country so you can see by country how much email is being sent and received. The side by side aggregation creates the same aggregation but it changes the WMI query to include both the 1019 and 1040 entry types and then creates a side by side aggregation so you can see by country how much normal email is sent vs. how much IMF detected Spam per country (I think this is very cool).

To use the script you need to first download the Iptocountry.csv file from Webnet77 . At the moment the script is set to read the file from the c:\temp directory and also create the database in the c:\temp directory this is controlled by the following lines.

$dbfilepath = "c:\temp\iptocountry.mdb"
$tmpdbfilepath = "c:\temp\iptocountrycomp.mdb"
$importfilepath = "c:\temp\iptocountry.csv"

The $tmpdbfilepath is used only during the compression section of the script.

I’ve put a downloadable copy of the script here. This script itself is a bit long to post verbatim heres what some of the mdb creation and maintenance routines.


function createdb{
$aoADOXDb = new-object -com ADOX.Catalog
$aoADOXDb.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + $dbfilepath)
$atADOXTable = new-object -com ADOX.Table
$atADOXTable.Name = "IPTOCOUNTRY"
$atADOXTable.Columns.Append("IPFrom", $adDouble)
$atADOXTable.Columns.Append("IPTo", $adDouble)
$atADOXTable.Columns.Append("Registry", $adVarWChar, 25)
$atADOXTable.Columns.Append("ASSIGNED", $adDouble)
$atADOXTable.Columns.Append("CTRY", $adVarWChar, 2)
$atADOXTable.Columns.Append("CNTRY", $adVarWChar, 3)
$atADOXTable.Columns.Append("COUNTRY", $adVarWChar, 100)
$atindex = new-object -com ADOX.index
$atindex.Name = "idxIPFrom"
$atindex.Columns.Append("IPFrom")
$atindex.Unique = $True
$atADOXTable.Indexes.Append($atindex)

$atindex1 = new-object -com ADOX.index
$atindex1.Name = "idxIPTo"
$atindex1.Columns.Append("IPTo")
$atindex1.Unique = $True
$atADOXTable.Indexes.Append($atindex1)

$aoADOXDb.Tables.Append($atADOXTable)
$atADOXTablever = new-object -com ADOX.Table
$atADOXTablever.Name = "Version"
$atADOXTablever.Columns.Append("FileModifiedDate", $adVarWChar, 255)
$aoADOXDb.Tables.Append($atADOXTablever)
# Cleanup
$aoADOXDb.ActiveConnection.close()

[System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$atindex)
[System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$atindex1)
[System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$atADOXTable)
[System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$atADOXTablever)
[System.Runtime.InteropServices.Marshal]::ReleaseComObject([System.__ComObject]$aoADOXDb)
[system.gc]::Collect()
$atindex = $null
$atindex1 = $null
$atADOXTable = $null
$atADOXTablever = $null
$aoADOXDb = $null

}

function populatedb{

$file = get-item $importfilepath
$ocOdbcConnection.Open()
$dcOdBcommand = new-object System.Data.OleDb.OleDbCommand
$dcOdBcommand.connection = $ocOdbcConnection
$dcOdBcommand.commandtext = "Insert into Version values('" + $file.lastwritetime + "')"
$dcOdBcommand.ExecuteNonQuery()
$rcRowCount = 0
"Filling Database this may take a few minutes"
get-content $importfilepath | %{
$linarr = $_.replace("'","``").split(",")
if ($linarr[0].indexofany("#") -band $linarr.length -gt 1){
$stSQLStatement = "Insert into IPTOCOUNTRY values('" + $linarr[0].replace("`"","") + "','" `
+ $linarr[1].replace("`"","") + "','"+ $linarr[2].replace("`"","") + "','"+ `
$linarr[3].replace("`"","") + "','" + $linarr[4].replace("`"","") + "','" + `
$linarr[5].replace("`"","") + "','" + $linarr[6].replace("`"","") + "')"
$dcOdBcommand.commandtext = $stSQLStatement
$inResult = $dcOdBcommand.ExecuteNonQuery()
if ($inResult -ne 1){$inResult}
}
$rcRowCount = $rcRowCount + 1
if ($rcRowCount -eq 10000){
$rcRowCount = 0
"10000 Rows Inserted"
}
}
"Fill completed"
$dcOdBcommand = $null
$ocOdbcConnection.Close()
$ocOdbcConnection = $null
$jrJroobj = new-object -com JRO.JetEngine
$dbSourceDB = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + $dbfilepath
$dbDestinationDB = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + $tmpdbfilepath
$jrJroobj.CompactDatabase($dbSourceDB, $dbDestinationDB)
remove-item $dbfilepath
copy-item $tmpdbfilepath $dbfilepath
remove-item $tmpdbfilepath
}

function checkfileversion{
$ocOdbcConnection.Open()
$dcOdBcommand = new-object System.Data.OleDb.OleDbCommand
$dcOdBcommand.connection = $ocOdbcConnection
$dcOdBcommand.commandtext = "select FileModifiedDate from Version"
$drDBreader = $dcOdBcommand.ExecuteReader()
$retval = 0
while ($drDBreader.read()){
if ($drDBreader[0] -ne $file.lastwritetime){$retval = 1}
else{"DB up to date"}
}
$dcOdBcommand = $null
$ocOdbcConnection.Close()
return $retval
}

Thursday, October 26, 2006

Showing which mailboxes have been enabled with Outlook Direct Booking via a script

Outlook direct booking is one of the many ways in which you can handle resource mailboxes in Exchange for a full list of other methods have a look at slipstick. Knowing which mailboxes have been enabled in your Exchange Organization could be a little tricky to keep a track of if you have a large number of mailboxes. One method of finding what mailboxes are using Direct booking is by using the freebusy data on a server. How Free-Busy data works and is stored is documented on Technet and this post on the Exchange Team Blog . The Free Busy data also comes into to play with Outlook Direct booking. When a user configures direct booking via the 3 check boxes under resource scheduling in Outlook – Calendar options there are 3 Mapi properties that get set on the local freebusy object in a mailbox. (Note this is not the only thing that happens you do enable direct booking permissions are also modified on the calendar and freebusy object in the resource mailbox)

Automatically accept meeting and process cancellations relates to 0x686D000B
Automatically decline conflicting meeting requests relates to 0x686F000B
Automatically decline recurring meeting requests relates to 0x686E000B

These properties are also published on the users freebusy object located in the SCHEDULE+ FREE BUSY public folder. When a user creates an appointment and selects a mailbox as a resource Outlook queries the public free busy object for that mailbox to determine if Direct Booking is enabled. So basically what you can do in a script is to walk the objects in the public free busy folder and check for these properties on each of the objects. If you read the documentation in the links I provided above you will see that Freebusy data is stored per Administrative Group in Exchange. The script I wrote uses CDO 1.2 to connect to a mailbox (its not really that important which mailbox as long as it is publishing freebusy data). In then uses the PR_FREEBUSY_ENTRYIDS 0x36E41102 mapi property which is a multivalued binary property stored in the root of a mailbox that holds the EntryID’s of various freebusy objects for that mailbox. The second ID in the prop holds the EntryID for the local freebusy object the third holds the EntryID for the public freebusy object for this mailbox. I’ve made use of this third one by using it to connect to the public freebusy object of the mailbox the script is connecting to and then just moving up to the parent folder object which will give the script the ability to walk all the objects in the public freebusy folder by using the message collection of this parent folder. To finish with the script generates a small html report called DOBreport.htm is c:\temp with a list of what mailboxes have direct booking enabled and what resource scheduling options have been selected. If you have multiple Administrative groups in your Exchange Organization you will need to connect to a mailbox in each exchange admin group to cover all mailboxes. Before running the script you need to configure the following two variables with the servername and mailbox of a mailbox to use.

MailServer = "servername"
Mailbox = "mailbox"

I’ve put a downloadable copy of the script here the script itself look like

MailServer = "mailbox"
Mailbox = "user"
Const PR_FREEBUSY_ENTRYIDS = &H36E41102
Const PR_PARENT_ENTRYID = &H0E090102
Const PR_RECALCULATE_FREEBUSY = &H10F2000B
Const PR_FREEBUSY_DATA = &H686C0102
report = "<table border=""1"" width=""100%"">" & vbcrlf
report = report & " <tr>" & vbcrlf
report = report & "<td align=""center"" bgcolor=""#000080""><b><font color=""#FFFFFF"">Mailbox-Name</font></b></td>"
& vbcrlf
report = report & "<td align=""center"" bgcolor=""#000080""><b><font color=""#FFFFFF"">Auto
Process Meetings</font></b></td>" & vbcrlf
report = report & "<td align=""center"" bgcolor=""#000080""><b><font color=""#FFFFFF"">Auto
Decline conflicts</font></b></td>" & vbcrlf
report = report & "<td align=""center"" bgcolor=""#000080""><b><font color=""#FFFFFF"">Auto
Decline recurring</font></b></td>" & vbcrlf
report = report & "</tr>" & vbcrlf

set objSession = CreateObject("MAPI.Session")
strProfile = MailServer & vbLf & Mailbox
objSession.Logon "",,, False,, True, strProfile
Set objInfoStores = objSession.InfoStores
set objInfoStore = objSession.GetInfoStore
Set objpubstore = objSession.InfoStores("Public Folders")
Set objRoot = objInfoStore.RootFolder

set non_ipm_rootfolder =
objSession.getfolder(objroot.fields.item(PR_PARENT_ENTRYID),objInfoStore.id)
fbids = non_ipm_rootfolder.fields.item(PR_FREEBUSY_ENTRYIDS).value
set publicfbusy = objSession.getmessage(fbids(2),objpubstore.id)
set publicfbusyfold =
objSession.getfolder(publicfbusy.fields.item(PR_PARENT_ENTRYID),objpubstore.id)
on error resume next
for each fbmess in publicfbusyfold.messages
wscript.echo fbmess.subject
wscript.echo "Automatically accept meeting and process cancellations : " &
fbmess.fields.item(&H686D000B)
wscript.echo "Automatically decline conflicting meeting requests : " &
fbmess.fields.item(&H686F000B)
wscript.echo "Automatically decline recurring meeting requests : " &
fbmess.fields.item(&H686E000B)
wscript.echo
if err.number <> 0 then
err.clear
else
if fbmess.fields.item(&H686D000B).value = true then
report = report & "<tr>" & vbcrlf
report = report & "<td align=""center"">" & fbmess.subject & "&nbsp;</td>" &
vbcrlf
report = report & "<td align=""center"">" & fbmess.fields.item(&H686D000B) &
"&nbsp;</td>" & vbcrlf
report = report & "<td align=""center"">" & fbmess.fields.item(&H686F000B) &
"&nbsp;</td>" & vbcrlf
report = report & "<td align=""center"">" & fbmess.fields.item(&H686E000B) &
"&nbsp;</td>" & vbcrlf
report = report & "</tr>" & vbcrlf
end if
end if
next
report = report & "</table>" & vbcrlf
Set fso = CreateObject("Scripting.FileSystemObject")
set wfile = fso.opentextfile("c:\temp\DOBreport.htm",2,true)
wfile.write report
wfile.close
set wfile = nothing
set fso = nothing

Friday, October 20, 2006

Creating a Domain based auto response rule using rule.dll

Somebody asked last week about creating a personalized auto response message based on the sender domain. While this would be pretty easy to do with a normal store based event sink another option you can use is to create a server side rule using the rule.dll. The script to do this is pretty basic there are numerous samples that show how to create a rule that will process mail based on a string in the subject field. Well all you really need to do is take that same logic and apply it to the sender address instead. So you basically end up with a rule that does a substring on the sender address looking for in this case a particular email domain and then use the Reply action which creates a normal automatic reply email using the text you configure.

To use the script just configure the following line with the domain you want to use

importPropVal.Value = "@domain.com”

And this line with the text to respond with

objReplyMsg.Text = "Im Sorry this Mailbox isn't currently maned for after hour enquires please contact 2223-2222-222"

I’ve put a download copy of the script here the script itself looks like.

const SUBSTRING = 1 ' Substring
const IGNORECASE = &H00010000 ' Ignore case
const PR_SENDER_EMAIL_ADDRESS = &H0C1F001E
Const ACTION_REPLY = 4


servername = "servername"
mailboxname = "mailbox"

Set objSession = CreateObject("MAPI.Session")

objSession.Logon "","",false,true,true,true,servername & vbLF & mailboxname
Set objRules = CreateObject("MSExchange.Rules")
objRules.Folder = objSession.Inbox
Set objInbox = objSession.Inbox

Set CdoInfoStore = objSession.GetInfoStore
Set CdoFolderRoot = CdoInfoStore.RootFolder
Set CdoFolders = CdoFolderRoot.Folders

Set importPropVal = CreateObject("MSExchange.PropertyValue")
importPropVal.Tag = PR_SENDER_EMAIL_ADDRESS
importPropVal.Value = "@domain.com”

Set importPropCond = CreateObject("MSExchange.ContentCondition")
importPropCond.PropertyType = PR_SENDER_EMAIL_ADDRESS
importPropCond.Operator = SUBSTRING + IGNORECASE
importPropCond.Value = importPropVal

' Create reply message and store in HiddenMessages collection.
Set objReplyMsg = objInbox.HiddenMessages.Add

' Set reply message properties.
objReplyMsg.Type = "IPM.Note.Rules.ReplyTemplate.Microsoft"
objReplyMsg.Text = "Im Sorry this Mailbox isn't currently maned for after hour enquires please contact 333-333-33"
objReplyMsg.Update


' Create action
Set objAction = CreateObject("MSExchange.Action")
objAction.ActionType = ACTION_REPLY
objAction.Arg = Array(objReplyMsg.ID,objReplyMsg.FolderID)

' Create new rule
Set objRule = CreateObject("MSExchange.Rule")
objRule.Name = "Domain Reply Rule"

' Add action and assign condition
objRule.Actions.Add , objAction
objRule.Condition = importPropCond

' Add rule and update
objRules.Add , objRule
objRules.Update

' Log off and cleanup
objSession.Logoff

Set objRules = Nothing
Set objSession = Nothing
Set importProp = Nothing
Set importPropVal = Nothing
Set objAction = Nothing
Set objRule = Nothing

Thursday, October 12, 2006

Counting the number and type of Members in a Distribution list / Group via a script

A couple of months back I posted this script that would detect any empty distribution groups and give you the chance to delete them. Another useful thing to do now and again when your examining distribution lists in your Exchange organization is to look at how many members each group has and also what type of objects are members of certain lists. In Exchange apart from just user mailboxes you may have contacts (both internal and external), nested groups, Query based distribution lists or even public folders that are members of a distribution list. So its possible that some lists may have collected unwanted baggage in the normal course of events.

So what I’ve done is come up with 3 different scripts for counting members based on the type of member of each distribution list and then generating a little html report to show the number of each type of object within each distribution list. The first script just does a basic count of the members of each list the second expands any nested groups so it comes up with the true number of members of a list (e.g. if you send a mail to this list how many mailboxes a mail will be delivered to etc). The third script is a script that looks at all the Query based distribution lists (where the first two look just at groups) and expands each list.

The script is broken up into a multitude of ADSI queries the first query that is done is for the purpose of calculating whether contacts that are enumerated by the script have a external or internal target address. To differentiated between internal and external all the domains that are listed in each of the recipient policies are enumerated and then added to a scripting dictionary. The dictionary can then be easily checked to see if the domain of the target email address for the contact is internal or external.

The next query then enumerates all groups that are mail enabled and goes through each group one at a time and looks though the members collection of each group looking at the email address and type of each member. Separate variables track the number of each of the type of objects and if the type of object is a group or dynamic distribution list then the group is expanded and each member of that group is also checked. To guard against circular references a separate scripting dictionary object is maintained to ensure that each object is checked and recorded only once per iteration. A separate sub exists to handle the expansion of query based distribution lists by reading the filter AD attributes and then doing a separate ADSI query baaed on these values. Finally there is some code that then builds the result into a html table to display to the users. The report is named Groupreport.htm and is stored in c:\temp directory.

I’ve put a downloadable copy of the three scripts here basically the three scripts contained in the download are

gnumbs.vbs Only counts member of the current list doesn’t process nested lists

gnumbsnested.vbs Counts all member of a groups including those in nested groups and Query based DL’s

gnumbsqbdl.vbs Displays total number of members for Querybased Distribution lists

the nested group script looks like

set conn = createobject("ADODB.Connection")
set com = createobject("ADODB.Command")
Set iAdRootDSE = GetObject("LDAP://RootDSE")
strDefaultNamingContext = iAdRootDSE.Get("defaultNamingContext")
Conn.Provider = "ADsDSOObject"
Conn.Open "ADs Provider"

report = "<table border=""1"" width=""100%"">" & vbcrlf
report = report & " <tr>" & vbcrlf
report = report & "<td align=""center"" bgcolor=""#000080""><b><font color=""#FFFFFF"">Group-Name</font></b></td>"
& vbcrlf
report = report & "<td align=""center"" bgcolor=""#000080""><b><font color=""#FFFFFF"">Total
Number of Members</font></b></td>" & vbcrlf
report = report & "<td align=""center"" bgcolor=""#000080""><b><font color=""#FFFFFF"">Mailboxes</font></b></td>"
& vbcrlf
report = report & "<td align=""center"" bgcolor=""#000080""><b><font color=""#FFFFFF"">Contacts-Internal</font></b></td>"
& vbcrlf
report = report & "<td align=""center"" bgcolor=""#000080""><b><font color=""#FFFFFF"">Contacts-External</font></b></td>"
& vbcrlf
report = report & "<td align=""center"" bgcolor=""#000080""><b><font color=""#FFFFFF"">Groups</font></b></td>"
& vbcrlf
report = report & "<td align=""center"" bgcolor=""#000080""><b><font color=""#FFFFFF"">QueryBased
Dl's</font></b></td>" & vbcrlf
report = report & "<td align=""center"" bgcolor=""#000080""><b><font color=""#FFFFFF"">Public
Folders</font></b></td>" & vbcrlf
report = report & "<td align=""center"" bgcolor=""#000080""><b><font color=""#FFFFFF"">InterOrg-Person</font></b></td>"
& vbcrlf
report = report & "</tr>" & vbcrlf

trackCicularref = ""

numcheck = 0
usNumberUsers = 0
cnNumberinContacts = 0
cnNumberexContacts = 0
nsNumberGroups = 0
pfNumberPublicFolders = 0
ioNumberIorgPersons = 0
QbNumberQBDLs = 0
set trackmembers = CreateObject("Scripting.Dictionary")


GALQueryFilter = "(&(mail=*)(objectCategory=group))"
strQuery = "<LDAP://" & strDefaultNamingContext & ">;" & GALQueryFilter & ";distinguishedName,displayname,legacyExchangeDN,homemdb;subtree"
Com.ActiveConnection = Conn
Com.Properties("SearchScope") = 2 ' we want to search everything
Com.Properties("Page Size") = 500 ' and we want our records in lots of 500 (must
be < query limit)
polQuery = "<LDAP://" & iAdRootDSE.Get("configurationNamingContext") & ">;(objectCategory=msExchRecipientPolicy);distinguishedName,gatewayProxy;subtree"
Com.CommandText = polQuery
set sdSMTPDomains = CreateObject("Scripting.Dictionary")
Set adRs = Com.Execute
while not adRs.eof
for each adr in adRs.fields("gatewayproxy").value
if instr(lcase(adr),"smtp:") then
if sdSMTPDomains.exists(LCase(right(adr,(len(adr)-instr(adr,"@"))))) then
else
sdSMTPDomains.Add LCase(right(adr,(len(adr)-instr(adr,"@")))),1
end if
end if
next
adRs.movenext
Wend

Com.CommandText = strQuery
Set Rs = Com.Execute

while not rs.eof
trackmembers.RemoveAll
numcheck = 0
usNumberUsers = 0
cnNumberinContacts = 0
cnNumberexContacts = 0
nsNumberGroups = 0
pfNumberPublicFolders = 0
ioNumberIorgPersons = 0
QbNumberQBDLs = 0
trackmembers.add rs.fields("distinguishedName"),1
enumgroup(getobject("LDAP://" &
replace(rs.fields("distinguishedName"),"/","\/")))
wscript.echo rs.fields("displayname")
wscript.echo usNumberUsers
wscript.echo cnNumberinContacts
wscript.echo cnNumberexContacts
wscript.echo nsNumberGroups
wscript.echo QbNumberQBDLs
wscript.echo pfNumberPublicFolders
wscript.echo ioNumberIorgPersons
report = report & "<tr>" & vbcrlf
report = report & "<td align=""center"">" & rs.fields("displayname") &
"&nbsp;</td>" & vbcrlf
report = report & "<td align=""center"">" & numcheck & "&nbsp;</td>" & vbcrlf
report = report & "<td align=""center"">" & usNumberUsers & "&nbsp;</td>" &
vbcrlf
report = report & "<td align=""center"">" & cnNumberinContacts & "&nbsp;</td>" &
vbcrlf
report = report & "<td align=""center"">" & cnNumberexContacts & "&nbsp;</td>" &
vbcrlf
report = report & "<td align=""center"">" & nsNumberGroups & "&nbsp;</td>" &
vbcrlf
report = report & "<td align=""center"">" & QbNumberQBDLs & "&nbsp;</td>" &
vbcrlf
report = report & "<td align=""center"">" & pfNumberPublicFolders &
"&nbsp;</td>" & vbcrlf
report = report & "<td align=""center"">" & ioNumberIorgPersons & "&nbsp;</td>"
& vbcrlf
report = report & "</tr>" & vbcrlf
rs.movenext
wend

report = report & "</table>" & vbcrlf
Set fso = CreateObject("Scripting.FileSystemObject")
set wfile = fso.opentextfile("c:\temp\Groupreport.htm",2,true)
wfile.write report
wfile.close
set wfile = nothing
set fso = nothing

sub enumGroup(objgroup)
for each member in objgroup.members
if Not trackmembers.exists(member.distinguishedName) Then
trackmembers.add member.distinguishedName,1
wscript.echo member.class & " " & member.displayname
If member.mail <> "" then
Select Case member.Class
Case "user" usNumberUsers = usNumberUsers + 1
Case "contact" wscript.echo
right(member.mail,(len(member.mail)-instr(member.mail,"@")))
If
sdSMTPDomains.exists(LCase(right(member.mail,(len(member.mail)-instr(member.mail,"@")))))
Then
cnNumberinContacts = cnNumberinContacts + 1
Else
cnNumberexContacts = cnNumberexContacts + 1
End if
Case "group" nsNumberGroups = nsNumberGroups + 1
enumgroup(getobject("LDAP://" & replace(member.distinguishedName,"/","\/")))
Case "publicFolder" pfNumberPublicFolders = pfNumberPublicFolders + 1
Case "inetOrgPerson" ioNumberIorgPersons = ioNumberIorgPersons + 1
Case "msExchDynamicDistributionList" QbNumberQBDLs = QbNumberQBDLs + 1
enumQBDL(getobject("LDAP://" & replace(member.distinguishedName,"/","\/")))
End Select
numcheck = numcheck + 1
End if
end if
Next
end Sub

Sub enumQBDL(obdlobject)
if obdlobject.msExchDynamicDLBaseDN = "" then
strQuerydl = "<LDAP://" & strDefaultNamingContext & ">;" &
obdlobject.msExchDynamicDLFilter &
";mail,ObjectClass,distinguishedName,displayname,legacyExchangeDN,homemdb;subtree"
else
strQuerydl = "<LDAP://" & obdlobject.msExchDynamicDLBaseDN & ">;" &
obdlobject.msExchDynamicDLFilter &
";mail,ObjectClass,distinguishedName,displayname,legacyExchangeDN,homemdb;subtree"
end if
set com1 = createobject("ADODB.Command")
Com1.ActiveConnection = Conn
Com1.Properties("SearchScope") = 2 ' we want to search everything
Com1.Properties("Page Size") = 500 ' and we want our records in lots of 500
(must be < query limit)
wscript.echo strQuerydl
Com1.CommandText = strQuerydl
Set qdlRs = Com1.Execute
While Not qdlRS.eof
Set member = getobject("LDAP://" &
replace(qdlRS.fields("distinguishedName"),"/","\/"))
if Not trackmembers.exists(member.distinguishedName) Then
trackmembers.add member.distinguishedName,1
wscript.echo member.class & " " & member.displayname
If member.mail <> "" then
Select Case member.Class
Case "user" usNumberUsers = usNumberUsers + 1
Case "contact" wscript.echo
right(member.mail,(len(member.mail)-instr(member.mail,"@")))
If
sdSMTPDomains.exists(LCase(right(member.mail,(len(member.mail)-instr(member.mail,"@")))))
Then
cnNumberinContacts = cnNumberinContacts + 1
Else
cnNumberexContacts = cnNumberexContacts + 1
End if
Case "group" nsNumberGroups = nsNumberGroups + 1
enumgroup(getobject("LDAP://" & replace(member.distinguishedName,"/","\/")))
Case "publicFolder" pfNumberPublicFolders = pfNumberPublicFolders + 1
Case "inetOrgPerson" ioNumberIorgPersons = ioNumberIorgPersons + 1
Case "msExchDynamicDistributionList" QbNumberQBDLs = QbNumberQBDLs + 1
enumQBDL(getobject("LDAP://" & replace(member.distinguishedName,"/","\/")))
End Select
numcheck = numcheck + 1
End if
end if
qdlRS.movenext
wend

End Sub

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