Thursday, January 31, 2008

Managing BlackBerry Configuration Settings Remotely - Part Four

Now that we know how to update the settings on the device, here is a screenshot showing the default settings, and a list of Data strings with all the different Message List 'General Settings' options:


Default Message List / General Options settings:


[Data String for Defaults above]
0x350000010101000001010001000301010200051E0001000600020008
01000100090102000B000002000C000002000D000001000E00000000

[Display Time: NO]
0x350000000101000001010001000301010200051E0001000600020008
01000100090102000B000002000C000002000D000001000E00000000

[Display Name: NO]
0x350000010001000001010001000301010200051E0001000600020008
01000100090102000B000002000C000002000D000001000E00000000

[Display Message Header On: 1 line]
0x350000010101000001010001000301010200051E0001000600020008
01000100090102000B010002000C000002000D000001000E00000000

[Display Message Header On: 2 lines]
0x350000010101000001010001000301010200051E0001000600020008
01000100090102000B020002000C000002000D000001000E00000000

[Display Message Count: None]
0x350000010101000001010001000301010200051E0001000600020008
00000100090102000B000002000C000002000D000001000E00000000

[Display New Message Indicator: No]
0x350000010101000001010001000301010200051E0001000600020008
01000100090002000B000002000C000002000D000001000E00000000

[Confirm Delete: No]
0x350000010101000001010000000301010200051E0001000600020008
01000100090102000B000002000C000002000D000001000E00000000

[Hide Filed Messages: YES]
0x350000010101000001010101000301010200051E0001000600020008
01000100090102000B000002000C000002000D000001000E00000000

[Hide Sent Messages: YES]
0x350000010101000001010001000301010200051E0001000601020008
01000100090102000B000002000C000002000D000001000E00000000

[Make PIN Messages Level 1: No]
0x350000010101000001010001000300010200051E0001000600020008
01000100090102000B000002000C000002000D000001000E00000000

[Auto More: No]
0x350000010101000001010001000301000200051E0001000600020008
01000100090102000B000002000C000002000D000001000E00000000

[Keep Messages: 15 Days]
0x350000010101000001010001000301010200050F0001000600020008
01000100090102000B000002000C000002000D000001000E00000000

[Keep Messages: 2 Months]
0x350000010101000001010001000301010200053C0001000600020008
01000100090102000B000002000C000002000D000001000E00000000

[Keep Messages: 3 Months]
0x350000010101000001010001000301010200055A0001000600020008
01000100090102000B000002000C000002000D000001000E00000000

[Keep Messages: 4 Months]
0x35000001010100000101000100030101020005780001000600020008
01000100090102000B000002000C000002000D000001000E00000000

[Keep Messages: 5 Months]
0x35000001010100000101000100030101020005960001000600020008
01000100090102000B000002000C000002000D000001000E00000000

[Keep Messages: 6 Months]
0x35000001010100000101000100030101020005B40001000600020008
01000100090102000B000002000C000002000D000001000E00000000

[Keep Messages: Forever]
0x35000001010100000101000100030101020005FFFF01000600020008
01000100090102000B000002000C000002000D000001000E00000000

[SMS and Email Inboxes: Combined]
0x350000010101000001010001000301010200051E0001000600020008
01000100090102000B000002000C010002000D000001000E00000000

[SMS and Email Inboxes: Separate]
0x350000010101000001010001000301010200051E0001000600020008
01000100090102000B000002000C020002000D000001000E00000000



The first line contains all the default settings from my test device which is an 8700g with the 4.2 OS software. The latter lines are the defaults with only the option shown enabled. The highlighted numbers in the rows show which number controls the setting in that row.

Keep in mind that this may be different from what you see depending upon your device type and OS version, however I suspect they would be similar if not the same for the same OS version.

Next: Calendar, Address Book, and Tasks options database settings.

Managing BlackBerry Configuration Settings Remotely - Part Three

To begin, of course, a Disclaimer: The BES SQL database is the heart of your BES environment. Making changes to it outside of the UI is not supported by RIM and can do irreparable harm to your entire BES environment. The following is for informational purposes only, and I am not responsible for the thousands of midnight telephone calls you will get from your BlackBerry users when an errant SQL command has rendered your BES servers mere hunks of plastic and metal, and transmogrified into a CLM [career limiting move]. I for one will not be implementing these findings in my production environment! Now, onto to the madness...

