BlackBerry User Summary Reports (5.0.2 Compatible)

Although many people really like the standard reporting in the BlackBerry Monitor Server, we needed some more details for our usage at my company.

This resulted in a new view being created for queries that spanned multiple tables to give “point in time” status on BlackBerry devices.  I wrote something similar a few years ago while we were still on 4.1.X, but decided to update it for the 5.0 servers.

The information I felt was absolutely necessary was: Display Name (of the user), Model of the BlackBerry, Handheld Application Version, Handheld Platform Version, Phone Number assigned to device (even if it is a data-only device), IMEI (Serial Number), Home Network (or Cellular Provider), IT Policy Name (in which policy are they placed), PIN Email Address Exchange Server, BES Server (we run a total of seven and identification of the BES is a must), Number of Messages Pending, Number of Messages Sent, Number of Messages Forwarded, Last Contact (as a time difference from the current), and User ID in the Database

After tinkering around with it for a while and getting assistance from a friend who knows the cool SQL stuff better than I do, we came up with a view.  Most of this information is available in one view, the vUserConfigStats view, but some is buried in the ServerConfig Table.  Here’s what my view looks like with some very simple comments.

-- ***********************************************
-- * VIEW: vUserSummary
-- ***********************************************
-- Purpose: Collect user-specific information from multiple tables and return it in a very simple (and readable) format.
CREATE VIEW [dbo].[vUserSummary]
AS
SELECT     dbo.vUserConfigStats.DisplayName
         , dbo.vUserConfigStats.ModelName AS Model
         , dbo.vUserConfigStats.AppsVer AS ApplicationVersion
         , dbo.vUserConfigStats.PlatformVer AS PlatformVersion
         , CASE -- This checks the phone number for the number of digits (10 or 11) in the US and parses it to the ###-###-#### format
                WHEN len(ltrim(rtrim(vUserConfigStats.PhoneNumber))) = '10'
                THEN '(' + SUBSTRING(vUserConfigStats.PhoneNumber, 1, 3) + ')' + ' ' + SUBSTRING(vUserConfigStats.PhoneNumber, 4, 3) + '-' + SUBSTRING(vUserConfigStats.PhoneNumber, 7, 4)
                WHEN len(ltrim(rtrim(vUserConfigStats.PhoneNumber))) = '11'
                THEN '(' + SUBSTRING(vUserConfigStats.PhoneNumber, 2, 3) + ')' + ' ' + SUBSTRING(vUserConfigStats.PhoneNumber, 5, 3) + '-' + SUBSTRING(vUserConfigStats.PhoneNumber, 8, 4)
                ELSE 'INVALID' -- if it's not 10 or 11 digits throw an error (and return 'INVALID')
           END AS PhoneNumber
         , UPPER(dbo.vUserConfigStats.IMEI) AS IMEI
         , dbo.vUserConfigStats.HomeNetwork
         , dbo.vUserConfigStats.ITPolicyName
         , dbo.vUserConfigStats.PIN
         , dbo.vUserConfigStats.MailboxSMTPAddr AS SMTPAddress -- MailboxSMTPAddr is a poor name for this, so I changed it
         -- The substring below makes me especially proud
         -- Exchange Servers are stored according to distinguished name.  Since we only need a part of this name, this very large substring extracts just what is needed
         , SUBSTRING(dbo.vUserConfigStats.ServerDN, PATINDEX('%Servers/cn=%', dbo.vUserConfigStats.ServerDN) + 11, PATINDEX('%/cn=Microsoft%',  dbo.vUserConfigStats.ServerDN) - (PATINDEX('%Servers/cn=%', dbo.vUserConfigStats.ServerDN) + 11)) AS ExchangeServer
         , dbo.ServerConfig.ServiceName AS BESServer -- Changed this name to make more sense
         , dbo.vUserConfigStats.MsgsPending
         , dbo.vUserConfigStats.MsgsSent
         , dbo.vUserConfigStats.MsgsForwarded
         -- This calculates the difference from the lst contact (in or out) of the device to now and returns the number of seconds
         -- This value is VERY useful in determining who has devices turned off, in desks, lost, or otherwise out of touch
         , DATEDIFF(second, CASE
                                 WHEN LastFwdTime >= LastSentTime
                                 THEN LastFwdTime
                                 ELSE LastSentTime
                            END, GetUTCDate()) AS LastContact
         -- This ID Number for the user is unique in the database and has some uses in running the Resource Tools
         , dbo.vUserConfigStats.Id
FROM dbo.vUserConfigStats INNER JOIN
                      dbo.ServerConfig ON dbo.vUserConfigStats.ServerConfigId = dbo.ServerConfig.Id

The result set looks something like this (items redacted for privacy):

Create_View_vUserSummary