Comments (1)

By Scott, March 1, 2010 10:29 AM

There are a couple of changes that have been made to improve the script.

First, after running a report for a user with thousands of messages and not having any idea of the progress while it was running, I added progress windows during the two phases (content conversion and HTML report creation). The progress window will tell you how many messages will be processed and how far along it is in that process.

Second, I discovered that hyperlinks in messages were not showing up in the report. Looking at the HTML (or XML) source, I could see the link was there, but the way it was formatted in the XML file (using element enclosures) meant the conversion to HTML was confusing the parser. Now I remove those characters prior to the link being written to the XML file.

I also optimized the message body conversion loop to remove redundant or obsolete code that was in the original script. There are also some minor cosmetic changes here and there.

Lastly, I realized last week that the script only reports on peer-to-peer (P2P) conversations, not multiparty. I spent some time trying to get multiparty conversations out of the database, but it is proving much more difficult than I anticipated. The format of multiparty IMs in the archiving database is very different than P2P conversations. I am still working on it, but I am not sure I will be able to make it work. In the meantime, I added a note in the report that states it is only for P2P conversations.

The original zip file with the script has been updated, but you can download it here:

  Get-ArchivingData.zip (3.1 KB)

Comments (0)

By Scott, February 9, 2010 11:08 AM

If you use, say, BlackBerry Web Desktop Manager to facilitate firmware upgrades for users, then you have a bunch of them installed somewhere. (I have 49 versions among 15 carriers.) I keep a list of the ones I have installed, but I wanted to be able to compare that to what is actually in use among all the users in the organization. This way I can know which installs are obsolete, when new models are in use, and when newer versions are in use by users.

To use this script, in BlackBerry Manager (I am using 4.1.6) highlight all of your users and right-click on them, then select Export Asset Summary Data. The script will look for BESHandhelds.txt, but you can edit it to be whatever you want. The file that I use to track installed versions is just a csv with the following headers: Carrier, Model, Model Name, Version, Installed. Model Name is not used in the script, but mirrors the fields I use in an internal KB article that users can reference. Here is a sample table of entries:

I added the Installed field as a way to track models that don’t actually have a local installation either because there is only one release from the carrier or all handhelds are already on that version so there isn’t a reason to have it installed. The script will look for a file called BBFirmware.csv but, again, you can change it to whatever you want.

The switch statement starting in line 17 is to map the carrier name as recorded in BES to how I record it in my file. The output of the script will let you know carriers that are not defined in the statement, but you will want to add or remove carriers as necessary first.

The output will also let you know if a model is in use that is not found in your installation file, if a there is a newer version for a particular model that is in use, and if all users are using the latest version you have installed. What this script does not do is determine if you have the latest version that has been released by the carrier (since that would be much harder to do). Lastly, it will let you know of installations you have for models that are not in use anymore so that you can uninstall them.

I am a perfectionist, so I can already see ways to improve the script, but I would never be done if I didn’t stop somewhere. You can download the ps1 file below.

  BBFirmwareCheck.zip (1.5 KB)

Comments (0)

By Scott, December 3, 2009 4:46 PM

Edit: The inline code is not the latest version of the script. To get the latest version, download the full script on the Downloads page (or via the link at the end of the post).

The foundation for my script is from the OCS team at Microsoft, who created the original version.

The script from Microsoft grabs all the messages and outputs them all in one big table. I wanted it to be displayed more logically, grouping an entire conversation (session) together so it is easier to follow the context of the messages. This proved to be more daunting than I had anticipated.

All messages that occur in the same session have the same value for the SessionIdTime. With that I can group those messages together. But I needed a way to know when a new conversation started and ended. To get that I used the RANK function in SQL so that any rank of 1 indicates a new conversation. The resulting SQL query for a single user is the following (the ticks at the end of the lines are for PowerShell):

SELECT RANK() OVER(PARTITION BY SessionIdTime ORDER BY SessionIdTime, MessageIdTime) AS 'Rank', `
	SessionIdTime, MessageIdTime, Body, ContentTypeId, [from], [to] `