Since we have discovered where the configuration setting "Hide Sent Messages" lives in the SQL database, let's try to modify it and get it pushed out to the device. First, let's reset the option back to the default of "No" and restart the wireless to get it changed in the SQL database. Here is the device screenshot showing the default settings:


And here is the SQL data, showing that the '1' has been changed back to a '0':

0x350000010101000001010001000300010200051E0001000600020008
01000100090102000B000002000C020002000D000001000E00000000

Now, let's use a SQL command to update this string to change the '0' back to a '1':


Perhaps if we restart the wireless on the device the change will sync down?



Alas and alack, the change did not come down. Oh well, that would have been too easy. Let's take a deeper look into the SQL tables to see where we can go from here. I see a promising table named SyncConfig...and it appears to... wait for it... hold the Synchronization Configuration settings! Just what we need:

I see there are many lines associated with UserConfigId 9, which is my single test user. I also see a column titled SyncDataSourceId, I wonder what the numbers in this column refer to?

After doing some digging I discover the SyncDataSource table, which contains mappings between the numbers in the SyncDataSourceId column and the names of these sources.


What we are interested in would be the Backup data source, or number 11109, as this is what we are working with. This leads us to row 102 in the SyncConfig table.

Now the question becomes, what do the other columns in this table mean, and what can we do with them? SyncType and ConflictResolution appear especially intriguing given what we are trying to accomplish here.

By changing some settings on the device and watching the effect on this table for other unrelated rows, I discovered that the SyncType field has three options available:

1 = Server to Device
2 = Device to Server
3 = Bi-directional

In our case, the 11109 Backup row in this table indicates '3', or Bi-Directional sync. I would have expected to see this set to '2' for one way backups from device to server only. However '3' may make sense, as the backup is from device to server for the most part, but can be from server to device when changing devices for instance.

Similar to SyncType, the ConflictResolution column appears to have two options available:

0 = Server Wins
1 = Device Wins

Our row 102 shows a '0' in this column, which again is a little unexpected as I assumed that the server side was a backup to what is on the device, so the device should always win in a conflict. Perhaps there are good reasons for these non-obvious settings that I am not aware of.

In any case, we have still updated the data in the SyncBackupRestore table but it has not sync'ed to the device, even though the sync settings shown above indicate that a) sync is bidirectional and b) the server will win on a conflict. So really this should just work without any changes, however we need to figure out some way of 'kicking' off a sync.

We know that when you disable and then re-enable synchronization from the device or BES manager for other functions such as Address Book, it will kick off an initial sync. The issue here is that there is no way through the GUI of the device (or the BES Manager) to kick off a Backup sync, as this is an underlying process not available to the end user or administrator.

Why don't we try to just flip the ServerEnabled setting from 1 to 0, and then back to 1 to see if this does it:


Unfortunately, looking at the logs it doesn't appear that this trick did anything. I see the following lines in both the SYNC and CBCK logs:

[46077] (01/31 13:09:36.021):{0x1178} [SYNC-Gate] Finished user config change check. No change detected.
[40039] (01/31 13:20:21.856):{0x1EE0} CheckResponseRcvd - no changes detected for any users.

Let's move on and try to change another field, how about we try to change the SyncType from '3' (bi-directional) to '1' (server to device):


Wow! Within a minute or so of making this change there was a flurry of activity in the SYNC and CBCK logs. I notice this line as well:

[46078] (01/31 13:26:53.092):{0x1178} [SYNC-Gate] Finished user config change check. Change detected.

Turns out this triggered a full synchronization, such that everything on both sides was compared and the changes pushed down to the device. Going back into our Message List Options screen, we are presented with:





As Dora would say, "Lo Hicimos! We did it!"






So in summary, we were able to push this change to the device by doing the following:

1) Locating the option in the data string of the particular settings database within SyncBackupRestore
2) Updating the data string with the particular option set, via SQL commands
3) Modifying the SyncType from '3' to '1'

Now, like a good boy scout, we must remember to clean up after ourselves. To get this to work we had to break the default SyncType, so let's set it back to '3' so that any future changes on the device continue to be backed up:


Next Up: Decoding the Data string in the "Message List Options" database to map out all the different option settings.

Wednesday, January 30, 2008

Managing BlackBerry Configuration Settings Remotely - Part Two

OK so let's dive in and see what we can do with the SQL table SyncBackupRestore. First of all it looks like there are separate entries for different types of settings. Let's start with the entry for Message List Options for UserConfigId #9, which is the only user in this test environment:


It looks like the Data column is encoded in some sort of binary format, let's see if we can use OSQL to see what's in there:


