How to export the Exchange MailboxStatistics along with PrimarySmtpAddress to .CSV?

Ask your Windows PowerShell-related questions, including questions on cmdlet development!
Forum rules
Do not post any licensing information in this forum.

Any code longer than three lines should be added as code using the 'Select Code' dropdown menu or attached as a file.
Locked
User avatar
ITEngineer
Posts: 138
Joined: Wed Oct 12, 2011 10:52 am

How to export the Exchange MailboxStatistics along with PrimarySmtpAddress to .CSV?

Post by ITEngineer » Mon Aug 13, 2018 3:22 am

I need some assistance to modify the script below to display one more column called PrimarySmtpAddress:

Code: Select all

Get-MailboxDatabase -Server SITE3SVR-EX01 | Get-Mailbox -ResultSize Unlimited -Database "PRODSITE3-DB" |
   Get-MailboxStatistics | 
   Select DisplayName,
   		  MailboxType, `
   		  DatabaseName, `
		  ServerName, `
   		  @{name="TotalItemSize (MB)"; expression={[math]::Round(($_.TotalItemSize.ToString().Split("(")[1].Split(" ")[0].Replace(",","")/1MB),2)}}, `
          ItemCount,
          PrimarySmtpAddress |
   Sort "TotalItemSize (MB)" -Descending | Export-Csv -Path C:\Scripts\Result.csv -NoTypeInformation
Thanks in advance.
/* IT Engineer */

User avatar
mxtrinidad
Site Admin
Posts: 294
Joined: Sun Mar 03, 2013 12:42 pm

Re: How to export the Exchange MailboxStatistics along with PrimarySmtpAddress to .CSV?

Post by mxtrinidad » Mon Aug 13, 2018 3:51 am

You will need to explore the object properties to make sure you got the correct spelling for the column.
Simply use the " .. | Select-Object -Property * " to display all properties to the console.

Also, you could use the get-member cmdlet to explore the PSObject you have created:
For example:
$mailStat = Get-MailboxStatistics ...
$mailStat | Get-Member

This will display both .Net PSObject properties and methods in this stored object.

Any information about Get-member can be found by using:
Get-Help Get-Member -ShowWindow

Hope this help!

User avatar
ITEngineer
Posts: 138
Joined: Wed Oct 12, 2011 10:52 am

Re: How to export the Exchange MailboxStatistics along with PrimarySmtpAddress to .CSV?

Post by ITEngineer » Mon Aug 13, 2018 3:56 am

Yes, you are right, there is no property in the Get-MailboxStatistics cmdlets, hence I have modified it into:

Code: Select all

Get-MailboxDatabase -Server SITE03-EX01 | Get-Mailbox -Database "PRDSITE3-DB04" -ResultSize Unlimited | 
	Select-Object DisplayName, 
	servername,database,
	RecipientTypeDetails,PrimarySmtpAddress,
	HiddenFromAddressListsEnabled,
	@{label="ItemCount";expression={(Get-MailboxStatistics $_).ItemCount}},
	@{label="TotalItemSize";expression={(Get-MailboxStatistics $_).TotalItemSize.ToString().Split("(")[1].Split(" ")[0].Replace(",","")/1MB),2}},  
	@{label="DeletedItemCount";expression={(Get-MailboxStatistics $_).DeletedItemCount}},
	@{label="TotalDeletedItemSize";expression={(Get-MailboxStatistics $_).TotalDeletedItemSize}},
	@{label="MailboxGuid";expression={(Get-MailboxStatistics $_).MailboxGuid}},
	@{label="LastLogoffTime";expression={(Get-MailboxStatistics $_).LastLogoffTime}}, 
	@{label="LastLogonTime";expression={(Get-MailboxStatistics $_).LastLogonTime}} | 
	Export-Csv -Path C:\Logs\Result.csv -NoTypeInformation
However, I am still having difficulties in the splitting the numbers?
here's the error:
At line:7 char:37
+ @{label="TotalItemSize";expression={(Get-MailboxStatistics $_).TotalItemSize.To ...
+ ~
Missing closing '}' in statement block.
At line:7 char:137
+ ... ace(",","")/1MB),2}},
+ ~
The hash literal was incomplete.
At line:7 char:137
+ ... ace(",","")/1MB),2}},
+ ~
Unexpected token ')' in expression or statement.
At line:7 char:140
+ ... (",","")/1MB),2}},
+ ~
Unexpected token '}' in expression or statement.
At line:7 char:141
+ ... ",","")/1MB),2}},
+ ~
Unexpected token '}' in expression or statement.
+ CategoryInfo : ParserError: (:) [], ParentContainsErrorRecordException
+ FullyQualifiedErrorId : MissingEndCurlyBrace
Your help is appreciated.
/* IT Engineer */

User avatar
jvierra
Posts: 13571
Joined: Tue May 22, 2007 9:57 am
Contact:

Re: How to export the Exchange MailboxStatistics along with PrimarySmtpAddress to .CSV?

Post by jvierra » Mon Aug 13, 2018 4:49 am

Code: Select all

	@{label="TotalItemSize";expression={
              $tis = (Get-MailboxStatistics $_).TotalItemSize
              $tis.ToString().Split('(')[1].Split(' ')[0].Replace(',','')/1MB
        }},  

User avatar
ITEngineer
Posts: 138
Joined: Wed Oct 12, 2011 10:52 am

Re: How to export the Exchange MailboxStatistics along with PrimarySmtpAddress to .CSV?

Post by ITEngineer » Mon Aug 13, 2018 6:32 am

It runs very slow but, yes, that works Mr. Vierra :)

Thanks for the help in correcting the code.
/* IT Engineer */

Locked