I have been struggling to delete the value in the mail attribute after a mailbox has been deleted. Exchange populates the mail attribute when a mailbox is created (even though Exchange has no use for the attribute), but doesn’t clear the attribute when the mailbox is deleted. With ADUC integration removed in Exchange 2007, a quick way to know if an account has a mailbox is to look at the mail attribute. But if removing a mailbox no longer clears that attribute, it is difficult know (just by looking at a user account in ADUC) if the account still has a mailbox.
Since Exchange doesn’t use the mail attribute, you can’t use the Set-Mailbox attribute, especially if the mailbox is deleted anyway. I tried using Set-User with the -WindowsEmailAddress parameter, but because the data type is Microsoft.Exchange.Data.SmtpAddress, setting the value to "" or $null doesn’t work because those aren’t properly formatted SMTP addresses.
So, I figured I needed to get away from any Exchange cmdlet. I used PowerShell’s native support for ADSI to bind to the user object: New-Object DirectoryServices.DirectoryEntry "LDAP://UserDN". But you will get an error if you try to set the attribute to null ($user.mail = $null). You can set it to an empty value (""), but you will then get an error when you try to commit the change: $user.SetInfo().
How can you possibly clear this attribute, one that is so easy to do in ADUC just by deleting the value in it? It is necessary to fall back to the PutEx method. Using that will let you use the ADS_PROPERTY_CLEAR constant (indicated by the numeric one in the first argument). It has taken me days to finally get to this point, so hopefully this post will shorten that time for others trying to do the same thing.
$user = Get-User "username"
$ldapDN = "LDAP://" + $user.distinguishedName
$adUser = New-Object DirectoryServices.DirectoryEntry $ldapDN
$adUser.PutEx(1, "mail", $null)
$adUser.SetInfo()
In a previous post I instructed how to load balance new mailboxes across databases. In a large environment, however, this determination can take upwards of 90 seconds. Normally I don’t run the mailbox creation script at my company (our IT Security department does), but I ran it yesterday and it took 55 seconds to determine the database. That’s just too long for me.
So I decided to add what was going to be my fallback option in the first place: using a cached list. This code uses an xml file to keep track of the database to use at a given location and a timestamp of when that entry was last updated. The xml file uses the following format:
<?xml version="1.0"?>
<sites>
<site id="Name1">
<database name="Server1\Storage Group 1\Mailbox Store 1" />
<timestamp time="2009-09-22T08:59:29.4878574-07:00" />
</site>
<site id="Name2">
<database name="Server2\Storage Group 5\Mailbox Store 1" />
<timestamp time="2009-09-22T08:17:51.5632031-07:00" />
</site>
....
</sites>
Set the $sourcexml variable to the full path to the file. The function that calls this code should set the $Location parameter to whatever value is stored in the id attribute of the site nodes. The xml file is opened, the timestamp for a given site name is returned, and compared to a time interval (in this case, 24 hours, but you can set it to anything you want). If less than 24 hours old, the database name in the site node is used. If more than 24 hours old, it will run the code to determine the database to use (from the other post). After the determination is made, it writes the database name and the current time back into the xml file and saves it.
The on-demand query for one site ran in 53 seconds through mailbox creation for the first run, but created the second mailbox in 11 seconds. That is much more tenable, at least for an impatient person like myself.
$start = Get-Date
$sourcexml = "Path to input xml file.xml"
[xml]$db = Get-Content $sourcexml
$site = $db.sites.site | where {$_.id -eq $Location}
[datetime]$lastUpdate = $site.Timestamp.Time
if (($start - $lastUpdate).TotalHours -lt 24)
{
Write-Host "Timstamp for selected site is less than 24 hours old. Using cached entry."
$site.database.name
}
else
{
Write-Host "Timestamp for selected site is more than 24 hours old. Calculating database to use."
#Run code here to determine database to use
#
#When done, continue
$site.database.name = ($mailboxcount.GetEnumerator() | sort value | select -first 1).key
$site.timestamp.time = (Get-Date -Format o).ToString()
$db.save($sourcexml)
$site.database.name
}
This VBScript uses native SQL connectivity via ADO to query your BlackBerry Enterprise Server configuration database for the current number of users. Since the license total is not stored in the database you have to set it as a variable in the script. It then compare the two numbers and if your defined threshold is exceeded, it will email you a notification. Just schedule the script to run daily. Download a zipped version here, or copy below.
'BES license usage monitor. Set current license total, alert threshold, And
'other variables to be notified when the license usage exceeds the threshold.
Dim strSQLServer, strBESDB, iCurrentLicenseTotal, iLicenseThreshold
Dim strMailRecipients, strMailSender, strMailSubject, strMailServer
'*************************************
'------Configurable Variables---------
iCurrentLicenseTotal = 1000 'Total number of licenses in License Management
iLicenseThreshold = 20 'Free licenses threshold for notification
strSQLServer = "ServerName" 'NetBIOS name of SQL Server
strBESDB = "DatabaseName" 'Database name in SQL
'------Notification configuration-----
strMailRecipients = "user1@domain.com,user2@domain.com" 'Command-separated list of addresses
strMailSender = "BESLicenseCounter@domain.com" 'From address
strMailSubject = "BES license usage at critical level" 'Subject of notification message
strMailServer = "smtpserver.domain.com" 'FQDN of smarthost
'*************************************
Dim iCurrentUserTotal, iAvailableLicenses, strSQLQuery, strSQLConn
Dim objSQLConn, objSQLRecordSet, objSQLExec
strSQLConn = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Data Source=" & _
strSQLServer & "; Initial Catalog=" & strBESDB
strSQLQuery = "Select Count(*) As Total_Users From UserStats"
Set objSQLConn = CreateObject("ADODB.Connection")
Set objSQLRecordSet = CreateObject("ADODB.Recordset")
objSQLConn.Open = strSQLConn
objSQLRecordSet.Open strSQLQuery, objSQLConn
Set objSQLExec = objSQLRecordSet
While Not objSQLExec.EOF
iCurrentUserTotal = objSQLExec.Fields("Total_Users").Value
objSQLExec.MoveNext
Wend
iAvailableLicenses = iCurrentLicenseTotal - iCurrentUserTotal
If iAvailableLicenses < iLicenseThreshold Then
Dim strMailBody
strMailBody = iCurrentUserTotal & " of " & iCurrentLicenseTotal & " licenses are in use."
strMailBody = strMailBody & " It is time to order more licenses."
SendMail
End If
Sub SendMail
Set objEmail = CreateObject("CDO.Message")
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = strMailServer
objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
objEmail.Configuration.Fields.Update
objEmail.From = strMailSender
objEmail.To = strMailRecipients
objEmail.Subject = strMailSubject
objEmail.HTMLBody = strMailBody
objEmail.Send
End Sub
I needed a way to have new mailboxes be automatically distributed across a list of databases on multiple servers, and I recalled seeing a script somewhere. The blog post with those details can be found here. As the author points out, it can take a long time to build the hash table, too long for my requirements. For example, one of my longer queries builds the table for 9 databases with over 5000 mailboxes in 58 seconds. That is just way too long for someone to feed a user account into to get a mailbox created.
So I broke down the query being used to find where efficiency could be improved. The Get-ExchangeServer cmdlet slows the script down and can be avoided altogether by feeding the server list directly into the Get-MailboxDatabase cmdlet. Additionally, I discovered just how inefficient the Exchange cmdlets are. I already knew they were based on running other cmdlets for targets servers across a WAN that return not a lot of data, but this reinforced those findings.
To get away from the Exchange cmdlets, I query AD directly by instantiating ADSI in .NET manually. Instead of slowly building the hash table I just take the record count of the search results and populate the table entry at once. This reduced the time to completion from 58 seconds to 24 seconds. These findings are consistent across different database counts and mailbox totals: 60% reduction in time. While still too long for my taste, it certainly is much better than before and is within reason for the people at my company that make use of the script.
Since my script encompasses more than just the load balancing part (I create a GUI form where the location for the mailbox is selected, which feeds a list of appropriate servers to the function), this is just the part that creates the hash table with the database counts and returns the database with the lowest count:
$mbxServers = @()
$mailboxcount = @{}
$domain = [System.DirectoryServices.ActiveDirectory.Domain]::GetCurrentDomain()
$root = [ADSI]"GC://$($domain.Name)"
$mbxServers | %{Get-MailboxDatabase -server $_} | ?{$_.recovery -eq $FALSE} | %{
$filter = "(&(objectcategory=user)(homeMDB=" + ($_.DistinguishedName) + "))"
$search = new-Object System.DirectoryServices.DirectorySearcher($root,$filter)
$search.PropertiesToLoad.Add("homeMDB") | Out-Null
$result = $search.FindAll()
$mailboxcount["$($_.Identity)"] = $result.count
}
($mailboxcount.GetEnumerator() | sort value | select -first 1).key
Version 1.3.1 fixes a small issue where the subject would still say everything is okay even though the threshold for the last backup had been exceeded. (I didn’t set the scope of the variable correctly after moving part of the code into a function.) Download.
This is a quick and dirty way of retrieving the self-signed SSL certificate that Exchange 2007 servers use out of the box. Get-ExchangeCertificate only works locally on the box that has the certificate…not fun when you have a long list to find out so you can build a list for renewing them. This uses psexec to connect to the remote server and retrieve any personal certificates and display the expiration date. Short and sweet.
.\psexec \\server cmd /c "echo . | powershell (gci -path cert:\localmachine\my).notafter"
This update fixes reporting for public folder databases. I failed to account for the fact that PF databases won’t be returned when using Get-MailboxDatabase. The structure of the script has changed a little bit to accommodate that, but the resulting change in the output is only that PF databases will now be included (and storage groups that only had a PF database won’t report that no databases are present). Download v1.3.
I added notification of what the checkmark means, automatically load the Exchange snapin if not already, and append the subject of the email with a status summary. If there are no backups that need attention, all okay is added. If there are, it says that attention is needed. This provides a quick way to know if anything needs attention. Download the script here.
Version 1.11 correctly resets the checkmark status during each loop; when cleared before because no backup has taken place, it remained as a null value for later backups that were successful. Updated version is available here.
I have made some minor updates to my PowerShell script that reports (and emails) the last backup of each database in your organization. I was referencing the wrong variable when looping through the databases in a storage group, so each storage group in the report listed all the databases on the server. I also change the formatting a little so the text in different table cells in the same row actually look to be in the same row (the checkmark was offsetting that cell a bit). You can download v1.1 here.
Comments (3) 
Themocracy