So after a bunch of header information we finally see the data string, and it looks like this:

0x350000010101000001010001000301010200051E0001000600020008
01000100090102000B000002000C000002000D000001000E00000000


As it exists now, this string represents the state of the options for the Message List, i.e. the Messages screen on the BlackBerry. The best way to discover what pieces control which device settings is to change an option on the device, wait for it to get synced up to the server, then check this string again to see what is different.

We will first test by changing a single option on the BlackBerry, Hide Sent Messages from the default of No to Yes (a common enough request!). Now the issue is waiting for the device's backup agent to push the change to the BES server, however I discover to my pleasant surprise that turning off the antenna and turning it back on will trigger the handheld agent to push any device changes up to the server. (Restarting the BlackBerry has the same effect).

I have been using the Unix "tail" utility to follow the BES server's CBCK log file (Backup connector), which is set to the debug logging level of 7 through the registry (4 is the max via the UI). When the device connects to the network we see the following update come through:

[60000] (01/30 14:26:07.086):{0x151C} [ODBCRecord::DoSetValue] DATA = "DbVersion[1] Data(first 32 bytes)[0x350000010101000001010001000300010200051E000100060102000801000100] UserConfigID[9] DatabaseName[Message List Options] UID[1] ".

This log entry only shows the first 32 bytes of the updated field, so we have to go back to the OSQL utility to display the full field again, and it is now set to the following:

0x350000010101000001010001000301010200051E0001000601020008
01000100090102000B000002000C000002000D000001000E00000000

The only change in this string is the highlighted '1' near the end of the first line has now been changed from a '0' previously. This leads us to believe that this number controls the Hide Sent Messages
option on the device, and that modifying it from '0' to '1' enables it.

In the next post we will try to update this field in the SQL database and (somehow!) get that update pushed to the handheld.

Managing BlackBerry Configuration Settings Remotely - Part One

Much of the configuration of BlackBerry devices can be controlled through IT Policy options, especially those having to do with security. However there are many more configuration options available on the device that don't have IT Policy correlates. In these cases it can be difficult to control the user experience or to enforce standard organizational settings.

There are a few options to automate the configuration of these device settings:

1) Configure a BlackBerry with your custom settings, then back it up to a .IPD file. For each new BlackBerry deployed, restore the .IPD file before activation.

2) Configure a BlackBerry with your custom settings, then assign it to the new user's account. Then activate a new BlackBerry against the same account, which will inherit the device configuration settings from the old device.

These options can set an initial standard, however they cannot be changed remotely or locked down and enforced over time. The BlackBerry device user will have the ability to modify these settings and break the standard.

The fact that option #2 above exists tells us that the device configuration settings are stored within the BES environment, as that is how swapping in brand new device for a user can inherit all the old device's settings. Unfortunately the location of these settings is not easily accessible to the BES Administrator, as this function was really only designed for backup and restoration purposes.

However... if we can figure out where these settings are stored perhaps we can modify them somehow from the back end, and push them down to the device. We know that most configuration information is stored in the central SQL database, so this is where the configuration must be stored as well.

Peeking around my test environment I discovered a table in the database called SyncBackupRestore. It appears that much of the device's configuration settings are stored in this table. In the next post we will dig deeper into this table to discover what it holds and how perhaps we can modify it and push the changes to a remote BlackBerry.

Wednesday, January 23, 2008

BlackBerry Lookup - The Details (Updated for 4.1 SP4)

After doing some troubleshooting on lookups the other day I discovered that there are now some additional fields that are returned via lookup, that were not there back in the 4.0 days when I posted my first post on this over a year ago.

Specifically, the Home Address fields have now been added, which is nice. Here is a lookup result that includes as the data the Notes Person document field names that the lookup pulls from for reference:


Please note two missing items from this lookup:

1) PIN is missing due to the test Person document not having a BlackBerry device

2) The "Fax: OfficeFAXPhoneNumber" lookup result is missing, though populated in the Person document. This is due to a known issue introduced in SP4 MR1 and scheduled to be fixed in SP5.


Now, onto something interesting that I wasn't aware of before. You are able to customize the lookup results to a certain extent, by using the 4 "User Defined x" fields present in the "Edit PIM Sync Global Field Mappings" command from the BES Manager. [I didn't think this was an option as I always thought that the Global Field Mappings had to do with Address Book synchronization, I never thought they would impact the lookup results.]