FROM `
(SELECT SessionIdTime, MessageIdTime, Body, ContentTypeId, u1.UserUri AS [from], u2.UserUri AS [to] FROM Messages, `
	Users u1, Users u2 WHERE Messages.FromId = u1.UserId AND Messages.ToId = u2.UserId AND u1.UserUri = '$User1' `
	AND LcsLog.dbo.Messages.Toast IS NULL `
UNION ALL `
SELECT SessionIdTime, MessageIdTime, Body, ContentTypeId, u2.UserUri AS [from], u1.UserUri AS [to] FROM Messages, `
	Users u2, Users u1 WHERE Messages.FromId = u2.UserId AND Messages.ToId = u1.UserId AND u1.UserUri = '$User1' `
	AND LcsLog.dbo.Messages.Toast IS NULL `
)AS dConversation

Now that I had the data, I needed to be able to use the XSLT to conditionally close a table the next time a rank of 1 is in the loop. I had the hardest time trying to make the xsl:if and xsl:choose statements work. Trying to include a </table> tag without the loop seeing the opening tag only caused errors. If I used HTML notation, &lt;/table&gt;, those characters were rendered as their literal ASCII representations rather than being parsed as HTML tags.

After trying for many, many hours, I decided to give up relying on the XML parser to convert the file to HTML and just do that parsing in the PowerShell script. This has the benefit of not requiring external files to run the script (the XSLT and msxsl.exe). The code below takes the XML output from the first part of the script and loops through each node (message). If the rank is 1, it closes the previous table, opens a new one, and writes the message to a row. If the rank is not 1, it means that a conversation has already been started and the message can simply being written in a new row.

#Convert XML to HTML
$sourceXML = [xml](Get-Content $LocalPath)
Remove-Item -path IM.html -ea SilentlyContinue
pwd | % {[string]$LocalPath = $_.path}
$LocalPath = $LocalPath + "\IM.html"
Add-Content -path $LocalPath -Value '<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN"'
Add-Content -Path $LocalPath -Value '  "http://www.w3.org/TR/html4/loose.dtd">'
Add-Content -Path $LocalPath -Value "<html>"
Add-Content -Path $LocalPath -Value "<head>"
Add-Content -Path $LocalPath -Value "<title>IM Conversation Archive</title>"
Add-Content -Path $LocalPath -Value "</head>"
Add-Content -Path $LocalPath -Value "<body>"

Add-Content -Path $LocalPath -Value '<font size="2" face="Verdana">'
Add-Content -Path $LocalPath -Value "<h2 align=`"center`">$($sourceXML.IMConversation.Title)</h2>"
Add-Content -Path $LocalPath -Value "<h3 align=`"center`">$($sourceXML.IMConversation.Subtitle)</h2>"
Add-Content -Path $LocalPath -Value "</font>"
Add-Content -Path $LocalPath -Value '<table border="0" cellpadding="1" width="100%" style="FONT-SIZE:8pt;FONT-FAMILY:verdana">'
foreach($IM in $sourceXML.IMConversation.IM)
	{
	if ($IM.Rank -eq "1")
		{
		Add-Content -Path $LocalPath -Value "</tbody></table><br>"
		Add-Content -Path $LocalPath -Value '<table border="1" cellpadding="1" width="100%" style="FONT-SIZE:8pt;FONT-FAMILY:verdana">'
		Add-Content -Path $LocalPath -Value '<thead><tr bgcolor="#C0C0C0" align="center">'
		Add-Content -Path $LocalPath -Value	'<th><font color="#0000FF">Session Time (UTC):</font></th>'
		Add-Content -Path $LocalPath -Value	'<th><font color="#0000FF">Message Time (UTC):</font></th>'
		Add-Content -Path $LocalPath -Value	'<th><font color="#0000FF">From:</font></th>'
		Add-Content -Path $LocalPath -Value	'<th><font color="#0000FF">To:</font></th>'
		Add-Content -Path $LocalPath -Value '<th width="40%"><font color="#0000FF">Message:</font></th>'
		Add-Content -Path $LocalPath -Value	"</tr></thead>"
		Add-Content -Path $LocalPath -Value	"<tbody><tr>"
		Add-Content -Path $LocalPath -Value	"<td rowspan=`"100`" valign=`"top`">$($IM.SessionTime)</td>"
		Add-Content -Path $LocalPath -Value	"<td>$($IM.DateTime)</td>"
		Add-Content -Path $LocalPath -Value	"<td>$($IM.From)</td>"
		Add-Content -Path $LocalPath -Value	"<td>$($IM.To)</td>"
		Add-Content -Path $LocalPath -Value	"<td>$($IM.Body.get_FirstChild().get_Data())</td>"
		Add-Content -Path $LocalPath -Value	"</tr>"
		}
	else
		{
		Add-Content -Path $LocalPath -Value	"<tr>"
		Add-Content -Path $LocalPath -Value	"<td>$($IM.DateTime)</td>"
		Add-Content -Path $LocalPath -Value	"<td>$($IM.From)</td>"
		Add-Content -Path $LocalPath -Value	"<td>$($IM.To)</td>"
		Add-Content -Path $LocalPath -Value	"<td>$($IM.Body.get_FirstChild().get_Data())</td>"
		Add-Content -Path $LocalPath -Value	"</tr>"
		}
	}

