Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Question
Thursday, September 19, 2013 9:23 PM
Hi there.
I'd like to merge the following two "chain of commands"
Get-Mailbox | FT name,server,*quota*
and
Get-MailboxDatabase PREF* | Get-MailboxStatistics | FR name,*size*
so that I have all the information to single table which match name, quota configured and current size for each mailbox
Is it possible?
Thank you!!!
All replies (16)
Tuesday, September 24, 2013 2:12 PM ✅Answered | 1 vote
You can't pipe Format-Table's output to Export-Csv. If you want to transform the data a bit before putting it in a CSV file, you can use Select-Object instead of Format-Table.
Ok, clear! ... but, when I use select object I can't specify the format of a property... How can I manage that? I need a MB format so that everything is consistent and I can then analyze data with Excel... If I leave the default output "2,340 MB (2,523,455 bytes)" I can't order data in Excel...
You can use calculated properties with Select-Object, that really shouldn't be a problem. Example:
Get-ChildItem .\9-24-2013 |
Select @{N='This is the file name';E={$_.Name}}, @{N='This is the last write time in short format';E={$_.LastWriteTime.ToShortDateString()}}
Also, I recommend adding a -NoTypeInformation parameter to your Export-Csv command.
Don't retire TechNet! - (Maybe there's still a chance for hope, over 11,925+ strong and growing)
Tuesday, September 24, 2013 2:22 PM ✅Answered | 1 vote
Yep, what Mike said. :) Here's an example of how you might do this in your code. You also have the option of doing it inside the foreach loop, when you pipe Get-MailBox to Select-Object (and when you add the TotalItemSize property via Add-Member, just change -Value to $stat.TotalItemSize.value.ToMB() )
# Instead of this:
$FullResult | ft samaccountname,legacyExchangeDN,servername,database,UseDatabaseQuotaDefaults,@{Expression={$_.IssueWarningQuota.value.ToMB()}},@{Expression={$_.ProhibitSendQuota.value.ToMB()}},`
@{Expression={$_.ProhibitSendReceiveQuota.value.ToMB()}},`
ItemCount,`
@{Expression={$_.TotalItemSize.value.ToMB()}},DateAndTime -autosize | Export-csv -Path c:\Tasks\FullStatistics.csv -Delimiter ";"
# Try this (created the $properties array ahead of time to
# make the code easier to read):
$properties = @(
'samaccountname'
'legacyExchangeDN'
'servername'
'database'
'UseDatabaseQuotaDefaults'
'ItemCount'
'DateAndTime'
@{ Name = 'IssueWarningQuota'; Expression = { $_.IssueWarningQuota.value.ToMB() } }
@{ Name = 'ProhibitSendQuota'; Expression = { $_.ProhibitSendQuota.value.ToMB() } }
@{ Name = 'ProhibitSendReceiveQuota'; Expression = { $_.ProhibitSendReceiveQuota.value.ToMB() } }
@{ Name = 'TotalItemSize'; Expression = { $_.TotalItemSize.value.ToMB() } }
)
$FullResult | Select-Object -Property $properties | Export-csv -Path c:\Tasks\FullStatistics.csv -Delimiter ";"
Note: I haven't tested this code. I just used the expressions from your original Format-Table command, assuming that they work as you intend.
Friday, September 20, 2013 12:04 AM | 2 votes
Hi there,
This script will help you with it
http://exchangeserverpro.com/powershell-script-create-mailbox-size-report-exchange-server-2010/
Kottees :My Blog Please mark it as an answer if it really helps you.
Friday, September 20, 2013 12:15 AM | 2 votes
Yep, it's possible. There are probably many ways to do this (and some may not take as many lines of code as what I threw together), but here's one idea:
Get-Mailbox |
ForEach-Object {
$mailbox = $_
$object = $mailbox | Select-Object DisplayName,*Quota*
$object | Add-Member -MemberType NoteProperty -Name TotalDeletedItemSize -Value $null
$object | Add-Member -MemberType NoteProperty -Name TotalItemSize -Value $null
$statistics = $mailbox | Get-MailboxStatistics
if ($statistics -ne $null)
{
$object.TotalDeletedItemSize = $statistics.TotalDeletedItemSize
$object.TotalItemSize = $statistics.TotalItemSize
}
Write-Output $object
}
On a side note, I never knew you could use wildcards in the property names of Select-Object and the Format cmdlets before reading this post. That's handy!
Friday, September 20, 2013 3:43 AM | 2 votes
Hi there.
I'd like to merge the following two "chain of commands"
Get-Mailbox | FT name,server,*quota*
and
Get-MailboxDatabase PREF* | Get-MailboxStatistics | FR name,*size*
so that I have all the information to single table which match name, quota configured and current size for each mailbox
Is it possible?
Thank you!!!
Yes , it is possible .
Here you go ......
$mb=@()
$temp=@()
$myresult=@()
$mbx=get-mailbox -database "<databasename>" # put here your database name
foreach($m in $mbx)
{
$mb=Get-Mailbox -identity $m.name |select name,serverName,*quota*
$stat=Get-mailboxstatistics -identity $m.name
$mb | Add-Member -MemberType NoteProperty -Name TotalDeletedItemSize -Value $stat.totaldeleteditemsize
$mb | Add-Member -MemberType NoteProperty -Name TotalItemSize -Value $stat.TotalItemSize
$temp += $mb
}
$myresult=$temp
write-output $myResult # if you want result as Table , use write-output $myresult|ft -autosize
Hello David,
This script is inspired by your logic , unfortunately your script is not showing the correct output and even giving some error as well.
Below Error showing :
Pipeline not run because a pipeline is already running. Pipelines cannot be run concurrently. + CategoryInfo : OperationStopped: (Microsoft.Power...tHelperRunspace:ExecutionCmdletHelperRunspace) [], PSInvalidOperationException + FullyQualifiedErrorId : RemotePipelineExecutionFailed
I have modified little bit and now Its showing correct output.
If you found my post helpful, please give it a Helpful vote. If it answered your question, remember to mark it as an Answer. MCITP - Exchange 2010 | MCITP - Windows Server 2008 R2
Friday, September 20, 2013 10:37 AM
Hi there,
This script will help you with it
Good, but difficult to customize :-)
Anywhay I'll try it out
Friday, September 20, 2013 10:37 AM
Yep, it's possible. There are probably many ways to do this (and some may not take as many lines of code as what I threw together), but here's one idea:
Get-Mailbox | ForEach-Object { $mailbox = $_ $object = $mailbox | Select-Object DisplayName,*Quota* $object | Add-Member -MemberType NoteProperty -Name TotalDeletedItemSize -Value $null $object | Add-Member -MemberType NoteProperty -Name TotalItemSize -Value $null $statistics = $mailbox | Get-MailboxStatistics if ($statistics -ne $null) { $object.TotalDeletedItemSize = $statistics.TotalDeletedItemSize $object.TotalItemSize = $statistics.TotalItemSize } Write-Output $object }On a side note, I never knew you could use wildcards in the property names of Select-Object and the Format cmdlets before reading this post. That's handy!
Thank you!
Friday, September 20, 2013 10:38 AM
I have modified little bit and now Its showing correct output.
If you found my post helpful, please give it a Helpful vote. If it answered your question, remember to mark it as an Answer. MCITP - Exchange 2010 | MCITP - Windows Server 2008 R2
Thank you so much! I will try it out in a few hours
Friday, September 20, 2013 10:57 AM
Hello David,
This script is inspired by your logic , unfortunately your script is not showing the correct output and even giving some error as well.
Below Error showing :
Pipeline not run because a pipeline is already running. Pipelines cannot be run concurrently. + CategoryInfo : OperationStopped: (Microsoft.Power...tHelperRunspace:ExecutionCmdletHelperRunspace) [], PSInvalidOperationException + FullyQualifiedErrorId : RemotePipelineExecutionFailedI have modified little bit and now Its showing correct output.
That's odd. I tested the script before I posted it, and it worked fine for me. It might have to do with the different ways you can access Exchange through PowerShell, though. I did it by opening PowerShell on the Exchange server and using Add-PSSnapin to get at the Exchange Cmdlets (so none of the implicit remoting headaches that come with the Exchange Management Shell or Import-PSSession).
I would have tested with the Exchange Management Shell, but my test environment is giving me HTTP 500 errors for some reason. I need to rebuild it at some point.
Friday, September 20, 2013 7:37 PM
Hi David,
I never said that your script is not working , I said that's not showing the correct output and giving some error.
Please see below when I ran your script :
DisplayName : user20
ProhibitSendQuota : Unlimited
ProhibitSendReceiveQuota : Unlimited
RecoverableItemsQuota : Unlimited
RecoverableItemsWarningQuota : Unlimited
CalendarLoggingQuota : Unlimited
UseDatabaseQuotaDefaults : True
IssueWarningQuota : Unlimited
RulesQuota : 64 KB (65,536 bytes)
ArchiveQuota : Unlimited
ArchiveWarningQuota : Unlimited
TotalDeletedItemSize :
TotalItemSize :
Pipeline not run because a pipeline is already running. Pipelines cannot be run concurrently.
+ CategoryInfo : OperationStopped: (Microsoft.Power...tHelperRunspace:ExecutionCmdletHelperRunspace) [], PSInvalidOperationException
+ FullyQualifiedErrorId : RemotePipelineExecutionFailed
DisplayName : user3
ProhibitSendQuota : Unlimited
ProhibitSendReceiveQuota : Unlimited
RecoverableItemsQuota : Unlimited
RecoverableItemsWarningQuota : Unlimited
CalendarLoggingQuota : Unlimited
UseDatabaseQuotaDefaults : True
IssueWarningQuota : Unlimited
RulesQuota : 64 KB (65,536 bytes)
ArchiveQuota : Unlimited
ArchiveWarningQuota : Unlimited
TotalDeletedItemSize :
TotalItemSize :
Pipeline not run because a pipeline is already running. Pipelines cannot be run concurrently.
+ CategoryInfo : OperationStopped: (Microsoft.Power...tHelperRunspace:ExecutionCmdletHelperRunspace) [], PSInvalidOperationException
+ FullyQualifiedErrorId : RemotePipelineExecutionFailed
DisplayName : user4
ProhibitSendQuota : Unlimited
ProhibitSendReceiveQuota : Unlimited
RecoverableItemsQuota : Unlimited
RecoverableItemsWarningQuota : Unlimited
CalendarLoggingQuota : Unlimited
UseDatabaseQuotaDefaults : True
IssueWarningQuota : Unlimited
RulesQuota : 64 KB (65,536 bytes)
ArchiveQuota : Unlimited
ArchiveWarningQuota : Unlimited
TotalDeletedItemSize :
TotalItemSize :
Pipeline not run because a pipeline is already running. Pipelines cannot be run concurrently.
+ CategoryInfo : OperationStopped: (Microsoft.Power...tHelperRunspace:ExecutionCmdletHelperRunspace) [], PSInvalidOperationException
+ FullyQualifiedErrorId : RemotePipelineExecutionFailed
DisplayName : user5
ProhibitSendQuota : Unlimited
ProhibitSendReceiveQuota : Unlimited
RecoverableItemsQuota : Unlimited
RecoverableItemsWarningQuota : Unlimited
CalendarLoggingQuota : Unlimited
UseDatabaseQuotaDefaults : True
IssueWarningQuota : Unlimited
RulesQuota : 64 KB (65,536 bytes)
ArchiveQuota : Unlimited
ArchiveWarningQuota : Unlimited
TotalDeletedItemSize :
TotalItemSize :
DisplayName : user6
ProhibitSendQuota : Unlimited
ProhibitSendReceiveQuota : Unlimited
RecoverableItemsQuota : Unlimited
RecoverableItemsWarningQuota : Unlimited
CalendarLoggingQuota : Unlimited
UseDatabaseQuotaDefaults : True
IssueWarningQuota : Unlimited
RulesQuota : 64 KB (65,536 bytes)
ArchiveQuota : Unlimited
ArchiveWarningQuota : Unlimited
TotalDeletedItemSize : 0 B (0 bytes)
TotalItemSize : 2.923 KB (2,993 bytes)
DisplayName : user7
ProhibitSendQuota : Unlimited
ProhibitSendReceiveQuota : Unlimited
RecoverableItemsQuota : Unlimited
RecoverableItemsWarningQuota : Unlimited
CalendarLoggingQuota : Unlimited
UseDatabaseQuotaDefaults : True
IssueWarningQuota : Unlimited
RulesQuota : 64 KB (65,536 bytes)
ArchiveQuota : Unlimited
ArchiveWarningQuota : Unlimited
TotalDeletedItemSize : 0 B (0 bytes)
TotalItemSize : 2.923 KB (2,993 bytes)
DisplayName : user8
ProhibitSendQuota : Unlimited
ProhibitSendReceiveQuota : Unlimited
RecoverableItemsQuota : Unlimited
RecoverableItemsWarningQuota : Unlimited
CalendarLoggingQuota : Unlimited
UseDatabaseQuotaDefaults : True
IssueWarningQuota : Unlimited
RulesQuota : 64 KB (65,536 bytes)
ArchiveQuota : Unlimited
ArchiveWarningQuota : Unlimited
TotalDeletedItemSize : 0 B (0 bytes)
TotalItemSize : 2.923 KB (2,993 bytes)
DisplayName : user9
ProhibitSendQuota : Unlimited
ProhibitSendReceiveQuota : Unlimited
RecoverableItemsQuota : Unlimited
RecoverableItemsWarningQuota : Unlimited
CalendarLoggingQuota : Unlimited
UseDatabaseQuotaDefaults : True
IssueWarningQuota : Unlimited
RulesQuota : 64 KB (65,536 bytes)
ArchiveQuota : Unlimited
ArchiveWarningQuota : Unlimited
TotalDeletedItemSize : 0 B (0 bytes)
TotalItemSize : 2.923 KB (2,993 bytes)
[PS] C:\>
And if want to Display output in Table format , for this I have piped write-Output $object |ft -autosize , see what happened :
[PS] C:\>.\david.ps1
Pipeline not run because a pipeline is already running. Pipelines cannot be run concurrently.
+ CategoryInfo : OperationStopped: (Microsoft.Power...tHelperRunspace:ExecutionCmdletHelperRunspace) [], PSInvalidOperationException
+ FullyQualifiedErrorId : RemotePipelineExecutionFailed
Pipeline not run because a pipeline is already running. Pipelines cannot be run concurrently.
+ CategoryInfo : OperationStopped: (Microsoft.Power...tHelperRunspace:ExecutionCmdletHelperRunspace) [], PSInvalidOperationException
+ FullyQualifiedErrorId : RemotePipelineExecutionFailed
Pipeline not run because a pipeline is already running. Pipelines cannot be run concurrently.
+ CategoryInfo : OperationStopped: (Microsoft.Power...tHelperRunspace:ExecutionCmdletHelperRunspace) [], PSInvalidOperationException
+ FullyQualifiedErrorId : RemotePipelineExecutionFailed
Pipeline not run because a pipeline is already running. Pipelines cannot be run concurrently.
+ CategoryInfo : OperationStopped: (Microsoft.Power...tHelperRunspace:ExecutionCmdletHelperRunspace) [], PSInvalidOperationException
+ FullyQualifiedErrorId : RemotePipelineExecutionFailed
ForEach-Object : A parameter cannot be found that matches parameter name 'atuosize'.
At C:\david.ps1:2 char:1
+ ForEach-Object {
+ ~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidArgument: (:) [ForEach-Object], ParameterBindingException
+ FullyQualifiedErrorId : NamedParameterNotFound,Microsoft.PowerShell.Commands.ForEachObjectCommand
[PS] C:\>
Now see Output from my script :
[PS] C:\>.\databaseStat.ps1
Name ServerName ProhibitSendQuota ProhibitSendReceiveQuota RecoverableItemsQu
ota
Administrator win2012 Unlimited Unlimited Unlimited
user1 win2012 Unlimited Unlimited Unlimited
user2 win2012 Unlimited Unlimited Unlimited
user3 win2012 Unlimited Unlimited Unlimited
user4 win2012 Unlimited Unlimited Unlimited
user5 win2012 Unlimited Unlimited Unlimited
user6 win2012 Unlimited Unlimited Unlimited
user7 win2012 Unlimited Unlimited Unlimited
user8 win2012 Unlimited Unlimited Unlimited
user9 win2012 Unlimited Unlimited Unlimited
user10 win2012 Unlimited Unlimited Unlimited
user11 win2012 Unlimited Unlimited Unlimited
user12 win2012 Unlimited Unlimited Unlimited
user13 win2012 Unlimited Unlimited Unlimited
user14 win2012 Unlimited Unlimited Unlimited
user15 win2012 Unlimited Unlimited Unlimited
user16 win2012 Unlimited Unlimited Unlimited
user17 win2012 Unlimited Unlimited Unlimited
user18 win2012 Unlimited Unlimited Unlimited
user19 win2012 Unlimited Unlimited Unlimited
user20 win2012 Unlimited Unlimited Unlimited
DiscoverySearchMailbox {D919BA05-46A6-415f-80AD-7E09334BB852} win2012 50 GB (53,687,091,200 bytes) 50 GB (53,687,091,200 bytes) Unlimited
salesowner win2012 Unlimited Unlimited 30 GB
(32,212,254,720
bytes)
MettingRoom1 win2012 Unlimited Unlimited 30 GB
(32,212,254,720
bytes)
SharedMbx win2012 Unlimited Unlimited 30 GB
(32,212,254,720
bytes)
testuser win2012 Unlimited Unlimited Unlimited
journal win2012 Unlimited Unlimited 30 GB
(32,212,254,720
bytes)
[PS] C:\>
If you found my post helpful, please give it a Helpful vote. If it answered your question, remember to mark it as an Answer. MCITP - Exchange 2010 | MCITP - Windows Server 2008 R2
Friday, September 20, 2013 7:47 PM
I get it. I just didn't see those errors when I tested my code, but that may be because of how I accessed the Exchange cmdlets (through Add-PSSnapin instead of Import-PSSession or the management shell.)
Tuesday, September 24, 2013 1:25 PM
Ok guys, thank you so much!!!
with your precious suggestions I made this script. :-)
What I don't understand is why, when I output to screen it is correct, but when I output to CSV I get errors
#Adding Exchange Snapin
add-pssnapin Microsoft.Exchange.Management.PowerShell.E2010
#Cleaning Arrays
$MailboxSelectedProperties=@()
$TempArray=@()
$FullResult=@()
$MailboxArray = Get-Mailbox -ResultSize Unlimited | where {$_.servername -ilike 'DBHH0*'}
ForEach($ExchRecipient in $MailboxArray)
{
$MailboxSelectedProperties = Get-Mailbox -identity $ExchRecipient.name | select samaccountname,legacyExchangeDN,servername,database,UseDatabaseQuotaDefaults,IssueWarningQuota,ProhibitSendQuota,ProhibitSendReceiveQuota
$Stat= Get-mailboxstatistics -identity $ExchRecipient.name | select ItemCount,TotalItemSize
$MailboxSelectedProperties | Add-Member -MemberType NoteProperty -Name ItemCount -Value $stat.ItemCount
$MailboxSelectedProperties | Add-Member -MemberType NoteProperty -Name TotalItemSize -Value $stat.TotalItemSize
$now = get-date
$MailboxSelectedProperties | Add-Member -MemberType NoteProperty -Name DateAndTime -Value $now
$TempArray += $MailboxSelectedProperties
}
$FullResult=$TempArray
$FullResult | ft samaccountname,legacyExchangeDN,servername,database,UseDatabaseQuotaDefaults,@{Expression={$_.IssueWarningQuota.value.ToMB()}},@{Expression={$_.ProhibitSendQuota.value.ToMB()}},`
@{Expression={$_.ProhibitSendReceiveQuota.value.ToMB()}},`
ItemCount,`
@{Expression={$_.TotalItemSize.value.ToMB()}},DateAndTime -autosize | Export-csv -Path c:\Tasks\FullStatistics.csv -Delimiter ";"
If I remove final " | export-csv...." the screen output is correct!
If I output to CSV I get this:
"ClassId2e4f51ef21dd47e99d3c952918aff9cd";"pageHeaderEntry";"pageFooterEntry";"autosizeInfo";"shapeInfo";"groupingEntry"
"033ecb2bc07a4d43b5ef94ed5a35d280";;;"Microsoft.PowerShell.Commands.Internal.Format.AutosizeInfo";"Microsoft.PowerShell.Commands.Internal.Format.TableHeaderInfo";
"9e210fe47d09416682b841769c78b8a3";;;;;
"27c87ef9bbda4f709f6b4002fa4af63c";;;;;
"27c87ef9bbda4f709f6b4002fa4af63c";;;;;
"27c87ef9bbda4f709f6b4002fa4af63c";;;;;
"27c87ef9bbda4f709f6b4002fa4af63c";;;;;
"27c87ef9bbda4f709f6b4002fa4af63c";;;;;
"27c87ef9bbda4f709f6b4002fa4af63c";;;;;
"27c87ef9bbda4f709f6b4002fa4af63c";;;;;
Please note that if don't use "@expressions" to convert to MB my values, the output to CSV is correct!!!
Any Idea? :-(
Tuesday, September 24, 2013 1:32 PM
You can't pipe Format-Table's output to Export-Csv. If you want to transform the data a bit before putting it in a CSV file, you can use Select-Object instead of Format-Table.
Tuesday, September 24, 2013 1:53 PM
You can't pipe Format-Table's output to Export-Csv. If you want to transform the data a bit before putting it in a CSV file, you can use Select-Object instead of Format-Table.
Ok, clear! ... but, when I use select object I can't specify the format of a property... How can I manage that? I need a MB format so that everything is consistent and I can then analyze data with Excel... If I leave the default output "2,340 MB (2,523,455 bytes)" I can't order data in Excel...
Tuesday, September 24, 2013 1:55 PM
Yea , I agree with David .
If you found my post helpful, please give it a Helpful vote. If it answered your question, remember to mark it as an Answer. MCITP - Exchange 2010 | MCITP - Windows Server 2008 R2
Tuesday, September 24, 2013 2:44 PM
Guys!! YOU R-O-C-K ! :-)
It works like a charm! You really made my day! :-D Thank you sooo much!