Correct Order to Start and Stop BlackBerry Enterprise Server Services Using PowerShell

A common activity I perform after patching Exchange Servers and Domain Controllers is to restart BlackBerry services on client BlackBerry servers. Failure to do so can lead to issues for BlackBerry users such as failed delivery, lookup failures and inability to send. RIM recommends a specific order for these services to be stopped and started. Since patching occurs typically occurs in the dead of night to avoid impacting end users, you want to get things done quickly but accurately so you can get some sleep. In the code samples below, I take advantage of Get-Service, Stop-Service and Start-Service to execute the correct order of the process. I store the required service order in an array and take advantage of the natural looping order of elements in "foreach" to manipulate the services. The remaining services are discovered by obtaining all services with the name BlackBerry and filtering out the services that required a specific order for stopping and starting. If there are any issues with stopping or stopping the services, the code immediately exits so you can take manual intervention, research and resolve the problem.

Stopping BlackBerry Services:

#--------------------------------------------------------------------------------------------------#
Set-Variable -name selectedServices -option Constant -value @("BlackBerry Controller","BlackBerry Dispatcher","BlackBerry Router")
#--------------------------------------------------------------------------------------------------#
foreach($selectedService in $selectedServices) {
 if((Get-Service -Name $selectedService).Status -ne "Stopped") {
  Write-Output "Stopping $selectedService..."
  Stop-Service -Name $selectedService
  if((Get-Service -Name $selectedService).Status -ne "Stopped") {
   Write-Output "$selectedService failed to stop. Exiting..."
   exit
  } else {
   Write-Output "$selectedService successfully stopped."
  }
 } else {
  Write-Output "$selectedService already stopped!"
 }
}
foreach($blackberryService in (Get-Service -Name Blackberry*)) {
 if($selectedServices -notcontains $blackberryService.Name) {
  if($blackberryService.Status -ne "Stopped") {
   Write-Output ("Stopping " + $blackberryService.Name + "...")
   Stop-Service -Name $blackberryService.Name
   if((Get-Service -Name $blackberryService.Name).Status -ne "Stopped") {
    Write-Output ($blackberryService.Name + " failed to stop. Exiting...")
    exit
   } else {
    Write-Output ($blackberryService.Name + " successfully stopped.")
   }
  } else {
   Write-Output ($blackberryService.Name + " already stopped!")
  }
 }
}

Starting BlackBerry Services:

#--------------------------------------------------------------------------------------------------#
Set-Variable -name selectedServices -option Constant -value @("BlackBerry Router","BlackBerry Dispatcher","BlackBerry Controller")
#--------------------------------------------------------------------------------------------------#
foreach($selectedService in $selectedServices) {
 if((Get-Service -Name $selectedService).Status -ne "Running") {
  Write-Output "Starting $selectedService..."
  Start-Service -Name $selectedService
  if((Get-Service -Name $selectedService).Status -ne "Running") {
   Write-Output "$selectedService failed to start. Exiting..."
   exit
  } else {
   Write-Output "$selectedService successfully started."
  }
 } else {
  Write-Output "$selectedService already running!"
 }
}
foreach($blackberryService in (Get-Service -Name Blackberry*)) {
 if($selectedServices -notcontains $blackberryService.Name) {
  if($blackberryService.Status -ne "Running") {
   Write-Output ("Starting " + $blackberryService.Name + "...")
   Start-Service -Name $blackberryService.Name
   if((Get-Service -Name $blackberryService.Name).Status -ne "Running") {
    Write-Output ($blackberryService.Name + " failed to start. Exiting...")
    exit
   } else {
    Write-Output ($blackberryService.Name + " successfully started.")
   }
  } else {
   Write-Output ($blackberryService.Name + " already running!")
  }
 }
}

Build a Report of Unused BlackBerry Devices

A crucial element of BlackBerry management is reducing the cost of providing BlackBerry service. An unused BlackBerry is a wasted license, an unnecessary cell phone bill with a data plan and possibly a security issue. When thousands of handhelds are under management, dozens can be idle. In the current economic climate, every penny counts in your IT budget. Finding these idle devices can add up to thousands of dollars in savings annually.

In the code sample below, I build upon a previous blog post, "BlackBerry User Report". The command text is updated to select information from the UserStats table where the connection data for the BlackBerry devices is stored. If no value is held in the row for connectivity, a DBNull value will be returned. This is not the same as PowerShell's built-in "$null" and you will need to test against the DBNull class to determine this. The end result of this code example is a comma separated values text file with the employees neglecting their BlackBerry. Reach out to them and see if they no longer need their handheld or if they have moved on to an ActiveSync device as their primary mobile messaging device.

In a change from previous blog posts, I am using System.DirectoryServices.DirectorySearcher class to perform the user object lookup instead of ActiveX Data Object (ADO). I have a tendency to use ADO as the methodology is the same in PowerShell, Perl and VBScript making the translation of ADSI queries between languages simple.

Function Get-ForestRootDn {
 return ((New-Object System.DirectoryServices.DirectoryEntry("LDAP://RootDSE")).rootDomainNamingContext).ToString()
}

Function Get-ObjectAdDomain($distinguishedName) {
 return ((($distinguishedName -replace "(.*?)DC=(.*)",'$2') -replace "DC=","") -replace ",",".")
}

