Exchange Mailbox Report

Whether in an environment I am familiar or one that is new to me, information about it is important to my management of it. In a large environment, having timely information readily available is key to making good decisions. It can take hours to generate that data and that decision might be time sensitive enough that I can't wait. That is why I run daily, weekly and monthly audits of key systems and store them away for later retrieval. Microsoft Exchange is just one of those systems where I am constantly making business decisions based on daily audits. Who are the employees with the biggest mailboxes? Which server has the most messages? How active is a mailbox? Which mailbox server is under-utilized and which is overloaded? What is the combined storage used by all messages in the organization?

Below is a PowerShell script that I have developed over time to provide relevant information about Exchange mailbox servers and the employee mailboxes contained within. It assumes that you have at least the Exchange 2007 PSSnapin available to execute but will detect if the Exchange 2010 PSSnapin is available and load it. Because I run into diverse installations of Exchange, the script can report on Exchange 2003, Exchange 2007 and Exchange 2010 mailbox servers. The largest environment I have successfully tested had all three versions in an Exchange organization, 40 mailbox servers and covered installations in multiple countries worldwide. The end result will be a nicely formatted Excel spreadsheet with an overview worksheet hyperlinking to a worksheet for each of the mailbox servers with at least 1 mailbox in the workbook. Because I can run into new and unknown environments, the PowerShell script has fairly detailed logging. You need the permission to read mailboxes on each of the Exchange mailbox servers with security context you execute the script. The logging will catch permission errors and log them. Unmodified, it will store the logs, work files and final spreadsheet in child directories of the root of the executed script. Review the Set-Variable statements after the Functions and update them to your needs. In its current state, it will not delete work files allowing you to see the raw product used to produce the Excel spreadsheet.

