Thursday, March 26, 2015

List all Exchange mailbox sizes for all people that report up to a single manager (direct and indirect reports of a manager)

If you track a manager for a user in Active Directory, then you have the ability to report on everyone that reports to a specific person by calling the directReports system only attribute.

get-aduser username -properties directReports | select directReports | fl

However, there are a number of things you can do with this that come in handy.  For me, I was tasked with reporting on how much space was consumed in Exchange mailboxes broken down by Business Unit. Since ultimately each Business Unit had a specific leader, I was able to utilize the directReports attribute to get everyone that reported up to the leader of the BU, then grab all their mailbox sizes.

There is a great post that got me started here: http://www.lazywinadmin.com/2014/10/powershell-who-reports-to-whom-active.html. That was very helpful to get me started with a function to list everyone that reports up to a specific person. I had to do some modifications in order to get what I wanted, but basically I switched to get-user to pipe properly to the get-mailboxstatistics command, then exported everything to a nice looking CSV for easy sorting.

My Version: (note...execute from Exchange Management Shell)

function ADDirectReports
{
param([string]$Identity)
Get-Aduser -Identity $Identity -Properties directreports|
ForEach-Object -Process {
$_.directreports|ForEach-Object -Process{
$MGR = (get-user $PSitem).manager|get-user
Get-User -identity $PSitem|get-mailboxstatistics|select Database,DisplayName,@{label="Total Size (MB)";expression={$_.TotalItemSize.Value.ToMB()}},@{ L = "Manager"; E = {$MGR.DisplayName}}|Export-CSV -Path C:\Temp\Mailboxes.csv -Append -Confirm:$false -NoClobber
ADDirectReports -Identity $PSItem -Recurse
}}}

(Tip for PowerShell Beginners...paste the function into your PowerShell Window...then press enter twice...once you get back to the command prompt, type in ADDirectReports USERNAME and press enter to gather this information for all the reports of a top level manager).



This creates a CSV that looks as follows in Excel: