BES and SQL some Basics

qe[1]Personally I don’t have much love Black Berry, but I work with Exchange and there are many BES addicts out there so I’ve been forced to support BES throughout the years. – I start every BES topic this way just to make sure ya’ll don’t think I am starting to like it.  One of the features I do like about is BES is that all of the configuration data is stored in an easy to get to database. I’ve been asked on occasion to gather reports about user usage, user settings, ETC. With BES this is a fairly simple task. You install Query Express, connect to the BES server, write a simple SQL query and you are off and running.

 

To use Query Express the basic steps are as follows:

  1. In Query Press connect to the BES server with Windows Authentication using a user with BES access
  2. Open the BESMgmt5 database (assuming this your BES 5.x database name)
  3. Run your query – see example below for a simple left join and simple none join
  4. When you are done you can click File and select Save Query Results to save your results and play in Excel

Basic simple none join Query

To return the display name, PIN, and email address for cohesivelogic.com, you would run the following Query:

select DisplayName, PIN, MailboxSMTPAddr,activationtime from UserConfig where mailboxsmtpaddr like ‘%cohesivelogic.com%’

Basic Left Join Query

Below is a basic example of a Left Join query you can modify to attach 2 tables together if you need a report with more information than you can find in a single table. A Left Join will pull all data from the source and only data from the linked table where there is data. In the following example that means you get all of the information from UserConfig table, and if there is information in the SyncDeviceMgmtSummary.Homenetwork field in the SyncDeviceMgmtSummary table it will be included as well. It will also filter and only return users who have an email address in ending in Nalco.com

select UserConfig.DisplayName

               ,UserConfig.PIN

               ,UserConfig.MailboxSMTPAddr

               ,UserConfig.activationtime

               ,SyncDeviceMgmtSummary.HomeNetwork

from UserConfig

left join SyncDeviceMgmtSummary

               on userconfig.id = SyncDevicemgmtSummary.ID

where mailboxsmtpaddr like ‘%cohesivelogic.com%’

Related Posts with Thumbnails

About Kevinm