#--------------------------------------------------------------------------------------------------#
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 Write-LogEntry($logEntry,$logFile) {
 Add-Content -Path $logFile -Value ((Get-TimeStamp) + " $logEntry")
}
#--------------------------------------------------------------------------------------------------#
Function Write-LogEntryLineBreak($logFile) {
 Write-LogEntry "#-----------------------------------------------------------------------------------#" $logFile
}
#--------------------------------------------------------------------------------------------------#
Function Get-TimeStamp {
 return (Get-Date -format yyyyMMddHHmmss)
}
#--------------------------------------------------------------------------------------------------#
Function Get-DayStamp {
 return (Get-Date -format yyyyMMdd)
}
#--------------------------------------------------------------------------------------------------#
Function Get-RunTime($runTime) {
 $runTimeHours = ($runTime.Hours).ToString()
 $runTimeMinutes = ($runTime.Minutes).ToString()
 $runTimeSeconds = ($runTime.Seconds).ToString()
 if($runTimeHours.Length -eq 1) {
  $runTimeHours = "0$runTimeHours"
 }
 if($runTimeMinutes.Length -eq 1) {
  $runTimeMinutes = "0$runTimeMinutes"
 }
 if($runTimeSeconds.Length -eq 1) {
  $runTimeSeconds = "0$runTimeSeconds"
 }
 return ($runTimeHours + ":" + $runTimeMinutes + ":" + $runTimeSeconds)
}
#--------------------------------------------------------------------------------------------------#
Function Initialize-Directory($directoryPath) {
 if(!(Test-Path -Path $directoryPath)) {
  New-Item -Path $directoryPath -Type Directory | Out-Null
 }
 if(Test-Path -Path $directoryPath) {
  $success = $true
 } else {
  $success = $false
 }
 return $success
}
#--------------------------------------------------------------------------------------------------#
Function Load-ExchangeSnapin {
 $registeredPsSnapins = @(Get-PSSnapin -registered | ForEach-Object { $_.Name })
 $loadedPsSnapins = Get-PSSnapin
 if($registeredPsSnapins -contains "Microsoft.Exchange.Management.PowerShell.E2010") {
  $snapinLoaded = $false
  foreach($snapin in $loadedPsSnapins) {
   if($snapin.name -eq "Microsoft.Exchange.Management.PowerShell.E2010") {
    $snapinLoaded = $true
   }
  }
  if($snapinLoaded -eq $false) {
   Add-PSSnapin -Name 'Microsoft.Exchange.Management.PowerShell.E2010'
  }
 } elseif($registeredPsSnapins -contains "Microsoft.Exchange.Management.PowerShell.Admin" -and $registeredPsSnapins -contains "Microsoft.Exchange.Management.PowerShell.Support") {
  $adminPsSnapinLoaded = $false
  $supportPsSnapinLoaded = $false
  foreach($snapin in $loadedPsSnapins) {
   if($snapin.name -eq "Microsoft.Exchange.Management.PowerShell.Admin") {
    $adminPsSnapinLoaded = $true
   }
   if($snapin.name -eq "Microsoft.Exchange.Management.PowerShell.Support") {
    $supportPsSnapinLoaded = $true
   }
  }
  if($adminPsSnapinLoaded -eq $false) {
   Add-PSSnapin -Name "Microsoft.Exchange.Management.PowerShell.Admin"
  }
  if($supportPsSnapinLoaded -eq $false) {
   Add-PSSnapin -Name "Microsoft.Exchange.Management.PowerShell.Support"
  }
 }
}
#--------------------------------------------------------------------------------------------------#
Function Unload-ExchangeSnapin {
 $registeredPsSnapins = @(Get-PSSnapin -registered | ForEach-Object { $_.Name })
 $loadedPsSnapins = Get-PSSnapin
 if($registeredPsSnapins -contains "Microsoft.Exchange.Management.PowerShell.E2010") {
  $snapinLoaded = $false
  foreach($snapin in $loadedPsSnapins) {
   if($snapin.name -eq "Microsoft.Exchange.Management.PowerShell.E2010") {
    $snapinLoaded = $true
   }
  }
  if($snapinLoaded -eq $true) {
   Remove-PSSnapin -Name 'Microsoft.Exchange.Management.PowerShell.E2010'
  }
 } elseif($registeredPsSnapins -contains "Microsoft.Exchange.Management.PowerShell.Admin" -and $registeredPsSnapins -contains "Microsoft.Exchange.Management.PowerShell.Support") {
  $adminPsSnapinLoaded = $false
  $supportPsSnapinLoaded = $false
  foreach($snapin in $loadedPsSnapins) {
   if($snapin.name -eq "Microsoft.Exchange.Management.PowerShell.Admin") {
    $adminPsSnapinLoaded = $true
   }
   if($snapin.name -eq "Microsoft.Exchange.Management.PowerShell.Support") {
    $supportPsSnapinLoaded = $true
   }
  }
  if($adminPsSnapinLoaded -eq $true) {
   Remove-PSSnapin -Name "Microsoft.Exchange.Management.PowerShell.Admin"
  }
  if($supportPsSnapinLoaded -eq $true) {
   Remove-PSSnapin -Name "Microsoft.Exchange.Management.PowerShell.Support"
  }
 }
}
#--------------------------------------------------------------------------------------------------#
Function Get-UserInformation($legacyExchangeDn) {
 $directorySearcher = New-Object System.DirectoryServices.DirectorySearcher
 $directorySearcher.SearchRoot = (New-Object System.DirectoryServices.DirectoryEntry("GC://$forestRootDn"))
 $directorySearcher.Filter = ("(&(objectclass=user)(objectCategory=person)(legacyExchangeDN=$legacyExchangeDn))")
 $directorySearcher.PropertiesToLoad.Clear()
 $directorySearcher.PropertiesToLoad.Add("distinguishedName") | Out-Null
 $searchResult = $directorySearcher.FindOne()
 $directorySearcher.Dispose()
 if($searchResult) {
  return $searchResult.Properties.Item("distinguishedName")[0]
 } else {
  return $null
 }
}
#--------------------------------------------------------------------------------------------------#
Function Get-StorageLimitInfo($storageLimitInfo, $issuedWarning, $prohibitedSend, $mailboxDisabled, $unlimitedMailboxes) {
 if($storageLimitInfo -eq 1) {
  $storageLimitInfo = "Below Limit"
 } elseif($storageLimitInfo -eq 2) {
  $storageLimitInfo = "Issued Warning"
  $issuedWarning++
 } elseif($storageLimitInfo -eq 4) {
  $storageLimitInfo = "Prohibited Send"
  $prohibitedSend++
 } elseif($storageLimitInfo -eq 8) {
  $storageLimitInfo = "Unlimited Mailbox"
  $unlimitedMailboxes++
 } elseif($storageLimitInfo -eq 16) {
  $storageLimitInfo = "Mailbox Disabled"
  $mailboxDisabled++
 } else {
  $storageLimitInfo = "Disabled Account w/ No Permissions"
  $orphanedMailboxes++
 }
 return $storageLimitInfo, $issuedWarning, $prohibitedSend, $mailboxDisabled, $unlimitedMailboxes, $orphanedMailboxes
}
#--------------------------------------------------------------------------------------------------#
Function Get-StorageLimitStatus($storageLimitStatus, $issuedWarning, $prohibitedSend, $mailboxDisabled, $unlimitedMailboxes) {
 if($storageLimitStatus -eq  "BelowLimit") {
  $storageLimitStatus = "Below Limit"
 } elseif($storageLimitStatus -eq  "IssueWarning") {
  $storageLimitStatus = "Issued Warning"
  $issuedWarning++
 } elseif($storageLimitStatus -eq  "ProhibitSend") {
  $storageLimitStatus = "Prohibited Send"
  $prohibitedSend++
 } elseif($storageLimitStatus -eq  "NoChecking") {
  $storageLimitStatus = "Unlimited Mailbox"
  $unlimitedMailboxes++
 } elseif($storageLimitStatus -eq  "MailboxDisabled") {
  $storageLimitStatus = "Mailbox Disabled"
  $mailboxDisabled++
 } else {
  $storageLimitStatus = "Disabled Account w/ No Permissions"
  $orphanedMailboxes++
 }
 return $storageLimitStatus, $issuedWarning, $prohibitedSend, $mailboxDisabled, $unlimitedMailboxes, $orphanedMailboxes
}
#--------------------------------------------------------------------------------------------------#
Function Get-ExcelTableStyle($current,$tableStyles) {
 $tableStyle = $tableStyles[$current]
 if($current -ge ($tableStyles.Count - 1)) {
  $current = 0
 } else {
  $current++
 }
 return $current, $tableStyle
}
#--------------------------------------------------------------------------------------------------#
Set-Variable -name forestRootDn -option Constant -value ([ADSI]("LDAP://" + (([System.DirectoryServices.ActiveDirectory.Forest]::GetCurrentForest()).name) + "/rootDSE")).defaultNamingContext
Set-Variable -name ping -option Constant -value (New-Object -typeName System.Net.NetworkInformation.Ping)
#--------------------------------------------------------------------------------------------------#
# Typically I set to 2x larger than the standard mailbox size.
Set-Variable -name largeMailboxSize -option Constant -value (1GB / 1KB)
#--------------------------------------------------------------------------------------------------#
Set-Variable -name logDirectory -option Constant -value "$pwd\Logs"
#--------------------------------------------------------------------------------------------------#
# Must be an absolute path for Excel.
Set-Variable -name workDirectory -option Constant -value ($env:temp + "\Work")
#--------------------------------------------------------------------------------------------------#
# Must be an absolute path for Excel.
Set-Variable -name reportDirectory -option Constant -value "$pwd\Reports"
#--------------------------------------------------------------------------------------------------#
Set-Variable -name logFilename -option Constant -value ($logDirectory + "\"  + (Get-DayStamp) + "-" + ($myinvocation.mycommand.name -Replace ".ps1",".txt"))
Set-Variable -name masterExchangeServerFilename -option Constant -value ($workDirectory + "\Master Exchange Servers List.csv")
Set-Variable -name filteredExchangeServerFilename -option Constant -value ($workDirectory + "\Filtered Exchange Servers List.csv")
Set-Variable -name excelFilename -option Constant -value ($reportDirectory + "\Exchange Mailbox Report-" + (Get-DayStamp) + ".xlsx") # Assuming Excel 2007 or better
#--------------------------------------------------------------------------------------------------#
# Style Cheat Sheet in French/English: http://msdn.microsoft.com/fr-fr/library/documentformat.openxml.spreadsheet.tablestyle.aspx
Set-Variable -name excelTableStyles -option Constant -value @("TableStyleMedium16","TableStyleMedium17","TableStyleMedium18","TableStyleMedium19","TableStyleMedium20","TableStyleMedium21")
#--------------------------------------------------------------------------------------------------#
Set-Variable -name excelCurrentTableStyle -value 0
#--------------------------------------------------------------------------------------------------#
# Set to $false to review data generated by the mailbox scans.
# Set to $true to delete the work directory at completion.
Set-Variable -name cleanupWorkDirectory -option Constant -value $false
#--------------------------------------------------------------------------------------------------#
# If $true, no screen output will be displayed. Best for a scheduled task.
# $false if you like staring at progress bars along with superfluous and gratuitous information.
Set-Variable -name beQuiet -option Constant -value $false
#--------------------------------------------------------------------------------------------------#
Set-Variable -name sheetNumber -value 1
Set-Variable -name serverCount -value 1
#--------------------------------------------------------------------------------------------------#
$startTime = Get-Date
if(!$beQuiet) { Clear-Host }
if(!$beQuiet) { Write-Progress  -id 1 -activity "Exchange Server Report" -status "Setup Environment" -percentComplete "50" }
if((Initialize-Directory $logDirectory) -eq $false) {
 Write-Host "Unable to access $logDirectory for writing logs. Exiting." -Foregroundcolor Red
 Set-Content -path ("$pwd\" + ($myinvocation.mycommand.name -Replace ".ps1","") + " MAJOR ERROR.txt") -value "Unable to access $logDirectory for writing logs. Exiting."
 exit
}
if(!$beQuiet) { Write-Progress  -id 1 -activity "Exchange Server Report" -status "Setup Environment" -percentComplete "75" }
if((Initialize-Directory $workDirectory) -eq $false) {
 Write-Host "Unable to access $workDirectory for writing temp files. Exiting." -Foregroundcolor Red
 Write-LogEntry "[ERROR] Unable to access $workDirectory for writing temp files. Exiting." $logFilename
 exit
}
if((Initialize-Directory $reportDirectory) -eq $false) {
 Write-Host "Unable to access $reportDirectory for writing the Excel file. Exiting." -Foregroundcolor Red
 Write-LogEntry "[ERROR] Unable to access $reportDirectory for writing the Excel file. Exiting." $logFilename
 exit
}
Write-LogEntry ("Log Directory: " + $logDirectory) $logFilename
Write-LogEntry ("Work Directory: " + $workDirectory) $logFilename
Write-LogEntry ("Report Directory: " + $reportDirectory) $logFilename
Write-LogEntry ("Log Filename: " + $logFilename) $logFilename
Write-LogEntry ("Master Server List Filename: " + $masterExchangeServerFilename) $logFilename
Write-LogEntry ("Filtered Server List Filename: " + $filteredExchangeServerFilename) $logFilename
Write-LogEntry ("Excel Filename: " + $excelFilename) $logFilename
if(!$beQuiet) { Write-Progress  -id 1 -activity "Exchange Server Report" -status "Setup Environment" -percentComplete "100" }
if(!$beQuiet) { Write-Progress  -id 1 -activity "Exchange Server Report" -status "Loading Exchange Snapin" -percentComplete "50" }
Write-LogEntry "Loading Exchange Snapin" $logFilename
Load-ExchangeSnapin
Write-LogEntry "Exchange Snapin loaded" $logFilename
if(!$beQuiet) { Write-Progress  -id 1 -activity "Exchange Server Report" -status "Loading Exchange Snapin" -percentComplete "100" }
if(!$beQuiet) { Write-Progress  -id 1 -activity "Exchange Server Report" -status "Finding all Exchange Servers" -percentComplete "50" }
Write-LogEntry "Discover Exchange Servers" $logFilename
$exchangeServers = @((Get-ExchangeServer | Where-Object { $_.ServerRole -match "Mailbox" -or $_.ServerRole -eq "None" } ) | Select-Object -property Name,Fqdn,AdminDisplayVersion,Edition,ExchangeVersion,DistinguishedName,WhenChanged,WhenCreated | Sort-Object -property Name)
Write-LogEntry ($exchangeServers.Count.ToString() + " Exchange Servers found") $logFilename
if(!$beQuiet) { Write-Progress  -id 1 -activity "Exchange Server Report" -status "Finding all Exchange Servers" -percentComplete "100" }
$serversScanned = 0
foreach($exchangeServer in $exchangeServers) {
 $serversScanned++
 $exchangeServer.Name = ($exchangeServer.Name).ToUpper()
 $exchangeServer.Fqdn = ($exchangeServer.Fqdn).ToLower()
 Write-LogEntry $exchangeServer.Fqdn $logFilename
 if(!$beQuiet) { Write-Progress  -id 1 -activity "Exchange Server Report" -status ("Gathering information on " + $exchangeServer.Fqdn) -percentComplete (($serversScanned / $exchangeServers.Count) * 100) }
 Write-LogEntry "IPV4 Address Discovery" $logFilename
 try {
  $hostAddresses = [System.Net.Dns]::GetHostAddresses($exchangeServer.Fqdn)
     Add-Member -inputObject $exchangeServer -type NoteProperty -name "ipV4Address" -value $hostAddresses[0].IPAddressToString
 } catch {
  Add-Member -inputObject $exchangeServer -type NoteProperty -name "ipV4Address" -value "0.0.0.0"
 }
 Write-LogEntry ("IPV4 Address:" + $exchangeServer.ipV4Address) $logFilename
 Write-LogEntry "Testing if server online" $logFilename
 if($exchangeServer.ipV4Address -ne "0.0.0.0") {
  $onlineStatus = $ping.send($exchangeServer.ipV4Address)
  if($onlineStatus.Status -eq "Success") {
   Add-Member -inputObject $exchangeServer -type NoteProperty -name "online" -value $true
  } else {
   Add-Member -inputObject $exchangeServer -type NoteProperty -name "online" -value $false
  }
 } else {
  Add-Member -inputObject $exchangeServer -type NoteProperty -name "online" -value $false
 }
 Write-LogEntry ("Online status:" + $exchangeServer.online) $logFilename
 Write-LogEntry "Discovering number of databases" $logFilename
 if($exchangeServer.online -eq $true) {# -and $exchangeServer.configurationDistinguishedName -ne "Unknown" -and $exchangeServer.version -ne "Unknown") {
  if($exchangeServer.AdminDisplayVersion -match "Version 6.") {
   $directorySearcher = New-Object System.DirectoryServices.DirectorySearcher
   $directorySearcher.SearchRoot = (New-Object System.DirectoryServices.DirectoryEntry("GC://" + $exchangeServer.DistinguishedName))
   $directorySearcher.Filter = "(&(objectclass=msExchStorageGroup))"
   $directorySearcher.PropertiesToLoad.Clear()
   $directorySearcher.PropertiesToLoad.Add("distinguishedName") | Out-Null
   $exchangeSearchResults = $directorySearcher.FindAll()
   $directorySearcher.Dispose()
   Add-Member -inputObject $exchangeServer -type NoteProperty -name "numberOfStores" -value $exchangeSearchResults.Count
  } else {
   Add-Member -inputObject $exchangeServer -type NoteProperty -name "numberOfStores" -value @(Get-MailboxDatabase -server $exchangeServer.Fqdn).Count
  }
 } else {
  Add-Member -inputObject $exchangeServer -type NoteProperty -name "numberOfStores" -value "0"
 }
 if($exchangeServer.numberOfStores -eq "" -or $exchangeServer.numberOfStores -eq $null) { $exchangeServer.numberOfStores = 0 }
 Write-LogEntry ("Databases:" + $exchangeServer.numberOfStores) $logFilename
 Write-LogEntryLineBreak $logFilename
}
$mailboxes = @()
$serversScanned = 0
foreach($exchangeServer in $exchangeServers) {
 Write-LogEntry ("Scanning Mailboxes on " + $exchangeServer.Fqdn) $logFilename
 $serversScanned++
 if(!$beQuiet) { Write-Progress  -id 1 -activity "Exchange Server Report" -status ("Scanning Mailboxes: " + $exchangeServer.Fqdn) -percentComplete (($serversScanned / $exchangeServers.Count) * 100) }
 $mailboxesScanned = 0
 $mailboxCount = 0
 $totalStorage = 0
 $totalMessages = 0
 $issuedWarning = 0
 $prohibitedSend = 0
 $mailboxDisabled = 0
 $unlimitedMailboxes = 0
 $orphanedMailboxes = 0
 $largeMailboxes = 0
 $userMailboxes = @()
 if($exchangeServer.numberOfStores -eq 0) {
  Add-Member -inputObject $exchangeServer -type NoteProperty -name "mailboxCount" -value $mailboxCount
  Write-LogEntry ("There are no mailboxes on " + $exchangeServer.Fqdn) $logFilename
  Write-LogEntryLineBreak $logFilename
  continue
 }
 if($exchangeServer.AdminDisplayVersion -match "Version 6.") {
  Write-LogEntry "Exchange 2003 Server" $logFilename
  if(!$beQuiet) { Write-Progress  -id 1 -activity "Exchange Server Report" -status ("Loading Mailboxes on " + $exchangeServer.Fqdn) -percentComplete (($serversScanned / $exchangeServers.Count) * 100) }
  $error.clear()
  try {
   $mailboxes = @(Get-WmiObject -ComputerName $exchangeServer.Fqdn -Namespace "root\MicrosoftExchangeV2" -Class Exchange_Mailbox)
  } catch {
   Write-LogEntry ("[ERROR] Unable to obtain mailboxes on " + $exchangeServer.Fqdn) $logFilename
   foreach($errorEntry in $error) {
    Write-LogEntry $errorEntry $logFilename
   }
   $mailboxes = @()
   Add-Member -inputObject $exchangeServer -type NoteProperty -name "totalMessages" -value $totalMessages
   Add-Member -inputObject $exchangeServer -type NoteProperty -name "totalStorage" -value $totalStorage
   Add-Member -inputObject $exchangeServer -type NoteProperty -name "mailboxCount" -value $mailboxCount
   Add-Member -inputObject $exchangeServer -type NoteProperty -name "issuedWarning" -value $issuedWarning
   Add-Member -inputObject $exchangeServer -type NoteProperty -name "prohibitedSend" -value $prohibitedSend
   Add-Member -inputObject $exchangeServer -type NoteProperty -name "mailboxDisabled" -value $mailboxDisabled
   Add-Member -inputObject $exchangeServer -type NoteProperty -name "unlimitedMailboxes" -value $unlimitedMailboxes
   Add-Member -inputObject $exchangeServer -type NoteProperty -name "orphanedMailboxes" -value $orphanedMailboxes
   Add-Member -inputObject $exchangeServer -type NoteProperty -name "largeMailboxes" -value $largeMailboxes
   Write-LogEntryLineBreak $logFilename
   continue
  }
  if(!$beQuiet) { Write-Progress  -id 1 -activity "Exchange Server Report" -status ("Reviewing Mailboxes on " + $exchangeServer.Fqdn) -percentComplete (($serversScanned / $exchangeServers.Count) * 100) }
  Write-LogEntry ($mailboxes.Count.ToString() + " mailboxes found") $logFilename
  foreach($mailbox in $mailboxes) {
   $error.clear()
   $mailboxesScanned++
   if($mailbox.LegacyDN -and $mailbox.LegacyDN -notmatch "CN=MICROSOFT SYSTEM ATTENDANT" -and $mailbox.LegacyDN -notmatch "CN=SMTP" -and $mailbox.LegacyDN -notmatch "CN=SYSTEMMAILBOX") {
    if(!$beQuiet) { Write-Progress  -id 2 -parentId 1 -activity (($mailboxes.Count).ToString() + " Mailboxes to Review") -status ("Scanning Mailbox: " + $mailbox.MailboxDisplayName) -percentComplete (($mailboxesScanned / $mailboxes.Count) * 100) }
    if($mailbox.LastLogonTime -ne $null) {
     $lastLogonTime = Get-Date -year $mailbox.LastLogonTime.ToString().Substring(0,4) -month $mailbox.LastLogonTime.ToString().Substring(4,2) -day $mailbox.LastLogonTime.ToString().Substring(6,2) -hour $mailbox.LastLogonTime.ToString().Substring(8,2) -minute $mailbox.LastLogonTime.ToString().Substring(10,2) -second $mailbox.LastLogonTime.ToString().Substring(12,2) -format G
    } else {
     $lastLogonTime = ""
    }
    $storageLimitInfo, $issuedWarning, $prohibitedSend, $mailboxDisabled, $unlimitedMailboxes, $orphanedMailboxes = Get-StorageLimitInfo $mailbox.StorageLimitInfo $issuedWarning $prohibitedSend $mailboxDisabled $unlimitedMailboxes $orphanedMailboxes
    if($mailbox.Size -gt $largeMailboxSize) {
     $largeMailboxes++
    }
    $userObject = Get-ActiveDirectoryObject (Get-UserInformation $mailbox.LegacyDN)
    $mailboxCount++
    if($userObject.givenName) {
     $userGivenName = $userObject.givenName.ToString()
    } else {
     $userGivenName = ""
    }
    if($userObject.sn) {
     $userSn = $userObject.sn.ToString()
    } else {
     $userSn = ""
    }
    if($userObject.displayName) {
     $userDisplayName = $userObject.displayName.ToString()
    } else {
     $userDisplayName = $mailbox.MailboxDisplayName
    }
    if($userObject -ne $null) {
     $userDomain = (Get-ObjectADDomain $userObject.distinguishedName).Split(".")[0]
    } else {
     $userDomain = ""
    }
    if($userObject.sAMAccountName) {
     $userSAMAccountName = $userObject.sAMAccountName.ToString()
    } else {
     $userSAMAccountName = ""
    }
    if($userObject.mail) {
     $userMail = $userObject.mail.ToString()
    } else {
     $userMail = ""
    }
    $totalMessages += $mailbox.TotalItems
    $totalStorage += $mailbox.Size
    $userMailboxInformation = New-Object -typeName PSObject
    Add-Member -inputObject $userMailboxInformation -type NoteProperty -name "Mailbox Server" -value $exchangeServer.Name
    Add-Member -inputObject $userMailboxInformation -type NoteProperty -name "Storage Group" -value  $mailbox.StorageGroupName
    Add-Member -inputObject $userMailboxInformation -type NoteProperty -name "Store Name" -value  $mailbox.StoreName
    Add-Member -inputObject $userMailboxInformation -type NoteProperty -name "Display Name" -value  $userDisplayName
    Add-Member -inputObject $userMailboxInformation -type NoteProperty -name "First Name" -value  $userGivenName
    Add-Member -inputObject $userMailboxInformation -type NoteProperty -name "Last Name" -value  $userSn
    Add-Member -inputObject $userMailboxInformation -type NoteProperty -name "Domain" -value  $userDomain
    Add-Member -inputObject $userMailboxInformation -type NoteProperty -name "User ID" -value  $userSAMAccountName
    Add-Member -inputObject $userMailboxInformation -type NoteProperty -name "E-Mail Address" -value  $userMail
    Add-Member -inputObject $userMailboxInformation -type NoteProperty -name "Size (KB)" -value ("{0:N0}" -f $mailbox.Size)
    Add-Member -inputObject $userMailboxInformation -type NoteProperty -name "Messages" -value ("{0:N0}" -f $mailbox.TotalItems)
    Add-Member -inputObject $userMailboxInformation -type NoteProperty -name "Storage Limit" -value  $storageLimitInfo
    Add-Member -inputObject $userMailboxInformation -type NoteProperty -name "Last Logon" -value  $lastLogonTime
    Add-Member -inputObject $userMailboxInformation -type NoteProperty -name "Last User Access" -value  $mailbox.LastLoggedOnUserAccount
    $userMailboxes += $userMailboxInformation
    if($error) {
     Write-LogEntry ("[ERROR] Problems found scanning mailbox:" + $userDisplayName) $logFilename
     foreach($errorEntry in $error) {
      Write-LogEntry $errorEntry $logFilename
     }
    }
   } else {
    Write-LogEntry ("Skipping:" + $mailbox.LegacyDN) $logFilename
   }
   if(!$beQuiet) { Write-Progress  -id 2 -parentId 1 -activity "Mailbox review" -status "Completed" -Completed }
  }
 } else {
  if($exchangeServer.AdminDisplayVersion -match "Version 8.") {
   Write-LogEntry "Exchange 2007 Server" $logFilename
  } elseif($exchangeServer.AdminDisplayVersion -match "Version 14.") {
   Write-LogEntry "Exchange 2010 Server" $logFilename
  }
  if(!$beQuiet) { Write-Progress  -id 1 -activity "Exchange Server Report" -status ("Loading Mailboxes on " + $exchangeServer.Fqdn) -percentComplete (($serversScanned / $exchangeServers.Count) * 100) }
  $error.clear()
  try {
   $mailboxes = @(Get-MailboxStatistics -server $exchangeServer.Fqdn | Where-Object { $_.ObjectClass -ne "Mailbox, ExOleDbSystemMailbox" -and $_.DisconnectDate -eq $null } | Select-Object -property DisplayName,ItemCount,LegacyDN,LastLoggedOnUserAccount,LastLogoffTime,LastLogonTime,StorageLimitStatus,TotalDeletedItemSize,TotalItemSize,MailboxTableIdentifier,DatabaseName,OriginatingServer)
  } catch {
   Write-LogEntry ("[ERROR] Unable to obtain mailboxes on " + $exchangeServer.Fqdn)
   foreach($errorEntry in $error) {
    Write-LogEntry $errorEntry $logFilename
   }
   $mailboxes = @()
   Add-Member -inputObject $exchangeServer -type NoteProperty -name "totalMessages" -value $totalMessages
   Add-Member -inputObject $exchangeServer -type NoteProperty -name "totalStorage" -value $totalStorage
   Add-Member -inputObject $exchangeServer -type NoteProperty -name "mailboxCount" -value $mailboxCount
   Add-Member -inputObject $exchangeServer -type NoteProperty -name "issuedWarning" -value $issuedWarning
   Add-Member -inputObject $exchangeServer -type NoteProperty -name "prohibitedSend" -value $prohibitedSend
   Add-Member -inputObject $exchangeServer -type NoteProperty -name "mailboxDisabled" -value $mailboxDisabled
   Add-Member -inputObject $exchangeServer -type NoteProperty -name "unlimitedMailboxes" -value $unlimitedMailboxes
   Add-Member -inputObject $exchangeServer -type NoteProperty -name "orphanedMailboxes" -value $orphanedMailboxes
   Add-Member -inputObject $exchangeServer -type NoteProperty -name "largeMailboxes" -value $largeMailboxes
   Write-LogEntryLineBreak $logFilename
   continue
  }
  Write-LogEntry ($mailboxes.Count.ToString() + " mailboxes found") $logFilename
  if(!$beQuiet) { Write-Progress  -id 1 -activity "Exchange Server Report" -status ("Scanning Mailboxes: " + $exchangeServer.Fqdn) -percentComplete (($serversScanned / $exchangeServers.Count) * 100) }
  foreach($mailbox in $mailboxes) {
   $error.clear()
   $mailboxesScanned++
   if($mailbox.LegacyDN -and $mailbox.LegacyDN -notmatch "CN=MICROSOFT SYSTEM ATTENDANT" -and $mailbox.LegacyDN -notmatch "CN=SMTP" -and $mailbox.LegacyDN -notmatch "CN=SYSTEMMAILBOX") {
    if(!$beQuiet) { Write-Progress  -id 2 -parentId 1 -activity (($mailboxes.Count).ToString() + " Mailboxes to Review") -status ("Scanning Mailbox of " + $mailbox.DisplayName) -percentComplete (($mailboxesScanned / $mailboxes.Count) * 100) }
    if($mailbox.LastLogonTime) {
     $lastLogonTime = Get-Date $mailbox.LastLogonTime -format G
    } else {
     $lastLogonTime = ""
    }
    $storageLimitStatus, $issuedWarning, $prohibitedSend, $mailboxDisabled, $unlimitedMailboxes, $orphanedMailboxes = Get-StorageLimitInfo $mailbox.storageLimitStatus $issuedWarning $prohibitedSend $mailboxDisabled $unlimitedMailboxes $orphanedMailboxes
    $userObject = Get-ActiveDirectoryObject (Get-UserInformation $mailbox.LegacyDN)
    $mailboxCount++
    if($userObject.givenName) {
     $userGivenName = $userObject.givenName.ToString()
    } else {
     $userGivenName = ""
    }
    if($userObject.sn) {
     $userSn = $userObject.sn.ToString()
    } else {
     $userSn = ""
    }
    if($userObject.displayName) {
     $userDisplayName = $userObject.displayName.ToString()
    } else {
     $userDisplayName = $mailbox.DisplayName
    }
    if($userObject -ne $null) {
     $userDomain = (Get-ObjectADDomain $userObject.distinguishedName).Split(".")[0]
    } else {
     $userDomain = ""
    }
    if($userObject.sAMAccountName) {
     $userSAMAccountName = $userObject.sAMAccountName.ToString()
    } else {
     $userSAMAccountName = ""
    }
    if($userObject.mail) {
     $userMail = $userObject.mail.ToString()
    } else {
     $userMail = ""
    }
    if($mailbox.TotalItemSize.Value.ToKB() -gt $largeMailboxSize) {
     $largeMailboxes++
    }
    $totalMessages += $mailbox.ItemCount
    $totalStorage += $mailbox.TotalItemSize.Value.ToKB()
    $userMailboxInformation = New-Object -typeName PSObject
    Add-Member -inputObject $userMailboxInformation -type NoteProperty -name "Mailbox Server" -value $exchangeServer.Name
    Add-Member -inputObject $userMailboxInformation -type NoteProperty -name "Database Name" -value  $mailbox.DatabaseName
    Add-Member -inputObject $userMailboxInformation -type NoteProperty -name "Display Name" -value  $userDisplayName
    Add-Member -inputObject $userMailboxInformation -type NoteProperty -name "First Name" -value  $userGivenName
    Add-Member -inputObject $userMailboxInformation -type NoteProperty -name "Last Name" -value  $userSn
    Add-Member -inputObject $userMailboxInformation -type NoteProperty -name "Domain" -value  $userDomain
    Add-Member -inputObject $userMailboxInformation -type NoteProperty -name "User ID" -value  $userSAMAccountName
    Add-Member -inputObject $userMailboxInformation -type NoteProperty -name "E-Mail Address" -value  $userMail
    Add-Member -inputObject $userMailboxInformation -type NoteProperty -name "Size (KB)" -value ("{0:N0}" -f $mailbox.TotalItemSize.Value.ToKB())
    Add-Member -inputObject $userMailboxInformation -type NoteProperty -name "Messages" -value ("{0:N0}" -f $mailbox.ItemCount)
    Add-Member -inputObject $userMailboxInformation -type NoteProperty -name "Storage Limit" -value $storageLimitStatus
    Add-Member -inputObject $userMailboxInformation -type NoteProperty -name "Last Logon" -value $lastLogonTime
    Add-Member -inputObject $userMailboxInformation -type NoteProperty -name "Last User Access" -value $mailbox.LastLoggedOnUserAccount
    $userMailboxes += $userMailboxInformation
    if($error) {
     Write-LogEntry ("[ERROR] Problems found scanning mailbox:" + $userDisplayName) $logFilename
     foreach($errorEntry in $error) {
      Write-LogEntry $errorEntry $logFilename
     }
    }
   } else {
    Write-LogEntry ("Skipping:" + $mailbox.LegacyDN) $logFilename
   }
   if(!$beQuiet) { Write-Progress  -id 2 -parentId 1 -activity "Mailbox review" -status "Completed" -Completed }
  }
 }
 Add-Member -inputObject $exchangeServer -type NoteProperty -name "totalMessages" -value $totalMessages
 Add-Member -inputObject $exchangeServer -type NoteProperty -name "totalStorage" -value $totalStorage
 Add-Member -inputObject $exchangeServer -type NoteProperty -name "mailboxCount" -value $mailboxCount
 Add-Member -inputObject $exchangeServer -type NoteProperty -name "issuedWarning" -value $issuedWarning
 Add-Member -inputObject $exchangeServer -type NoteProperty -name "prohibitedSend" -value $prohibitedSend
 Add-Member -inputObject $exchangeServer -type NoteProperty -name "mailboxDisabled" -value $mailboxDisabled
 Add-Member -inputObject $exchangeServer -type NoteProperty -name "unlimitedMailboxes" -value $unlimitedMailboxes
 Add-Member -inputObject $exchangeServer -type NoteProperty -name "orphanedMailboxes" -value $orphanedMailboxes
 Add-Member -inputObject $exchangeServer -type NoteProperty -name "largeMailboxes" -value $largeMailboxes
 Write-LogEntry ("Total Messages:" + $totalMessages) $logFilename
 Write-LogEntry ("Total Storage:" + $totalStorage) $logFilename
 Write-LogEntry ("Mailboxes Reported count:" + $mailboxCount) $logFilename
 Write-LogEntry ("Issued Warning count:" + $issuedWarning) $logFilename
 Write-LogEntry ("Prohibited Send count:" + $prohibitedSend) $logFilename
 Write-LogEntry ("Mailbox Disabled count:" + $mailboxDisabled) $logFilename
 Write-LogEntry ("Unlimited Mailbox count:" + $unlimitedMailboxes) $logFilename
 Write-LogEntry ("Orphaned Mailbox count:" + $orphanedMailboxes) $logFilename
 Write-LogEntry ("Large Mailbox count:" + $largeMailboxes) $logFilename
 $userMailboxes | Export-Csv -path ($workDirectory + "\" + $exchangeServer.Name + ".csv") -noTypeInformation
 Write-LogEntryLineBreak $logFilename
}
Write-LogEntry "Saving Master Exchange Server List: $masterExchangeServerFilename" $logFilename
$exchangeServers | Export-Csv -path $masterExchangeServerFilename -noTypeInformation
Write-LogEntry ("Unloading Exchange SnapIn") $logFilename
Unload-ExchangeSnapin
if(!$beQuiet) { Write-Progress  -id 1 -activity "Exchange Server Report" -status "Build Excel Spreadsheet" -percentComplete "1" }
Write-LogEntry "Loading Master Exchange Server List: $masterExchangeServerFilename" $logFilename
$allExchangeServers =  $exchangeServers #Import-Csv -path $masterExchangeServerFilename
$exchangeServers = @()
if(!$beQuiet) { Write-Progress  -id 1 -activity "Exchange Server Report" -status "Build Excel Spreadsheet" -percentComplete "20" }
$progressBar = 0
Write-LogEntry ("Filtering Exchange Servers") $logFilename
foreach($exchangeServer in $allExchangeServers) {
 $progressBar++
 if(!$beQuiet) { Write-Progress  -id 2 -parentId 1 -activity "Filtering Exchange Servers" -status ($exchangeServer.Fqdn) -percentComplete (($progressBar / $allExchangeServers.Count) * 100) }
 if($exchangeServer.mailboxCount -eq 0) {
  Write-LogEntry ($exchangeServer.fqdn + " has no mailboxes. Skipping.") $logFilename
  continue
 }
 $filteredExchangeServer = New-Object -typeName PSObject
 Add-Member -inputObject $filteredExchangeServer -type NoteProperty -name "Name" -value $exchangeServer.Name
 if($exchangeServer.AdminDisplayVersion -match "Version 14") {
  $exchangeVersion = ("Exchange 2010 " + $exchangeServer.Edition)
 } elseif($exchangeServer.AdminDisplayVersion -match "Version 8") {
  $exchangeVersion = ("Exchange 2007 " + $exchangeServer.Edition)
 } elseif($exchangeServer.AdminDisplayVersion -match "Version 6") {
  $exchangeVersion = ("Exchange 2003 " + $exchangeServer.Edition)
 } else {
  $exchangeVersion = "Unknown"
 }
 Add-Member -inputObject $filteredExchangeServer -type NoteProperty -name "IP Address" -value $exchangeServer.ipV4Address
 Add-Member -inputObject $filteredExchangeServer -type NoteProperty -name "Exchange Version" -value $exchangeVersion
 Add-Member -inputObject $filteredExchangeServer -type NoteProperty -name "Online Since" -value $exchangeServer.WhenCreated
 Add-Member -inputObject $filteredExchangeServer -type NoteProperty -name "Total Mailboxes" -value ("{0:N0}" -f [int]$exchangeServer.mailboxCount)
 Add-Member -inputObject $filteredExchangeServer -type NoteProperty -name "Total Messages" -value ("{0:N0}" -f [int]$exchangeServer.totalMessages)
 Add-Member -inputObject $filteredExchangeServer -type NoteProperty -name "Total Storage (MB)" -value ("{0:N0}" -f ($exchangeServer.totalStorage / 1KB))
 Add-Member -inputObject $filteredExchangeServer -type NoteProperty -name "Space Warning" -value ("{0:N0}" -f [int]$exchangeServer.issuedWarning)
 Add-Member -inputObject $filteredExchangeServer -type NoteProperty -name "Prohibited Send" -value ("{0:N0}" -f [int]$exchangeServer.prohibitedSend)
 Add-Member -inputObject $filteredExchangeServer -type NoteProperty -name "Disabled Mailboxes" -value ("{0:N0}" -f [int]$exchangeServer.mailboxDisabled)
 Add-Member -inputObject $filteredExchangeServer -type NoteProperty -name "Unlimited Mailboxes" -value ("{0:N0}" -f [int]$exchangeServer.unlimitedMailboxes)
 Add-Member -inputObject $filteredExchangeServer -type NoteProperty -name "Orphaned Mailboxes" -value ("{0:N0}" -f [int]$exchangeServer.orphanedMailboxes)
 Add-Member -inputObject $filteredExchangeServer -type NoteProperty -name "Large Mailboxes" -value ("{0:N0}" -f [int]$exchangeServer.largeMailboxes)
 $exchangeServers += $filteredExchangeServer
 Write-LogEntry ($exchangeServer.fqdn + " will be added to the report.") $logFilename
}
Write-LogEntryLineBreak $logFilename
if(!$beQuiet) { Write-Progress  -id 1 -activity "Exchange Server Report" -status "Build Excel Spreadsheet" -percentComplete "40" }
Write-LogEntry ("Saving Filtered List: $filteredExchangeServerFilename") $logFilename
$exchangeServers | Export-Csv -path $filteredExchangeServerFilename -noTypeInformation
if(!$beQuiet) { Write-Progress  -id 2 -parentId 1 -activity "Creating Worksheets" -status "Overview" -percentComplete "25" }
if(Test-Path -path $excelFilename) { Remove-Item -path $excelFilename }
$excelObject = New-Object -comObject Excel.Application
$excelObject.Visible = $false
$excelObject.DisplayAlerts = $false
if(!$beQuiet) { Write-Progress  -id 2 -parentId 1 -activity "Creating Worksheets" -status "Overview" -percentComplete "50" }
Write-LogEntry "Injecting Filtered List, $filteredExchangeServerFilename, into first worksheet" $logFilename
$workbookObject = $excelObject.Workbooks.Open($filteredExchangeServerFilename)
$workbookObject.Title = ("Exchange Server Report for " + (Get-Date -Format D))
$workbookObject.Author = "Robert M. Toups, Jr."
$worksheetObject = $workbookObject.Worksheets.Item($sheetNumber)
$worksheetObject.UsedRange.Columns.Autofit() | Out-Null
$worksheetObject.Name = "Exchange Servers"
if(!$beQuiet) { Write-Progress  -id 2 -parentId 1 -activity "Creating Worksheets" -status "Overview" -percentComplete "75" }
$listObject = $worksheetObject.ListObjects.Add([Microsoft.Office.Interop.Excel.XlListObjectSourceType]::xlSrcRange, $worksheetObject.UsedRange, $null,[Microsoft.Office.Interop.Excel.XlYesNoGuess]::xlYes,$null)
$listObject.Name = "Exchange Servers Table"
$excelCurrentTableStyle, $excelTableStyle = Get-ExcelTableStyle $excelCurrentTableStyle $excelTableStyles
$listObject.TableStyle = $excelTableStyle
if(!$beQuiet) { Write-Progress  -id 2 -parentId 1 -activity "Creating Worksheets" -status "Overview" -percentComplete "100" }
if(!$beQuiet) { Write-Progress  -id 1 -activity "Exchange Server Report" -status "Build Excel Spreadsheet" -percentComplete "60" }
$progressBar = 0
foreach($exchangeServer in $exchangeServers) {
 $progressBar++
 if(!$beQuiet) { Write-Progress  -id 2 -parentId 1 -activity "Creating Worksheets" -status ($exchangeServer.Name) -percentComplete (($progressBar / $exchangeServers.Count) * 100) }
 $temporaryWorkFilename = ($workDirectory + "\" + $exchangeServer.Name + ".csv")
 Write-LogEntry ("Loading $temporaryWorkFilename into a temporary Workbook") $logFilename
 if(Test-Path -path $temporaryWorkFilename) {
  Write-LogEntry ("I found $temporaryWorkFilename. That's a good thing.") $logFilename
 } else {
  Write-LogEntry ("Can't find $temporaryWorkFilename. That is not expected. I am skipping " + $exchangeServer.name) $logFilename
  continue
 }
 $tempWorkbookObject = $excelObject.Workbooks.Open($temporaryWorkFilename)
 $tempWorksheetObject = $tempWorkbookObject.Worksheets.Item(1)
 Write-LogEntry "Copying all information to the clipboard" $logFilename
 $tempWorksheetObject.UsedRange.Copy() | Out-Null
 $sheetNumber++
 Write-LogEntry "Adding Worksheet #$sheetnumber to $excelFilename" $logFilename
 $workbookObject.WorkSheets.Add([System.Reflection.Missing]::Value, $workbookObject.Worksheets.Item($workbookObject.Sheets.Count)) | Out-Null
 $worksheetObject = $workbookObject.Worksheets.Item($sheetNumber)
 $worksheetObject.Activate()
 Write-LogEntry ("Naming Worksheet #$sheetnumber to " + $exchangeServer.Name) $logFilename
 $worksheetObject.Name = ($exchangeServer.Name)
 Write-LogEntry "Pasting clipboard to newly added worksheet" $logFilename
 $worksheetObject.Paste()
 $lastRow = $worksheetObject.UsedRange.Rows.Count
 $lastColumn = $worksheetObject.UsedRange.Columns.Count
 Write-LogEntry "Worksheet last row: $lastRow" $logFilename
 Write-LogEntry "Worksheet last column: $lastColumn" $logFilename
 $worksheetObject.UsedRange.Columns.Autofit() | Out-Null
 Write-LogEntry "Getting rid of the Select All from the paste" $logFilename
 $worksheetObject.Cells.Item(1,1).Select() | Out-Null
 $listObject = $worksheetObject.ListObjects.Add([Microsoft.Office.Interop.Excel.XlListObjectSourceType]::xlSrcRange, $worksheetObject.UsedRange, $null,[Microsoft.Office.Interop.Excel.XlYesNoGuess]::xlYes,$null)
 $listObject.Name = ($exchangeServer.Name + " Table")
 $excelCurrentTableStyle, $excelTableStyle = Get-ExcelTableStyle $excelCurrentTableStyle $excelTableStyles
 Write-LogEntry "Using table style: $ExcelTableStyle ($excelCurrentTableStyle)" $logFilename
 $listObject.TableStyle = $excelTableStyle
 if($lastColumn -eq 14) {
  Write-LogEntry "Adding totals to Columns J & K" $logFilename
  $worksheetObject.Cells.Item($lastRow+1,10) = "=SUM(J2:J" + $lastRow + ")"
  $worksheetObject.Cells.Item($lastRow+1,11) = "=SUM(K2:K" + $lastRow + ")"
  $worksheetObject.Cells.Item($lastRow+1,10).NumberFormat = "#,##0"
  $worksheetObject.Cells.Item($lastRow+1,11).NumberFormat = "#,##0"
 } else {
  Write-LogEntry "Adding totals to Columns I & J" $logFilename
  $worksheetObject.Cells.Item($lastRow+1,9) = "=SUM(I2:I" + $lastRow + ")"
  $worksheetObject.Cells.Item($lastRow+1,10) = "=SUM(J2:J" + $lastRow + ")"
  $worksheetObject.Cells.Item($lastRow+1,9).NumberFormat = "#,##0"
  $worksheetObject.Cells.Item($lastRow+1,10).NumberFormat = "#,##0"
 }
 Write-LogEntry "Destroying temporary workbook" $logFilename
 $tempWorkbookObject.Close()
 Write-LogEntryLineBreak $logFilename
}
if(!$beQuiet) { Write-Progress  -id 1 -activity "Exchange Server Report" -status "Build Excel Spreadsheet" -percentComplete "80" }
$worksheetObject = $workbookObject.Worksheets.Item(1)
$worksheetObject.Activate()
Write-LogEntry "Formatting Overview page" $logFilename
$lastRow = $worksheetObject.UsedRange.Rows.Count
Write-LogEntry "Last row: $lastRow" $logFilename
$worksheetObject.Cells.Item($lastRow+1,5) = "=SUM(E2:E" + $lastRow + ")"
$worksheetObject.Cells.Item($lastRow+1,6) = "=SUM(F2:F" + $lastRow + ")"
$worksheetObject.Cells.Item($lastRow+1,7) = "=SUM(G2:G" + $lastRow + ")"
$worksheetObject.Cells.Item($lastRow+1,8) = "=SUM(H2:H" + $lastRow + ")"
$worksheetObject.Cells.Item($lastRow+1,9) = "=SUM(I2:I" + $lastRow + ")"
$worksheetObject.Cells.Item($lastRow+1,10) = "=SUM(J2:J" + $lastRow + ")"
$worksheetObject.Cells.Item($lastRow+1,11) = "=SUM(K2:K" + $lastRow + ")"
$worksheetObject.Cells.Item($lastRow+1,12) = "=SUM(L2:L" + $lastRow + ")"
$worksheetObject.Cells.Item($lastRow+1,13) = "=SUM(M2:M" + $lastRow + ")"
Write-LogEntry "Added total rows" $logFilename
for($y=5; $y -le 13; $y++) {
 $worksheetObject.Cells.Item(($lastRow+1),$y).NumberFormat = "#,##0"
}
Write-LogEntry "Formatted Totals" $logFilename
foreach($exchangeServer in $exchangeServers) {
 if(!$beQuiet) { Write-Progress  -id 2 -parentId 1 -activity "Hyperlinking Worksheets" -status ($exchangeServer.Name) -percentComplete (($serverCount / $exchangeServers.Count) * 100) }
 Write-LogEntry ("Hyperlinked " + $exchangeServer.name + " worksheet to Overview page") $logFilename
 $serverCount++
 $selectedRange = $worksheetObject.Range("A" + $serverCount)
 $worksheetObject.Hyperlinks.Add($selectedRange, ("#`'" + $exchangeServer.Name + "`'!A1")) | Out-Null
 $selectedRange.Font.Bold = $true
}
Write-LogEntryLineBreak $logFilename
Write-LogEntry "Saving $excelFilename" $logFilename
$workbookObject.SaveAs($excelFilename,51) # http://msdn.microsoft.com/en-us/library/bb241279.aspx
$workbookObject.Saved = $true
$workbookObject.Close()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($workbookObject) | Out-Null
Write-LogEntry "Quiting Excel" $logFilename
$excelObject.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excelObject) | Out-Null
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()
if(Test-Path -path $excelFilename) {
 Write-LogEntry "Successfully saved $excelFilename"
} else {
 Write-LogEntry "This is embarassing, I failed to save $excelFilename. You might want to look into that."
}
if(!$beQuiet) { Write-Progress  -id 1 -activity "Exchange Server Report" -status "Build Excel Spreadsheet" -percentComplete "100" }
Write-LogEntryLineBreak $logFilename
if($cleanupWorkDirectory -eq $true) {
 Write-LogEntry "Removing the work directory: $workDirectory" $logFilename
 Remove-Item -literalPath $workDirectory -Recurse
 if(Test-Path -path $workDirectory) {
  Write-LogEntry "Failed to clean the work directory: $workDirectory" $logFilename
 } else {
  Write-LogEntry "Successfully cleaned the work directory: $workDirectory" $logFilename
 }
} else {
 Write-LogEntry "There still is work data located here: $workDirectory" $logFilename
}
Write-LogEntry ("Run Time: " + (Get-RunTime ((Get-Date) - $startTime))) $logFilename
Write-LogEntry "WHEW! I am done!" $logFilename
Write-LogEntryLineBreak $logFilename

Get the Creator of an Active Directory Object with PowerShell

$userObject = [ADSI]"LDAP://localdc.mydomain.ad.local/CN=Smith\, John,OU=Users,DC=mydomain,DC=ad,DC=local,DC=com"
$objectOwner = $userObject.PSBase.get_ObjectSecurity().GetOwner([System.Security.Principal.NTAccount]).Value

Exporting the Membership of a Large, Nested, Active Directory Group

One of the most common requests I receive is to enumerate the membership of an Active Directory group and provide some basic information back about the members. Simple enough task if the group is flat and contains a limited number of members. Once you start dealing with nested groups and groups containing more than 1,500 members (1,000 members for an Active Directory 2000 Forest), you need to start planning how you output the information.

With groups containing more than 1,500 members, performing the following will not provide the entire population.

$groupObject = New-Object System.DirectoryServices.DirectoryEntry("GC://CN=All Employees,OU=Distribution Lists,DC=ad,DC=mydomain,DC=local")
foreach($member in $groupObject.member) { Write-Output $member }

You will only enumerate the first 1,500 distinguished names of the multivalued attribute 'member' and output them to the console. Big enough to convince you that you retrieved all the members on first glance but on further investigation you will realize you are missing some people. Hopefully, you caught that and not the person that requested the report with the resolved distinguished names. To overcome this limitation, you need to search within the group object itself and return a range of values within the 'member' attribute; looping until you have enumerated each and every distinguished name.

Active Directory groups can contain user objects, computer objects, contact objects and other groups. To obtain the full membership of a Security Group to understand who has rights to an asset or a Distribution List to understand who will receive an e-mail, you must be aware of nested groups. If a group is a member is a member of the initial group, you will need to enumerate that group and any other groups that it may contain and so on. One of the issues that you can encounter with nested groups is the Administrator that nested a group that is member of a group that itself is patriline to that group. This leads to looping in reporting and must be accounted when recursively enumerating the membership. I tackle this problem by storing the nested group distinguished names that were enumerated in an array and at each recursion evaluating if that distinguished name is an element.

The code sample below deals with these two issues related to group enumeration. I have tested this against code against a group that contained 32,000+ member objects with multiple layers of nesting genreating a 2.9 megabyte report. The only big deficit in the code is there is no provision to deal with objects that are of the foreignSecurityPrincipal class. If you have cross-forest membership in groups, you can add that to the if-then statement and add your own formatting function for those objects. A nice feature of this script is that if you need to enumerate a large number of groups that have similar sAMAccountNames you can wild card them in the command line argument, such as "-group *AdSales*" or "-group DL_*". The code in the script is fairly portable. You can recombine these functions to enumerate the groups associated to NTFS security to provide an access report. You just need to update the functions that deal with the formatting of the objects to provide the desired information.

param([string]$domain,[string]$group,[switch]$verbose)
#-------------------------------------------------------------------------------------------------#
Function Find-GroupDistinguishedNamesBysAMAccountName($domain,$sAMAccountName) {
 $groupDistinguishedNames = @()
 $directorySearcher = New-Object System.DirectoryServices.DirectorySearcher
 $directorySearcher.SearchRoot = (New-Object System.DirectoryServices.DirectoryEntry(("LDAP://" + (Get-LocalDomainController $domain) + "/" + (Get-DomainDn $domain))))
 $directorySearcher.Filter = "(&(objectClass=group)(objectCategory=group)(sAMAccountName=$sAMAccountName))"
 $directorySearcher.PropertiesToLoad.Clear()
 $directorySearcher.PropertiesToLoad.Add("distinguishedName") | Out-Null
 $searchResults = $directorySearcher.FindAll()

 if($searchResults -ne $null) {
  foreach($searchResult in $searchResults) {
   if($searchResult.Properties.Contains("distinguishedName")) {
    $groupDistinguishedNames += $searchResult.Properties.Item("distinguishedName")[0]
   }
  }
 } else {
  $groupDistinguishedNames = $null
 }
 $directorySearcher.Dispose()
 return $groupDistinguishedNames
}
#-------------------------------------------------------------------------------------------------#
Function Get-GroupType($groupType) {
 if($groupType -eq -2147483646) {
  $groupType = "Global Security Group"
 } elseif($groupType -eq -2147483644) {
  $groupType = "Domain Local Security Group"
 } elseif($groupType -eq -2147483643) {
  $groupType = "BuiltIn Group"
 } elseif($groupType -eq -2147483640) {
  $groupType = "Universal Security Group"
 } elseif($groupType -eq 2) {
  $groupType = "Global Distribution List"
 } elseif($groupType -eq 4) {
  $groupType = "Local Distribution List"
 } elseif($groupType -eq 8) {
  $groupType = "Universal Distribution List"
 } else {
  $groupType = "Unknown Group Type"
 }
 return $groupType
}
#-------------------------------------------------------------------------------------------------#
Function Get-GroupMembers($distinguishedName,$level,$groupsReported) {
 $reportText = @()
 $groupObject = New-Object System.DirectoryServices.DirectoryEntry("GC://" + ($distinguishedName -replace "/","\/"))
 if($groupObject.groupType[0] -ne -2147483640 -or $groupObject.groupType[0] -ne 8) {
  $groupObject = New-Object System.DirectoryServices.DirectoryEntry("LDAP://" + (Get-LocalDomainController(Get-ObjectAdDomain $distinguishedName)) + "/" + ($distinguishedName -replace "/","\/"))
 }
 $directorySearcher = New-Object System.DirectoryServices.DirectorySearcher($groupObject)
 $lastQuery = $false
 $quitLoop = $false
 if($groupObject.member.Count -ge 1000) {
  $rangeStep = 1000
 } elseif($groupObject.member.Count -eq 0) {
  $lastQuery = $true
  $quitLoop = $true
 } else {
  $rangeStep = $groupObject.member.Count
 }
 $rangeLow = 0
 $rangeHigh = $rangeLow + ($rangeStep - 1)
 $level = $level + 2
 while(!$quitLoop) {
  if(!$lastQuery) {
   $attributeWithRange = "member;range=$rangeLow-$rangeHigh"
  } else {
   $attributeWithRange = "member;range=$rangeLow-*"  }
  $directorySearcher.PropertiesToLoad.Clear()
  $directorySearcher.PropertiesToLoad.Add($attributeWithRange) | Out-Null
  $searchResult = $directorySearcher.FindOne()
  $directorySearcher.Dispose()
  if($searchResult.Properties.Contains($attributeWithRange)) {
   foreach($member in $searchResult.Properties.Item($attributeWithRange)) {
    $memberObject = Get-ActiveDirectoryObject $member
    if($memberObject.objectClass -eq "group") {
     $reportText += Format-Group $memberObject $level $groupsReported
    } elseif ($memberObject.objectClass -eq "contact") {
     $reportText += Format-Contact $memberObject $level
    } elseif ($memberObject.objectClass -eq "computer") {
     $reportText += Format-Computer $memberObject $level
    } elseif ($memberObject.objectClass -eq "user") {
     $reportText += Format-User $memberObject $level
    } else {
     Write-Warning "NOT SUPPORTED: $member"
    }
   }
   if($lastQuery) { $quitLoop = $true }
  } else {
   $lastQuery = $true
  }
  if(!$lastQuery) {
   $rangeLow = $rangeHigh + 1
   $rangeHigh = $rangeLow + ($rangeStep - 1)
  }
 }
 return $reportText
}
#-------------------------------------------------------------------------------------------------#
Function Format-User($userObject,$level) {
 $reportText = @()
 if($userObject.displayName) {
  $identity = ((" " * $level) + $userObject.displayName.ToString() + " [" + (Get-ObjectNetBiosDomain $userObject.distinguishedName) + "\" + $userObject.sAMAccountName.ToString() + "]")
 } else {
  $identity = ((" " * $level) + $userObject.name.ToString() + " [" + (Get-ObjectNetBiosDomain $userObject.distinguishedName) + "\" + $userObject.sAMAccountName.ToString() + "]")
 }
 if($userObject.mail) {
  $identity = ("$identity <" + $userObject.mail.ToString() + ">")
 }
 if($userObject.userAccountControl[0] -band 0x0002) {
  $identity = "$identity (User Disabled)"
 } else {
  $identity = "$identity (User Enabled)"
 }
 $reportText += $identity
 $description = ((" " * $level) + "  Description: " + $userObject.description.ToString())
 $reportText += $description
 if($verbose) { Write-Host ($reportText | Out-String) }
 return $reportText
}

Function Format-Contact($contactObject,$level) {
 $reportText = @()
 $identity = ((" " * $level) + $contactObject.displayName.ToString() + " [" + (Get-ObjectNetBiosDomain $contactObject.distinguishedName) + "] <" + $contactObject.mail.ToString() + "> (Contact)")
 $description = ((" " * $level) + "  Description: " + $contactObject.description.ToString())
 $reportText += $identity
 $reportText += $description
 if($verbose) { Write-Host ($reportText | Out-String) }
 return $reportText
}

Function Format-Computer($computerObject,$level) {
 $reportText = @()
    $identity = ((" " * $level) + $computerObject.name + " [" + (Get-ObjectNetBiosDomain $computerObject.distinguishedName) + "\" + $computerObject.sAMAccountName.ToString() + "] (Computer)")
 $operatingSystem = ((" " * $level) + "  OS: " + $computerObject.operatingSystem.ToString())
 $reportText += $identity
    if($computerObject.dNSHostName) {
     $fqdn = ((" " * $level) + "  FQDN: " + ($computerObject.dNSHostName.ToString()).ToLower())
  $reportText += $fqdn
  $reportText += $operatingSystem
    } else {
  $reportText += $operatingSystem
    }
 if($verbose) { Write-Host ($reportText | Out-String) }
 return $reportText
}

Function Format-Group($groupObject,$level,$groupsReported) {
 $reportText = @()
 $identity = ((" " * $level) + $groupObject.name.ToString() + " [" + (Get-ObjectNetBIOSDomain $groupObject.distinguishedName) + "\" + $groupObject.sAMAccountName.ToString() + "]")
 if($groupObject.mail) {
  $identity = ("$identity <" + $groupObject.mail.ToString() + ">")
 }
 $identity = ("$identity (" + (Get-GroupType $groupObject.groupType) + ")")
 $reportText += $identity
 if($verbose) { Write-Host ($reportText | Out-String) }
 if($groupsReported -notcontains $groupObject.distinguishedName) {
  $groupsReported += $groupObject.distinguishedName
  $reportText += Get-GroupMembers $groupObject.distinguishedName.ToString() $level $groupsReported
 } else {
  $reportText += ((" " * $level) + "  [previously reported nested group]")
 }
 $reportText += ""
 return $reportText
}
#-------------------------------------------------------------------------------------------------#
Function Get-AllForestDomains {
 $domains = @()
 $forestInfo = [System.DirectoryServices.ActiveDirectory.Forest]::GetCurrentForest()
 foreach($domain in $forestInfo.Domains) {
  $domains += $domain.name
 }
 return $domains
}

Function Get-DomainDn($domain) {
 return ((New-Object System.DirectoryServices.DirectoryEntry("LDAP://$domain/RootDSE")).defaultNamingContext).ToString()
}

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

Function Get-ObjectNetBiosDomain($distinguishedName) {
 return ((Get-ObjectAdDomain $distinguishedName).Split(".")[0]).ToUpper()
}

Function Get-LocalDomainController($domain) {
 return ((New-Object System.DirectoryServices.DirectoryEntry("LDAP://$domain/RootDSE")).dnsHostName).ToString()
}

Function Get-ActiveDirectoryObject($distinguishedName) {
 return New-Object System.DirectoryServices.DirectoryEntry("LDAP://" + (Get-LocalDomainController (Get-ObjectAdDomain $distinguishedName)) + "/" + ($distinguishedName -replace "/","\/"))
}
#-------------------------------------------------------------------------------------------------#
Set-Variable -name reportsDirectory -option Constant -value "$pwd\Reports"
Set-Variable -name forestDomains -option Constant -value @(Get-AllForestDomains)
#-------------------------------------------------------------------------------------------------#
if(!([bool]$domain) -or !([bool]$group)) {
 Write-Host ("  Example: .\Export-GroupMembership.ps1 -domain " + $forestDomains[0] + " -group Administrators -verbose") -foregroundcolor Yellow
 Write-Host ""
}

if(!([bool]$domain)) {
 Write-Host " You are missing the `"-domain`" Switch" -Foregroundcolor Red
 Write-Host ""
 Write-Host " The domain of the group."
 Write-Host "  Valid Domains:"
 foreach($forestDomain in $forestDomains) {
  Write-Host ("   $forestDomain [" + ($forestDomain.Split("."))[0] + "]")
 }
 Write-Host ""
 Write-Host " Please enter the Domain below"
 while(!([bool]$domain)) {
  $domain = Read-Host -prompt "`tDomain"
 }
 Write-Host ""
}

if(!([bool]$group)) {
 Write-Host " You are missing the `"-group`" Switch" -Foregroundcolor Red
 Write-Host ""
 Write-Host " Please enter the group name below"
 while(!([bool]$group)) {
  $group = Read-Host -prompt "`tGroup"
 }
 Write-Host ""
}

$validDomain = $false
foreach($forestDomain in $forestDomains) {
 if($forestDomain -eq $domain) {
  $validDomain = $true
  break
 }
 if((($forestDomain.Split("."))[0]) -eq $domain) {
  $validDomain = $true
  break
 }
}

if($validDomain -eq $false) {
 Write-Host ""
 Write-Host "$domain is not a valid domain in your current forest." -foregroundcolor Red
 Write-Host ""
 exit
}

if(!(Test-Path -path $reportsDirectory)) {
 New-Item -path $reportsDirectory -type Directory | Out-Null
}

$groupDistinguishedNames = Find-GroupDistinguishedNamesBysAMAccountName $domain $group

if($groupDistinguishedNames -eq $null) {
 Write-Host "Unable to locate $domain\$group. Exiting..." -foregroundColor Red
 exit
}

foreach($groupDistinguishedName in $groupDistinguishedNames) {
 $groupObject = Get-ActiveDirectoryObject $groupDistinguishedName
 $groupName = $groupObject.name.ToString()
 $groupsAMAccountName = $groupObject.sAMAccountName.ToString()
 $groupDomain = Get-ObjectAdDomain $groupDistinguishedName
 $groupNetBiosDomain = Get-ObjectNetBiosDomain $groupDistinguishedName
 $groupType = Get-GroupType $groupObject.groupType
 $outputFilename = ($groupDomain + "-" + $groupsAMAccountName + ".txt")
 $reportText = @()
 $reportText += ("#" + ("-" * 78) + "#")
 $reportText += "  Members of $groupName [$groupNetBiosDomain\$groupsAMAccountName]"
 $reportText += ("  Description: " + $groupObject.description.ToString())
 if($groupObject.mail -and $groupType -match "Distribution") {
  $reportText += "  Distribution List E-Mail Address: " + $groupObject.mail.ToString()
 } elseif($groupObject.mail) {
  $reportText += "  Security Group E-Mail Address: " + $groupObject.mail.ToString()
 }
 $reportText += "  Group Type: $GroupType"
 $reportText += ("  Report generated on " + (Get-Date -format D) + " at " + (Get-Date -format T))
 $reportText += ("#" + ("-" * 78) + "#")
 if($verbose) { Write-Host ($reportText | Out-String) }
 $reportText += Get-GroupMembers $groupDistinguishedName 0 @()
 $reportText += ("#" + ("-" * 78) + "#")
 if($verbose) { Write-Host ("#" + ("-" * 78) + "#") }
 Set-Content -path "$reportsDirectory\$outputFilename" -value ($reportText | Out-String)
}

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

Audit Group Policy Objects

I needed to review a group of Group Policy Objects and determine if an Active Directory group is listed in the permissions for the GPO. If it was not, I needed to be aware of it. In the code sample, I take advantage of the Group Policy module to loop through all the GPOs in a domain that have "Workstation" in the display name, identify the ones that do not include the "ASIA\DesktopTeam" in the permission list and output them to a comma separated values text file.

#--------------------------------------------------------------------------------------------------#
Set-Variable -name accountDomain -option Constant -value "ASIA"
Set-Variable -name accountsAMAccountName -option Constant -value "DesktopTeam"
Set-Variable -name workingDomain -option Constant -value "asia.ad.mycompany.local"
Set-Variable -name matchFilter -option Constant -value "Workstation"
Set-Variable -name outputFile -option Constant -value  ($workingDomain + "-" + $matchFilter + ".csv")
#--------------------------------------------------------------------------------------------------#
if((Get-Host).Version.Major -ne 2) {
 Write-Host "You must have PowerShell V2 installed to use this script. Exiting!" -foregroundColor Red
 exit
}
if(!(Get-Module -ListAvailable | Where-Object { $_.Name -eq "GroupPolicy" }).Name) {
 Write-host "Unable to locate the GroupPolicy module. Exiting!" -foregroundColor Red
 exit
}
Import-Module -Name GroupPolicy

$badGpos = @()
$gpoList = Get-GPO -All -Domain $workingDomain | Where-Object { $_.DisplayName -match $matchFilter }
foreach($gpo in $gpoList) {
 $gpoPermissions = Get-GPPermissions -guid $gpo.Id -Domain $workingDomain -All
 $found = $false
 foreach($gpoPermission in $gpoPermissions) {
  if($gpoPermission.Trustee.Domain -eq $domain -and $gpoPermission.Trustee.Name -eq $sAMAccountName) {
   $found = $true
  }
 }
 if($found -eq $false) {
  $badGpos += $gpo
 }
}

$badGpos | Export-Csv -path $outputFile -noTypeInformation

Remove-Module -Name GroupPolicy

Creating Dynamic User Objects in Active Directory

Account management in Active Directory comes at a cost of time and effort for an Administrator. This is especially burdensome for objects that are only needed for a limited timeframe such as testing objects. Active Directory provides a low cost method for dealing with temporary objects through the dynamic object class which provides automatic garbage collection based on a time-to-live value in seconds. This benefit does come with several limitations:

• The maximum TTL of a dynamic object is 31,556,926 seconds (1 year).
• A deleted dynamic object due to its TTL expiring does not leave a tombstone behind.
• All DCs holding replicas of dynamic objects must run on Windows Server 2003 or greater.
• Dynamic entries with TTL values are supported in all partitions except the Configuration partition and Schema partition.
• Active Directory Domain Services do not publish the optional dynamicSubtrees attribute, as described in the RFC 2589, in the root DSE object.
• Dynamic entries are handled similar to non-dynamic entries when processing search, compare, add, delete, modify, and modifyDN operations.
• There is no way to change a static entry into a dynamic entry and vice-versa.
• A non-dynamic entry cannot be added subordinate to a dynamic entry.

In the code sample below, I take the advantage of the dynamic object class to create 50 test accounts that will expire and delete themselves from the forest on May 1, 2011 at 2:30 pm. The accounts created for a hypothetical finance application testing are placed in a specific OU, provided a unique sAMAccountName (tested to ensure this) and a pseudo-random password created for them. All this information is placed in a comma separated values text file so you can pass it over to your Quality Assurance team.

And remember, this script creates objects in Active Directory! Use at your own risk! I might have the best of intentions but my skill may betray you. Test, test and further test before implementing this code in a production environment.

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-DomainDistinguishedName($domain) {
 return ("dc=" + $domain.Replace(".",",dc="))
}

Function Test-sAMAccountName($sAMAccountName,$domain) {
 $objectConnection = New-Object -comObject "ADODB.Connection"
 $objectConnection.Open("Provider=ADsDSOObject;")
 $objectCommand = New-Object -comObject "ADODB.Command"
 $objectCommand.ActiveConnection = $objectConnection

 $ldapBase = ("LDAP://" + (Get-LocalDomainController $domain) + "/" + (Get-DomainDistinguishedName $domain))
 $ldapAttr = "sAMAccountName"
 $ldapScope = "subtree"
 $ldapFilter = "(&(objectClass=user)(sAMAccountName=$sAMAccountName))"
 $ldapQuery= "<$ldapBase>;$ldapFilter;$ldapAttr;$ldapScope"
 $objectCommand.CommandText = $ldapQuery
 $objectRecordSet = $objectCommand.Execute()
 if($objectRecordSet.RecordCount -gt 0) {
  $found = $true
 } else {
  $found = $false
 }
 return $found 
}
#--------------------------------------------------------------------------------------------------#
Set-Variable -name destinationOu -option Constant -value "ou=Finance Testing,dc=americas,dc=ad,dc=mycompany,dc=local"
Set-Variable -name totalNumberOfAccounts -option Constant -value 50
Set-Variable -name givenName -option Constant -value "Test"
Set-Variable -name sn -option Constant -value "Account"
Set-Variable -name passwordSize -option Constant -value 12
#--------------------------------------------------------------------------------------------------#
$randomNumber = New-Object System.Random
$accounts = @()
$endDate = "05/01/2011 14:30:00"
$entryTtlSeconds = [int]((Get-Date $endDate) - (Get-Date)).TotalSeconds
#######################################################################
# Or you can do a time span instead for the account time to live i.e.,
# $entryTtlSeconds = [int](New-TimeSpan -days 90).TotalSeconds 
# $entryTtlSeconds = [int](New-TimeSpan -minutes 60).TotalSeconds 
#######################################################################
if($entryTtlSeconds -gt 31556926) {
 Write-Host "Time-to-live greater than 1 year. Exiting!" -foregroundColor Red
 exit
}

$destinationOuObject = Get-ActiveDirectoryObject $destinationOu

if($destinationOuObject.distinguishedName -ne $destinationOu) {
 Write-Host "Unable to connect to $destinationOu. Exiting!" -foregroundColor Red
 exit
}

for($i = 1;$i -le $totalNumberOfAccounts;$i++) {
 $accountName = ($givenName + $sn + "$i")
 Write-Host "Creating $accountName..."
 
 if(Test-sAMAccountName $accountName (Get-ObjectADDomain $destinationOu)) {
  Write-Host "$accountName already exists, skipping..." -foregroundColor Yellow
  continue
 }
 
 $userObject = $destinationOuObject.Create("user","CN=$accountName")
 $userObject.PutEx(2,"objectClass",@("dynamicObject","user"))
 $userObject.Put("entryTTL",$entryTtlSeconds)
 $userObject.Put("sAMAccountName", $accountName) # Mandatory attribute
 $userObject.Put("givenName",$givenName)
 $userObject.Put("sn",($sn + "$i"))
 $userObject.Put("displayName",($givenName + " " + $sn + "$i"))
 $userObject.Put("description","Account Used for Application Testing")
 $userObject.Put("wWWHomePage","http://sharepointsite/projects/newfinancesystem")
 $userObject.Put("userPrincipalName",($accountName + "@" + (Get-ObjectADDomain $destinationOu)))
 $userObject.SetInfo()
 
 $password = ""
 for($x = 1;$x -le $passwordSize;$x++) {
  $password += [char]($randomNumber.Next(33,126))
 }
 
 $userObject.SetPassword($password)
 $userObject.Put("userAccountControl", 512)
 $userObject.SetInfo()

 $account = New-Object -typeName PSObject
 Add-Member -inputObject $account -type NoteProperty -name "domain" -value (Get-ObjectADDomain $destinationOu).Split(".")[0]
 Add-Member -inputObject $account -type NoteProperty -name "sAMAccountName" -value ($userObject.sAMAccountName).ToString()
 Add-Member -inputObject $account -type NoteProperty -name "givenName" -value ($userObject.givenName).ToString()
 Add-Member -inputObject $account -type NoteProperty -name "sn" -value ($userObject.sn).ToString()
 Add-Member -inputObject $account -type NoteProperty -name "userPrincipalName" -value ($userObject.userPrincipalName).ToString() 
 Add-Member -inputObject $account -type NoteProperty -name "password" -value $password
 $accounts += $account
}

$accounts | Export-Csv -path "$givenName $sn List.csv" -noTypeInformation

Retrieve the Fully Qualified Domain Name of a Local Active Directory Bound Computer

Here is the method I use to obtain the fully qualified domain name of a local Active Directory bound computer in my scripts.

$computerFqdn = (([System.Net.NetworkInformation.IPGlobalProperties]::GetIPGlobalProperties()).HostName + "." + ([System.Net.NetworkInformation.IPGlobalProperties]::GetIPGlobalProperties()).DomainName)

Add a New Mail Domain to Multiple Users

In my work, it is not uncommon to have new domains purchased for business units and employees immediately needing e-mail addresses with the new domain. So after the MX record is created and Exchange is updated to accept mail for the new domain, its time to target the users that require the new mail domain to be included in the proxyAddresses attribute. The proxyAddresses attribute is multivalued so you must use treat it as an array and use PutEx to append to the attribute with the new address. In the code sample below, I take a file of e-mail addresses that users already use, split off the "local-part" from the domain using the at sign as the delimiter and append the $newMailDomain constant to the local-part to create the new e-mail address for the user.  When inserting the new e-mail address into the proxyAddresses attribute with PutEx, you will need to send it as an array even if it only has 1 element. To be on the safe side, which you should also do when modifying production data, a backup of all the current elements of the user's proxyAddresses attribute are dumped into a subdirectory called "backup" in a file name that matches their mail attribute (with ".txt" appended) and the script is set to not write to the directory by default. You must alter the $writeEnabled constant in order to actually update the user's object. The script also checks to see if the e-mail address you are trying to assign to the employee's account is already present and skips if so. I am using Write-Output to document the scripts behavior so you can easily pipe it out to a text file for review.

This script does not make the new address the employee's primary SMTP address. In order to do this, you would need to remove the current primary denoted by the "SMTP:" at the start of the proxyAddress and return it back with "smtp:" then add the new e-mail address with "SMTP:" at the start. I will provide a code sample in the future that will detail that swap out and how to do it safely.

And remember, this script modifies data! Use at your own risk! I might have the best of intentions but my skill may betray you. Test, test and further test before implementing this code in a production environment.

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 "/","\/"))
}
#--------------------------------------------------------------------------------------------------#
Set-Variable -name forestRootDn -option Constant -value ([ADSI]("LDAP://" + (([System.DirectoryServices.ActiveDirectory.Forest]::GetCurrentForest()).name) + "/rootDSE")).defaultNamingContext
Set-Variable -name adsPropertyAppend -option Constant -value 3
Set-Variable -name newMailDomain -option Constant -value "@newdomain.local"
Set-Variable -name inputFile -option Constant -value "userlist.txt"
Set-Variable -name writeEnabled -option Constant -value $false
#--------------------------------------------------------------------------------------------------#
if(Test-Path -path $inputFile) { 
 $userList = Get-Content -path $inputFile
} else {
 Write-Host "Could not locate $inputFile. Exiting..." -foregroundColor Red
 exit
}
if(!(Test-Path -path "backup")) {
 New-Item -path "backup" -type directory | Out-Null
}

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

