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.

Blackberry BES 12 – Device Last Contact Reporting

We use Blackberry’s BES12 to manage all our mobile devices and all our clients’ mobile devices – be it Blackberry, Apple, Android, or even (shudder…) Windows Phone.  Blackberry BES12 is a fairly solid product, but with one major flaw that Blackberry still hasn’t fixed to my satisfaction.  Occasionally (well – more than occasionally), devices just plain old stop checking in and calling home to the BES. When this happens, the devices become unmanageable. At this point we usually need to contact the end user to have them manually open the BES12 client on their device, which will try to call home and reestablish connectivity.

Newer versions of Blackberry BES12 make it easy to show the last device contact time in the Advanced User view if you go in and turn it on in the GUI.  But unfortunately, BES12 doesn’t provide any way that I know of to get a scheduled report by email with these details. So this means we need to manually go log into customers’ BES servers on a regular basis to see if any devices have lost connectivity. This is a huge time sink and a pain in the rear for our help desk that we could probably do without.

So after some poking through the BES 12.5.1 database tables, I came up with a query that will give me the information I am looking for.  All that was left after this was to create a PowerShell wrapper to automatically run the script and email me the report on a daily basis (of which there is a picture below).

2016.08.30 - 09.39.16 - SNAGIT -  0013


SQL Server 2012 Express or newer is required for PowerShell integration I believe. I’ve tested this script with SQL Express 2012 SP3. You can check for PowerShell integration support by using these two PowerShell commands:   Import-Module “SQLPS”   and   “Invoke-Sqlcmd”

I wrote the SQL query based on the SQL tables in BES 12.5.1, but it appears to work against BES 12.3.1 too.

Code lines 1 and 2 are comments I like to add to my scripts to allow me a quick way to copy, paste, and setup my script from Outlook onto the target machine(s) so everyone is setup the same way. Code line 2 will set the script to run at 12:15 am daily under the System security context. Adjust as you see fit.

Code lines 10, 11 and 26 are variables that need to be changed and adjusted for customer environments as required.

Code line 10 – $emailTo is who the email needs sent to (i.e.
Code line 11 – $smtpServer is the SMTP server you are going to send through (i.e.
Code line 26 – BES12 is the name of your production BES12 SQL database

By default, if you don’t change anything, $emailTo and $smtpServer will auto-populate using the $DnsDomain variable, just like $emailFrom, which should end up as the server’s NetBios computername @ the server’s DNS name (i.e. JBGEEK-BES01@JBGEEK.NET).

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

###  begin cut & paste of start notepad++ c:\windows\BES_Connectivity_Report.ps1
###  cmd.exe /c schtasks /create /tn "Daily BES Connectivity Report" /tr "\"C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe\" -ExecutionPolicy RemoteSigned -noprofile -File C:\Windows\BES_Connectivity_Report.ps1" /sc daily /st 00:15:00 /ru "System"
###  Dean Colpitts / / 2016.08.30

Import-Module 'SQLPS' -DisableNameChecking;

$DnsDomain = Get-WmiObject -Class Win32_NTDomain -Filter "DSDirectoryServiceFlag='True'" | Select -ExpandProperty DnsForestName
$ThisComputerName = Get-WmiObject -Class Win32_ComputerSystem | Select -ExpandProperty Name
$emailFrom = "$($ThisComputerName.ToUpper())@$($DnsDomain.ToUpper())"
$emailTo = "besadmin@$DnsDomain"
$smtpServer = "mail.$DnsDomain"
$messageSubject = "$ThisComputerName.$DnsDomain BES Connectivity Report"
$message = New-Object System.Net.Mail.MailMessage $emailfrom, $emailto
$message.Subject = $messageSubject
$message.IsBodyHTML = $true

$style = @'
<style type="text/css">
  table {text-align: left; font-family: arial, font-size: 12px; padding: 5px 5px; border: 1px solid #000000; border-collapse: collapse;padding-right: 10px; padding-left: 10px;}
  th {text-align: left; font-family: arial, font-size: 12px; padding: 5px 5px; color: #000; column-width: 100px; border-top: 1px solid #000000; border-bottom: 1px solid #000000;background-color: #6495ED;padding-right: 10px; padding-left: 10px;}
  td {font-family: arial, font-size: 12px; padding: 5px 5px; color: #000; column-width: 100px; border-top: 1px solid #000000; border-bottom: 1px solid #000000;padding-right: 10px; padding-left: 10px;}

$sqlquery = @"
Use BES12
Select Top 1000000
  obj_user.display_name As [User],
  def_device_os_family.company_name as [Manufacturer],
  def_device_hardware.model as [Model], as [OS Version],
  obj_device.normalized_phone_number as [Phone 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
Order by [User]

$message.Body = Invoke-Sqlcmd -Query $sqlquery -ServerInstance '.' |  Select * -ExcludeProperty RowError, RowState, Table, ItemArray, HasErrors | sort-object "Last Contact" | convertto-html -Head $style
$smtp = New-Object Net.Mail.SmtpClient($smtpServer)