I stumbled across a good-looking bit of software called PINpoint by TENET that produces a list of users registered on a BlackBerry Enterprise Server (BES) and pushes that to the devices. In effect users then have a company BlackBerry directory from which they can look up email addresses, telephone numbers and even PIN numbers. Since the software is a bit pricey and I'm operating on a shoe-string budget, there was no way I could purchase that for our environment. Instead I attempted and succeeded at producing similar information, admitedly without the elegance of afore mentioned software but also without its price tag.

My version is not half as good looking as the commercial program, but it does the job. Oh, and you do always get what you pay for...

main screen dial-a-number

What I Used

Our BES runs atop of Lotus Domino, so in the following I'll have one or Lotus specific details, but the general principle will probably run on a Microsoft Exchange or Novell Groupwise-based BlackBerry Enterprise Server. All of these use a dedicated SQL server or, in smaller environments, an dedicated MSDE database server, into which the BES writes details concerning the devices configured to use that BES as well as its users.

The code below is not guaranteed to survive future versions of a BES; if RIM changes the database schema, the code will either have to be adapted, rewritten or even scrapped entirely. I developed and tested the code on a version 4.1 BES, but as far as I know, it should also work on a version 4.0, and perhaps even earlier versions. Your mileage may vary, and I have no way of testing that.

I've tested the code below on a BES running atop Windows 2003 SP2 with a copy of Active Perl for Windows as well as the DBI and ODBC modules installed. To convert the XML to XSLT, I used the XML C Parser and Toolkit which is also available for Windows, including a download section for binaries.

Getting the Details

The database schema used by the BES is undocumented as far as I can tell, but I knew that the information must be in there somewhere. If you look at the BlackBerry Desktop Manager, you see the details clearly: main screen

After poking around a bit inside the SQL server, or rather the MSDE server on our BES, I found the details I needed. Apart from username, telephone number, email address and PIN (those are the fields I most badly wanted to have), there are all sorts of goodies, such as the BlackBerry device operating system version, the model number, etc. The following Perl program runs on the BES server proper and dumps the information I want into an XML format.

<?xml version="1.0" encoding="ISO-8859-1"?>
<?xml-stylesheet type="text/xsl" href="userlist.xsl"?>
 <dn>CN=Jane Public,OU=Marketing,O=Example</dn>
 <lastcontact>2006-08-30 12:06:22.000</lastcontact>
 <name>Public, Jane</name>
 <username>CN=Jane Public/OU=Marketing/O=example</username>
 <dn>CN=John Doe,OU=Marketing,O=Example</dn>
 <homenetwork>vodafone ES</homenetwork>
 <lastcontact>2006-08-31 13:06:26.000</lastcontact>
 <name>Doe, John</name>
 <username>CN=John Doe/OU=Marketing/O=example</username>

The XML should be rather self-explanatory. There is one user entry per user defined on the BES (by the way: you'll only get entries for users who have already done an enterprise activation; the reason for this is that the join in the program's SQL joins with the device table, which in turn is only filled after activation). Each user entry in turn has an element corresponding to the database field I'm interested in. Do note, that the elements only exist if the corresponding database field has a value. If for example one of the BES users hasn't set her telephone number on the device's SIM Card settings, the element phone will not exist.

The Perl program userlist.pl shown below runs on Windows and connects via ODBC with the MSDE server. The SELECT statement joins three tables to find all the attributes I want to see.

# userlist.pl (C)2006 by Jan-Piet Mens <jpmens at gmail.com>
# Produce XML with list of BES users & their devices.
# --> adjust name of interpreter on first line above!

use DBI;
use Data::Dumper;
use strict;

my $server = 'localhost';

my $DSN = "DBI:ODBC:Driver={SQL Server};Server=$server;Database=BESMgmt";
my $dbh = DBI->connect($DSN);

print "Content-type: text/xml\n\n";