Function Find-ActiveDirectoryObjectByEMailAddress($smtpAddress) {
 $directorySearcher = New-Object System.DirectoryServices.DirectorySearcher
 $directorySearcher.SearchRoot = (New-Object System.DirectoryServices.DirectoryEntry("GC://$forestRootDn"))
 $directorySearcher.Filter = "(&(objectClass=user)(objectCategory=person)(proxyAddresses=smtp:$smtpAddress))"
 $searchResult = $directorySearcher.FindOne()
 if($searchResult -ne $null) {
  $userObject = New-Object System.DirectoryServices.DirectoryEntry($searchResult.Path)
 } else {
  $userObject = $null
 }
 $directorySearcher.Dispose()
 return $userObject
}
#--------------------------------------------------------------------------------------------------#
Set-Variable -name forestRootDn -option Constant -scope Script -value (Get-ForestRootDn)
Set-Variable -name dbNull -option Constant -value ([System.DBNull]::Value) # http://msdn.microsoft.com/en-us/library/system.dbnull.aspx
Set-Variable -name databases -option Constant -value @("mssql2008-east.ad.mydomain.local","mssql2008-west.ad.mydomain.local","mssql2008-europe.ad.mydomain.local","mssql2008-japan.ad.mydomain.local")
Set-Variable -name idleDays -option Constant -value 14
#--------------------------------------------------------------------------------------------------#
$blackberryUsers = @()

foreach($sqlServer in $databases) {
 $sqlConnection = New-Object System.Data.SQLClient.SQLConnection
 $sqlConnection.ConnectionString = "server=$sqlServer;database=BESMgmt;trusted_connection=true;"
 $sqlConnection.Open()
 
 $sqlCommand = New-Object System.Data.SQLClient.SQLCommand
 $sqlCommand.Connection = $sqlConnection
 $sqlCommand.CommandText = "SELECT u.DisplayName as DisplayName, u.MailboxSMTPAddr as MailboxSMTPAddr, u.PIN as PIN, d.phonenumber as phonenumber, s.MachineName as MachineName, t.LastFwdTime as LastFwdTime, t.LastSentTime as LastSentTime, t.MsgsPending as MsgsPending FROM UserConfig u, SyncDeviceMgmtSummary d, ServerConfig s, UserStats t WHERE u.id = d.userconfigid and u.ServerConfigId = s.id and u.id = t.userconfigid"
 
 $sqlDataReader = $sqlCommand.ExecuteReader()
 
 if($sqlDataReader.HasRows -eq $true) {
  while($sqlDataReader.Read()) {
   $displayName = $sqlDataReader.Item("DisplayName")
   $proxyAddress = $sqlDataReader.Item("MailboxSMTPAddr")
   $pin = $sqlDataReader.Item("PIN")
   $phoneNumber = $sqlDataReader.Item("phonenumber")
   $blackberryServer = $sqlDataReader.Item("MachineName")
   $lastFwdTime = ($sqlDataReader.Item("LastFwdTime"))
   $lastSentTime = $sqlDataReader.Item("LastSentTime") 
   $msgsPending = $sqlDataReader.Item("MsgsPending")
 
   if($lastSentTime -eq $dbNull) { $lastSentTime = "No Contact" } else { $lastSentTime = (Get-Date $lastSentTime) }
   if($lastFwdTime -eq $dbNull) { $lastFwdTime = "No Contact" } else { $lastFwdTime = (Get-Date $lastFwdTime) }
   
   if(($lastSentTime -eq "No Contact" -or $lastFwdTime -eq "No Contact") -or ((Get-Date $lastSentTime) -le (Get-Date).AddDays(-$idleDays) -or (Get-Date $lastFwdTime) -le (Get-Date).AddDays(-$idleDays))) {
    $userObject = Find-ActiveDirectoryObjectByEMailAddress $proxyAddress
    if($userObject -eq $null) {
     Write-Host "USER NOT FOUND: $proxyAddress" -foregroundColor Red
    } else {
     $blackberryUser = New-Object -typeName PSObject
     Add-Member -inputObject $blackberryUser -type NoteProperty -name "Domain" -value ((Get-ObjectAdDomain $userObject.distinguishedName.ToString()).Split(".")[0]).ToUpper()
     Add-Member -inputObject $blackberryUser -type NoteProperty -name "User ID" -value $userObject.sAMAccountName.ToString()
     Add-Member -inputObject $blackberryUser -type NoteProperty -name "First Name" -value $userObject.givenName.ToString()
     Add-Member -inputObject $blackberryUser -type NoteProperty -name "Last Name" -value $userObject.sn.ToString()
     Add-Member -inputObject $blackberryUser -type NoteProperty -name "Display Name" -value $userObject.displayName.ToString()
     Add-Member -inputObject $blackberryUser -type NoteProperty -name "E-Mail Address" -value $userObject.mail.ToString()
     Add-Member -inputObject $blackberryUser -type NoteProperty -name "Last Message Forwarded" -value $lastFwdTime
     Add-Member -inputObject $blackberryUser -type NoteProperty -name "Last Message Sent" -value $lastSentTime
     Add-Member -inputObject $blackberryUser -type NoteProperty -name "Messages Pending" -value $msgsPending
     Add-Member -inputObject $blackberryUser -type NoteProperty -name "PIN" -value $pin
     Add-Member -inputObject $blackberryUser -type NoteProperty -name "Cell Phone Number" -value $phoneNumber
     Add-Member -inputObject $blackberryUser -type NoteProperty -name "Desk Phone Number" -value $userObject.telephoneNumber.ToString()
     Add-Member -inputObject $blackberryUser -type NoteProperty -name "Street Address" -value ($userObject.streetAddress.ToString() -replace "`r`n",", ")
     Add-Member -inputObject $blackberryUser -type NoteProperty -name "City" -value $userObject.l.ToString()
     Add-Member -inputObject $blackberryUser -type NoteProperty -name "State" -value $userObject.st.ToString()
     Add-Member -inputObject $blackberryUser -type NoteProperty -name "Zip Code" -value $userObject.postalCode.ToString()
     Add-Member -inputObject $blackberryUser -type NoteProperty -name "Country" -value $userObject.c.ToString()
     Add-Member -inputObject $blackberryUser -type NoteProperty -name "BlackBerry Server" -value $blackberryServer
     $blackberryUsers += $blackberryUser
    }
   }
  }
 }
 $sqlConnection.Close()
}