foreach($user in $userList) {

 $ldapBase = "GC://$forestRootDn"
 $ldapAttr = "distinguishedName"
 $ldapScope = "subtree"
 $ldapFilter = "(&(objectClass=user)(proxyAddresses=smtp:$user))"
 $ldapQuery = "<$ldapBase>;$ldapFilter;$ldapAttr;$ldapScope"
 $objectCommand.CommandText = $ldapQuery
 $objectRecordSet = $objectCommand.Execute()
 
 if(!$objectRecordSet.EOF) {
  while(!$objectRecordSet.EOF) {
   $userObject = Get-ActiveDirectoryObject $objectRecordSet.Fields.Item('distinguishedName').Value
   $newEmailAddress = ("smtp:" + ($user.Split("@")[0]).ToLower() + $newMailDomain)
   Write-Output ($userObject.displayName).ToString()
   Write-Output "New Address: $newEmailAddress"
   $notFound = $true
   Set-Content -path ("backup\" + ($userObject.mail).ToString() + ".txt") -value $userObject.proxyAddresses
   foreach($proxyAddress in $userObject.proxyAddresses) {
    if($proxyAddress -eq $newEmailAddress) {
     $notFound = $false
    }
   }
   if($notFound -eq $true) {
    Write-Output "Adding $newEmailAddress"
    if($writeEnabled -eq $true) {
     $userObject.PutEx($adsPropertyAppend, "proxyAddresses", @($newEmailAddress))
     $userObject.SetInfo()
    }
   } else {
    Write-Output "Already has $newEmailAddress"
   }
   $objectRecordSet.MoveNext()
  }
 } else {
  Write-Output "Could not locate $user in the forest."
 }
 Write-Output ("-" * 50)
}

Quick Organizational Chart from Active Directory

Building on my blog post, Export Manager's Nested Direct Reports, I quickly threw together a revision of the code that will create visual (ASCII at least) organizational chart based on the directReports attribute of an employee in Active Directory. The nesting method I am using could easy be converted to simulate the "tree" DOS command using Get-ChildObject on file systems or about any programming challenge that deals with nested objects. The one main fault of the code, is that it is not pretty when it gets to the last nested object of a parent. It leaves the "|" in front of the last nested subordinate object. A little more work and I could clean this presentation issue, but not today.

Function Get-DirectReports($distinguishedName,$level) {
 $managerObject = Get-ActiveDirectoryObject $distinguishedName
 if($managerObject.directReports.count -gt 0) {
  foreach($directReport in $managerObject.directReports) {
   $directReportObject = Get-ActiveDirectoryObject $directReport
   Write-Output (("|  " * $level) + "|")
   Write-Output (("|  " * $level) + "+-" + ($directReportObject.givenName).ToString() + " " + ($directReportObject.sn).ToString())
   Get-DirectReports $directReport ($level + 1)
  }
 }
}

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 "/","\/"))
}
#--------------------------------------------------------------------------------------------------#
Set-Variable -name forestRootDn -option Constant -value ([ADSI]("LDAP://" + (([System.DirectoryServices.ActiveDirectory.Forest]::GetCurrentForest()).name) + "/rootDSE")).defaultNamingContext
#--------------------------------------------------------------------------------------------------#
$objectConnection = New-Object -comObject "ADODB.Connection"
$objectCommand = New-Object -comObject "ADODB.Command"
$objectConnection.Open("Provider=ADsDSOObject;")
$objectCommand.ActiveConnection = $objectConnection

