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