Saturday, February 23, 2008

Combining multiple Exchange Management Shell powershell cmdlets and maintaining the Pipeline

This post continues on a little bit from last weeks update of the mailbox size GUI where I combined three Exchange powershell cmdlets get-mailbox,get-mailboxstatistics and get-mailboxdatabase to get the percentage of quota used for a users mailbox. When I did this although I used the Pipeline I didn’t really maintain it because I didn’t really need to I had my own custom methods for displaying the data I was querying. But one of the most useful things about Powershell is when using the pipeline normally you have a lot of built in functionality as to how you can format and export the results of whatever command you are running for instance Export-Csv format-table etc.

Someone asked the question this week

We need to get the user's list from POWER SHELL sorting by database , ProhibitsendrecieveQuota, mailboxsize & lastlogontime ( those who have not been logged on their mails for 60 days).

I hope there should be merged these two comlets "get-mailbox "and "get-mailboxstatistics".

Out put format should be as follows:

Alias, Databasename, ProhibitsendrecieveQuota, lastlogontime

Peter MailDB 100 MB 30/11/2007 “

While I might go about this another way such as writing another little gui or mod the mailbox size report it’s a useful problem to confront for people who are getting more and more comfortable with Pipelining in Exchange Management Shell but maybe not really sure how you could go about combining two or three cmdlets like I have and still get the normal output features.

The answer is relatively easy all you need to do is create your own custom collection and then write the result of your combined cmdlet queries to your custom collection and then at the end of the pipeline you can then use the normal export-csv or format-table cmds. For example here’s a script that solves the question asked and allows both outputting the result to a screen and exporting to a csv.

$mbcombCollection = @()
Get-Mailbox -ResultSize Unlimited | foreach-object{
$mbstatis = get-mailboxstatistics $_.identity
$mbcomb = "" | select Alias,Databasename,ProhibitsendrecieveQuota,lastlogontime
$pequote = "unlimited"
$dbarry = $_.Database.ToString().split("\")
if ($_.UseDatabaseQuotaDefaults -eq $true){
$dbsetting = get-Mailboxdatabase $_.database
if ($dbsetting.ProhibitSendReceiveQuota -ne "unlimited"){
$pequote = $dbsetting.ProhibitSendReceiveQuota.Value.ToMB()
else {
if ($_.ProhibitSendReceiveQuota -ne "unlimited"){
$pequote = $_.ProhibitSendReceiveQuota.Value.ToMB()
$mbcomb.Alias = $_.Alias
$mbcomb.Databasename = $dbarry[2]
$mbcomb.ProhibitsendrecieveQuota = 299
$mbcomb.lastlogontime = $mbstatis.LastLogonTime
$mbcombCollection += $mbcomb

$mbcombCollection | Export-Csv C:\LogonStats.csv

This is a simple example if you have a large number of users for example this would be a little slow it would be better just to make one get-mailbox request and one get-mailboxstatitics request store the results in two hash tables then merge the result of the hash tables which is what I did with the mailbox size gui. But the above method is much easier for people to understand so it’s probably a better example in that regard.


Erik Weber said...

Thanks, I was looking for something like this and your script gave me a push in the right direction :-)

panicZ said...

Thanks a lot! This script point me in the right direction too!

WINNT said...

$mbcomb = "" | select Alias,Databasename,ProhibitsendrecieveQuota,lastlogontime

Should be:

$mbcomb = "" | select Alias,Databasename,ProhibitsendreceiveQuota,lastlogontime


$mbcomb.ProhibitsendrecieveQuota = 299

Should be:

$mbcomb.ProhibitsendreceiveQuota = $pequote