Simply choose an unmapped field, drop down the Device Field column field and choose one of the User Defined fields to assign it to:


In my test environment I have assigned the following custom fields:

User Defined 1 --> Assistant
User Defined 1 --> Manager
User Defined 1 --> Spouse
User Defined 1 --> Children

When I do this, I get the following lookup result:

I see the Assistant, Manager, and Spouse fields, but not the Children field I mapped. Perhaps there is an issue with the "User Defined 4" field not working correctly for lookups. Let me change the mapping of this field to "Department" and see what happens:

Interestingly, the Department field does come over. So perhaps it is not an issue with the "User Defined 4" field , but with the Children field not working correctly.

On a final note, the Comments field on the Person document can be multiple lines and all lines will show up on the BlackBerry lookup. So instead of the "User1:" etc mapping above you can create freeform text lines in the Comments field if you need them on the BlackBerry lookup. For example, I added two additional lines to the Comments field in the Person document, and here they are on the lookup:

I'm not sure what the limit on the number of lines or characters are for the free form text Comments field. I was able to put 60 lines of information in that field and they were all returned to the BlackBerry on a lookup, so there is quite a bit of information that could be stored here.

Well I hope this helps you customize your lookups so that you can give your users better information directly on their device. Now the greater problem is how to get all this good information from other sources, such as the HR database, into the Person documents in names.nsf in the first place. (Luckily for me that heavy lifting job was already done by our development team for another project.) Good luck!

Tuesday, January 22, 2008

Lookup Strangeness -> "Reset PIM Sync Global Field Mapping" is the Fix

I recently discovered that our BlackBerry lookups were returning some bad information. Our addressing information was coming up wrong, in that the Home Address header was missing, replaced by a second Work Address header. Also, the information in the home address fields was limited just to city and country.

Here is what we were seeing (fields filled with dummy info for testing):


The last address is actually the two home address fields city and country, but labeled as a second Work Address. I had never seen this before, at least this was working properly a few months ago. We have a secondary BES infrastructure in the EU, totally separate, but alas this was occuring there too, which pointed to a Domino problem.

I assumed this must be due to some corruption or weird customization in the names.nsf, where the BES pulls this information from. However copying and pasting my person document into my test BES environment, then doing a lookup from a test device, returned all the proper information! Puzzling...

It is difficult to explain this issue to RIM support, and they also could not recreate the solution in their environment. On a whim, I decided to investigate the "Reset PIM Sync Global Field Mapping" command. After testing this in the test environment and determining that this command would not reset user's custom address book mappings, I ran it. Guess what? All lookup problems solved:

The Home Address is now properly labeled, and although blanked out above, the fields for street address, state, and zip code now appear along with city and country.

This solution points to the address book mappings being off kilter within the BES SQL database, but how did that happen? We don't touch them. And since both of our completely separate BES environments experienced the issue, I must believe that a particular upgrade path (we keep versions the same in US and EU) caused this mis-mapping.

Easy solution to a strange issue!

p.s. I discovered some other interesting lookup related information during this search that I will discuss in an upcoming post.

Wednesday, January 16, 2008

How to Recover* from a Total Loss of BESMgmt SQL database (* with caveats...)

The BESMgmt SQL database is the heart of the Blackberry Enterprise environment. Without it, BES servers will continue to deliver mail but no meaningful changes can be made, or BES services restarted, until it is back up.

The only piece of the BES environment that is out of my control is the database, which is hosted and managed by our SQL team. Although I have full confidence in their replication, backup, and recovery plans, I still need to plan for the (hopefully very remote) possibility that the SQL db might go bye-bye someday.

Until now, this worst case scenario resulted in an "oh well, everybody will have to reactivate their devices" DR plan. I dread the day I have to say this to the CIO!

Recently, however I got wind of the idea of using the nbesmigration.exe utility for DR purposes. This utility was originally designed to populate user accounts in the 4.0 SQL database from a version 2.2 profiles database. Since the 4.x environment still maintains a BlackBerryProfiles notes database on each server, the tool could theoretically be used to re-create the user accounts from these profile entries into a brand new SQL database.

While running the tool in testing, I noticed the following text as part of the built-in help:

BlackBerry Enterprise Server(c) for Lotus Domino(c) Migration Tool, Version 4.1
Copyright (c) Research In Motion, Ltd. 2004, 2006. All rights reserved.
Modification date: May 8 2007

Usage: NBESMigration.exe [options]

