Monday, November 28, 2011

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

2 comments:

  1. The script doesn't appear to take the 2010 Online Archive mailboxes into account. It seems to list them as normal mailboxes.

    Other than that, awesome work :)

    ReplyDelete
  2. I have some issues with this script, i would love to use it since i was busy compiling something similar.

    Im having issues with double spaces in the script, is this working a 100%?

    ReplyDelete