if (not $dbh) {
    print "<error>" . $DBI::errstr . "</error>\n";

print '<?xml version="1.0" encoding="ISO-8859-1"?>' . "\n";
print '<?xml-stylesheet type="text/xsl" href="userlist.xsl"?>' . "\n";

my $sql =<<"END";
    u.id AS id,
    u.userName AS username,
    u.displayname AS name,
    u.PIN AS pin,
    s.MsgsForwarded AS mforw,
    s.MsgsSent AS msent,
    s.MsgsPending AS mpend,
    s.LastContactTime AS lastcontact,
    u.mailboxsmtpaddr AS mailaddr,
    u.mailstoredisplayname AS mailstore,
    d.modelname AS model,
    d.Platformver AS platformver,
    d.AppsVer AS appsver,
    d.phonenumber AS phone,
    d.HomeNetwork AS homenetwork,
    d.ITPolicyName AS itpolicy,
    d.IMEI AS imei
    SyncDeviceMgmtSummary d,
    UserConfig u,
    UserStats  s
    WHERE u.id = d.userconfigid AND u.id = s.id


my $st = $dbh->prepare($sql) || die $DBH::errstr;
$st->execute || die $DBH::errstr;

print "<bblist>\n";

my $c;
while (defined ($c = $st->fetchrow_hashref)) {
    # print $c->{username};
    # print Dumper($c);
    print "<user>\n";
    my $dn = $c->{username};
    $dn =~ s|/([a-zA-Z]+=)|,$1|g;    # normalize Lotus DN
    elem('dn', $dn);
    foreach my $k (sort keys(%$c)) {
        elem($k, %$c->{$k});
    print "</user>\n";
print "</bblist>\n";

sub elem {
    my ($key, $val) = @_;
    print " <$key>$val</$key>\n" if ($val);

It ought to be quite easy to modify the program to produce an output format of your preference, such as CSV or even to dump it into a separate database. In a subsequent article, I'll be using this XML to fill a MySQL database table.

Viewing Results on the Device

Now that we are able to produce XML output with the information we are interested in, we need to get that to the device. I'm going to use XSLT to transform the XML into HTML which can be retrieved via HTTP and viewed on the device. One word about the BlackBerry browser: do ensure that you've set it up to correctly use HTML tables and style sheets. My browser settings are main


XSLT is a transformation language which can transform XML into a different format. For the task at hand, the target should be HTML. If you put both the userlist.xml and userlist.xsl files in the same location (on your web server or even on your local computer) and open the XML with a Mozilla Firefox browser (others may or may not work), you may be astonished to see that Firefox pulls up the XML, sees that it requires a style sheet and loads and applies the XSL to the XML on the fly. Unfortunately the BlackBerry Browser cannot do that, so we'll transform the XML on the web server. The style sheet proper is shown below:

<?xml version="1.0"  encoding="ISO-8859-1" ?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">

<xsl:output method="html" indent="no"/>

<xsl:template match = "*"></xsl:template>

<xsl:template match = "bblist">

    <head><title>fupps: userdirectory</title>
    body { font: 12px Verdana,Arial,Sans-Serif; }
    h3 { color: red; }
    .pin { font-weight: bold; color: green; }
    .fon { font-size: 4; color: #eee; }
        <h1>BES User Directory</h1>
        <xsl:apply-templates select = "user" />

<xsl:template match = "user">
    <div><xsl:apply-templates /></div><br /><hr />

<xsl:template match = "name">
    <h3><xsl:value-of select = "." /></h3>

<xsl:template match = "pin">
    <div align="right">
    <!-- Build HTML href 
      ** The result of this mess will be
      ** <a href="pin:111111">11111</a>
    <xsl:element name="a">
        <xsl:attribute name="href">PIN:<xsl:value-of select='.'/></xsl:attribute>
        <xsl:attribute name="class">pin</xsl:attribute>
        <xsl:value-of select="."/>

<xsl:template match = "phone">
    <div align="right">
    <!-- Build HTML href for CTI -->
    <xsl:element name="a">
        <xsl:attribute name="href">cti:<xsl:value-of select='.'/></xsl:attribute>
        <xsl:attribute name="class">fon</xsl:attribute>
        <xsl:value-of select="."/>



Once we have the XML we can apply the style-sheet and transform it to HTML. The invocation of the xsltproc program is trivial: xsltproc -o userlist.html userlist.xml.

What this does is to call the XSLT transformation program which reads the name of the stylesheet to use and applies that to the specified XML input file. The output is written to the file userlist.html.

Note how in the resulting HTML, the links to both the phone number and the PIN use the BlackBerry-specific anchors to enable telephony and PIN recognition on the links PIN the user This transformation can be done periodically on the server by scheduling it.

How to Push the Directory to the Device?

In a future article I'll be demonstrating a technology with which to do this.


blog comments powered by Disqus