$blackberryUsers | Export-Csv -path "Stale Blackberry User Report.csv" -noTypeInformation

Replicating UNIX "tail -f" in PowerShell

Building upon my previous blog post, Unix Tail-like Functionality in PowerShell Revisited, I have completed the next step in providing tail functionality in my PowerShell scripts; the ability to emulate the "-f" argument.

From the TAIL(1) man page:

The -f option causes tail to not stop when end of file is reached, but rather to wait for additional data to be appended to the input.

My approach to replicating this functionality in PowerShell is to once again take advantage of System.IO.FileStream Class and read from the end of file as I did in my previous tail emulations. Originally, I thought this was going to be a more difficult task to accomplish but since I learned so much from my prior attempts, it turned out to be fairly simple and a lot less code to implement. My solution focuses on the fact that the file size grows as more data is appended to the text file. If I know how large the file was in a prior reading than it is currently, I know how many bytes have been added to the file and from there, I know the number of bytes I need to read from the end of the file and return to the console. All I need is a looping routine to constantly check the file for changes. In my code sample below, I emulate "tail -f" to the console by first reading the last 10 lines of a hypothetical BlackBerry Enterprise Server Management Agent log file (on an active server this file grows constantly) on a remote server as Unix "tail -f" would then start to monitor the log file for changes by comparing the file size waiting 100 milliseconds between each comparison. This process will continue until you send a break or the file is deleted.

With this bit of starter code, you should be able to implement some unique tools to monitor and react to data in log files in real-time.

UPDATE: I have made some improvements to this code here.

Function Read-EndOfFileByByteChunk($fileName,$totalNumberOfLines,$byteChunk) {
 if($totalNumberOfLines -lt 1) { $totalNumberOfLines = 1 }
 if($byteChunk -le 0) { $byteChunk = 10240 }
 $linesOfText = New-Object System.Collections.ArrayList
 if([System.IO.File]::Exists($fileName)) {
  $fileStream = New-Object System.IO.FileStream($fileName,[System.IO.FileMode]::Open,[System.IO.FileAccess]::Read,[System.IO.FileShare]::ReadWrite)
  $asciiEncoding = New-Object System.Text.ASCIIEncoding
  $fileSize = $fileStream.Length
  $byteOffset = $byteChunk
  [byte[]] $bytesRead = New-Object byte[] $byteChunk
  $totalBytesProcessed = 0
  $lastReadAttempt = $false
  do {
   if($byteOffset -ge $fileSize) {
    $byteChunk = $fileSize - $totalBytesProcessed
    [byte[]] $bytesRead = New-Object byte[] $byteChunk
    $byteOffset = $fileSize
    $lastReadAttempt = $true
   }
   $fileStream.Seek((-$byteOffset), [System.IO.SeekOrigin]::End) | Out-Null
   $fileStream.Read($bytesRead, 0, $byteChunk) | Out-Null
   $chunkOfText = New-Object System.Collections.ArrayList
   $chunkOfText.AddRange(([System.Text.RegularExpressions.Regex]::Split($asciiEncoding.GetString($bytesRead),"\r\n")))
   $firstLineLength = ($chunkOfText[0].Length)
   $byteOffset = ($byteOffset + $byteChunk) - ($firstLineLength)
   if($lastReadAttempt -eq $false -and $chunkOfText.count -lt $totalNumberOfLines) {
    $chunkOfText.RemoveAt(0)
   }
   $totalBytesProcessed += ($byteChunk - $firstLineLength)
   $linesOfText.InsertRange(0, $chunkOfText)
  } while($totalNumberOfLines -ge $linesOfText.count -and $lastReadAttempt -eq $false -and $totalBytesProcessed -lt $fileSize)
  $fileStream.Close()
  if($linesOfText.count -gt 1) {
   $linesOfText.RemoveAt($linesOfText.count-1)
  }
  $deltaLines = ($linesOfText.count - $totalNumberOfLines)
  if($deltaLines -gt 0) {
   $linesOfText.RemoveRange(0, $deltaLines)
  }
 } else {
  $linesOfText.Add("[ERROR] $fileName not found") | Out-Null
 }
 return $linesOfText
}
#--------------------------------------------------------------------------------------------------#
Function Read-FileUpdates($fileName,$startSize) {
 $asciiEncoding = New-Object System.Text.ASCIIEncoding
 while([System.IO.File]::Exists($fileName)) {
  $fileStream = New-Object System.IO.FileStream($fileName,[System.IO.FileMode]::Open,[System.IO.FileAccess]::Read,[System.IO.FileShare]::ReadWrite)
  $currentFileSize = $fileStream.Length
  if($currentFileSize -gt $startSize) {
   $byteChunk = $currentFileSize - $startSize
   [byte[]] $bytesRead = New-Object byte[] $byteChunk
   $fileStream.Seek((-$byteChunk), [System.IO.SeekOrigin]::End) | Out-Null
   $fileStream.Read($bytesRead, 0, $byteChunk) | Out-Null
   Write-Host ($asciiEncoding.GetString($bytesRead)) -noNewLine
   $startSize = $currentFileSize
  }
  $fileStream.Close()
  Start-Sleep -milliseconds 100
 }
}
#--------------------------------------------------------------------------------------------------#
Set-Variable -name inputFile -option Constant -value "\\japan-bes.ad.mydomain.local\E$\Program Files\Research In Motion\BlackBerry Enterprise Server\Logs\20110418\JAPAN-BES_MAGT_02_20110418_0001.txt"
#--------------------------------------------------------------------------------------------------#
if([System.IO.File]::Exists($inputFile)) {
 Write-Host (@(Read-EndOfFileByByteChunk $inputFile 10 1280) | Out-String) -noNewLine
 $fileStream = New-Object System.IO.FileStream($inputFile,[System.IO.FileMode]::Open,[System.IO.FileAccess]::Read,[System.IO.FileShare]::ReadWrite)
 $fileSize = $fileStream.Length
 $fileStream.Close()
 Read-FileUpdates $inputFile $fileSize
} else {
 Write-Host "Could not find $inputFile..." -foregroundColor Red
}

