How to export the Exchange MailboxStatistics along with PrimarySmtpAddress to .CSV? [Part 2]

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: 95
Joined: Wed Oct 12, 2011 10:52 am

How to export the Exchange MailboxStatistics along with PrimarySmtpAddress to .CSV? [Part 2]

Post by ITEngineer » Tue Aug 14, 2018 12:20 am

Hi All,

This is the modified script which is running faster than the script in the previous thread:

Code: Select all

$DataPath = "C:\TEMP\mbxresults.csv"
$Results = @()
$MailboxUsers = Get-Mailbox -ResultSize Unlimited
foreach($user in $mailboxusers) {
	$UPN = $user.UserPrincipalName
	$MbxStats = Get-MailboxStatistics $UPN
	$UserNotes = Get-User $UPN
	      $Properties = @{
		      Name = $user.name
		      PrimarySmtpAddress = $user.PrimarySmtpAddress
		      UPN = $UPN
		      Alias = $user.alias
		      OU = $user.organizationalunit
		      Server = $MbxStats.servername
		      Database = $MbxStats.databasename
		      TotaItemSize = {$MbxStats.totalitemsize.Value.ToGB()}
		      Notes = $UserNotes.Notes
	      }
	$Results += New-Object psobject -Property $properties
}
$Results | Sort-Object -Property TotaItemSize | Select-Object Name,UPN,Alias,OU,Server,Database,TotaItemSize,Notes | Export-Csv -notypeinformation -Path $DataPath
However, I cannot convert the value of TotaItemSize column into Gigabytes or even Megabytes so I can sort it on the Excel spreadsheet.

Excel column result:

Code: Select all

TotaItemSize
$MbxStats.totalitemsize.Value.ToMB()
$MbxStats.totalitemsize.Value.ToMB()
$MbxStats.totalitemsize.Value.ToMB()
$MbxStats.totalitemsize.Value.ToMB()
$MbxStats.totalitemsize.Value.ToMB()
$MbxStats.totalitemsize.Value.ToMB()
$MbxStats.totalitemsize.Value.ToMB()
$MbxStats.totalitemsize.Value.ToMB()
$MbxStats.totalitemsize.Value.ToMB()
$MbxStats.totalitemsize.Value.ToMB()
$MbxStats.totalitemsize.Value.ToMB()
$MbxStats.totalitemsize.Value.ToMB()
$MbxStats.totalitemsize.Value.ToMB()
$MbxStats.totalitemsize.Value.ToMB()
$MbxStats.totalitemsize.Value.ToMB()
$MbxStats.totalitemsize.Value.ToMB()
$MbxStats.totalitemsize.Value.ToMB()
$MbxStats.totalitemsize.Value.ToMB()
$MbxStats.totalitemsize.Value.ToMB()
$MbxStats.totalitemsize.Value.ToMB()
$MbxStats.totalitemsize.Value.ToMB()
$MbxStats.totalitemsize.Value.ToMB()
$MbxStats.totalitemsize.Value.ToMB()
$MbxStats.totalitemsize.Value.ToMB()
$MbxStats.totalitemsize.Value.ToMB()
$MbxStats.totalitemsize.Value.ToMB()
$MbxStats.totalitemsize.Value.ToMB()
$MbxStats.totalitemsize.Value.ToMB()
$MbxStats.totalitemsize.Value.ToMB()
$MbxStats.totalitemsize.Value.ToMB()
$MbxStats.totalitemsize.Value.ToMB()
$MbxStats.totalitemsize.Value.ToMB()
...
$MbxStats.totalitemsize.Value.ToMB()
as per this article: http://clintboessen.blogspot.com/2013/0 ... stics.html

Any help would be greatly appreciated.
/* IT Engineer */

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

Re: How to export the Exchange MailboxStatistics along with PrimarySmtpAddress to .CSV? [Part 2]

Post by jvierra » Tue Aug 14, 2018 3:10 am

Doesn't work when executing this over a remote connection. The methods are not martialed.

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

Re: How to export the Exchange MailboxStatistics along with PrimarySmtpAddress to .CSV? [Part 2]

