Running setup.asp for the first time

Hopefully, if all has been installed correctly, you should have been able to browse to http://berrystatsserver/berrystats and were redirected to setup.asp.

This initial setup page helps you configure the most basic options for BerryStats: Your BES database server, the name of the BES database (usually 'BESMgmt'), a SQL id to read the database and the password.

Note, as of .59b, a new field called 'port' was added.  Typically, you won't need to use this field, as by default, the BESMgmt database is available via named pipes.  But, if you do need to add a port number, the default value is 1433.


You have the ability to click 'Test connection' on this page to verify your SQL logon credentials.  If all is successful, you can click 'submit' to move on to the main BerryStats site.

Built-in local accounts and passwords

User:	Username:	Password:	Permissions:
Administrator	admin	admin	Full permissions over BerryStats (user must know BesUserAdmin Client password for some tasks).
Helpdesk	helpdesk	berrystats	Access to reporting and user information and user admin tools by default.  This can be customized to meet the needs of your environment.  No access to BerryStats configuration page.
Reporting	reporting	berrystats	Access to reporting and user information only by default, but this can be opened up for more admin-related tasks if necessary.  No access to BerryStats configuration page.

The built-in accounts also describe three out of the four roles that are used in BerryStats, with the fourth being 'Self-service'.  Self-service users includes any LDAP (Windows 2000 Active Directory and above) account that fall into the group configured in the 'Self-service group' field on the admin page. 

Note that the Self-service role is not active until LDAP is enabled in the BerryStats configuration page.

The main page (index.asp)

* Menu option will only be available if the logged on user account has a BlackBerry assigned to them (LDAP must be enabled for this feature)
** Menu option will only be available if the BESUserAdminClient password has been configured in the BerryStats configuration page

BerryStats configuration page (admin.asp)

This should be your first stop after reviewing the BerryStats index page.  The configuration page features a number of options that need to be configured in order for you to fully utilize all the capabilities of BerryStats.

Database server: This is the server IP address or hostname where your BES management database resides.  You must have named pipes enabled on the SQL server configuration (enabled by default if using SQL - you will have to enable it using MSDE or SQL Express).

Name of instance/DB:  This is the name of the database instance - in most cases, it is BESMgmt by default.

Port number (not shown/new as of .59b): The number of the SQL port that the server is using, if you are not using named pipes (by default, it is configured this way).  Default port is 1433

SQL DB user: The name of the read-only SQL user you created during the installation.p

SQL DB password:  The password for the read-only SQL user account.

BESUserAdminClient password:  You need to install the BESUserAdminClient utility on the BerryStats server in order to perform user administrative functions.  Also, you must install the BESUserAdminClient service on any server where a user resides if you wish to administer the properties of that user.

If the BESUserAdminClient password field is empty, various menu options and other features will be unavailable to you in BerryStats.

Show all users at startup:  When set to 'true', all users in your BES management database will be queried when you access the 'Find user' page.  If set to 'False', you will need to type in a username in the 'Find user' query field before any results will show up.

SMTP mail server:  The IP address or hostname of the server running an SMTP mail server. 

Activation text:  This is the text that is sent to a user when added to the server via BerryStats.  The user email (username) and password will be appended to the end of the email.

Reply-to email address:  This is the sender id that is used while sending out an Activation email from BerryStats.

Use LDAP authentication:  If set to 'True', BerryStats will attempt to use your Windows userid & password to gain access to the various tools.

LDAP root:  The root LDAP string where your LDAP lookups will start at.  Typically, 'dc=yourdomain, dc=com' will work, but you will need to check with your network administrators to be sure.  Another possibility is domaincontroller/dc=yourdomain,dc=com (or some variant of this).

User account:  The active directory user account in the form of 'user@yourdomain.com' or 'yourdomain\user' to perform all LDAP lookup requests while users are logging in.  This user account must have the ability to read group memberships for user objects in the domain!

User password:  The password for the active directory account used for LDAP authentication.

BerryStats admin group:  The domain group (same domain as BerryStats server) that will allow all of its members full administrative access to BerryStats (admin role).

Helpdesk group:  The domain group that will allow all of its members to log in without full administrative capabilities, but can be assigned various functions.

Reporting group:  The domain group that will allow all of its members to log in without full administrative capabilities, and by default has no user administrative functions.

Self-Service Group (not shown):  The domain group that will allow all of its members to log in and administer their device only. (.59+)