Write Excel Spreadsheets Fast in PowerShell

If you try to populate an Excel spreadsheet cell by cell using PowerShell expect it to take a long time. As I showed in a previous post, "Speed Up Reading Excel Files in PowerShell", when dealing with Excel work with comma separated value text files. The population of the cells appears to be the huge bottleneck in Excel permformace but if you let Excel load a comma separated values file, you can quickly format and manipulate the prepopulated cells.

One example from my own work involves a spreadsheet with ~3,500 rows. If I populate the rows directly through Excel it can take upwards of five minutes to complete the process. If I dump the data to a comma separated values text file then import it into Excel, it takes about 2 seconds -- a huge time savings. When you are running ad hoc reports, it pays off. If you want an excuse to head down to Starbucks from your office, it doesn't.

To demonstrate this technique, I will take a previous post, "BlackBerry User Report", and change the report from a comma separated values text file to Excel spreadsheet. First to make the final result look nice, the NoteProperty names are cleaned up so they make attractive headers in the spreadsheet. Swap out the following lines to achieve this.

   Add-Member -inputObject $blackberryUser -type NoteProperty -name "Domain" -value $domain
   Add-Member -inputObject $blackberryUser -type NoteProperty -name "User ID" -value $sAMAccountName
   Add-Member -inputObject $blackberryUser -type NoteProperty -name "First Name" -value $firstName
   Add-Member -inputObject $blackberryUser -type NoteProperty -name "Last Name" -value $firstName
   Add-Member -inputObject $blackberryUser -type NoteProperty -name "Display Name" -value $displayName
   Add-Member -inputObject $blackberryUser -type NoteProperty -name "E-Mail Address" -value $proxyAddress
   Add-Member -inputObject $blackberryUser -type NoteProperty -name "PIN" -value $pin
   Add-Member -inputObject $blackberryUser -type NoteProperty -name "Cell Phone Number" -value $phoneNumber
   Add-Member -inputObject $blackberryUser -type NoteProperty -name "Desk Phone Number" -value $telephoneNumber
   Add-Member -inputObject $blackberryUser -type NoteProperty -name "Street Address" -value $streetAddress
   Add-Member -inputObject $blackberryUser -type NoteProperty -name "City" -value $city
   Add-Member -inputObject $blackberryUser -type NoteProperty -name "State" -value $state
   Add-Member -inputObject $blackberryUser -type NoteProperty -name "Zip Code" -value $zipCode
   Add-Member -inputObject $blackberryUser -type NoteProperty -name "Country" -value $country
   Add-Member -inputObject $blackberryUser -type NoteProperty -name "BlackBerry Model" -value $modelName
   Add-Member -inputObject $blackberryUser -type NoteProperty -name "Carrier" -value $homeNetwork
   Add-Member -inputObject $blackberryUser -type NoteProperty -name "IMEI" -value $imei
   Add-Member -inputObject $blackberryUser -type NoteProperty -name "Password Enabled" -value $passwordEnabled
   Add-Member -inputObject $blackberryUser -type NoteProperty -name "Exchange Server" -value $exchangeServer
   Add-Member -inputObject $blackberryUser -type NoteProperty -name "BlackBerry Server" -value $blackberryServer

Now that you have nice and pretty column headers in your NoteProperty, its time to write out that Excel spreadsheet. This code assumes that you have at least Excel 2007 installed on your scripting workstation/server. First the code writes out to the temp directory using a GUID (just to be fancy) for the comma separated values file's name. We create an Excel object and load that temporary file. At that point, we can save out a fairly boring Excel spreadsheet. That won't impress anyone. If you want to look "pro" when you distribute reports, having a good layout is key. You want to save out a nicely formatted spreadsheet. So you autofit all the columns to longest cell in each, populate the worksheet's name, update some of the metadata in the spreadsheet and then apply a table style. Table styles are a quick way to add color to your spreadsheet with minimal effort by creating a list object. I have found that if you want to apply your own custom style (a future blog post) there is no speed penalty as long as the data is prepopulated. And finally we perform a Save As, remind Excel that we are saved, quit Excel and do some garbage cleanup. If you just quit Excel programatically, you will find that Excel will still be in memory.

$blackberryUsers = $blackberryUsers | Sort-Object "BlackBerry Server", "Last Name", "First Name"

