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