Enable debug mode?  When set to 'True', a new pane will appear in the footer of all the pages showing various statistics and other variables.  This is useful for troubleshooting and bug reports.

For the sake of the rest of this document, we will assume that LDAP is configured and working correctly within BerryStats.

Finding a BES user

To perform any user administration functions or view user's device details, you must search for them via the BES database.  You can do this by using the 'find user' page.  This page is basically a SQL query with an input field - you simply type a value in the field provided and press 'enter' - you will be returned with a listing of users that match the criteria - you can then click the username or PIN to get to the user details.

Currently, the search queries the BerryStats view using the following fields: Displayname, PIN, LastNetwork and ModelName.

By default, all the users will show up on this page when you first open it (before running your search), but you can adjust this behavior from the BerryStats configuration page.

.59 introduced a a more granular search mechanism, and allows you to perform a specific search for a username, device model, device PIN, user Email address and cell phone carrier.

Also, you can select various operators to bring you your desired search results.  You can select 'is exactly', 'contains', 'starts with...' and 'ends with...'

User information page (lite.asp)

The user information page has a device picture and a rundown of the user/device statistics, including device model, display name, message statistics, assigned IT policy, group name, BES server and much more.

From the user details page, you can access all the user administration functions assigned to your role by hovering over the 'User' menu.

When you click any of the functions, a pane will appear at the bottom of the user details page with further instructions.

Activation password

Set an activation password for a user - this option is handy if a user is currently added to the BES server but has not activated yet (and forgot their current password).

Owner information

Use this function to set a name and other information for the device when it is locked.

Send PIN message

This function can be used to send a PIN message to the device directly from the BES server (the sender ID is 'BlackBerry Administrator').

Send EMail message

This function can be used to send an EMail message to the device directly from the BES server (the sender ID is 'BlackBerry Administrator').

Assign user to group

You can use this to assign a user to a particular user group.  BES version 4.1.x and up.

Assign IT policy to user

Use this function to change the IT policy for a user.

Device password

Set the device lock password.  This command is good for situations where the user has forgotten their device password.

Resend Servicebook

This will resend the servicebooks to the device, helpful for synchronization troubles.

Resend peer to peer key

This will resend the peer-to-peer encryption key from the server to the device, allowing secure communications from the device to other devices within your BES environment (specifically, PIN communications).  The BES server must have a peer-to-peer key set in order for this function to work.

Show handheld apps

Lists all handheld applications installed on the device.

Show handheld modules