$excelFile = ("\\fileserver.ad.mydomain.local\it_reports\blackberry\" + (Get-Date -format yyyyMMdd) + "-BlackBerry User Report.xlsx")

$temporaryCsvFile = ($env:temp + "\" + ([System.Guid]::NewGuid()).ToString() + ".csv")
$blackberryUsers | Export-Csv -path $temporaryCsvFile -noTypeInformation

if(Test-Path -path $excelFile) { Remove-Item -path $excelFile }

$excelObject = New-Object -comObject Excel.Application
$excelObject.Visible = $false  

$workbookObject = $excelObject.Workbooks.Open($temporaryCsvFile) 
$workbookObject.Title = ("BlackBerry User List for " + (Get-Date -Format D))
$workbookObject.Author = "Robert M. Toups, Jr."

$worksheetObject = $workbookObject.Worksheets.Item(1) 
$worksheetObject.UsedRange.Columns.Autofit() | Out-Null
$worksheetObject.Name = "BlackBerry Users"

$listObject = $worksheetObject.ListObjects.Add([Microsoft.Office.Interop.Excel.XlListObjectSourceType]::xlSrcRange, $worksheetObject.UsedRange, $null,[Microsoft.Office.Interop.Excel.XlYesNoGuess]::xlYes,$null)
$listObject.Name = "User Table"
$listObject.TableStyle = "TableStyleMedium4" # Style Cheat Sheet in French/English: http://msdn.microsoft.com/fr-fr/library/documentformat.openxml.spreadsheet.tablestyle.aspx

$workbookObject.SaveAs($excelFile,51) # http://msdn.microsoft.com/en-us/library/bb241279.aspx
$workbookObject.Saved = $true
$workbookObject.Close()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbookObject) | Out-Null

$excelObject.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excelObject) | Out-Null
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers() 

if(Test-Path -path $temporaryCsvFile) { Remove-Item -path $temporaryCsvFile }

BlackBerry User Report

This is a report that I run on a regular basis to keep track of BlackBerry users worldwide for review. The BES administration tools can provide decent reports but it doesn't provide all the information I want to have or need to pass on to others. It marries information from the BlackBerry database and Active Directory using an e-mail address as the primary key between the two data sources. This report is especially useful when you have BlackBerry servers scattered at different data centers because of the MAPI latency requirements of the BES. In large BES environments, expect this script to take a generous amount of time to complete. You will need to ensure that the security context that this script runs under has read rights to the BES databases that it connects. There are definitely areas for improvement in the data returns. I have sort of punted on the formatting of the BlackBerry phone number. I am primarily focused on 10 digit numbers found in the USA and not formatting for foreign numbers. You can take advantage of the c attribute to tweak that formatting. I will update the code in the future to add this.

Over the years, I have massed a list of vendor IDs that I use when there is missing or odd data from the home network column in the BlackBerry database. Having this information gives you the flexibility to ignore the home network and use the vendor ID for reporting. Please leave a comment if you see missing data or updates for these vendor IDs.

Function Get-LocalDomainController($objectDomain) {
 return ([System.DirectoryServices.ActiveDirectory.ActiveDirectorySite]::GetComputerSite()).Servers | Where-Object { $_.Domain.Name -eq $objectDomain } | ForEach-Object { $_.Name } | Select-Object -first 1
}
 
Function Get-ObjectADDomain($distinguishedName) {
 return ((($distinguishedName -replace "(.*?)DC=(.*)",'$2') -replace "DC=","") -replace ",",".")
}
 
