Bits & Other Bytes

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).

Notes:

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. besadmin@jbgeek.net)
Code line 11 – $smtpServer is the SMTP server you are going to send through (i.e. mail.jbgeek.net)
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 / blog.jbgeek.net / 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;}
</style>
'@

$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],
  def_device_os.name 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)
$smtp.Send($message)