Nov 21 2011

Powershell: Blackberry and Activesync mobile devices inventory

When you setup a corporate Exchange mail system users can access their mailbox on most mobile devices (iPhone, Android, Windows Phone…) through the ActiveSync protocol. For blackberry owners to access your mail system, you need to setup a BES infrastructure.

The purpose of this Powershell script is to make an inventory of all the mobile devices accessing your Exchange mail system. First we will retrieve BlackBerry devices information by querying the BES SQL configuration database, then the other devices using the ActiveSync protocol by querying the Active Directory.

BlackBerry Inventory:

Your BackBerry system configuration is stored in the BESMGMT SQL database, among other things you will find information about the Blackberry devices connecting to your mail system. We will query three tables in that database:

  • UserConfig: Contains among others the user’s Email address (MailboxSMTPAddr value).
  • SyncDeviceMgmtSummary: Blackberry model and OS version (ModelName and AppsVer).
  • UserStats: Time the last message was received by the BlackBerry (LastFwdTime).

If you browse the database with SQL Management Studio you can see all that information:


You just need to use Powershell to connect to the SQL database and retrieve those values for each user. The primary key between the three tables is the ID value. We will filter only the users who received an Email during the last 60 days (LastFwdTime).

$days = 60
$dbServer = "SQLBES-SRV"
$db = "BESMgmt"
 
#If you use SA account to connect database, change pwd=XXXXXX!, or use Windows Authentication, see below
$connString = "Server=$dbServer;Database=$db;uid=sa;pwd=XXXXXX"
 
#If you use Windows Authentication to connect BESMGMT SQL Database, need setup the database
#$connString = "Server=$dbServer;Database=$db;Integrated Security=True"
 
#SQL Query to retrieve BB devices Info (only devices who  recieved mail in the last $days)
$Query = "Select UserConfig.DisplayName,MailboxSMTPAddr,ModelName,LastFwdTime,AppsVer from UserConfig,SyncDeviceMgmtSummary,UserStats where UserConfig.ID=SyncDeviceMgmtSummary.UserConfigID AND UserConfig.ID=UserStats.UserConfigID AND DeviceType <> 0 AND ModelName <> '' AND DateDiff(dd,LastFwdTime,GETDATE()) < "+$days
 
#Connect to Database, run Query, Disconnect
 
$SqlConnection = New-Object 
 
System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = $connString
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $Query
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
$bbs = $DataSet.Tables[0]

If you need more details on how to query the BESMGMT database you can read this post or this forum topic, which discusses how to query the database with SQL 2008 CmdLets (you will need to install the Microsoft SQL Server 2008 Feature Pack.) The above script uses the .NET System.Data.SqlClient Namespace, so you won’t need any additional installation. All the information about your user’s Blackberry devices is retrieved in the $bbs variable.

ActiveSync Inventory:

For other mobile devices connecting to your mail system it is more simple because they use the ActiveSync protocol, and all the information you need is stored in the Active Directory. Florian’s blog post explains that msExchActiveSyncDevice objects are a subcontainer of your AD account. Just open dsa.msc to check this out:

Thanks to the Exchange Management Shell (2007 and 2010), the Get-ActiveSyncDeviceStatistics cmdlet gives you all the information you need about the msExchActiveSyncDevice objects. You can read Ben Lye’s post to know how to identify ActiveSync users. With that Cmdlet we will retrieve the following values:

  • DeviceType: Mobile type (Iphone, WP, Android…)
  • DeviceModel: Mobile model (HTC, LG, Samsung, Iphone…)
  • DeviceUserAgent: OS build is displayed in that value (Iphone, WP, Android…)
  • LastSuccessSync: Last successful device synchronization, like BlackBerry devices we will filter only users who successfully synchronized during the last 60 days

We retrieve more or less the same information as the one retrieved with the SQL query for Blackberry devices.

How the script works:

We will describe how to aggregate both queries. First we query the BESMGMT SQL database to retrieve Blackberry devices information. For each Email address MailboxSMTPAddr we also check if the user has a device using the ActiveSync protocol (when you are a real VIP you have a Blackberry and an iPad 😉 ) by using the following Powershell command:

$acts = Get-ActiveSyncDeviceStatistics -Mailbox $bb.MailboxSMTPAddr
if (($acts | Measure-object).count -eq 0) 
{User has just one BB no ActiveSync device}
else {User has one ActiveSync device in addition to BB}

