Exchange powershell mailbox reports

[joeledwards]

Hi all

I’m trying to find a viable method to export a CSV report from Exchange 2010 PowerShell, but unable to find a way to get all the fields I want in a single report, hoping someone might be able to help me out with this.

I’m essentially looking to get a report with the following colums:

Display Name, Primary Smtp Address, Any Alias Addresses, Item Count, Total Item Size. All in descending order of Total Item Size.

I’ve found methods to extract portions of each but not all in one document.

For example:

Like this I can get everything I need except the Primary Address and Aliases

Get-MailboxStatistics -Database "Mailbox Database 1" | Select DisplayName, ItemCount, TotalItemSize | Sort-Object TotalItemSize -Descending | Export-CSV C:\MBSizes.csv

And like this I can get the Primary Address and Aliases

Get-Mailbox -ResultSize Unlimited |Select-Object DisplayName,PrimarySmtpAddress, @{Name=“EmailAddresses”;Expression={$_.EmailAddresses |Where-Object {$_.PrefixString -ceq “smtp”} | ForEach-Object {$_.SmtpAddress}}} | Export-CSV c:\exportsmtp.csv -NoTypeInformation

But I can’t find a way to get all the fields I’m looking for into one report.

Thanks in advance

[jayadams7]

Hi,

You can use Either this

Get-Mailbox -ResultSize Unlimited | Select-Object DisplayName, IssueWarningQuota, ProhibitSendQuota, @{label=“TotalItemSize(MB)”;expression={(Get-MailboxStatistics $).TotalItemSize.Value.ToMB()}}, @{label=“ItemCount”;expression={(Get-MailboxStatistics $).ItemCount}}, Database | Export-Csv “C:\Scripts\UserMailboxSizes.csv” -NoTypeInformation

OR

This Command for above Procedure

Get-Mailbox -ResultSize Unlimited | Where {$.UseDatabaseQuotaDefaults -eq $false} | Select-Object DisplayName, IssueWarningQuota, ProhibitSendQuota, @{label=“TotalItemSize(MB)”;expression={(Get-MailboxStatistics $).TotalItemSize.Value.ToMB()}}, @{label=“ItemCount”;expression={(Get-MailboxStatistics $_).ItemCount}}, Database | Export-Csv “C:\Scripts\UserMailboxSizes.csv” -NoTypeInformation

use where to get only the mailboxes that do not use the database default quota.

Where {$_.UseDatabaseQuotaDefaults -eq $false

Cheers

Jay Adams

[jitensh]

$outtbl = @()
$users = Get-Mailbox -ResultSize Unlimited | Where-Object {$_.UseDatabaseQuotaDefaults -eq $false} 
$users | % {
  $x = Get-MailboxStatistics $_ 
  $t = New-Object PSObject -Property @{
     DisplayName = $_.Name
     Emailaddress = $_.PrimarySmtpAddress
     ItemCount=$x.ItemCount
     TotalItemSizeMB=$x.totalItemSize.Value.ToMB()
     TotalItemSize=$x.TotalItemSize
  }
  $outtbl += $t
}
$outtbl |Sort TotalItemSize -Descending | Export-Csv c:\report.csv -NoTypeInformation

with this way you can combine to commands looks neat

[joeledwards]

@jitensh

with this way you can combine to commands looks neat

Thanks JitenSH, this method does look quite neat. Just that I can’t quite figure out how to add in the other objects I was looking for such as aliases (additional SMTP addresses). Can you help me out with that as well?

[jitensh]

Try this do not forget to mark answers as best answer

$outtbl = @()
$users = Get-Mailbox -ResultSize Unlimited | Where-Object {$_.UseDatabaseQuotaDefaults -eq $false} 
$users | % {
  $x = Get-MailboxStatistics $_ 
  $t = New-Object PSObject -Property @{
     DisplayName=    $_.Name
     Emailaddress=   $_.PrimarySmtpAddress
     ItemCount=      $x.ItemCount
     Alias=          $_.alias
     ADsmtp=         $_.EmailAddresses -join '.'
     TotalItemSizeMB=$x.totalItemSize.Value.ToMB()
     TotalItemSize=  $x.TotalItemSize
  }
  $outtbl += $t
}
$outtbl |Sort TotalItemSize -Descending | Export-Csv c:\report.csv -NoTypeInformation