Hack 81. Export User Stats to a Text File

Use the BlackBerry User Administration client to export the current users on your server to a text file.

Research In Motion supplies a far underused utility for performing user administration from a command prompt. The BlackBerry User Administration client can be used to add users to the BES, remove users, and export the users along with some BlackBerry specific statistics to a text file suitable for viewing and manipulating in a spreadsheet program. Starting with BES 4.0, BlackBerry User Administration has been enhanced to perform several additional tasks that were not previously possible from the command line.

The BlackBerry User Administration software used to come as an optional component of the BES installation. Starting with BES 4.0, it is no longer included with the server software, but is still available for download as part of the BlackBerry Resource Kit (http://www.blackberry.com/support/downloads/resourcekit.shtml). There is a service that must be installed on your BlackBerry Enterprise Server and a client utility that is also installed on the server, but can be copied to other servers without BES installed.

7.11.1. Install the Service

For versions of BES 3.6 and earlier, you can install the BlackBerry User Administration client from the BlackBerry Enterprise Server installation. You will need to create MAPI profiles on your machine similar to the profiles that you created for the BlackBerry Service account mailbox, or you can use the same profile as the service account. There is documentation that comes with the installation files.

For Versions 4.0 and greater, the BlackBerry User Administration client is no longer bundled with the server software, but with the BlackBerry Resource Kit. Simply unzip the archive to whatever location you'd like on the BlackBerry Enterprise Server, although a convenient directory in which to install it is alongside the BES install in:

C:\Program Files\Research In Motion\BlackBerry Enterprise Server\BRK

7.11.2. Export User Stats with the 4.0 Version

To export user statistics from a BES running 4.0 or greater, bring up a command prompt, cd to the directory where besuseradminclient.exe is located, and type the following command, substituting the values for your environment for the variables in all caps.

 C:\>besuseradminclient  n COMPUTER  b BES  p PASSWORD  stats  users 

At first glance, the output from the command doesn't look very helpful. You should get output similar to the following.

 BlackBerry User Administration Client Version 4.0.1.8 Copyright (c) Research In Motion, Ltd. 1997-2004. All rights reserved. Modification date: May 2 2005 besuseradminclient -n COMPUTER -b BES -p PASSWORD -users -stats BESUserAdmin::main - Log Start BESUserAdmin::DoListUsersStats - Listing users stats… User name,MailBoxDN,ServerDN,PIN,Device Type,State,Message Server,Forwarded,Sent,Pending,Filtered,Expired,Status,Last fwd time,Last sent time,Last contact time (h),Last result,SMTP Address,Mobile Data Service,OTA Calendar,ITPolicy name,ITPolicy status,ITPolicy time sent,ITPolicy time received,Wireless Message Reconciliation "Mabe, David M",/o=org/ou=site/cn=Recipients/cn=dmabe,/o=org/ou=site/ cn=Configuration/cn=Servers/cn=WIN2K3/cn=Microsoft Private MDB,20000008,GPRS,Enabled,WIN2K3,14,0,0,0,0,(**WIN2K3) Running,Wed Jun 01 13:14: 52 2005,Wed Jun 01 13:14:35 2005,2.77,,SMTP:dmabe@domain. com,Enabled,Enabled,Default,Applied successfully,Mon May 30 10:13:32 2005,Mon May 30 10:13:33 2005,Enabled BESUserAdmin::DoListUsersStats - …done. BESUserAdmin::main - Log End 

The command prints its log messages to standard error while printing the actual user statistics to standard out. This allows you to separate the different outputs fairly easily. The following command redirects standard error (the debug messages) to the Windows equivalent of Unix's /dev/null (i.e., the bit bucket) and redirects the actual output we want to a local file (you must type this command all on one line):

 besuseradminclient  n COMPUTER  b BES  p PASSWORD  stats users > users.csv 2> NUL 

This command should print no output to the screen and it should have created a file called users.csv in the current working directory. This file should contain the user statistics from all users on the BES. You can repeat this command for each BES in your environment if you have multiple BES instances.

7.11.3. Export User Stats with the 3.6 Version

The command to run to export user statistics with the 3.6 (and earlier) version of BES is significantly different. The filename is different along with the output format and command-line options that are required. To create an export, run the following command in a prompt in the same directory as the bbuseradminclient.exe file (note the different filename in this version).

 C:\>bbuseradminclient  n COMPUTER  b BES  x 

You should get output similar to the following:

 BlackBerry User Administration Client, Version 2.1 Copyright (c) Research In Motion, Ltd. 1997-2002. All rights reserved. Modification date: Oct 19 2004 Users currently on BES BlackBerry Server: Mabe, David M PIN: 20000008 Device Type: GPRS State: Enabled Exchange Server: EXCHANGE_SERVER Forwarded: 8042 Sent: 77 Pending: 0 Filtered: 59 Expired: 0 Status: Running Last fwd time: Wed Jun 01 07:02:32 2005 Last sent time: Wed Jun 01 06:47:02 2005 Last contact time: 00:03:39 Last result: Delivered to handheld External Services: Enabled OTA Calendar: Enabled ITPolicy name: Default ITPolicy status: Applied successfully ITPolicy time sent: Fri Feb 18 22:22:27 2005 ITPolicy time received: Tue May 24 10:40:04 2005 Wireless Email Reconciliation: Enabled bbuseradminclient.exe finished 

It will list the users on the BES you've specified and print out a bunch of statistics that pertain to the BlackBerry service. At first glance, this looks like a pretty readable and convenient format. However, the instant you try to bring up the output in a spreadsheet program to do something useful with it, you'll realize what a silly format this is.

7.11.4. Parse the Output

If it can't be automated, I'm not interested in it. That's why I wrote some Perl code to convert this ridiculous format into a more reasonable tab-delimited format. Type the following text into your favorite text editor and save the file as bbuseradmin.pl. You'll probably need to substitute the correct path for the $BB_USER_ADMIN variable unless it is in your path.

 use Getopt::Long; use strict; # change this if it's not in your path my $BB_USER_ADMIN = "bbuseradminclient.exe"; my $BES = ""; my $COMPUTER = ""; my $FILE = ""; my $INPUT = ""; GetOptions( "bes=s" => \$BES, "computer=s" => \$COMPUTER, "file=s" => \$FILE, "input=s" => \$INPUT, ); my $output_file = $FILE || "output.txt"; my $temp_file = $INPUT || "temp.txt"; my $command = "\"$BB_USER_ADMIN\" -n $COMPUTER -s $BES -u > $temp_file"; if (not $INPUT) { my $return_code = system $command; if ($return_code) { print "Uh-oh. The command was unsuccessful. " . "Return code was: $return_code\n"; die; } else { print "Command was successful. Creating csv file…\n"; } } my @header_row = ( "Display Name", "PIN", "Type", "State", "Server", "Forwarded", "Sent", "Pending", "Filtered", "Expired", "Status", "Last fwd time", "Last sent time", "Last contact time", "Last result", "OTA Calendar", "External Services", "ITPolicy name", "ITPolicy status", "ITPolicy time sent", "ITPolicy time received", "Wireless Email Reconciliation", ); if (open FILE, $temp_file) { my $line_count = 0; open OUTPUT,">$output_file" || die "Can't open $output_file for write: $!"; print OUTPUT join("\t", @header_row),"\n"; my $cached_text = ''; my $names = 0; while (<FILE>) { chomp; $line_count++; next if ($line_count < 6); if (/bbuseradminclient\.exe finished/) { last; } if (not $_) { $cached_text =~ s/\t$//; next; } if (/^[^\t]/) { $names++; $cached_text .= "\n" if $names != 1; $cached_text .= $_ . "\t"; } else { $_ =~ s/^\t[^:]+:\s?//; $cached_text .= $_ . "\t"; } } print OUTPUT $cached_text; close OUTPUT; close FILE; } 

To run the code, type the following command, substituting the appropriate command-line options for your environment.

 C:\>perl bbuseradmin.pl --bes BES --computer COMPUTER 

This command runs the bbuseradminclient.exe utility with the appropriate options and redirects output to a temporary file. It then opens the temporary file, parses the text for each user, and massages it into a tab-delimited line. It writes the output to a file named output.txt in the current directory. You can easily open the resulting file in Microsoft Excel.