Friday, November 23, 2007

Exchange 2007 Mailbox Size Powershell Form Script version 3

Version 4 has now been released that includes mailbox quotas here

I've been promising to update this for a while to fix a few issues and fulfill of few requests . I've switched the FolderSize code from using EWS to query the foldersizes to now use the Get-MailboxFolderStatistics Cmdlet instead. This makes the code a lot more functional and easy to use and doesn't require all that messing around with Impersonation and SSL (I kind of missed the whole Get-MailboxFolderStatistics cmdlet but it was fun building the EWS stuff anyway).

I've also now used DataGrids to display the result which means it is now sortable (Yeah!) you can now sort to your hearts content on any of the displayed columns. The only thing with Datagrid 's because of the threading issue with Powershell i couldn't use the click event so to display the foldersize you need to select the mailbox you want to show and then click the Get Folder Size button.

Another feature I've added is the ability to export both the Mailbox Size Grid and the Folder size grid. There are buttons bellow both the Datagrids that will open a dialogue box to allow you to choose a location to export to and produce a CSV file of the current results in the datagrid.

The last feature I've added is the ability to show just the Disconnected mailboxes so this will allow you to report just the sizes of all the Disconnected Mailboxes on you server. The one thing you cant do with a disconnected mailbox is show the foldersizes.

One last bug fix was to switch to using 64 Bit integers to fix a problem when the folder sizes go over 2 GB. I haven't been able to test this properly yet so please let me know if its still an issue.

The only real requirement now to run this code is that you run it from within the Exchange Management Shell because it uses a couple of the Exchange cmdlets the user running the code needs to have a least Exchange View-Only Administrator role rights.

I've put a download of the new script here the new code looks like

