Hack 84. Query the BES 4.0 Database

Version 4.0 of the BES and the handheld added some excellent capabilities for reporting. Run reports based on model number, code version, and even applications installed on devices.

If you are the curious type and you ever looked into the schema of the BlackBerry Enterprise Server's database before 4.0, you probably noticed something striking. The database was hardly used for anything! Before IT policies were introduced as a feature, there were only two tables in the BESMgmt database. This stands in stark contrast to what exists in the 4.0 database schema. The 4.0 database is used to a much greater degree my installation contains no fewer than 63 tables!

Where was all this stuff stored in previous versions? In many cases, the feature that the data applies to just didn't exist. Other types of data were stored in the BlackBerry service account mailbox in the hidden folders. With this shift to storing all data in SQL, this opens up all kinds of opportunities for running reports using this central database.

One nifty feature of 4.0 is that along with syncing all the different PIM items (mail messages, contacts, tasks, etc.) over the air, it also syncs other valuable information that administrators might find interesting. For example, the handheld system software version number and the device model number are both stored in the database. Here's a script to produce a report on some of the more interesting statistics that BlackBerry administrators didn't have access to in previous versions. This requires that you be logged onto a computer with an account that has access to the BESMgmt database on the SQL server.

7.14.1. The Code

 use DBI; use strict; my $SERVER = "bes.server.com"; my $DSN = "DBI:ODBC:Driver={SQL Server};Server=$SERVER;Database=BESMgmt"; my $dbh = DBI->connect($DSN); if (not $dbh) { print $DBI::errstr,"\n"; die; } my $sql =<<"END"; SELECT u.displayName,u.PIN,s.modelname,s.PlatformVer,s.AppsVer, s.PhoneNumber,s.IMEI,s.HomeNetwork,s.PasswordEnabled,s.FlashSize ,s.ITPolicyName FROM SyncDeviceMgmtSummary AS s INNER JOIN userconfig AS u ON u.id = s.UserConfigId ORDER BY u.displayName END my $st = $dbh->prepare($sql) || die $DBI::errstr; $st->execute; my @headings = qw( DisplayName PIN ModelName PlatformVer AppsVer PhoneNumber IMEI HomeNetwork PasswordEnabled FlashSize ITPolicyName ); print join("\t", @headings),"\n"; while (my @fields = $st->fetchrow) { print join("\t", @fields),"\n"; } 

The code has a few prerequisites. You have to install ActiveState Perl (http://www.activestate.com) and some optional Perl modules: DBI and DBD:: ODBC. It's best to use the Perl Package Manager to install the required modules by running the following commands: ppm install DBI and ppm install DBD::ODBC.

7.14.2. Run the Code

Save the code in a file called deviceinfo.pl. From a command prompt, change directory (cd) to the directory where you saved the file and type the following command to run the code.

 C:\>perl deviceinfo.pl 

7.14.3. Output

In your command window, you should see a tab-delimited list of your BlackBerry users along with the following statistics for each.

• Display name of the user

• PIN

• Model number

• Platform version

• Application versions

• Device phone number

• Home network

• Password featured enabled

• Flash memory capacity

• Current BlackBerry IT policy in effect on device

To redirect the output of the script to a file named output.txt, simply run the following command:

 C:\>perl deviceusers.pl > output.txt 

This creates a tab-delimited file that can be easily viewed in a spreadsheet program such as Microsoft Excel.