This tool can be used to migrate entries from BESD 2.2.x Profiles database to BESMgmt database (UserConfig table). For migration, create the database first using CreateDB, create a server entry in ServerConfig, and run this tool from your BESD server's Domino directory.

This tool can also used for disaster recovery or fixup purposes on already installed BESD server (4.0 and up). Shut down the BES, and run this tool from your BESD server's Domino directory. The tool will check all user docs in Profiles DB, create entries, if needed, in BESMgmt DB (UserConfig table), and it will link entries using SQLID field.

From the text it appears that this tool does support a limited form of DR. Good news! So to test, I did the following in my test environment:

1. Detach the BES SQL db and drop all connections.

2. Stop BES server task and services.

3. Run the BlackBerry Congfiguration Utility and choose the Change Database option.

4. Enter a different database name, thus creating a brand new database.

5. Re-enter CAL and SRP information, but do not restart services.

6. From the tools subdirectory of the BES 4.1 installation share, copy the nbesmigration.exe executable to the c:\lotus\domino directory

7. Run the following command nbesmigration -d: "[SQL Server name]" "[SQL DB name]" -u: [SQL login] [password]. In my case this command line looked like this:

nbesmigration -d: "SQLTEST01" "BESMgmtTest" -u: besdbowner besdbpassword

The tool indicated that it found my one test account and migrated it successfully.

8. Restart the BES server task and services.

The server fired up and I was able to immediately send and receive messages, and do lookups from my device! So this is a great way to recover basic functionality in the case of a total loss of the SQL db.

Keep in mind that you need some information documented and handy to provide to the configuration wizard:
  • CAL licenses
  • SRP ID's
  • SRP Authorization keys
Now, for the caveats:
  • IT Policies are reset to defaults (!)
  • PIM Sync Mappings are reset to defaults
  • Email Settings are reset to defaults
  • Probably a bunch of other stuff is gone or reset to defaults
Although some of the other stuff is fixable, I was unable to successfully apply the default IT policy or a newly created IT policy in the new enviroment, even when it was named the same as the old IT policy. It always came back with an error. On the other hand, when I switched DB's back to the original, the original IT policy was able to be re-applied successfully.

The upshot is that this is a last-last-resort option to get mail flowing to the devices without having everyone reactivate. If you can later restore the original SQL DB and switch back to it, then IT Policies, PIM Sync settings, etc, can be restored back to their original good state.

If you can never get the original DB back, however, then you might have to move forward and find some creative solutions to the problems above. If the solution does eventually require handheld reactivation, well then at least you can control and stage it on your terms, without having hundreds of people screaming at you to get their BlackBerry email fixed!

Tuesday, January 15, 2008

Last Handheld Contact Times - Bogus?

This is a useful field to know when the last time the server talked to the handheld... until I started to suspect this information, as there were contact times very recently with handhelds that I knew were disabled.

To test this out I turned off my BlackBerry and restarted the BES task. The last handheld contact time updated to the current time, with the device turned off in front of me! See the below screenshot, showing last contact time of 1/15/2008 10:16:51AM, when the device was turned off at about 10:10 AM.


To see why this is updating, I checked the MAGT logs for activity on my account during this time:

[40000] (01/15 10:16:51.733):{0xB04} {Test User/Domain} Sending PIM Transaction to Sync Server, Tag 225 (config request)
[40000] (01/15 10:16:51.733):{0x1F9C} [BIPP] Send data, Tag=225
[40000] (01/15 10:16:51.748):{0x19E0} [BIPP] Received status DELIVERED, Tag=225
[40392] (01/15 10:16:51.748):{0xB04} {Test User/Domain} SRP: TID=225, type PIMSYNC returned DELIVERED
[40000] (01/15 10:16:51.748):{0x19E0} [BIPP] Received datagram, Tag=436
[40076] (01/15 10:16:51.748):{0xB04} {Test User/Domain} SendStatusToWirelessNetworkUsingSRP SendQ, TID=436
[40400] (01/15 10:16:51.748):{0xB04} {Test User/Domain} Received datagram with content type agent_sync, TID=436, for user Test User/Domain
[40000] (01/15 10:16:51.748):{0x1F9C} [BIPP] Send status DATA_ACCEPTED, Tag=436


The only activity that occured was an internal communication to the Sync server, and what looks like a request to perform a handheld agent sync. Note that both transactions appear to show successful connections with the device, however these are only internal communications between components on the BES server itself. Perhaps the time of successful internal communication is being reported?

In any case, it appears that this is quite a bogus field which provides no useful real world information.