Page 1 of 1

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

Posted: Tue Aug 14, 2018 12:20 am
by ITEngineer
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.

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

Posted: Tue Aug 14, 2018 3:10 am
by jvierra
Doesn't work when executing this over a remote connection. The methods are not martialed.

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

Posted: Tue Aug 14, 2018 3:38 am
by ITEngineer
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?

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

Posted: Tue Aug 14, 2018 3:56 am
by jvierra
You can't. The method does not exist if this is a remote connection.

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

Posted: Tue Aug 14, 2018 6:53 am
by mxtrinidad
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) ".

:)

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

Posted: Tue Aug 14, 2018 7:34 am
by mxtrinidad
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!

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

Posted: Tue Aug 14, 2018 3:45 pm
by ITEngineer
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 :)

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

Posted: Tue Aug 14, 2018 4:18 pm
by mxtrinidad
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!!

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

Posted: Tue Aug 14, 2018 5:31 pm
by jvierra
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