$manager = "the.ceo@mycompany.local"
 
$ldapBase = "GC://$forestRootDn"
$ldapAttr = "distinguishedName"
$ldapScope = "subtree"
$ldapFilter = "(&(objectClass=user)(proxyAddresses=smtp:$manager))"
$ldapQuery = "<$ldapBase>;$ldapFilter;$ldapAttr;$ldapScope"
$objectCommand.CommandText = $ldapQuery
$objectRecordSet = $objectCommand.Execute()
 
while(!$objectRecordSet.EOF) {
 $firstLevelObject = Get-ActiveDirectoryObject $objectRecordSet.Fields.Item('distinguishedName').Value
 $topLevelManager = (($firstLevelObject.givenName).ToString() + " " + ($firstLevelObject.sn).ToString())
 Write-Output $topLevelManager
 Get-DirectReports $firstLevelObject.distinguishedName 0
 $objectRecordSet.MoveNext()
}

Export Manager's Nested Direct Reports

In the code sample below, I create an export in two flavors for the nested direct reports of managers. By taking a text file listing the e-mail addresses of user objects in a forest, this code proceeds to drill recursively through the mutli-valued string directReports attribute of the object until it reaches a nested user object with that attribute set to null. After obtaining all the distinguished names, the code proceeds to perform LDAP lookups to obtain relevant attribute data. At the conclusion, it saves two files, a comma separated values text file with all the relevant attributes and an HTML file with a subset of those attributes.

