BerryStats - .ASP 0.5x qc_metal edition, 09/02/08

Original version and code courtesy of Brent Grim at http://www.thegrims.com/berrystats

PHP version is available, see link at bottom of Readme for blackberry forums thread.

 This is Brent Grim's original "the why" for creating the first version of BerryStats. 

"I felt like there needed to be an easy way to view this information and provide it to our support technicians and cell contract administrators who wouldn't have access to it through the console. The support technicians can use it to see last contact times and to verify that a user is on the BES. The cell contract admins can verify that devices are not being dormant and can use it to keep up with inventory."

Requirements

Server

• BES 4.x or above
• ~11Mb disk space on server
  
• SQL Enterprise Manager (doesn't need to be on BES)
• IIS on something (preferably your BES server for best results), running ASP (check add/remove programs> windows components to verify this is configured).  Works best with 32-bit IIS, although a workaround has been found for 64-bit.
• Some knowledge of ASP and SQL

Client

• Internet Explorer 6.x or above (BerryStats doesn't play nice with Firefox yet)
  
• BlackBerry browser

User Administration

• BesUserAdminClient tool installed on IIS server, and BesUserAdminClient Service installed on BES servers you wish to administer

Setting up the database

Note: If the BesMgmt database is MSDE, it can still be accessed using SQL Enterprise manager or SQL Server Management Studio Express.

1.   Convert Authentication of SQL Instance on BES Server over to SQL Server and Windows Auth

2.   Right click on your BES (default is ‘Besmgmt’) instance> 'Properties'

3.   Click the Security tab

4.   Select "SQL Server and Windows"

5.   Click 'OK'

Create a read only SQL ID for the webpage

1.   Expand the tree structure, and go to Security and Logins

2.   Right click logins> 'New login'

3.   Type in a unique name

4.   Change this logon ID authorization to "SQL Auth"

5.   Type in a password

6.   Click the Database Access tab

7.   Check BESMgmt

8.   Check 'DataReader' (if using 4.1 BES, instead check 'rim_db_admin_enterprise')

9.   Click OK

Create the BerryStats view

1.   Right-click 'Views'> 'New view' - if prompted to select tables, etc. - hit 'Cancel.' 

2.   In the field provided, copy and paste the contents from the supplied 'view.txt' from the ‘views’ folder- NOTE, be sure to select the proper view for your environment (Notes v. Exchange and BES 4.0.x vs. 4.1.x) – the Domino and Groupwise views are the same, however I have no way of testing the functionality of Groupwise.

3.   Right-click in the field after pasting the text, and click 'Execute' SQL (This may differ depending on what DB management tool you are using).

Configuring BESUserAdminService – required if you wish to add/remove users or adjust any user details

1.   Download and install the BESUserAdminService on your BES server.  

o  Configure the service for all your BES servers that are using the database that Berrystats is pulling information from (see the doc that comes with the tool).  The BESUserAdminService is part of the BES Server Resource Kit.  Download it from: http://na.blackberry.com/eng/support/server_resourcekit.jsp

o  Configure the service to run with SQL authentication (for backwards compatibility), using the user account you created in step 1. in ‘Create a read only SQL ID for the webpage’.

2.   Copy the BESUserAdminClient.exe tool into the %windir%\system32 folder of your webserver.  Make sure you have given your users execute permissions to this file (right-click the file > ‘security’).

3.   Browse to http://yourwebserver/BerryStats/admin.asp and configure your password option.

4.   Copy the file 'dynu.dll' to the web server's %windir%\system32 folder

o  Register dynu.dll using regsvr32.exe (hint: you can [shift]+right-click and 'Open-with' and browse for regsvr32.exe from the %windir%\system32 folder)

o  Give Internet users read/execute permissions on dynu.dll - this will allow the page to run the UserAdminClient tool within the context of .ASP.

Regarding the dynu component, this was a part of a now-defunct set of asp tools from http://www.dynu.com.  This is a freeware component, and as such, is licensed to distribute freely.  If you prefer, you can instead instantiate the shell object to execute the file - it's not quite as clean, but it does work (note, the code has to be written and entered to do this).

Installing the web pages

For the sake of this document, I will use a default install of IIS as an example (i.e. where 'inetpub' is in the root of c:\)

1.   Create a new directory at c:\inetpub\wwwroot called 'BerryStats' (i.e. c:\inetpub\wwwroot\BerryStats')

2.   Create a new directory under c:\inetpub called 'Config' (i.e. 'c:\inetpub\config').  

3.   Copy setup.xml, selfservice.xml and configuration.xml to the newly created ‘Config’ folder.  

4.   Adjust the folder permissions for the Config folder so that everyone has write permissions, so they can change administrative options via the web interface (this is locked down by BerryStats logon credentials)

5.   Adjust the folder permissions on the BerryStats folder structure so that ‘Domain users’ has read permissions.  You may want to set up appropriate modify permissions for those who might manually update the xml queries in the ‘queries’ subfolder, but this is not necessary at this point.

6.   Extract all other files to your folder that you created in step 1. (i.e. global.asp and other files should be in this folder).

7.   Open the computer management MMC by right-clicking on 'My Computer'> 'Manage'.  Expand 'Services,' then 'Internet Information Server,' then 'Default Web Site.'

8.   Create a new IIS virtual directory called 'BerryStats,' point it to your newly created 'BerryStats' folder.  

9.   Right-click the BerryStats virtual folder under ‘Default Web Site’> ‘Properties’

10. From the ‘Directory’ tab, click ‘Create’ to create an application pool for BerryStats.

11. Click the ‘Configuration’ button.

12. Click the ‘Options’ tab.  Make sure that ‘Enable parent paths’ is enabled.  Click ‘Close’.

13. Click the Documents tab.  Add 'index.asp' and remove all other document names, click ‘Apply’.

14. Click the ‘Directory Security’ tab.  Under ‘Authentication and access control’, click ‘Edit…’

15. Disable ‘Enable anonymous access’ and enable ‘Integrated Windows authentication’.  Click ‘OK’.  If you want to run BerryStats on a BlackBerry, you must enable ‘Basic authentication’ as well.

16. Click ‘OK’ to close out of the management console.

17. With notepad or similar app, edit the Config.asp page.  Make sure the following line refers to the appropriate folder where your XML file is stored.

                         strConfigurationPath = (Server.MapPath("../../config/"))

                Note that the single set of "../" means one folder ABOVE the current folder where global.asp resides.
               

Setting up BerryStats for first time use

1.   From a web browser, go to http://yourwebserver/BerryStats/setup.asp.

2.    Enter the DB server name, DB instance, DB username and password into the provided fields.  This page updates the configuration.xml file.

3.   Click ‘Test connection’ if you want to verify your settings, or click ‘submit’ to continue to the admin page where you can set the rest of your options.

 Use the default admin account and password to gain administrative access to BerryStats:

These values are stored in the setup.xml file.  Currently, you must edit this file manually to alter the settings.  This will change with later versions.

Configuring BerryStats (admin.asp)

Click on ‘BerryStats’> ‘Configure’ and set your options.  If you are NOT using the BESUserAdminClient tool (see below) then leave the password field blank.

You should be ready to go.  If you have troubles accessing some data, you might consider opening up read permissions in the database for the account you created in the first section.  No part of this asp code alters the SQL data in any way, so using the DB admin account is safe.

Troubles?

Enable the debug option in the configuration page and try to recreate the problem you ran into initially.  You should see a variety of variables and other information at the bottom of the pages in BerryStats.  Post your problem on BlackBerryForums.com.

Modules used in BerryStats/Credits (individual respective licenses apply)

• FusionCharts Free for Flash-based charts
• Vista_Toolbar code for main toolbar buttons (highly modified)
• TinyMCE for inline rich-editing for activation email on admin page
• HTML Table Filter by Max Guglielmi
• SQL code highlighter by alex gorbatchev