Lists all handheld modules installed on the device (this would include core modules like 'net_rim_app_manager, etc.)

Disable redirection

This function disables all email redirection to the device.

Enable redirection

This function enables all email redirection to the device.

Delete user

This will delete the user from the BES server.

Kill handheld

This will delete all data from the device and disable all communications between the device and BES server.

Adding a user to the BES server

Hover your mouse over the 'User' menu option in the navbar at the top.  A drop-down menu will appear.  Click 'Add user'. 

If you do not see the 'Add user' option, then you need to specify a BESUserAdminClient password in the configuration screen.  Also, verify that you have the appropriate BESUserAdminClient function assigned to your role in the 'Roles administration' page.

The add user page will bring up a drop down list for BES server and IT policy (soon to come: User group).  You can select which BES server or IT policy (listed in the BES DB) you would like to assign to the new user.

In the unique email address, you must type the email address that is assigned to the user being added.  If you aren't sure what the email address is, you can search for their name in the 'Search for network user' field below (only shown when LDAP is enabled).  Once you searched for the user and a result is brought back, you can click on their email address and it will auto-populate the email address field for you.

If your search never brings up any results, verify that you have a valid SMTP address entered into the 'mail' field in the user's Active Directory object properties.

Next, enter an activation password.  Note that if you use numbers in the password, the user will have to press the shift key on their device to type them when activating.  If you would rather have BerryStats send out a random password of 8 letters, you can fill the 'Use random password' checkbox, which will disable the 'Activation password' textbox.

NOTE:  You can control whether or not a helpdesk or other role can deselect the random password option for security reasons, by configuring the role-based permissions.

Select a valid activation timeout.  This is the number of hours that the activation password will be valid.  Once the time expires, the user will not be able to activate.  You must then reset their activation password once again.  Valid choices are 48, 72, 96, 120 and 270 hours.

Select whether to send an activation email to the specified user address when they are added to the server.

Click 'Submit' to add the user - usually this process takes around 2-10 seconds.

Reports

The 'Reports' drop-down menu selection comes with three options:

• BerryStats Classic
• Custom reports
• Run SQL Query

BerryStats Classic (advanced_query.asp)

BerryStats Classic is the latest rendition of the original BerryStats web-view introduced by Brent Grim back in 2005.  The code has been updated slightly (but not much) only to integrate it with the current version of BerryStats.

This web-view is based entirely on the BerryStats view that was created in the BES management database, so there are no references to any external tables or fields at all.

The page is made up of three sections: Search, Query results and Field chooser. 

The search pane allows you to enter either a username or PIN into the search criteria - when you press [enter] or click 'Refresh/Show Data' - while the Query results pane will update with a sortable/filterable table.  You can click on either a username or PIN to take you to the user details page (lite.asp).

At the bottom of the page you can adjust which fields you would like to select or deselect when bringing up the query results in the Field chooser pane.  Any time you want to update the columns, you select the or deselect the checkboxes and press the 'update results' button.  Alternatively you can select all fields or deselect all fields if you wish to start from scratch.

Exporting data from BerryStats Classic

Viewing this data is nice, but perhaps someone (like management) would like to view it in Excel or another XLS-capable spreadsheet application?

You can click on the 'Export full details' link at the bottom of the Query results pane to save the query results to an XLS file (CSV coming soon).

Custom reports (sql.asp)

The newest reporting feature to be added to BerryStats is the 'Custom reporting' option.  Custom reporting allows you to create any number of custom SQL definitions and save them in the 'queries' subfolder, making it possible to easily update and customize BerryStats to any length you see fit.

The custom reports page shows you a sortable/filterable table of all the custom SQL queries saved in the /queries subfolder in the BerryStats web structure.

Each report definition is saved in an XML file, and can easily be customized or copied to create new reports.

A number of reports are included in the BerryStats distribution, including:

• Device model distribution
• Device application modules
• Device operating system distribution
• Users not update for past 7 days
• Server CAL packs (good for disaster recovery purposes)
• and more...

Exporting custom report data

At the top of the custom reporting page is an option which allows you to select whether you would like to export the report you are about to run to a Microsoft Excel (XLS) file.  If you do not select this option, you will be shown the query results in a sortable/filtered table format.

Show results on multiple pages

When selected, the results of the query will be contained within a table broken up into smaller, manageable pages.  This makes it a little easier to view larger amounts of data.

Creating your own custom report

Creating your own custom report is not difficult - the category, title, query, description and filtered field selections are all stored within an XML file.

XML Structure

The Query node is made up of five attributes:

• Category - The general category for this report.  Thes are arbitrary.
  
• Description - Quick description of the report.
  
• Title - Title of the report...the shorter, the better.
  
• SQL - The complete SQL query that produces the results of the report.
  
• Filter - A comma (no spaces after the commas) separated list of fieldnames you wish to enable for filtering via drop-down selection boxes.
  

As you can see, the majority of the XML definition is made up of the SQL query itself. 

The XML attributes above are pretty self-explanatory, with the exception of the Filter attribute.  Enter a comma-separated (no space) list of fields you wish to enable with a drop-down filter selection.  For example, in the above query, if we want to create drop-down filters in the results for DisplayName and KeyGenExpiryTime, you would set the Filter attribute to "DisplayName,KeyGenExpiryTime".

The XML attributes ARE case-sensitive.

Hint: Try your queries out in a SQL server management tool like EMS SQL Manager for SQL Server Freeware or Microsoft SQL Manager Express.

Subqueries (queries linked from report results)

In some reports, you will find another node called 'Subquery'.  This is a secondary query that is linked from a field from the results of the first query. 

In this example, let's look at the report called 'User IT Policy Distribution'.

<?xml version="1.0"?>
<BerryStats>
    <Query Category="User" Description="List all IT policies and count of users who have been assigned to each policy." Title="User IT Policy Distribution" SQL="SELECT DISTINCT PolicyName, COUNT(*) AS Count_Assigned, ITPOLICY2ID AS ITPolicy_ID FROM USERCONFIG INNER JOIN ITPOLICY2 ON (USERCONFIG.ITPOLICY2ID=ITPOLICY2.ID) GROUP BY USERCONFIG.ITPOLICY2ID,POLICYNAME" Filter="PolicyName"/>
    <Subquery Keyword="PolicyName" SQL="SELECT DisplayName, MailboxSMTPAddr as SMTP_Email_Address, PIN FROM USERCONFIG INNER JOIN ITPOLICY2 ON (USERCONFIG.ITPOLICY2ID=ITPOLICY2.ID) WHERE [Keyword] = '[KEYWORDVALUE]'" Filter="DisplayName,PIN"/>
</BerryStats>

So, the 'Subquery' node is made up of three attributes:

• Keyword - The name of a returned column name from the first query
• SQL - The query that will be based upon the Keyword from the first query
  
• Filter - A comma (no spaces after the commas) separated list of fieldnames you wish to enable for filtering via drop-down selection boxes.

The SQL attribute is almost the same as the primary query with the notable exception of the [keyword] and [keywordvalue] definitions.  In the above primary query, we are returning a list of the IT policies that are being used on the BES server.  Since we would like to know who has the listed policies applied, we are going to create a link from the 'PolicyName' [keyword] field from the first query.  Each returned result listed in the 'PolicyName' field is considered a value - so is termed as [keywordvalue].  From the table of results listed after running the primary query, clicking the name of an IT policy will tell BerryStats to run the subquery...i.e. looking for all the users who have that policy name [keywordvalue] applied.

Below is the result of running the subquery linked from the Keywordvalue (in this case, users who are currently using the IT Policy with the PolicyName [keyword] value of 'IT Test' [keywordvalue]).

BerryStats will by default link the PIN or Displayname to the user details page.

Having a subquery defined in your XML report definition file is not required, but it is a nice additional feature to allow you to "drill down" from report results.

At the bottom of each report is a pane showing the actual query being run.  You can copy and paste this query into a SQL management tool to tweak to your liking.

Run a SQL query

In BerryStats .57, a new feature was added which allows you to run your own SQL query and output it's results to the screen.

Since this could open up any data in the BES management database, it is highly recommended that you limit this page to the administrator role (you can specify which role can access this page in the Roles administration page).

Here you can enter text into the textarea field, and either choose to 'highlight code' or submit the query for BerryStats to run.

The results will show up like any other report, with displaynames and PINs being linked back to the user/device details page.

If you decide to highlight the code, you will see a color coded view of your SQL.

NOTE:  During the setup you used a SQL account that had READ access only.  If you attempt to run update, delete, or create SQL statements here, they will not work.  If you use an account with full permissions, BerryStats will not stop you from running potentially destructive queries/statements.

User roles and granular assignment of user administration functions

Earlier, we looked at the BerryStats configuration page underneath the 'BerryStats .ASP' menu option.

Below this selection is a page that allows you to configure options for your specific roles in BerryStats.  Click on the "Roles Administration" link.

 

This is a list of all functions that can be enabled or disabled for each user role, including 'Self-Service'.

Most options here relate to the BESUserAdminClient tool - these options appear under the user information (lite.asp/'My BlackBerry') page.

 

One notable difference with regards to the options:

Any user administration function for self-service users will apply to the self-service user ONLY.  These users cannot apply any user admin function to any other device other than their own.

 

This information is stored within the 'configuration.xml' file.

 

 

The Self-service administration menu option is ONLY available while you are working in the roles administration page.  This can be found under 'BerryStats .ASP' underneath the 'Roles adminstration' menu item.  Alternatively, you can click the text 'Self-service' in the user roles table header to get to this page.

 

The purpose of the Self-service administration page is to provide a way for BES administrators to customize the self-service options for the end-user while they are in 'self-service' mode while logged into BerryStats.

 

Currently you can only tweak the 'Add user' function of Self-service.

 

New as of .58, you can uncheck this checkbox if you wish to disable self-service access.  If disabled, the end user (who is not a member of the admin/reporting/helpdesk or self-service role) will see a message stating that self-service has been disabled, and will only have a 'logoff' option available to them.

 

 

Allow self-service users to select IT policy/server when activating?  Allow/Disallow.  In most cases, you will want to pre-define the IT- policy or server that the user will be assigned when they add themselves to the BES server (Disallow).

 

Default IT policy/Server assigned to new activations via self-service:  Set this to the desired IT policy or server.  This should populate with a list of configured IT policy names/servers already configured on your BES server.

 

Hide IT policy/server selection from self-service user?  As the title suggests, this will hide the IT policy/server selection drop-down from the end user while adding themselves to the BES server.   Some BES administrators may want to hide the names of the policies, especially if they contain words like 'restrict' or 'disallow', etc.

 

 

Allow self service users to choose to send activation email?  Allow/Disallow.  If set to disallow, the user will receive an activation email no matter what (yes/no option will be disabled on the add user screen).

 

This information is stored in the selfservice.xml file.