Post by ITEngineer » Tue Aug 14, 2018 3:38 am

jvierra wrote:
Tue Aug 14, 2018 3:10 am
Doesn't work when executing this over a remote connection. The methods are not martialed.
So how to modify the line TotaItemSize = {$MbxStats.totalitemsize.Value.ToGB()} so it works when I execute it?
/* IT Engineer */

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

Re: How to export the Exchange MailboxStatistics along with PrimarySmtpAddress to .CSV? [Part 2]

Post by jvierra » Tue Aug 14, 2018 3:56 am

You can't. The method does not exist if this is a remote connection.

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

Re: How to export the Exchange MailboxStatistics along with PrimarySmtpAddress to .CSV? [Part 2]

Post by mxtrinidad » Tue Aug 14, 2018 6:53 am

First, you need to breakdown the objects you're trying to use to build this results. You need to understand that you'll be working with object types that are not only strings or integers.

For exemple, the TotalItemSize is not a numeric value, and this is shown when displaying the value by itself. Also, there's no ".ToGB" method.
You will need to parse the value in order to get the GB of the mailbox.

Also, there's a better way to create the PSObject that will exported to the CSV file.
Use the following:
$MyCSVPath = "C:\Temp\PSObjFile.csv";
$myPSObject = foreach($item on $Object){
[pscustomobject]$MyProperties = New-Object psobject -property @{
Name = $item.Name
:
}; $MyProperties;
};
$MyCSVPath | Export-Csv -Path $MyCSVPath -NoTypeInformation

Keep in mind, what you are trying to do inside the Foreach loop it will not retain the object after is use due to scoping. The method shown above will help build the object that way you want.

Your code will look like the following: (a little more streamlined)

Code: Select all

$DataPath = "C:\TEMP\mbxresults.csv";
$results = $null;
$results = foreach ($user in (Get-Mailbox -ResultSize Unlimited)) {
	[pscustomobject]$Properties = New-Object psobject -property @{
		Name = $user.name
		PrimarySmtpAddress = $user.PrimarySmtpAddress
		UPN  = $user.UserPrincipalName
		Alias = $user.alias
		OU   = $user.organizationalunit
		Server = (Get-MailboxStatistics $user.UserPrincipalName).servername
		Database = (Get-MailboxStatistics $user.UserPrincipalName).databasename
		## - This need to be parse: (below line is not fixed)
		#TotaItemSize = { ((Get-MailboxStatistics $user.UserPrincipalName).totalitemsize.value) /1gb }
		Notes = (Get-User $user.UserPrincipalName).Notes
	}; $Properties;
};
Export-Csv -Path $DataPath -NoTypeInformation
Work on parsing the "((Get-MailboxStatistics $user.UserPrincipalName).totalitemsize.value) ".

:)

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

Re: How to export the Exchange MailboxStatistics along with PrimarySmtpAddress to .CSV? [Part 2]

Post by mxtrinidad » Tue Aug 14, 2018 7:34 am

Ah!! I completed the parsing myself.

Here's the complete script:

Code: Select all

$DataPath = "C:\TEMP\mbxresults.csv";
$results = $null;
$results = foreach ($user in (Get-Mailbox -ResultSize Unlimited)) {
	
	$TotItemSize1 = ((Get-MailboxStatistics $user.UserPrincipalName).totalitemsize.Value).ToString().split(" ")[0];
	$TotItemSize2 = ((Get-MailboxStatistics $user.UserPrincipalName).totalitemsize.Value).ToString().split(" ")[1];
	$UserTotItemSize = "$TotItemSize1$TotItemSize2"/1gb
	
	[pscustomobject]$Properties = New-Object psobject -property @{
		Name = $user.name
		PrimarySmtpAddress = $user.PrimarySmtpAddress
		UPN  = $user.UserPrincipalName
		Alias = $user.alias
		OU   = $user.organizationalunit
		Server = (Get-MailboxStatistics $user.UserPrincipalName).servername
		Database = (Get-MailboxStatistics $user.UserPrincipalName).databasename
		TotaItemSize = $UserTotItemSize
		Notes = (Get-User $user.UserPrincipalName).Notes
	}; $Properties;
};
$results | Export-Csv -Path $DataPath -NoTypeInformation -NoClobber;
Invoke-Item $DataPath;
Hope this helps!

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

