#Blackberry UEM Server – Exporting the serial numbers of all devices

We use Blackberry’s UEM 12.7.2 to manage all our mobile devices and all our clients’ mobile devices – be it Blackberry, Apple, Android, or even (shudder…) Windows Phone. Recently a client asked me for a list of all his devices with the serial numbers. Since there were only 30 devices or so, I figured I’d just go into the device report for each user and cut and paste the serial number into a spreadsheet and send it to the customer. Wow – was I ever wrong. The serial number isn’t reported in the Device Report, nor is on the user’s device tab. The only place I could actually find it listed is on the All Users tab if you enable Advanced View, but that is useless because cut and paste is disabled on this page.

But since the serial number appears there, then it must be in the database.

So, I went back to one of my previous blog posts about how to script and report the last contact time of a device (see that blog post here) and did a bit of modification to it.  And since I didn’t know which table the serial number was listed in, I used some code that I found elsewhere (I don’t remember where, it was a while ago, so I’m not able to link to it, and I won’t republish it since I don’t know who to give credit to) to search all the database tables for one of the serial number that I manually copied down.

Turns out the device serial number is stored in the table obj_device_setting, and that id_device_setting_definition “60” is the device’s serial number.  So knowing that, I was able to create a new query based on my previous blog post that now also includes the device serial number.

Here is the updated SQL query to include device serial numbers.

Select Top 1000000
obj_user.display_name As [User],
def_device_os_family.company_name as [Manufacturer],
def_device_hardware.model as [Model],
def_device_os.name as [OS Version],
obj_device.normalized_phone_number as [Phone Number],
obj_device_setting.value as [Device Serial Number],
Convert(Varchar(10), obj_user_device.last_communication, 102) As [Last Contact]
From obj_user
Inner Join obj_user_device On obj_user_device.id_user = obj_user.id_user
Inner Join obj_device On obj_device.id_device = obj_user_device.id_device
Inner Join def_device_hardware on def_device_hardware.id_device_hardware = obj_device.id_device_hardware
Inner Join def_device_os on def_device_os.id_device_os = obj_device.id_device_os
Inner Join def_device_os_family on def_device_os_family.id_device_os_family = def_device_os.id_device_os_family
Inner Join obj_device_setting On obj_device_setting.id_device = obj_user_device.id_device
Where obj_device_setting.id_device_setting_definition = '60'
Order by [User]

As always – Use any tips, tricks, or scripts I post at your own risk.

HOWTO: Find the SRP & Auth Key in the SQLDB of an existing BES12 installation

This post is going to be pretty short and simple.  We had a customer who had lost track of the corresponding Auth Key to their existing SRP ID in Blackberry Enterprise Server 12.  A simple SQL query was all that is required to pull the SRP and Auth Key from the existing production SQL database.  This **should** work on any version of BES12, but I only tested it on BES 12.5.2 and BES 12.6.

  1. Open Microsoft SQL Server Management Studio.
  2. Connect to the SQL server instance that hosts the BES database.
  3. Click the “New Query” button on the toolbar (or press CTRL + N).
  4. Paste the following query into the query editor, then click the Execute Button (adjust the BES_DATABASE_NAME as required)
SELECT TOP 1000 [id_sws_tenant]
FROM [BES_DATABASE_NAME].[dbo].[obj_sws_tenant]

The output will contain the SRP (external_tenant_id) and Auth Key (external_authenicator_id).


As always – Use any tips, tricks, or scripts I post at your own risk.