Add-Content -Path $LocalPath -Value "</table>"
Add-Content -Path $LocalPath -Value "</body>"
Add-Content -Path $LocalPath -Value "</html>"

Lastly, I wanted to be able to filter on a date range within the archiving database. So I added two arguments to the script after the users: -start and -end, both which are independently optional. If you don’t specify a second user or you only specify an end date with one or two users, you need to use the parameter names in the command. Otherwise, they are positional. The date format to use is "YYYY-MM-DD". To account for the presence of the date filter, this code is added to the script:

If ($start -ne '')
	{
	#Start date has been specified
	$startdate = $start
	$start = "and Messages.MessageIdTime >= '$start' "
	}
Else
	{
	$startdate = "Oldest record in the database"
	}

If ($end -ne '')
	{
	#End date has been specified
	$enddate = $end
	$end = "and Messages.MessageIdTime <= '$end' "
	}
Else
	{
	$enddate = "Newest record in the database"
	}

The code is able to correctly parse the presence of a start date, end date, or both. Now the following can be added to the end of the code block in the SQL query that makes up the derived table (the part in the nested parentheses): + $start + $end +.

Finally, the output is a pretty page with the subtitle that indicates the date range used, the table headers are formatted to visually separate each conversation, and each table is an indicated of a single conversation, regardless of the number of messages shown.

I don’t expect you to copy and paste all the code above into the script and have it magically work (especially since I made other minor changes to account for the subtitle, additional arguments, etc.), so you can download the entire script below or on the Downloads page.

  Get-ArchivingData.zip (3.1 KB)

Comments (2)

By Scott, November 18, 2009 11:08 AM

The OCS team blog has a post with a script for retrieving messages from the archiving database. As is, the script will retrieve both the plain text version of the first message in a new conversation (as seen in the toast) and the RTF version that is displayed in the conversation window. The output of the report, however, doesn’t distinguish between the toast message and the RTF message, so it just looks like it is a duplicate message.

I have done some testing, and both versions are logged in the database even if you ignore the toast or decline the conversation. I can’t think of a case where you will only have the toast logged and not the full message, so I have excluded all toast messages from my copy of the script. To add this exclusion yourself, add AND LcsLog.dbo.Messages.Toast IS NULL to the SQL queries on lines 23 and 28 before the UNION operator and before the ORDER BY clause at the end, for a total of four times.

Comments (0)

By Scott, November 9, 2009 8:09 AM

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()

Comments (0)

By Scott, September 22, 2009 12:57 PM

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
	}

Comments (0)

By Scott, July 30, 2009 4:57 PM

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

Comments (0)

By Scott, May 27, 2009 2:42 PM

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

Comments (0)

By Scott, December 18, 2008 7:46 AM

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.

Comments (0)

By Scott, December 10, 2008 4:34 PM

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"