[System.Reflection.Assembly]::LoadWithPartialName("System.Drawing")
[System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms")


function getMailboxSizes(){
$msTable.clear()

if ($mtTypeDrop.SelectedItem -ne $null){
if ($mtTypeDrop.SelectedItem.ToString() -eq "Disconnected"){
get-mailboxstatistics -Server $snServerNameDrop.SelectedItem.ToString() | Where {$_.DisconnectDate -ne $null} | ForEach-Object{
$icount = 0
$tisize = 0
$disize = 0
if ($_.DisplayName -ne $null){$dname = $_.DisplayName}
if ($_.ItemCount -ne $null){$icount = $_.ItemCount}
if ($_.TotalItemSize.Value.ToMB() -ne $null){$tisize = $_.TotalItemSize.Value.ToMB()}
if ($_.TotalDeletedItemSize.Value.ToKB() -ne $null){$disize = $_.TotalDeletedItemSize.Value.ToKB()}
$msTable.Rows.add($dname,$icount,$tisize,$disize)
}
}
else{ get-mailboxstatistics -Server $snServerNameDrop.SelectedItem.ToString() | Where {$_.DisconnectDate -eq $null} | ForEach-Object{
$icount = 0
$tisize = 0
$disize = 0
if ($_.DisplayName -ne $null){$dname = $_.DisplayName}
if ($_.ItemCount -ne $null){$icount = $_.ItemCount}
if ($_.TotalItemSize.Value.ToMB() -ne $null){$tisize = $_.TotalItemSize.Value.ToMB()}
if ($_.TotalDeletedItemSize.Value.ToKB() -ne $null){$disize = $_.TotalDeletedItemSize.Value.ToKB()}
$msTable.Rows.add($dname,$icount,$tisize,$disize)
}

}
}
else{
get-mailboxstatistics -Server $snServerNameDrop.SelectedItem.ToString() | ForEach-Object{
$icount = 0
$tisize = 0
$disize = 0
if ($_.DisplayName -ne $null){$dname = $_.DisplayName}
if ($_.ItemCount -ne $null){$icount = $_.ItemCount}
if ($_.TotalItemSize.Value.ToMB() -ne $null){$tisize = $_.TotalItemSize.Value.ToMB()}
if ($_.TotalDeletedItemSize.Value.ToKB() -ne $null){$disize = $_.TotalDeletedItemSize.Value.ToKB()}
$msTable.Rows.add($dname,$icount,$tisize,$disize)
}

}
write-host $fstring

$dgDataGrid.DataSource = $msTable

}


function GetFolderSizes(){
$fsTable.clear()
$snServername = $snServerNameDrop.SelectedItem.ToString()
write-host $dgDataGrid.CurrentCell.RowIndex
$siSIDToSearch = get-user $msTable.DefaultView[$dgDataGrid.CurrentCell.RowIndex][0]
write-host $siSIDToSearch.SamAccountName.ToString()
Get-MailboxFolderStatistics $siSIDToSearch.SamAccountName.ToString() | ForEach-Object{
$ficount = 0
$fisize = 0
$fsisize = 0
$fscount = 0
$fname = $_.Name
if ($_.FolderSize -ne $null){$fsisize = [math]::round(($_.FolderSize/1mb),2)}
if ($_.ItemsInFolder -ne $null){$ficount = $_.ItemsInFolder}
if ($_.ItemsInFolderAndSubfolders -ne $null){$fscount = $_.ItemsInFolderAndSubfolders}
if ($_.FolderAndSubfolderSize -ne $null){$fsisize = [math]::round(($_.FolderAndSubfolderSize/1mb),2)}
$fsTable.Rows.add($fname,$ficount,$fsisize,$fscount,$fsisize)
}
$dgDataGrid1.DataSource = $fsTable
}

function ExportMBcsv{

$exFileName = new-object System.Windows.Forms.saveFileDialog
$exFileName.DefaultExt = "csv"
$exFileName.Filter = "csv files (*.csv)|*.csv"
$exFileName.InitialDirectory = "c:\temp"
$exFileName.ShowDialog()
if ($exFileName.FileName -ne ""){
$logfile = new-object IO.StreamWriter($exFileName.FileName,$true)
$logfile.WriteLine("UserName,# Items,MB Size(MB),DelItems(KB)")
foreach($row in $msTable.Rows){
$logfile.WriteLine("`"" + $row[0].ToString() + "`"," + $row[1].ToString() + "," + $row[2].ToString() + "," + $row[3].ToString())
}
$logfile.Close()
}
}

function ExportFScsv{

$exFileName = new-object System.Windows.Forms.saveFileDialog
$exFileName.DefaultExt = "csv"
$exFileName.Filter = "csv files (*.csv)|*.csv"
$exFileName.InitialDirectory = "c:\temp"
$exFileName.ShowDialog()
if ($exFileName.FileName -ne ""){
$logfile = new-object IO.StreamWriter($exFileName.FileName,$true)
$logfile.WriteLine("DisplayName,# Items,Folder Size(MB),# Items + Sub,Folder Size + Sub(MB)")
foreach($row in $fsTable.Rows){
$logfile.WriteLine("`"" + $row[0].ToString() + "`"," + $row[1].ToString() + "," + $row[2].ToString() + "," + $row[3].ToString() + "," + $row[4].ToString())
}
$logfile.Close()
}
}

$form = new-object System.Windows.Forms.form
$global:LastFolder = ""
# Add DataTable

$Dataset = New-Object System.Data.DataSet
$fsTable = New-Object System.Data.DataTable
$fsTable.TableName = "Folder Sizes"
$fsTable.Columns.Add("DisplayName")
$fsTable.Columns.Add("# Items",[int64])
$fsTable.Columns.Add("Folder Size(MB)",[int64])
$fsTable.Columns.Add("# Items + Sub",[int64])
$fsTable.Columns.Add("Folder Size + Sub(MB)",[int64])
$Dataset.tables.add($fsTable)

$msTable = New-Object System.Data.DataTable
$msTable.TableName = "Mailbox Sizes"
$msTable.Columns.Add("UserName")
$msTable.Columns.Add("# Items")
$msTable.Columns.Add("MB Size(MB)",[int64])
$msTable.Columns.Add("DelItems(KB)",[int64])
$Dataset.tables.add($msTable)

# Add Server DropLable
$snServerNamelableBox = new-object System.Windows.Forms.Label
$snServerNamelableBox.Location = new-object System.Drawing.Size(10,20)
$snServerNamelableBox.size = new-object System.Drawing.Size(80,20)
$snServerNamelableBox.Text = "ServerName"
$form.Controls.Add($snServerNamelableBox)

# Add Server Drop Down
$snServerNameDrop = new-object System.Windows.Forms.ComboBox
$snServerNameDrop.Location = new-object System.Drawing.Size(90,20)
$snServerNameDrop.Size = new-object System.Drawing.Size(100,30)
get-mailboxserver | ForEach-Object{$snServerNameDrop.Items.Add($_.Name)}
$snServerNameDrop.Add_SelectedValueChanged({getMailboxSizes})
$form.Controls.Add($snServerNameDrop)

# Add Mailbox Type DropLable
$mtTypeDroplableBox = new-object System.Windows.Forms.Label
$mtTypeDroplableBox.Location = new-object System.Drawing.Size(200,20)
$mtTypeDroplableBox.size = new-object System.Drawing.Size(80,20)
$mtTypeDroplableBox.Text = "MailboxType"
$form.Controls.Add($mtTypeDroplableBox)

# Add Mailbox Type Drop Down
$mtTypeDrop = new-object System.Windows.Forms.ComboBox
$mtTypeDrop.Location = new-object System.Drawing.Size(290,20)
$mtTypeDrop.Size = new-object System.Drawing.Size(100,30)
$mtTypeDrop.Items.Add("Disconnected")
$mtTypeDrop.Items.Add("Connected")
$mtTypeDrop.Add_SelectedValueChanged({if ($snServerNameDrop.SelectedItem -ne $null){getMailboxSizes}})
$form.Controls.Add($mtTypeDrop)

# Add Export MB Button

$exButton1 = new-object System.Windows.Forms.Button
$exButton1.Location = new-object System.Drawing.Size(10,560)
$exButton1.Size = new-object System.Drawing.Size(125,20)
$exButton1.Text = "Export Mailbox Grid"
$exButton1.Add_Click({ExportMBcsv})
$form.Controls.Add($exButton1)

# Add Export FG Button

$exButton2 = new-object System.Windows.Forms.Button
$exButton2.Location = new-object System.Drawing.Size(500,560)
$exButton2.Size = new-object System.Drawing.Size(135,20)
$exButton2.Text = "Export FolderSize Grid"
$exButton2.Add_Click({ExportFScsv})
$form.Controls.Add($exButton2)

# Add DataGrid View

$dgDataGrid = new-object System.windows.forms.DataGridView
$dgDataGrid.Location = new-object System.Drawing.Size(10,50)
$dgDataGrid.size = new-object System.Drawing.Size(450,500)
$form.Controls.Add($dgDataGrid)

$dgDataGrid1 = new-object System.windows.forms.DataGridView
$dgDataGrid1.Location = new-object System.Drawing.Size(500,50)
$dgDataGrid1.size = new-object System.Drawing.Size(450,500)
$form.Controls.Add($dgDataGrid1)

# folder Size Button

$fsizeButton = new-object System.Windows.Forms.Button
$fsizeButton.Location = new-object System.Drawing.Size(500,19)
$fsizeButton.Size = new-object System.Drawing.Size(120,23)
$fsizeButton.Text = "Get Folder Size"
$fsizeButton.visible = $True
$fsizeButton.Add_Click({GetFolderSizes})
$form.Controls.Add($fsizeButton)



$form.Text = "Exchange 2007 Mailbox Size Form"
$form.size = new-object System.Drawing.Size(1000,620)
$form.autoscroll = $true
$form.topmost = $true
$form.Add_Shown({$form.Activate()})
$form.ShowDialog()

23 comments:

Anonymous said...

can u pls thorw an example on how to sign the script, so that exchange server 2007 can run the script from scripts folder? at the movemnet i couldn't run the script as it is not signed

Anonymous said...

Love it Glen, excellent script.

Have a few minor updates but I will ping you them later

Glen said...

In regards to the signing issue have a read of http://www.hanselman.com/blog/SigningPowerShellScripts.aspx

What i usually do however is just create a new file from with in the shell locally on that machine and then cut and paste the contents of the script into the new file.

Cheers
Glen

Glen said...

An easier method to address the signing issue is all you need to do is in Windows Explorer right click the file and select properties. Then from the General Tab click Unblock. This will allow you to run the script without needing it to be signed.

Cheers
Glen

Jasen Walker said...

Thanks for the update - version 2 was great, but I fought the access issues - this one is flawless!

Thanks again!

Anonymous said...

can i ask a stupid question: how do you run this script?
I opened the PS and just browsed to the directory where the script is, then i simply typed the name of the script and this is the error I get:
"The term 'mbsizereportv3.ps1' is not recognized as a cmdlet, function, operable program, or script file. Verify the term and try again.
At line:1 char:18
+ mbsizereportv3.ps1 <<<<"
I read the post about unblocking the script and have already done it.
Can somwbody please tell me what I am doing wrong? I am sure it is something stupid, but....
Thanks

Anonymous said...

ok i now used the PowerGUI to try to run the script and when i do it I get an error on line 142 where the script is trying to execute the get-mailboxserver cmdlet. It says that it doesn't recognize it. but if i type that cmdlet in the PS window it works fine. so what am I not seeing???

Glen said...

This script uses the Exchange Management Shell cmdlets (get-mailbox etc) so you need to run it from a machine that has the Exchange 2007 Management Tools installed. You need to run it from within the Exchange management shell (or else you can load the Exchange Management shell snapin in a normal powershell session). see http://technet.microsoft.com/en-us/library/bb123778.aspx

Cheers
Glen

Anonymous said...

Glen thanks for your reply. However, I am running the script on the machine that has exchange 2007 on it and i do run it from the exchange PS. this is the output:
[PS] C:\Documents and Settings\administrator.HAWKGRP\Desktop\mbsizereportv3>mbsizereportv3.ps1
The term 'mbsizereportv3.ps1' is not recognized as a cmdlet, function, operable program, or script file. Verify the term and try again.
At line:1 char:18
+ mbsizereportv3.ps1 <<<<


I have tried it both ways (the ones you/technet described, to no avail. I am sure it is something stupid I am doing but right now i can't figure it out. Any help will be appreciated

Glen said...

To run a powershell script you need to include the complete path to the script file eg C:\Documents and Settings\administrator.HAWKGRP\Desktop\mbsizereportv3\mbsizereportv3.ps1

Theres a full discussion on this if you haven't run powershell scripts before in http://www.microsoft.com/technet/scriptcenter/topics/winpsh/manual/run.mspx

Cheers
Glen

Anonymous said...

awesome. now it works...

Screemer said...

I love the script.. Really nice stuff.
I can't believe Microsoft made the GUI worse than on previous version.

Just a question, would it be possible to add the output of StorageLimitStatus and also a send it as e-mail function?

That would make my life as exchange admin much easier..:)

Thank you for a great script.

Glen said...

It's doable but wouldn't having the the percent of Quota used by more useful ?. Send as email is a little difficult as you cant be sure the mail server in question will actually accept email from anonymous sources.

Cheers
Glen

Anonymous said...

Hi Glen thanks for your work on this script, use it often!

Couple of issues:
1. Sorting by #Items seems to sort alphabetically rather than numerically. e.g.
102277
107
11794
instead of
107
11794
102277
Not sure if this is a problem with the control or the script?

2. The Mailbox Size in displayed in MB, but the Deleted Items size is displayed in KB, which can be confusing. Can the script be updated so that the delItems are displayed in MB?

Keep up the good work!

Glen said...

1. Yep thats a bug I didn't make the Items Column the Interger Datatype.

2. I'm working on a new version to show the % of quota used this should be done in the next couple of weeks so watch this space for a new version.

Cheers
Glen

Marcelo said...

Hi Glen, you made an excelent job! I wonder where should I begin since I want to make a form which shows you the result of 4 mailbox servers where it must have Server name (1st col), Mailboxes total quantity (2nd col), SG1MB1 mailboxes (3rd col) and so on... what do you recommend me?

Thanks and great work!

Jason said...

Awesome script Glen, would there be a way to run this on a monthly basis to export the csv data for reporting purposes?

AFD said...

absolutely brilliant!!!! I am loving you big time right now...thanks again

Glen said...

Make sure you use the latest version of the script which support exports etc http://gsexdev.blogspot.com/2008/05/version-5-of-mailbox-size-gui-script.html

Cheers
Glen

Anonymous said...

This script ran fine and gets the mailbox sizes for all users, but when I try to get the folder sizes I get the following error on the majority of our mailboxes.

It seems to be the mailboxes that were inported from our previous Exchange 2003 system that error and those created on Exchange 2007 work fine.

The error reads:

Get-MailboxFolderStatistics : Unable to retrieve mailbox folder statistics for mailbox username. Failure: 'username' cannot be found as a recipient.
At C:\PSScripts\mbsizereportv3.ps1:60 char:28
+ Get-MailboxFolderStatistics <<<< $siSIDToSearch.SamAccountName.ToString() | ForEach-Object{

Any help appreciated.

Anonymous said...

Perhaps I'm not reading the code right, but I don't see where I point this script to read mailbox server. The script errors at "get mailboxserver" stating that is not a valid command.

Glen said...

This script needs to be run from within the Exchange Management Shell else you will have no access to the
Exchange cmdlets such as get-mailbox etc.

cheers

Glen

Kimulus said...

Hello,

Very handy script, thank you for your work!

I wonder if it would be possible to add quota (%). We have some heavy users who have bigger quota and it would be nice to see that in the list.