Function Get-ActiveDirectoryObject($distinguishedName) {
 return [ADSI]("LDAP://" + (Get-LocalDomainController (Get-ObjectADDomain $distinguishedName)) + "/" + ($distinguishedName -replace "/","\/"))
}
Function Get-CarrierNetwork($carrier) {
 if($carrier -eq "100") { $network = "T-Mobile US"
 } elseif($carrier -eq "101") { $network = "Cingular Wireless"
 } elseif($carrier -eq "102") { $network = "AT&T Wireless"
 } elseif($carrier -eq "103") { $network = "Nextel"
 } elseif($carrier -eq "104") { $network = "Sprint PCS"
 } elseif($carrier -eq "105") { $network = "Verizon Wireless"
 } elseif($carrier -eq "106") { $network = "Alltel"
 } elseif($carrier -eq "107") { $network = "Rogers AT&T"
 } elseif($carrier -eq "108") { $network = "Microcell"
 } elseif($carrier -eq "109") { $network = "Bell Mobility"
 } elseif($carrier -eq "110") { $network = "BT Cellnet"
 } elseif($carrier -eq "111") { $network = "O2 Germany"
 } elseif($carrier -eq "112") { $network = "Digifone"
 } elseif($carrier -eq "113") { $network = "Telfort"
 } elseif($carrier -eq "114") { $network = "T-Mobile Germany Austria"
 } elseif($carrier -eq "115") { $network = "Tim Italy"
 } elseif($carrier -eq "116") { $network = "Hutchison"
 } elseif($carrier -eq "117") { $network = "Bouygues Telecom"
 } elseif($carrier -eq "118") { $network = "Vodafone SFR France"
 } elseif($carrier -eq "119") { $network = "Orange France"
 } elseif($carrier -eq "120") { $network = "Vodafone UK Netherlands"
 } elseif($carrier -eq "121") { $network = "Telcel Mexico"
 } elseif($carrier -eq "122") { $network = "Telstra"
 } elseif($carrier -eq "123") { $network = "T-Mobile UK"
 } elseif($carrier -eq "124") { $network = "Vodafone Germany"
 } elseif($carrier -eq "125") { $network = "O2 UK Ireland Isle Of Man Netherlands"
 } elseif($carrier -eq "126") { $network = "Telus"
 } elseif($carrier -eq "127") { $network = "Smart"
 } elseif($carrier -eq "128") { $network = "Starhub"
 } elseif($carrier -eq "129") { $network = "Telefonica Spain"
 } elseif($carrier -eq "130") { $network = "Vodafone Switzerland Swisscom"
 } elseif($carrier -eq "131") { $network = "Cable Wireless West Indies"
 } elseif($carrier -eq "132") { $network = "Vodafone Italy"
 } elseif($carrier -eq "133") { $network = "Vodafone Spain"
 } elseif($carrier -eq "134") { $network = "T-Mobile Netherlands"
 } elseif($carrier -eq "135") { $network = "Cincinnati Bell"
 } elseif($carrier -eq "136") { $network = "Telefonica Mexico"
 } elseif($carrier -eq "137") { $network = "Vodafone Austria"
 } elseif($carrier -eq "138") { $network = "Vodafone Australia Fiji"
 } elseif($carrier -eq "139") { $network = "Vodafone Ireland"
 } elseif($carrier -eq "140") { $network = "Telenor Sweden"
 } elseif($carrier -eq "141") { $network = "CSL"
 } elseif($carrier -eq "142") { $network = "Orange UK"
 } elseif($carrier -eq "143") { $network = "Vodafone New Zealand"
 } elseif($carrier -eq "144") { $network = "Singtel"
 } elseif($carrier -eq "145") { $network = "Globe"
 } elseif($carrier -eq "146") { $network = "Optus"
 } elseif($carrier -eq "147") { $network = "Orange Be Mobistar"
 } elseif($carrier -eq "148") { $network = "Vodafone Hungary"
 } elseif($carrier -eq "149") { $network = "Bharti"
 } elseif($carrier -eq "150") { $network = "KPN NL"
 } elseif($carrier -eq "151") { $network = "Wind Hellas Tim Greece"
 } elseif($carrier -eq "152") { $network = "Vodafone Belgium"
 } elseif($carrier -eq "153") { $network = "Vodafone Portugal"
 } elseif($carrier -eq "154") { $network = "Tim Brazil"
 } elseif($carrier -eq "155") { $network = "BT-Mobile"
 } elseif($carrier -eq "156") { $network = "Earthlink"
 } elseif($carrier -eq "157") { $network = "Aether"
 } elseif($carrier -eq "158") { $network = "E Plus"
 } elseif($carrier -eq "159") { $network = "Base"
 } elseif($carrier -eq "160") { $network = "Dobson Communications"
 } elseif($carrier -eq "161") { $network = "Vodafone Egypt"
 } elseif($carrier -eq "162") { $network = "Orange Switzerland"
 } elseif($carrier -eq "163") { $network = "Rim Wlan"
 } elseif($carrier -eq "164") { $network = "T-Mobile Suncom"
 } elseif($carrier -eq "165") { $network = "Maxis"
 } elseif($carrier -eq "166") { $network = "Vodafone Denmark TDC"
 } elseif($carrier -eq "167") { $network = "Vodafone Singapore M1"
 } elseif($carrier -eq "168") { $network = "Vodacom South Africa"
 } elseif($carrier -eq "169") { $network = "T-Mobile Poland"
 } elseif($carrier -eq "170") { $network = "T-Mobile Czech"
 } elseif($carrier -eq "171") { $network = "T-Mobile Hungary"
 } elseif($carrier -eq "172") { $network = "AT&T Sprint"
 } elseif($carrier -eq "173") { $network = "Mtn South Africa"
 } elseif($carrier -eq "174") { $network = "Tim Chile Entel PCS"
 } elseif($carrier -eq "175") { $network = "Orange Spain"
 } elseif($carrier -eq "176") { $network = "Vodafone Smartone Hong Kong"
 } elseif($carrier -eq "177") { $network = "TCS Telecommunication Systems"
 } elseif($carrier -eq "178") { $network = "Avea"
 } elseif($carrier -eq "179") { $network = "Fast 100"
 } elseif($carrier -eq "180") { $network = "Turkcell"
 } elseif($carrier -eq "181") { $network = "Partner Communications"
 } elseif($carrier -eq "183") { $network = "Orange Romania"
 } elseif($carrier -eq "186") { $network = "Telkomsel"
 } elseif($carrier -eq "188") { $network = "Vodafone Greece"
 } elseif($carrier -eq "189") { $network = "United States Cellular Corp"
 } elseif($carrier -eq "190") { $network = "Mobilink"
 } elseif($carrier -eq "191") { $network = "Velocita Wireless"
 } elseif($carrier -eq "192") { $network = "Vodafone Croatia"
 } elseif($carrier -eq "193") { $network = "Vodafone Slovenia"
 } elseif($carrier -eq "194") { $network = "Vodafone Luxembourg"
 } elseif($carrier -eq "195") { $network = "Vodafone Iceland"
 } elseif($carrier -eq "196") { $network = "Vodafone Fiji"
 } elseif($carrier -eq "197") { $network = "Vodafone Romania"
 } elseif($carrier -eq "198") { $network = "Vodafone Czech"
 } elseif($carrier -eq "199") { $network = "Vodafone Bahrain"
 } elseif($carrier -eq "200") { $network = "Vodafone Kuwait"
 } elseif($carrier -eq "201") { $network = "T-Mobile Croatia"
 } elseif($carrier -eq "202") { $network = "T-Mobile Slovakia"
 } elseif($carrier -eq "203") { $network = "Nortel"
 } elseif($carrier -eq "204") { $network = "China Mobile"
 } elseif($carrier -eq "205") { $network = "Movilnet"
 } elseif($carrier -eq "209") { $network = "Sympac"
 } elseif($carrier -eq "210") { $network = "Personal Argentina"
 } elseif($carrier -eq "212") { $network = "Etisalat UAE"
 } elseif($carrier -eq "213") { $network = "Cbeyond"
 } elseif($carrier -eq "214") { $network = "AMX"
 } elseif($carrier -eq "215") { $network = "Telefonica Venezuela"
 } elseif($carrier -eq "216") { $network = "Telefonica Brazil"
 } elseif($carrier -eq "217") { $network = "Orange Romania"
 } elseif($carrier -eq "218") { $network = "Ktpowertel Korea"
 } elseif($carrier -eq "219") { $network = "Rolling Stones"
 } elseif($carrier -eq "220") { $network = "Docomo"
 } elseif($carrier -eq "222") { $network = "Vodafone Bulgaria"
 } elseif($carrier -eq "223") { $network = "Nextel International"
 } elseif($carrier -eq "224") { $network = "PCCW Sunday"
 } elseif($carrier -eq "225") { $network = "Hawaiian Telcom Credo Mobile"
 } elseif($carrier -eq "226") { $network = "Verizon Mvno"
 } elseif($carrier -eq "227") { $network = "Mobily"
 } elseif($carrier -eq "228") { $network = "BWA"
 } elseif($carrier -eq "229") { $network = "O2 Czech Republic"
 } elseif($carrier -eq "230") { $network = "Hutchison India"
 } elseif($carrier -eq "231") { $network = "Celcom"
 } elseif($carrier -eq "234") { $network = "Dialog"
 } elseif($carrier -eq "235") { $network = "XL"
 } elseif($carrier -eq "236") { $network = "Reliance"
 } elseif($carrier -eq "237") { $network = "Verizon Wireless Wholesale"
 } elseif($carrier -eq "238") { $network = "Vodafone Turkey"
 } elseif($carrier -eq "239") { $network = "Telefonica Morocco Meditel"
 } elseif($carrier -eq "240") { $network = "Indosat"
 } elseif($carrier -eq "241") { $network = "Alcatel Shanghai Bell"
 } elseif($carrier -eq "245") { $network = "3 UK Italy Sweden Denmark Austria Ireland"
 } elseif($carrier -eq "247") { $network = "Vodafone Essar"
 } elseif($carrier -eq "248") { $network = "Centennial Wireless"
 } elseif($carrier -eq "250") { $network = "T-Mobile Austria"
 } elseif($carrier -eq "254") { $network = "OI Brazil"
 } elseif($carrier -eq "255") { $network = "Telecom New Zealand"
 } elseif($carrier -eq "258") { $network = "Hutchinson 3G Australia"
 } elseif($carrier -eq "259") { $network = "Cable & Wireless Trinidad Tobago"
 } elseif($carrier -eq "268") { $network = "Bmobile"
 } elseif($carrier -eq "269") { $network = "Tata Teleservices India"
 } elseif($carrier -eq "271") { $network = "T-Mobile Croatia"
 } elseif($carrier -eq "273") { $network = "BT Italy"
 } elseif($carrier -eq "274") { $network = "1&1"
 } elseif($carrier -eq "277") { $network = "MTS Mobility"
 } elseif($carrier -eq "278") { $network = "Virgin Mobile"
 } elseif($carrier -eq "280") { $network = "Orange Slovakia"
 } elseif($carrier -eq "282") { $network = "Taiwan Mobile"
 } elseif($carrier -eq "285") { $network = "Orange Austria"
 } elseif($carrier -eq "286") { $network = "Vodafone Malta"
 } elseif($carrier -eq "288") { $network = "Base Jim Mobile"
 } elseif($carrier -eq "295") { $network = "CMCC Peoples"
 } elseif($carrier -eq "298") { $network = "Digitel Wireless"
 } elseif($carrier -eq "299") { $network = "SK Telecom"
 } elseif($carrier -eq "300") { $network = "Solo Mobile"
 } elseif($carrier -eq "301") { $network = "Carphone Warehouse"
 } elseif($carrier -eq "302") { $network = "20:20 Mobile Group"
 } elseif($carrier -eq "308") { $network = "XL Indonesia"
 } elseif($carrier -eq "309") { $network = "Fido Solutions"
 } elseif($carrier -eq "310") { $network = "Wind Italy"
 }
 return $network
}
#--------------------------------------------------------------------------------------------------#
Set-Variable -name forestRootDn -option Constant -value ([ADSI]("LDAP://" + (([System.DirectoryServices.ActiveDirectory.Forest]::GetCurrentForest()).name) + "/rootDSE")).defaultNamingContext
# Modify the array below to include your SQL servers or local SQL instance on a BES
Set-Variable -name sqlServers -option Constant -value @("east-coast-sql.usa.ad.mydomain.local","west-coast-sql.usa.ad.mydomain.local","remote-bes-server.japan.ad.mydomain.local")
#--------------------------------------------------------------------------------------------------#
$blackberryUsers = @()