The concept for this script was done before PowerShell V2 was released and provided better typing including 64 bit integers. Like in my older Perl and VBScript code dealing with Active Directory attributes that stored 64 bit integers like accountExpires, it required manipulating the return data in order to perform comparisons. The code below contains a Function written by Adam Weigert which has saved me the time translating the same conversion I've done before in Perl and VBScript. A link to his blog entry on the topic is commented in the code for your review.

param([string]$file)

Function Get-DirectReports($objectDn, $distinguishedNames) {
 $distinguishedNames += $objectDn
 $managerObject = Get-ActiveDirectoryObject $objectDn
 if($managerObject.directReports.count -gt 0) {
  foreach($directReport in $managerObject.directReports) {
   $distinguishedNames = Get-DirectReports $directReport $distinguishedNames
  }
 }
 return $distinguishedNames
}
Function Read-UserStatus($userFlags) {
 if ($userFlags -band 0x0002) {
  $enabled = $false
    } else {
  $enabled = $true
 } 
 return $enabled
}
Function Convert-ADSLargeInteger([object]$adsLargeInteger) { # http://weblogs.asp.net/adweigert/archive/2007/03/23/powershell-convert-active-directory-iadslargeinteger-to-system-int64.aspx
 $highPart = $adsLargeInteger.GetType().InvokeMember("HighPart", [System.Reflection.BindingFlags]::GetProperty, $null, $adsLargeInteger, $null)
 $lowPart  = $adsLargeInteger.GetType().InvokeMember("LowPart",  [System.Reflection.BindingFlags]::GetProperty, $null, $adsLargeInteger, $null)

 $bytes = [System.BitConverter]::GetBytes($highPart)
 $temp   = [System.Byte[]]@(0,0,0,0,0,0,0,0)
 [System.Array]::Copy($bytes, 0, $temp, 4, 4)
 $highPart = [System.BitConverter]::ToInt64($temp, 0)

 $bytes = [System.BitConverter]::GetBytes($lowPart)
 $lowPart = [System.BitConverter]::ToUInt32($bytes, 0)
 
 return $lowPart + $highPart
}
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 "/","\/"))
}
#--------------------------------------------------------------------------------------------------#
Set-Variable -name forestRootDn -option Constant -value ([ADSI]("LDAP://" + (([System.DirectoryServices.ActiveDirectory.Forest]::GetCurrentForest()).name) + "/rootDSE")).defaultNamingContext
#--------------------------------------------------------------------------------------------------#
if(![bool]$file) {
 Write-Host "You are missing the `"-file`" command line argument" -foregroundColor Red
 Write-Host ""
 Write-Host "This is the file that contains the list of employee e-mail addresses"
 Write-Host ""
 while((![bool]$file)) {
  $file = Read-Host -prompt "`tFile"
 }
 Write-Host ""
}
if(!(Test-Path -path $file)) {
 Write-Host "Unable to locate: $file" -foregroundColor Red
 Write-Host ""
 Write-Host "Please review the correct path"
 Write-Host ""
 while(!(Test-Path -path $file)) {
  $file = Read-Host -prompt "`tFile"
 }
 Write-Host ""
}
   