Once the Blackberry inventory is done we load every Exchange mailbox with this command:

$Mailboxes = Get-Mailbox -RecipientTypeDetails UserMailbox -ResultSize Unlimited

Then we compare the PrimarySmtpAddress value (retrieved with the get-mailbox Cmdlet) with every MailboxSMTPAddr value (stored in the $BESsmtpArray variable) in order to exclude the users already processed during the BlackBerry devices inventory. For each remaining user we check if they have any devices using the ActiveSync protocol. The result of the inventory is written in a CSV file containing the following information:

  • User’s Email address.
  • Number of mobile devices (ActiveSync + BlackBerry).
  • Device type: BlackBerry, IPhone, WP, Android…
  • Device model: BlackBerry model, Samsung, HTC, LG…
  • OS version.
  • Last connection: Last mail received on the BlackBerry device, last ActiveSync synchronization.

Here is an example of inventory result imported in Excel:

Just modify the following variables in the script and launch it from the Exchange management shell:

  • $days: Inactivity threshold in days (default is 60).
  • $dbServer: Name of the server hosting the SQL BES configuration database.
  • $connString: Authentication to the SQL database (Windows integrated or with SA account).

To download the full script, just click below:


PS: The script was tested under Exchange 2010 and BES 5.02, thanks for your feedback on other versions.

This post is also available in: French

Tags: , , ,

Filed in scripts, tools |

