Get large Mailboxes, sorted with Size and with AD Status

Question

I'm looking for some help from the community.

I'm working on a script that would get:

1. mailbox statistics of each user mailbox, convert the total size to bytes to sort it and get the top 5 ones for each of the mailbox DBs.
2. get active directory status (Enabled/Disabled) of each of those users as well

I'm expecting an output like below but the AD field at the end comes out empty

Querying Mailbox Database: DBX ......
DisplayName      ItemCount TotalItemSize                   SizeInBytes Server   AD
-----------      --------- -------------                   ----------- ------   --
abc xyz             240259 40.05 GB (43,004,724,140 bytes) 43004724140 ******
ab xyzd****         126020 33.2 GB (35,646,143,893 bytes)  35646143893 ******
ab xyzd****         126020 33.2 GB (35,646,143,893 bytes)  35646143893 ******

Powershell Script:

$DBlist=(Get-MailboxDatabase * | where Server -EQ "EXCHANGE_Server").Name | sort
$DB_Count = ($DBlist | Measure-Object).Count

Write-Host "`n There are $DB_Count DBs with active copy on EXCHANGE_Server: "
Write-Host " $DBlist" -NoNewline

#echo $DBlist

foreach ($DB in $DBlist)
        {
        Write-Host "`n Querying Mailbox Database: $DB ......"
        Get-Mailbox -Database $DB | Get-MailboxStatistics -WarningAction SilentlyContinue | Select-Object -Property DisplayName,ItemCount,TotalItemSize,@{Label="SizeInBytes";Expression={$_.TotalItemSize.Value.ToString().Split('(')[1].Split(' ')[0].Replace(',','').ToInt64($null)}}, @{n='Server';e={(Get-MailboxStatistics -identity $_.DisplayName).ServerName}}, @{n='AD';e={(Get-ADUser -Filter {DisplayName -like $_.DisplayName}).Enabled}} | Sort-Object -Property SizeInBytes -Descending |Select-Object DisplayName,ItemCount,TotalItemSize,SizeInBytes,Server, AD -First 20 | ft -AutoSize
        }

Answer 1

You will have better luck with the following calculated property:

@{n='AD';e={(Get-ADUser -Filter "DisplayName -eq '$($_.DisplayName)'").Enabled}}

---

There are a couple of issues to address.

1. The -Filter parameter value should not be a script block despite what the documentation says. It should be a double quoted string, e.g. -Filter "DisplayName -like '$($_.DisplayName)'". The inner single quotes are needed so there won't a syntax error when $($_.DisplayName) is evaluated and then is put into the query as a string.
2. Using the -like operator without any wildcards doesn't help your search results and is less efficient that just using -eq.
3. When interpolating an object property using the syntax object.property inside of an expandable string (double quoted string), the expression needs to be evaluated before the value is converted to a string. By default a variable without member access such as $var will have its value converted to string (your actual results may vary depending on what $var actually is). However, $var.property will convert $var value to string and then append .property to the end of the string. To avoid that behavior, you can use a subexpression $() to evaluate the inner contents before the string conversion. An example of this is $($var.property).