$answer = $null
while($answer -ne "Y" -and $answer -ne "N") {
 $answer = Read-Host -prompt "Are you sure you want to use $file (Y/N)?"
}
   
if($answer -eq "N") {
 Write-Host ""
 Write-Host "Quiting..." -foregroundColor Yellow
 exit
}
 
$outputFile = (((Get-Item -path $file).name).Replace(((Get-Item -path $file).extension),".csv"))
 
$managers = Get-Content -path $file

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

$distinguishedNames = @()
$users = @()

foreach($manager in $managers) {

 $ldapBase = "GC://$forestRootDn"
 $ldapAttr = "distinguishedName"
 $ldapScope = "subtree"
 $ldapFilter = "(&(objectClass=user)(ProxyAddresses=smtp:$manager))"
 $ldapQuery= "<$ldapBase>;$ldapFilter;$ldapAttr;$ldapScope"
 $objectCommand.CommandText = $ldapQuery
 $objectRecordSet = $objectCommand.Execute()

 while(!$objectRecordSet.EOF) {
  $distinguishedNames = Get-DirectReports $objectRecordSet.Fields.Item('distinguishedName').Value $distinguishedNames
  $objectRecordSet.MoveNext()
 }
}

foreach($distinguishedName in $distinguishedNames) {
 $userObject = Get-ActiveDirectoryObject $distinguishedName

 if($userObject.objectClass -eq "User") {
  $accountExpires = (Convert-ADSLargeInteger $userObject.accountExpires[0])

  if($accountExpires -ne 0 -and $accountExpires -ne 9223372036854775807) {
   $account_expiration = (Get-Date ($userObject.psbase.invokeget("AccountExpirationDate")) -Format d)
  } else {
   $account_expiration = "N/A"
  }
 
  if($userObject.manager) {
   $manager_object = Get-ActiveDirectoryObject $userObject.manager
   if($manager_object.displayName) {
    $manager = ($manager_object.displayName).ToString()
   } else {
    $manager = ($manager_object.name).ToString()
   }
  } else {
   $manager = "N/A"
  }
  
  $user = New-Object -typeName PSObject
  Add-Member -inputObject $user -type NoteProperty -name "domain" -value ((Get-ObjectADDomain $userObject.distinguishedName).Split(".")[0]).ToUpper()
  Add-Member -inputObject $user -type NoteProperty -name "sAMAccountName" -value (($userObject.sAMAccountName).ToString()).ToLower()
  Add-Member -inputObject $user -type NoteProperty -name "givenName" -value ($userObject.givenName).ToString()
  Add-Member -inputObject $user -type NoteProperty -name "sn" -value ($userObject.sn).ToString()
  Add-Member -inputObject $user -type NoteProperty -name "displayName" -value ($userObject.displayName).ToString()
  Add-Member -inputObject $user -type NoteProperty -name "mail" -value ($userObject.mail).ToString()
  Add-Member -inputObject $user -type NoteProperty -name "title" -value ($userObject.title).ToString()
  Add-Member -inputObject $user -type NoteProperty -name "department" -value ($userObject.department).ToString()
  Add-Member -inputObject $user -type NoteProperty -name "manager" -value $manager
  Add-Member -inputObject $user -type NoteProperty -name "streetAddress" -value (($userObject.streetAddress).ToString() -Replace "`r`n",", ")
  Add-Member -inputObject $user -type NoteProperty -name "l" -value ($userObject.l).ToString()
  Add-Member -inputObject $user -type NoteProperty -name "st" -value ($userObject.st).ToString()
  Add-Member -inputObject $user -type NoteProperty -name "postalCode" -value ($userObject.postalCode).ToString()
  Add-Member -inputObject $user -type NoteProperty -name "c" -value ($userObject.c).ToString()
  Add-Member -inputObject $user -type NoteProperty -name "telephoneNumber" -value ($userObject.telephoneNumber).ToString()
  Add-Member -inputObject $user -type NoteProperty -name "accountExpiration" -value $account_expiration
  Add-Member -inputObject $user -type NoteProperty -name "enabled" -value (Read-UserStatus $userObject.userAccountControl[0])
  Add-Member -inputObject $user -type NoteProperty -name "organizationalUnit" -value ($userObject.psBase.parent.distinguishedName).ToString()
  $users += $user
 }
}

$users | Export-Csv -path $outputFile -noTypeInformation
$users | ConvertTo-Html -property givenName,sn,mail,telephoneNumber,title,department -title "Important Contacts" > "contacts.html"

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