$objectConnection = New-Object -comObject "ADODB.Connection"
$objectCommand = New-Object -comObject "ADODB.Command"
$objectConnection.Open("Provider=ADsDSOObject;")
$objectCommand.ActiveConnection = $objectConnection

$ldapBase = "GC://$forestRootDn"
$ldapAttr = "distinguishedName,sAMAccountName,givenName,sn,homeMDB,telephoneNumber,streetAddress,l,st,postalCode,c"
$ldapScope = "subtree"

foreach($sqlServer in $sqlServers) {

 $sqlConnection = New-Object System.Data.SQLClient.SQLConnection
 $sqlConnection.ConnectionString = "server=$sqlServer;database=BESMgmt;trusted_connection=true;"
 $sqlConnection.Open()
 
 $sqlCommand = New-Object System.Data.SQLClient.SQLCommand
 $sqlCommand.Connection = $sqlConnection
 $sqlCommand.CommandText = "SELECT u.DisplayName as DisplayName, u.MailboxSMTPAddr as MailboxSMTPAddr, u.PIN as PIN, d.phonenumber as phonenumber, d.ModelName as ModelName, d.VendorID as VendorID, d.IMEI as IMEI, d.HomeNetwork as HomeNetwork, d.PasswordEnabled as PasswordEnabled, s.MachineName as MachineName FROM UserConfig u, SyncDeviceMgmtSummary d, ServerConfig s WHERE u.id = d.userconfigid and u.ServerConfigId = s.id"
 
 $sqlDataReader = $sqlCommand.ExecuteReader()
 
 if($sqlDataReader.HasRows -eq  $true) {
  while($sqlDataReader.Read()) {
   $displayName = $sqlDataReader.Item("DisplayName")
   $proxyAddress = $sqlDataReader.Item("MailboxSMTPAddr")
   $pin = $sqlDataReader.Item("PIN")
   $phoneNumber = $sqlDataReader.Item("phonenumber")
   $modelName = $sqlDataReader.Item("ModelName")
   $vendorId = $sqlDataReader.Item("VendorID")
   $imei = $sqlDataReader.Item("IMEI")
   $homeNetwork = $sqlDataReader.Item("homeNetwork")
   $passwordEnabled = $sqlDataReader.Item("PasswordEnabled")
   $blackberryServer = $sqlDataReader.Item("MachineName")

  if($passwordEnabled -eq 1) { 
    $passwordEnabled = $true
   } else { 
    $passwordEnabled = $false
   }
 
   $phoneNumber = $phoneNumber -Replace "^\+",""
 
   if(($phoneNumber).length -eq 10) {
    $phoneNumber = ("(" + ($phoneNumber).SubString(0,3) + ") " + ($phoneNumber).SubString(3,3) + "-" + ($phoneNumber).SubString(6,4))
   }
 
   if(($phoneNumber).length -eq 11 -and ($phoneNumber).SubString(0,1) -eq "1") {
    $phoneNumber = ("(" + ($phoneNumber).SubString(1,3) + ") " + ($phoneNumber).SubString(4,3) + "-" + ($phoneNumber).SubString(7,4))
   }
 
   if($homeNetwork -eq "" -or $homeNetwork -cmatch "vodafone") {
    $homeNetwork = Get-CarrierNetwork $vendorId
   }
 
   $ldapFilter = ("(&(objectCategory=person)(objectClass=user)(proxyAddresses=smtp:$proxyAddress))")
   $ldapQuery = "<$ldapBase>;$ldapFilter;$ldapAttr;$ldapScope"
 
   $objectCommand.CommandText = $ldapQuery
   $objectRecordSet = $objectCommand.Execute()
 
   while(!$objectRecordSet.EOF) {
    $domain = ((Get-ObjectADDomain $objectRecordSet.Fields.Item('distinguishedName').Value).Split(".")[0]).ToUpper()
    $exchangeServer = ((((((Get-ActiveDirectoryObject $objectRecordSet.Fields.Item('homeMDB').Value).psbase.parent).psbase.parent).psbase.parent).networkAddress[4]).ToString() -replace "ncacn_ip_tcp:","").ToLower()
    $sAMAccountName = $objectRecordSet.Fields.Item('sAMAccountName').Value
    $firstName = $objectRecordSet.Fields.Item('givenName').Value
    $lastName = $objectRecordSet.Fields.Item('sn').Value
    $telephoneNumber = $objectRecordSet.Fields.Item('telephoneNumber').Value
    $streetAddress = ($objectRecordSet.Fields.Item('streetAddress').Value -replace "`r`n",", ")
    $city = $objectRecordSet.Fields.Item('l').Value
    $state = $objectRecordSet.Fields.Item('st').Value
    $zipCode = $objectRecordSet.Fields.Item('postalCode').Value
    $country = $objectRecordSet.Fields.Item('c').Value
    $objectRecordSet.MoveNext()
   }

   $blackberryUser = New-Object -typeName PSObject
   Add-Member -inputObject $blackberryUser -type NoteProperty -name "domain" -value $domain
   Add-Member -inputObject $blackberryUser -type NoteProperty -name "sAMAccountName" -value $sAMAccountName
   Add-Member -inputObject $blackberryUser -type NoteProperty -name "firstName" -value $firstName
   Add-Member -inputObject $blackberryUser -type NoteProperty -name "lastName" -value $firstName
   Add-Member -inputObject $blackberryUser -type NoteProperty -name "displayName" -value $displayName
   Add-Member -inputObject $blackberryUser -type NoteProperty -name "emailAddress" -value $proxyAddress
   Add-Member -inputObject $blackberryUser -type NoteProperty -name "pin" -value $pin
   Add-Member -inputObject $blackberryUser -type NoteProperty -name "blackberryPhoneNumber" -value $phoneNumber
   Add-Member -inputObject $blackberryUser -type NoteProperty -name "officePhoneNumber" -value $telephoneNumber
   Add-Member -inputObject $blackberryUser -type NoteProperty -name "streetAddress" -value $streetAddress
   Add-Member -inputObject $blackberryUser -type NoteProperty -name "city" -value $city
   Add-Member -inputObject $blackberryUser -type NoteProperty -name "state" -value $state
   Add-Member -inputObject $blackberryUser -type NoteProperty -name "zipCode" -value $zipCode
   Add-Member -inputObject $blackberryUser -type NoteProperty -name "country" -value $country
   Add-Member -inputObject $blackberryUser -type NoteProperty -name "modelName" -value $modelName
   Add-Member -inputObject $blackberryUser -type NoteProperty -name "carrier" -value $homeNetwork
   Add-Member -inputObject $blackberryUser -type NoteProperty -name "imei" -value $imei
   Add-Member -inputObject $blackberryUser -type NoteProperty -name "passwordEnabled" -value $passwordEnabled
   Add-Member -inputObject $blackberryUser -type NoteProperty -name "exchangeServer" -value $exchangeServer
   Add-Member -inputObject $blackberryUser -type NoteProperty -name "blackberryServer" -value $blackberryServer 
   $blackberryUsers += $blackberryUser
  }
 }  else {
  Write-Warning "Unable to obtain BES data from $sqlServer"
  exit
 }
}
$blackberryUsers | Export-Csv -path "Blackberry Users.csv" -noTypeInformation