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