Re: How to export the Exchange MailboxStatistics along with PrimarySmtpAddress to .CSV? [Part 2]

Post by ITEngineer » Tue Aug 14, 2018 3:45 pm

mxtrinidad wrote:
Tue Aug 14, 2018 6:53 am
First, you need to breakdown the objects you're trying to use to build this results. You need to understand that you'll be working with object types that are not only strings or integers.

For exemple, the TotalItemSize is not a numeric value, and this is shown when displaying the value by itself. Also, there's no ".ToGB" method.
You will need to parse the value in order to get the GB of the mailbox.

Also, there's a better way to create the PSObject that will exported to the CSV file.
Use the following:
$MyCSVPath = "C:\Temp\PSObjFile.csv";
$myPSObject = foreach($item on $Object){
[pscustomobject]$MyProperties = New-Object psobject -property @{
Name = $item.Name
:
}; $MyProperties;
};
$MyCSVPath | Export-Csv -Path $MyCSVPath -NoTypeInformation

Keep in mind, what you are trying to do inside the Foreach loop it will not retain the object after is use due to scoping. The method shown above will help build the object that way you want.

Your code will look like the following: (a little more streamlined)

Code: Select all

$DataPath = "C:\TEMP\mbxresults.csv";
$results = $null;
$results = foreach ($user in (Get-Mailbox -ResultSize Unlimited)) {
	[pscustomobject]$Properties = New-Object psobject -property @{
		Name = $user.name
		PrimarySmtpAddress = $user.PrimarySmtpAddress
		UPN  = $user.UserPrincipalName
		Alias = $user.alias
		OU   = $user.organizationalunit
		Server = (Get-MailboxStatistics $user.UserPrincipalName).servername
		Database = (Get-MailboxStatistics $user.UserPrincipalName).databasename
		## - This need to be parse: (below line is not fixed)
		#TotaItemSize = { ((Get-MailboxStatistics $user.UserPrincipalName).totalitemsize.value) /1gb }
		Notes = (Get-User $user.UserPrincipalName).Notes
	}; $Properties;
};
Export-Csv -Path $DataPath -NoTypeInformation
Work on parsing the "((Get-MailboxStatistics $user.UserPrincipalName).totalitemsize.value) ".

:)
Great, let us know here if it is posible MX :)
/* IT Engineer */

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

Re: How to export the Exchange MailboxStatistics along with PrimarySmtpAddress to .CSV? [Part 2]

Post by mxtrinidad » Tue Aug 14, 2018 4:18 pm

Hey!! I want to make sure you notice the corrected script code.
As I have Office365 I was able to complete the script after all!

:)

PowerShell .NET scripting is Awesome!!

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

Re: How to export the Exchange MailboxStatistics along with PrimarySmtpAddress to .CSV? [Part 2]

Post by jvierra » Tue Aug 14, 2018 5:31 pm

The following will be much faster as it avoids multiple unnecessary trips to the server.

Code: Select all

$filePath = 'C:\TEMP\mbxresults.csv'
$results = foreach ($mbx in (Get-Mailbox -ResultSize Unlimited)) {
    $stats = Get-MailboxStatistics $mbx.UserPrincipalName
    if($stats.TotalItemSize.Value -match '\((.*)\sbytes\)'){
        $totalItemSize = ([int]$matches[1])/1Gb
    }else{
        $totalItemSize = $null
    }
	[pscustomobject]@{
		Name = $mbx.name
		PrimarySmtpAddress = $mbx.PrimarySmtpAddress
		UPN  = $mbx.UserPrincipalName
		Alias = $mbx.alias
		OU   = $mbx.organizationalunit
		Server = $mbx.servername
		Database = $mbx.database
		TotaItemSize = $totalItemSize
		Notes = ($mbx | Get-User).Notes
	}
}
$results | Export-Csv -Path $filePath -NoTypeInformation

Locked