12 Comments

  • By Sean Duffy, November 29, 2011 @ 1:14 am

    Thanks for this great PowerShell script – I have taken it, modified it a bit and scheduled it on two client servers – main modifications I did were, compile info into a neat table and format nicely in HTML then attach to an email that is sent via PS to the client’s IT personnel. On the 1 x client that only had Exchange 2003, I modified it to only retrieve BES Express users for them and tabulate these. Great work!

  • By ldap389, November 29, 2011 @ 1:45 pm

    Hi Sean,

    Thanks for the feedback, happy this script was useful to you.

  • By Simon Goodier, June 1, 2012 @ 5:04 pm

    Shaun,

    Wondered if I could grab your copy please?
    admin@simongoodier.com

    OR, to the author, if you have this powershell script that outputs an object/table – then we can use that object with the ConvertTo-Html powershell command ourselves.

    Si

  • By Sean Duffy, June 3, 2012 @ 10:45 am

    Hi Simon,

    I had a look through my script repository but unfortunately don’t have a copy. This version I made I left on a server at my last job. I recently changed jobs so don’t have access to this anymore 🙁 Sorry!

    Sean

  • By Simon Goodier, June 4, 2012 @ 8:08 pm

    Sean, no probs. I took this scripts offerings and re-modded it to provide a friendlier HTML view. I shall write about it all as soon as I get chance, and of course link the original script back here.

  • By ldap389, June 4, 2012 @ 8:20 pm

    Hi Simon,
    Thanks in advance for sharing your version of the script when your blog post is ready.
    Regards

  • By Simon Goodier, June 5, 2012 @ 5:18 pm

    Sean/Ldap389
    Here you go:
    http://www.simongoodier.com/home/projects/powershell-reporting/

  • By Gail, July 19, 2012 @ 5:19 pm

    Hi nice script. I was wondering if anyone could help me with a query for Blackberry Sql database.
    I don’t need powershell just a query
    that shows
    messages sent, messages received, and availability percentage of blackberry service. I know where messagesent is in the userstats table and serverstats table,
    not sure where to get messages received or blackberry availability.
    Since there is do public documentation on this database (data dictionary etc) finding some of this stuff is like looking for a needle in a haystack.

  • By ldap389, July 20, 2012 @ 3:49 pm

    Hi Gail,

    Sorry, I did not find any “Messages received” or “blackberry availability” information in the BESMGMT database.
    I used the following SQL query to list all column names from all tables that match “%receiv%”

    USE BESMGMT
    GO
    SELECT t.name AS table_name,
    SCHEMA_NAME(schema_id) AS schema_name,
    c.name AS column_name
    FROM sys.tables AS t
    INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
    WHERE c.name LIKE ‘%recei%’
    ORDER BY schema_name, table_name;

    And had no result, did the same for availabilty, but still no chance. So I suppose this information does not exist in the BB database.
    Found this SQL query on SQLAuthority.com.

  • By Gail, July 20, 2012 @ 4:09 pm

    Thanks LDAP. I am not sure exactly what the fields would be called, but that query may help me fine what I need. After further research I actually think the msgsforward column in the userstats table may be what I need for messages received. I read that on the blackberry site
    http://docs.blackberry.com/en/admin/deliverables/16661/Managing_message_forwarding_224564_11.jsp

    Still need to find out where I can get availability/uptime from the DB

  • By Simon Jackson, June 5, 2013 @ 3:44 pm

    Hi,

    I’ve modified some of the SQL in your query.. basically utilising SQL Server to store a table view.. smaller select statements:

    CREATE VIEW [viewBlackberries] AS
    SELECT
    SERVERNAME.dbo.UserConfig.DisplayName, lower(SERVERNAME.dbo.UserConfig.MailboxSMTPAddr) as MailboxSMTPAddr, ‘Blackberry’ as Make,
    SERVERNAME.dbo.SyncDeviceMgmtSummary.ModelName, SERVERNAME.dbo.SyncDeviceMgmtSummary.IMEI, SERVERNAME.dbo.SyncDeviceMgmtSummary.AppsVer,
    SERVERNAME.dbo.UserConfig.PIN, SERVERNAME.dbo.SyncDeviceMgmtSummary.HomeNetwork,
    REPLACE(SERVERNAME.dbo.SyncDeviceMgmtSummary.PhoneNumber,’+44′,’07’) as PhoneNumber,
    CASE SERVERNAME.dbo.SyncDeviceMgmtSummary.PasswordEnabled WHEN 1 THEN ‘True’ WHEN 0 THEN ‘False’ END AS ‘PasswordEnabled’,
    CASE SERVERNAME.dbo.UserStats.Status WHEN 12 THEN ‘Running’ WHEN 10 THEN ‘In cradle’ WHEN 0 THEN ‘Initializing’ END AS ‘Status’,
    SERVERNAME.dbo.SyncDeviceMgmtSummary.ITPolicyName, SERVERNAME.dbo.UserStats.UserConfigId, SERVERNAME.dbo.UserStats.MsgsForwarded, SERVERNAME.dbo.UserStats.MsgsSent,
    SERVERNAME.dbo.UserStats.MsgsPending, SERVERNAME.dbo.UserStats.MsgsExpired, SERVERNAME.dbo.UserStats.MsgsFiltered, SERVERNAME.dbo.UserStats.MsgsFailed,
    –SERVERNAME.dbo.UserConfig.CreationTime,
    CONVERT(varchar(10),SERVERNAME.dbo.UserConfig.CreationTime, 101)CreationDate,
    –SERVERNAME.dbo.UserConfig.ActivationTime,
    CONVERT(varchar(10),SERVERNAME.dbo.UserConfig.ActivationTime, 101)ActivationDate,
    SERVERNAME.dbo.UserStats.LastFwdTime, SERVERNAME.dbo.UserStats.LastSentTime,
    CASE SERVERNAME.dbo.UserConfig.RedirectWhenInCradle WHEN 1 THEN ‘True’ WHEN 0 THEN ‘False’ END AS ‘RedirectWhenInCradle’
    FROM SERVERNAME.dbo.UserConfig FULL OUTER JOIN
    SERVERNAME.dbo.GroupConfig ON SERVERNAME.dbo.UserConfig.GroupConfigId = SERVERNAME.dbo.GroupConfig.Id FULL OUTER JOIN
    SERVERNAME.dbo.UserStats ON SERVERNAME.dbo.UserConfig.Id = SERVERNAME.dbo.UserStats.UserConfigId FULL OUTER JOIN
    SERVERNAME.dbo.SyncDeviceMgmtSummary ON SERVERNAME.dbo.UserConfig.Id = SERVERNAME.dbo.SyncDeviceMgmtSummary.UserConfigId
    GO

    …

  • By Shahela, November 5, 2014 @ 2:54 am

    Hi,

    I have pretty much gone through all tables in BES 5 and 10 and I don’t see any entry where I can find the bes application.
    I just see package name in the BASApplications but not the friendly name.

    Eg. OCs.client is the package name but application name is OCS Client.
    This application belongs to a group.

    Users assigned to the group gets the OCs application to their bb device.

    I pretty exhausted looking through all sql tables but don’t see anywhere where I can get the application names and group the application belong too.

    Any help is appreciated.

Other Links to this Post

RSS feed for comments on this post. TrackBack URI

Leave a comment

CAPTCHA

*

WordPress Themes

Blossom Icon Set

Software Top Blogs