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

Ask your 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.
This topic is 5 years and 7 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked
User avatar
ITEngineer
Posts: 216
Last visit: Thu Mar 23, 2023 5:45 pm
Has voted: 4 times

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

Post 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.
/* IT Engineer */
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

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

Post by jvierra »

Doesn't work when executing this over a remote connection. The methods are not martialed.
User avatar
ITEngineer
Posts: 216
Last visit: Thu Mar 23, 2023 5:45 pm
Has voted: 4 times

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

Post 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?
/* IT Engineer */
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

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

Post by jvierra »

You can't. The method does not exist if this is a remote connection.
User avatar
mxtrinidad
Posts: 399
Last visit: Tue May 16, 2023 6:52 am

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

Post 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) ".

:)
User avatar
mxtrinidad
Posts: 399
Last visit: Tue May 16, 2023 6:52 am

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

Post 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!
User avatar
ITEngineer
Posts: 216
Last visit: Thu Mar 23, 2023 5:45 pm
Has voted: 4 times

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

Post 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 :)
/* IT Engineer */
User avatar
mxtrinidad
Posts: 399
Last visit: Tue May 16, 2023 6:52 am

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

Post 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!!
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

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

Post 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
This topic is 5 years and 7 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked