Saturday, May 31, 2008

Version 5 of the Mailbox Size Gui Script for Exchange 2007

***** If your running Exchange 2010 please see http://gsexdev.blogspot.com/2010/03/mailbox-size-gui-exchange-2010-remote.html ********

Okay time for a new version of the mailbox size gui the last version had a number of large bugs and the quota code looked like it had been written by a drunk squirrel. So this version firstly fixes the bugs in the quota code and the bugs with Deleted items sizes and adds some new functionality.

The first addition is a drop down box that allows selection of which quota value you want to report on Eg exchange offers 3 quota levels Warning, Prohibit Send and Prohibit Send and Receive so the script will now allow reporting on which ever Quota usage you want to look at. This section still needs a little work to get better performance as it re-queries the server for mailbox sizes when you change the quota dropdown box instead of just reusing the current result.

The second and most exciting feature is the mailbox size growth feature this is the biggest change and requires some explanation.

Firstly when the script runs now it will create a folder on the c drive called mbsizehistory, it will then record the results of each mailbox size query you make to a csv file in this directory with a serial date as the file name along with the servername . It will only create one file per day and per server you run it against.

So basically every time you run this script it will create this file so if you run it once a week or one a day or every three days you will start collecting adhoc history data about your mailbox size growth. Convention wisdom and what I’ve done in the past is record this type of data to a database every day and then build table, view and graph report on this data. While this is possible with the script the amount of complexity of setup and other backend you need starts getting beyond the technology that might be generally available for a simple script like this. But this where the cool and exciting part comes in to cater for this and allow aggregation of random history files using a few simple date algorithms, hash table and loops I’ve come up with a serial date, random file aggregation datatable. This part is highly experimental as it was only written last week so I haven’t tested it with real data only mock data and haven’t tested it with multiple servers so going on past experience the squirrel may make another appearance. But with the limited testing I’ve done it does work and I think its pretty cool and useful. Basically it aggregates the previous result from the files and tries to show the mailbox growth over 1 day,7days,1 month and 1 year. Because the dates of the file may be intermediate the script handles getting the file that is as closest to the growth date's as possible.

I’ve put a downloadable of the new version here the growth section looks like

function ShowGrowth(){


$gtTable.clear()
$datetime = get-date
$arArrayList = New-Object System.Collections.ArrayList
dir c:\mbsizehistory\*.csv | foreach-object{
$fname = $_.name
$nmArray = $_.name.split("-")
if ($nmArray[1].Replace(".csv","") -eq $snServerNameDrop.SelectedItem.ToString()) {
[VOID]$arArrayList.Add($nmArray[0])
}
}
$arArrayList.Sort()
$spoint = $arArrayList[$arArrayList.Count-1]
$oneday = $spoint
$sevenday = $spoint
$onemonth = $spoint
$oneyear = $spoint
foreach ($file in $arArrayList){
if ($file -gt ($datetime.Adddays(-2).ToString("yyyyMMdd")) -band $file -lt $oneday) {$oneday = $file}
if ($file -gt ($datetime.Adddays(-7).ToString("yyyyMMdd")) -band $file -lt $sevenday) {$sevenday = $file}
if ($file -gt ($datetime.Adddays(-31).ToString("yyyyMMdd")) -band $file -lt $onemonth) {$onemonth = $file}
if ($file -gt ($datetime.Adddays(-256).ToString("yyyyMMdd")) -band $file -lt $oneyear) {$oneyear = $file}
}
write-host $oneday
write-host $sevenday
write-host $onemonth
write-host $oneyear

$onedaystats = @{ }
$sevendaystats = @{ }
$onemonthsdaystats = @{ }
$oneyearstats = @{ }

Import-Csv ("c:\mbsizehistory\" + $oneday + "-" + $snServerNameDrop.SelectedItem.ToString() + ".csv") | %{
$onedaystats.add($_.DisplayName,$_.TotalItemSize)
}
Import-Csv ("c:\mbsizehistory\" + $sevenday + "-" + $snServerNameDrop.SelectedItem.ToString() + ".csv") | %{
$sevendaystats.add($_.DisplayName,$_.TotalItemSize)
}
Import-Csv ("c:\mbsizehistory\" + $onemonth + "-" + $snServerNameDrop.SelectedItem.ToString() + ".csv") | %{
$onemonthsdaystats.add($_.DisplayName,$_.TotalItemSize)
}
Import-Csv ("c:\mbsizehistory\" + $oneyear + "-" + $snServerNameDrop.SelectedItem.ToString() + ".csv") | %{
$oneyearstats.add($_.DisplayName,$_.TotalItemSize)
}

foreach($row in $msTable.Rows){
if ($onedaystats.ContainsKey($row[0].ToString())){
$ondaysizegrowth = $row[2] - $onedaystats[$row[0].ToString()]
}
else{$ondaysizegrowth = 0}
if ($sevendaystats.ContainsKey($row[0].ToString())){
$sevendaysizegrowth = $row[2] - $sevendaystats[$row[0].ToString()]}
else{$sevendaysizegrowth = 0}
if ($onemonthsdaystats.ContainsKey($row[0].ToString())){
$onemonthsizegrowth = $row[2] - $onemonthsdaystats[$row[0].ToString()]}
else{$onemonthsizegrowth = 0}
if ($oneyearstats.ContainsKey($row[0].ToString())){
$oneyearsizegrowth = $row[2] - $oneyearstats[$row[0].ToString()]}
else{$oneyearsizegrowth = 0}
$gtTable.rows.add($row[0].ToString(),$row[2],$ondaysizegrowth,$sevendaysizegrowth,$onemonthsizegrowth,$oneyearsizegrowth)

}
$dgDataGrid.DataSource = $gtTable

}


110 comments:

Flaphead said...

Love it buddy, but I have one tweek for ya ... which is to use a variable for the folder path, so you just need to change it in once place ;-) will send you the update

Johnny'O said...

How about being able to specify a greater than value to search for? I really dont care about the guy who has a 40 mb mailbox but would like to know of everyone that is over 500 mb.

Otherwise looks like a valuable tool to me.

Anonymous said...

Love the script. Haven't picked it apart yet but I did notice one issue right away.

I have an account that shows up by default when I first pick a server. When I pick "Warning" dropdown it dissappears. When I pick the 2 "Send" dropdowns it re-appears.

I also get a bunch of output in my shell window like...

AllowDBNull : True
AutoIncrement : False
AutoIncrementSeed : 0
AutoIncrementStep : 1
Caption : # Items
ColumnName : # Items
Prefix :
DataType : System.Int64
DateTimeMode : UnspecifiedLocal
DefaultValue :
Expression :

Is that normal?

Thanks again.

Glen said...

The outputs is fine thats just from ADO.NET and mainly because i haven't sent the output to VOID. If you are getting anything in Red then this is an error and something to worry about.

The problem you are describing sounds like a bug but i cant reproduce it. With the user in question how are their quota settings set. Eg are they set to use Store default or explicitly set. If so what are the values.

Thanks
Glen

Anonymous said...

I resolved the issue with the mailboxes dissappearing as I picked the quota type dropdown.

This line in the script...

$msTable.Rows.add($dname,$icount,$tisize,$disize,$quQuota.replace("%",""))

Generated this error...

Exception calling "Add" with "1" argument(s): "Input string was not in a correct format.Couldn't store <2,000 > in Quota Used Column. Expected type is Int64.
"
At C:\Temp\test.ps1:174 char:20
+ $msTable.Rows.add( <<<< $dname,$icount,$tisize,$disize,$quQuota.replace("%",""))

Basically, I was 2,000% over quota and the $msTable.Rows.add didn't like that "," in 2,000; so I stripped it out with a $quQuota = $quQuota.replace(",","").

I'm sure you have a slicker way to fix this...I'm a hack.

Scott

Anonymous said...

Sorry if you have covered this before, but I see a discrepency in the way DeletedItems are reported.

These 2 commands appears to be seeing different values for deleted items.

1) get-mailboxstatistics - TotalDeletedItemSize = 130M

2) Get-MailboxFolderStatistics - $fsisize = 33M

Scott

Glen said...

Get-MailboxFolderStatistics will only look at the dumpster of one particular folder where Get-MailboxStatistics would be the accrued total of all folder within a mailbox

cheers
Glen

Anonymous said...

Has anyone received any issues when running this script. I doubt it is the script that is the issue but my powershell. When I try to run this script I get the error:

The term 'get-mailboxserver' is not recognized as a cmdlet, function, operable program, or script file. Verify the term and try again.
At D:\scripts\mbsizereportv5.ps1:358 char:18
+ get-mailboxserver <<<< | ForEach-Object{$snServerNameDrop.Items.Add($_.Name)
}
0
1
0
1
2

Can someone point the way? I am new to powershell and sorely miss my vbscripts. :(

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...

Thanks. Unfortunately, I am running it at the exchange 2007 server. The weird part is that cmdlet is valid when I run it alone. Do you know of a place where I can troubleshoot powershell errors? Looked in google and its decentralized resolutions here and there.

Glen said...

Sorry to harp on this but even if you are running it at the server eg in a powershell session the script wont work. It needs to run from within the Exchange Management shell or from within a powershell session when the addin has been loaded.

Cheers
Glen

Anonymous said...

Glen...
this is a great script, but i have a one question or comment... when i export the mailbox grid i don't get all the columns. Just username, # of items, mb size, and delitems. Why won't it export the quota used column?

Anonymous said...

Glen,

This is a great script. However I think I found one small bug. n the "Show Growth History" when you click on "Folder Size" to sort it, it doesn't sort correctly.

Thank you.
Keep the good work!

Glen said...

Well spotted thanks i'll get that bug fixed soon.

much appreciated
Glen

Anonymous said...

Glen,
greate script, but one question - in what place and how we must add something to change code page?
In CSV file we have non latin names as "??????".
Thank you

Glen said...

Good question I'll have to try and test this somewhere and get back to you.

Cheers
Glen

Anonymous said...

A bit off topic, but how would you give another user (helpdesk) access to a script using the get-mailboxstatistics cmdlet? Via a shared folder on my computer, or linking a web page with the script?

Glen said...

Okay I've fixed the problem with the sorting in Show Growth (forget to add the mailbox size as an interger type in the ADO.NET table).

I've fixed the issue with Nonlatin characters in ShowGrowth as well you need to include –encoding "unicode" in the export-csv line. I've updated the download with these changes

Glen said...

A user would need to be delegated at least view only admin Exchange rights to run get-mailboxstatistics

Anonymous said...

Another n00bish question, can the Exchange snap-in just be loaded or does the user running mailboxstatistics need EMC installed?

Glen said...

You need to have the Exhcange Management Tools installed i believe this will install all the underlying .NET class need for the EMS snapins

Cathy said...

I loved your script. How can I get all of t he columns to export? I have tried to get the quotas column to export with the rest, but I have not been successful. I get only username, # of items, mb size, and delitems. Why won't it export the quota used column?
Thanks,
Cathy

Glen said...

Thanks Cathy that was another bug I've update the download with a fix.

Cheers
Glen

Anonymous said...

I like it, but I'd love to be able to sort by mailbox store, company name, location... anything really

Matt said...

Hi Glen,

Once again (I commented on version 4) thanks for a great script! I do still get the following error when clicking on Get Folder Size:

You cannot call a method on a null-valued expression.
At C:\mbsizereportv5.ps1:261 char:50
+ write-host $siSIDToSearch.SamAccountName.ToString( <<<< )
You cannot call a method on a null-valued expression.
At C:\mbsizereportv5.ps1:262 char:67
+ Get-MailboxFolderStatistics $siSIDToSearch.SamAccountName.ToString( <<<< ) |
ForEach-Object{

Could it be because we don't have any quotas at all enabled, only warnings?

Glen said...

Actually i came accross that problem last week in my 2008 test enviroment its to do with using the samaccountname with Get-mailboxfolderstatistics. I've switched to using the WindowsEmailAddress which seems to work everytime. I've just updated the script so give the new script a try and let me know how it goes

Cheers
Glen

Matt said...

Hi Glen!

I tried the new version, but unfortunately Get Folder Size still fails on some of the users. But I think I've found a pattern! It fails for users where "get-user [firstname] [lastname]" returns more than one user. For different reasons many of our users have more than one Active Directory account.

Matt

Aaron Perrault said...

Glen,
I am having a problem with this script. I can run it and it generates the initial information, but when I click on a user, and then click on Show Growth History, i throw an error. A wonderful little box pops up that says Cannot open file c:\mbsizehistory\-SSV-MAIL-OAK01.csv.

This doesnt look right to me.

Any assistance would be greatly apprecaited. I would love to be able to show this to Management. Keep up the great work. Love the blog.

app

Glen said...

Hi Matt,

Thanks for letting me know how it went. I've made another update to the script so it can handle multiple objects returned from get-user and then it will try to match based on the name. This may or may not fix your problem do you have multiple domains ? If the objects names are different this fix should work or you may have to try and tweak the command a little.

cheers
Glen

Glen said...

Hi Arron,

It looks like the get-date cmdlet is failing for you for some reason I've not seen this before basically what should be appended in front of the servername is the date which allows the datetime aggregation. Make sure you download the latest version of the script also you could try replacing all lines that have

$datetime = get-date

with

$datetime = [datetime]::Now.Date

That might be a workaround for you.

Cheers
Glen

Matt said...

Hi again,

Unfortunately it got worse. ;) Now the "null-valued expression" error occurs on every user.

When get-user returns more than one account, maybe the script could pick the first one with RecipientType=UserMailBox? In our case usually just one of the accounts is "UserMailBox" and the rest (if any) are "User".

Matt

Glen said...

Okay i've made that change the get-user cmd should now filter on RecipientType=UserMailBox see how that goes

Cheers
Glen

Matt said...

Hi Glen,

Success! Now it works wonderfully!

Matt

Anonymous said...

Glen,

After the script is open, and when I select a server, the following error is displaed in the EMS:

Attempted to divide by zero.
At C:\Program Files\Microsoft\Exchange Server\Scripts\mbsizereportv5.ps1:152 char:58
+ $quQuota = "{0:P0}" -f ($_.TotalItemSize.Value.ToMB()/$ <<<< usrquotas[$_.MailboxGUID].Value.ToMB())



Then when I select the quota tab and make a selection the following error is displayed in the EMS:

Exception calling "Add" with "1" argument(s): "Input string was not in a correct format.Couldn't store <1,650 > in Quota Used Column. Expected type
is Int64."
At C:\Program Files\Microsoft\Exchange Server\Scripts\mbsizereportv5.ps1:134 char:20
+ $msTable.Rows.add( <<<< $dname,$icount,$tisize,$disize,$quQuota.replace("%",""))


Any suggestions:

Glen said...

This will happen if quota values are set to less the 1 MB which will geneate quota usage of more the 100 percent. I've updated the script to fix both of these issues if the quota values are under 1 MB the script will show usage of 100 %

Cheers
Glen

Jeff said...

Glen - Thanks for sharing all of your hard work.

When I export the Maibox Grid the CSV file labels the Deleted Items column as KB, while the grid says it should be MB. I'm pretty sure MB is correct.

Can you point me in the right direction to understand your previous comment regarding "Get-MailboxFolderStatistics will only look at the dumpster of one particular folder where Get-MailboxStatistics would be the accrued total of all folder within a mailbox"? I'm trying to get my arms around the concept of what each is really reporting in the real world and why they would be different. Thanks!

Jeff

Glen said...

Thanks I've fixed the label problem and uploaded a new script.

With deleted Items Get-MailboxFolderstatistics returns sizes related to each of the IPM_Subtree Folders. Get-MailboxStatistics returns size relating to all folders in a mailbox including NON_IPM_Subtree and the IPM_Subtree. While there isn't usually much space used by the NON_IPM_Subtree folder's this is the reason you can see some difference between mailbox sizes depending on the way you are calculating.

Cheers
Glen

Anonymous said...

I'm not sure how to run this. Can anyone help me? I run the ps1 script through an EMS shell, but the directory doesn't get created on my C:\. I am doing this on a Windows Vista x64 workstation with the Ex07 tools loaded. Thanks in advance!

Glen said...

If your not logged on with a User that has local admin rights on the workstation then normal users cant create folder under the root with vista. So you need to either create the folder yourself with the rights or change the path to a location where you do have rights to create folder such as the user profile directory or mydocuments etc

Cheers
Glen

Anonymous said...

Glen,

Great script!!!! Howevr when running it I noticed he following error:

Exception calling "Add" with "2" argument(s): "Item has already been added. Key
in dictionary: 'Katrina @ Contoso Company' Key being added: 'Katrina @
Contoso Company'"
At C:\Documents and Settings\Administrator.TAA\Desktop\mbsizereportv5.ps1:240 c
har:18
+ $onedaystats.add( <<<< $_.DisplayName,$_.TotalItemSize)
Exception calling "Add" with "2" argument(s): "Item has already been added. Key
in dictionary: 'Katrina @ Contoso Company' Key being added: 'Katrina @
Contoso Company'"
At C:\Documents and Settings\Administrator.TAA\Desktop\mbsizereportv5.ps1:243 c
har:20
+ $sevendaystats.add( <<<< $_.DisplayName,$_.TotalItemSize)
Exception calling "Add" with "2" argument(s): "Item has already been added. Key
in dictionary: 'Katrina @ Contoso Company' Key being added: 'Katrina @
Contoso Company'"
At C:\Documents and Settings\Administrator.TAA\Desktop\mbsizereportv5.ps1:246 c
har:24
+ $onemonthsdaystats.add( <<<< $_.DisplayName,$_.TotalItemSize)

Tank you
Sharapov

Glen said...

That error wont really affect the operation of the script its look like you have one account with the same displayname. Generally this isn't possible to do because the management tools generally stop this happening but its possible to set. The main cause of the problem is that i have used the displyname as a key field.

Cheers
Glen

Brian said...

Glen,
This script is great.

Is there a way to add an entry in the servername drop down that would include all servers? That would be really nice to see all users in one view, rather than jumping through each server.

Glen said...

Hi Brian Good idea easy to cater for I've updated the download basically it just add's a * to the server list selecting this will then make all the -Server parameters do a wildcard search which should get all the servers and users. let me know if you have a problem.

Cheers
Glen

Brian said...

Glen,
Thanks for the quick response. I'm getting the following error when I select the * from the dropdown. The rest of the App works fine:

Get-MailboxDatabase : There are multiple exchange servers matching the identity
"*". Please specify a unique value.
At V:\Data_Warehouse\Installs\Exchange2007SP1\Scripts\mbsizereportv5.ps1:21 cha
r:20
+ get-mailboxdatabase <<<< -server $snServerNameDrop.SelectedItem.ToString() |
ForEach-Object{
Get-Mailbox : There are multiple exchange servers matching the identity "*". Pl
ease specify a unique value.
At V:\Data_Warehouse\Installs\Exchange2007SP1\Scripts\mbsizereportv5.ps1:55 cha
r:12
+ Get-Mailbox <<<< -server $snServerNameDrop.SelectedItem.ToString() -ResultSi
ze Unlimited | foreach-object{
Get-MailboxStatistics : There are multiple Exchange servers matching the identi
ty "*". Please specify a unique value.
At V:\Data_Warehouse\Installs\Exchange2007SP1\Scripts\mbsizereportv5.ps1:160 ch
ar:24
+ get-mailboxstatistics <<<< -Server $snServerNameDrop.SelectedItem.To
String() | ForEach-Object{

Export-Csv : Index was out of range. Must be non-negative and less than the siz
e of the collection.
Parameter name: index
At V:\Data_Warehouse\Installs\Exchange2007SP1\Scripts\mbsizereportv5.ps1:199 ch
ar:32
+ $mbcombCollection | export-csv <<<< -encoding "unicode" -noTypeInformati
on $fname

Glen said...

Okay sorry that was just me being lazy and only trying to change 1 line of the script. I've updated the script again and it should work this time. If it doesn't please let me know.

Cheers
Glen

Brian said...

Thank,
I'm now getting the following error:

Get-MailboxStatistics : The local machine is not an Exchange server. Please spe
cify the name of a Mailbox server.
At mypath\mbsizereportv5.ps1:184 ch
ar:42
+ $mailboxestats = get-mailboxstatistics <<<<
Exception calling "ContainsKey" with "1" argument(s): "Key cannot be null.
Parameter name: key"
At mypath\mbsizereportv5.ps1:191 ch
ar:29
+ if ($usrquotas.ContainsKey( <<<< $_.MailboxGUID)){
You cannot call a method on a null-valued expression.
At mypath\mbsizereportv5.ps1:210 ch
ar:34
+ if ($_.TotalItemSize.Value.ToMB( <<<< ) -ne $null){$tisize = $_.Total
ItemSize.Value.ToMB()}
You cannot call a method on a null-valued expression.
At mypath\mbsizereportv5.ps1:211 ch
ar:41
+ if ($_.TotalDeletedItemSize.Value.ToMB( <<<< ) -ne $null){$disize = $
_.TotalDeletedItemSize.Value.ToMB()}

Glen said...

Okay i see the problem now I've update the script hope this should fix it now.

Cheers
Glen

Brian said...

PERFECT!

Thanks Glen. This makes my life much easier.

Brian said...

This is not a big deal, but the file created for the All Servers selection shows as follows:

20081201-ALLSERVERS.csv20081201-ALLSERVERS.csv20081201-ALLSERVERS.csv

Glen said...

Okay thanks again that bug should be fixed.

Cheers
Glen

Raj said...

Great script. This helps me save my time. How to extract mailbox statistics from each OU and output the results of mailbox usage report per OU, instead of displaying all the users in flat.

Where should I tweak in this script?

Once again, thanks a lot for this script.

Glen said...

Have a look at http://gsexdev.blogspot.com/2008/06/more-fun-with-combining-mutliple.html which domenstrates a method that might help

Cheers
Glen

Anonymous said...

Your program doesn't work my e2k7 sp1 ;

[PS] C:\>mbsizereportv5.ps1
The term 'mbsizereportv5.ps1' is not recognized as a cmdlet, function, operable program, or script file. Verify the term and try again.
At line:1 char:18
+ mbsizereportv5.ps1 <<<<
[PS] C:\>

Glen said...

It looks like your a beginner in Powershell ? I'd suggest you do a quick revision on how to run powershell scripts http://www.microsoft.com/technet/scriptcenter/topics/winpsh/manual/run.mspx this will help you overcome issue you will face trying to run scripts.

Cheers
Glen

Anonymous said...

Hi Glen this script looks great. I know the title syas 07 server but can this be run in a Exchange 2003 environment? I know powershell scripts can be run against 03 exchange but haven't had any luck getting this to work.

thanks for the help
"no google acct"
WZ

Glen said...

No this wont work on 2003 you would need to rewrite it for this to work on 2003 using WMI and the Exchange_Mailbox class. There is no equivilent to get-folderstatistics on 2003 you would need to use and Exchange API like WebDAV to retrieve the folder sizes by logging on the mailbox you wanted to check.

Cheers
Glen

John said...

Hi Glen,

This is a great tool. I prefer it over using PFDAVADMIN personally. One feature I'd love to see is a filter by database option.

Thanks for this great tool.

Anonymous said...

Hi Glen,

I have my Exchange servers in a root domain and the user mailboxes in subdomains. When I try to get folder sizes, the script throws an error that it can't locate the object on the DC/GC that is part of the root domain.

Any ideas?
Thanks,
Brit

Glen said...

So how does a valid Get-MailboxFolderStatistics look for you ? if you can work that out you will then need to modify the script appropriately.

Cheers
Glen

Anonymous said...

This is great!

No complaints. No bugs. No suggestions. Just wanted to say thanks for sharing this with us!

Ben_13 said...

Hey Glen, as usual great job on the script.

I'm having one small issue , same as Aaron did earlier and I tried your fix replaced datetime but still no luck. I still end up with this:

Import-Csv : Cannot open file C:\mbsizehistory\-VC-EXCH-01.csv.
At C:\Temp\Bens Scripts\mbsizereportv5.ps1:281 char:11
+ Import-Csv <<<< ("$script:HistoryDir\" + $oneday + "-" + $snServerNameDrop.S
electedItem.ToString() + ".csv") | %{

Glen said...

You need to find why the datetime is not getting returned. Its bascially just getting the datetime as a string and then appending this to create a filename. It should work and does for most people i cant say why its not working for you but all it is just strings it should be easy to troubleshoot. eg just try simple things like outputting the date and time as a string in a simple script.

Cheers
Glen

ChristianWickham said...

We needed to modify this so that it would list the Mailbox Store database (we have differing quotas & Policies on mailstores).
I added the database parameter;

$icount = 0
$tisize = 0
$disize = 0
$ddatabase = $_.database
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.ToMB() -ne $null){$disize = $_.TotalDeletedItemSize.Value.ToMB()}
$msTable.Rows.add($dname,$icount,$tisize,$disize,$ddatabase,$quQuota.replace("%","").replace(",",""))
$mbcomb = "" | select Date,ServerName,DisplayName,ItemCount,TotalItemSize,TotalDeletedItemSize,DatabaseName
$mbcomb.Date = $datetime.ToString("yyyyMMdd")
$mbcomb.ServerName = $snServerNameDrop.SelectedItem.ToString()
$mbcomb.DisplayName = $dname
$mbcomb.ItemCount = $icount
$mbcomb.TotalItemSize = $tisize
mbcomb.TotalDeletedItemSize = $disize
$mbcomb.DatabaseName = $ddatabase
$mbcombCollection += $mbcomb



And then for the export to CSV function, I just added the parameter + "," + $row[5].ToString()


Then, to display it on the form, I added


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


Importantly, I found that if the column "Database" was the last one, then it was not re-sizable. So, I needed to make "Quota" the last column - otherwise the "database" column was too small to be useful.

Hope this helps other people.

Glen said...

Thanks for Sharing Christian.

Cheers
Glen

Will said...

This is one of the coolest scripts I have seen to date. It is tailored to exactly what I needed. Thanks for being so smart and for sharing that knowledge with the rest of us.

Mikey said...

Love the script

Anonymous said...

What would be nice is to restrict this to a message store, or to have the report include the databse/store they are in. We do a lot of load leveling in databases to keep theirs sizes down.

Anonymous said...

HI.. new to Exchange 2007 but very much interested in using this script, please can any one tell me how i go about runing it

Glen said...

To run this script you need to run it from a workstation or server that has the Exchange Management Shell install. You need to start the the Exchange Management Shell and the run the script from within this console window by using the full path to the script eg if you put it in c:\temp using c:\temp\mbsizereportv5.ps1 you may get a message about the script being blocked if you right click the script and select properties you should be able to select unblock from the properties menu.

Cheers
Glen

Duane said...

In regards to Christian's modifications to show the storage group/database.

How would i split up the output?

When exported to CSV the database shows servername/SG/DB

I would like to seperate each of those into thier own columns when exported.

Thanks
Duane

Glen said...

A simple fix would be just do a replace on the "/" character with a "," because its a CSV file when you open it in Excel it should mean that you get seperate columns for this.

Cheers
Glen

Duane said...

Thanks Glen. Obvious solutions sometimes are forgotten!

Another question though.

While running this script i'm noticing something wrong with the data output.

Opening ESM and I set 2 filters based on server name and database.

Currently on this specific database I have 55 user mailboxes listed.

45 of them have been accessed since June 1st and the rest back to May 27th.

I then run your script to see what each mailbox size is. I select the servername and then select connected. The output showing for that database is 31 which also is including 2 system mailboxes. I select disconnected and only 1 shows up. If the accounts are disabled...does it disregard those mailboxes? or is something else going on?


Thanks again for the great script!

Duane

Glen said...

Disabled account should show up fine well they do for me. Its just using standard EMS cmdlet queries that you can test eg

get-mailboxstatistics -server servername | Where {$_.DisconnectDate -eq $null}

Try running this and see if you can see those mailbox. Also look at the cmd windows the reason you may not see certain mailboxes is because an error or bug in the script is happening so you should see this error outputed to the cmd windows. If you let me know the error I might be able to troubleshoot further. If your using your own modified version of the script try to download the original again and see if you get the same issue.

Cheers
Glen

Anonymous said...

Glen,

This is an awesome script.
I added Christian's changes and my database column is "0" and quota column is empty. any ideas?

Glen said...

typo ? do you get any errors ?

Cheers
Glen

Duane said...

Hey Glen,

Got the script working great. Thanks for the help.

Another question...with your experience what would be the best way to implement something like this?

Currently my exchange servers have no mailbox quota's set to stores or users. I've got users with mailbox sizes ranging from 1mb to 21GB. I finally have the approval to implement mailbox quota's. I won't be able to implement it it by store since since there are many execs that will not fall into this restriction. I will have to do it on a per user basis. Suxs for me!

My thinking was to export everything with your version 5 script to the CSV.
Then I should be able to modify the data (say everybody listed with 1GB mailbox size or less) and then create a script that would query/pull the data from the CSV and implement the 1GB quota limit to thier account.

For the past two days i've been scouring the net to find anything similar to this but not having alot of luck. Is this possible?

Thanks
Duane

Glen said...

You should be able to do that if you use Import-csv and the process the CSV file a line at time and then get this to run a Set-Mailbox to modify the mailbox Quotas.

Rgds
Glen

Zetsway said...

Dude, you totally rock!! Thank you so much for this script. You save me a lot of headaches.

10cha said...

Great tool! I ran the script (after install)and I'm not sure how the growth history works. I was able to view it for "mailbox size"...but I do not see any information beyond that?

Glen said...

The growth information only shows for Mailbox size at the moment

Cheers
Glen

Daniel said...

I have no real prior knowledge of scripts for Exchange. Even I can see this is so extremely useful. I did have a question about implementation though. I am looking to schedule this to run once a day so that I can get some historical data. Much of the time no one is on this server and it isn't every day someone will be able to launch the script. The way I found that seems like it would work is to create a batch file to call the script up and then use Scheduler in Windows to run the batch file. The command in my batch file is

Powershell -command "& {C:\Scripts\Send-As\Send-As.ps1 }"

I am pretty sure something on the batch file is wrong since when I manually run it it does not bring up information about the server. Initially I thought that I needed to pass the server name on to the script at time of running (I don't know how to do that of course) then when I checked the pull down the server wasn't even listed. As I said this is my first introduction to scripts for exchange and I was able to find some info I have run to the end and no longer even know what I should search for in order to get useful knowledge on the subject. I read through all the comments so far and didn't find my question answered (I hope I didn't miss it) so now I am asking you, well anyone really. Thank you for any answers you may provide, I am sorry I wasn't able to find the answer on my own.

Glen said...

What you need is a different version of this script I've posted one on http://msgdev.mvps.org/exdevblog/mssched.zip. This script takes one cmdline parameter which is the name of the sever you want to run it against it has all the gui components removed and just produces the history files. To run this from a scheduled task see http://www.microsoft.com/technet/scriptcenter/topics/winpsh/manual/run.mspx#E5B

Cheers
Glen

Daniel said...

Thank you very much for your help. that script worked. Now comes the hard part of waiting for historical information to get collected. And thank you very much for being understanding toward someone new trying to learn.

Daniel said...

It is me again. So we have been running your script for a week. I have the independently running script scheduled for once a day. So we have about two weeks of data. When I try to run the GUI script it brings up today's information however I do not get any of the growth history. I seem to be getting the same error and Ben13 earlier. I figure that if it was just the date not coming in correctly then today's csv file would not be dated and yet they are. Any troubleshooting suggestions, including good references for me to refer to?

Glen said...

In the powershell box are you getting any errors ? are the files okay ? You could try putting in the line

$erroractionpreference = "SilentlyContinue"

which will make it skip any errors to see if this makes a differance. Most probably a bug but it hard if i cant reproduce it.

PlanetShadow said...

Can this script be utilized for this purpose:

I need for a script to check the sizes of all user's mailbox and to kick an event into the event log when a mailbox reaches a set size threshold, say 500MB...

Anonymous said...

I am getting the following error while i tried to get the mailbox size

The local machine is not an Exchange server. Please specify the name of a Mailbox server. (Get-MailboxStatistics) At line: 1

Glen said...

You need to select the exchange server name to run it against from the drop down list.

Cheers
Glen

Glen said...

I've never really work much with linked mailboxes if you can do it via get-mailboxstatistics then it should work because this is what this script use to get the mailbox size

Cheers
Glen

Anonymous said...

Excellent work Glen...
Had to thank you

Teknologist said...

Thanks a bunch! Very handy!

It's rather slow on my server though...I'm sure it's my dbsizes though...I'm attempting to spread the mailboxes out a bit more to lower the database sizes this week...but thanks again!

guruleenyc said...

AWESOME SCRIPT AND THANK YOU FOR BEING SO GENEROUS!!!

MUCH APPRECIATED!!!

-guruleenyc

Computer Nets-To-Go said...

Glenn,

Love the script for user lookups. I also needed a script to create a file for reporting on all mailboxes and one that can be scheduled. Here is what I was able to produce based on your script:


Add-PSSnapin Microsoft.Exchange.Management.PowerShell.Admin
$datetime = get-date
$filename = "c:\temp\mbhist" + $datetime.ToString("MMddyyyy")+ ".csv"
$logfile = new-object IO.StreamWriter($filename,$true)
$logfile.WriteLine("User, DisplayName,# Items,Folder Size(MB),# Items + Sub,Folder Size + Sub(MB)")
$mscombCollection += get-mailboxstatistics -server Your server name here
$q=[char]34
$mscombCollection | ForEach-Object{
if ($_.DisplayName -ne $null){$dname = $_.DisplayName}
get-user $dname -RecipientType "UserMailbox" | foreach-object{
$siSIDToSearch = $_
}

Get-MailboxFolderStatistics $siSIDToSearch.WindowsEmailAddress.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)

foreach($row in $fsTable.Rows){
$logfile.WriteLine($q +$dname+ $q +","+ $q + $fname + $q +"," + $ficount + "," + $fsisize + "," + $fscount + "," + $fsisize )
}


}
}

Anonymous said...

Bless you!

Anonymous said...

Thanks a million for this wonderful script.

It is of great help to people like me who do not have much scripting expertise.

I was wondering if the script can be modified to display the current Quotas imposed on the mailbox like the current values of IssueWarningQuota, ProhibitSendQuota and ProhibitSendReceiveQuota.

Any guidance will be appreciated

Anonymous said...

Hey Glen,

In my org. I use a "Custom Attributes" in AD....any way to add a colum to the form, custom attribute 1?

Thanks!
The form is awesome!!!!

Glen said...

You should be able to get the custom attribute information out of get-mailbox its not that easy to modify the script have a look at http://msgdev.mvps.org/exdevblog/mbsizereportv5ms3.zip which is a modified version to include more info if you replace one of these extra fields that might make it easy for you.

Cheers
Glen

Audun said...

Hi, this script is good.
I would like to be able to shedule this script to run. Can you add command line options for server name and silent run and exit?

Regards,
Audun

Adrian. said...

awesome script! thanks so much!

Anonymous said...

Thanks for your script, it has been very useful.

csilvest said...

Great script - thank you. I still can't believe Microsoft left this out of 2007. They must be trying to herd people into paying for some of their management solutions...

Anyway, I just have one request - how can I get a report of all users, not just those exceeding quotas? I'm going to play around with the code but I'm not familiar with the objects being used so I'm hoping you have a quick fix for me.

Thanks again!

RichardX said...

Hey Glen,

Thanks very much for the script - proving to be super useful. I have been trying to pick it apart, though, so I can pull and add the StorageGroupName to the output but after my last edit, I am getting no output back, just an empty form. Kind of at my PS knowledge-end with this one, me thinks..... I will keep plugging at it but in the meantime, is that an easy add for you for maybe a next rev?

Thanks again!

Richard

RichardX said...

Just saw the link posted at http://msgdev.mvps.org/exdevblog/mbsizereportv5ms3.zip - perfecto, just what I needed! Thanks!

Jonathan Barker said...

I'm having the same issue as Aaron. I've replaced the datetime as you suggested but it's still occuring. I'm not running this script daily which may be the issue. Here is the error:

Import-Csv : Could not find file 'C:\mbsizehistory\-ALL Servers.csv'.
At K:\powershell\Exchange\MailBoxSizeGUI\mbsizereportv5.ps1:281 char:1
+ Import-Csv ("$script:HistoryDir\" + $oneday + "-" + $snServerNameDrop.SelectedIt ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : OpenError: (:) [Import-Csv], FileNotFoundException
+ FullyQualifiedErrorId : FileOpenFailure,Microsoft.PowerShell.Commands.ImportCsvCommand

Import-Csv : Could not find file 'C:\mbsizehistory\-ALL Servers.csv'.
At K:\powershell\Exchange\MailBoxSizeGUI\mbsizereportv5.ps1:284 char:1
+ Import-Csv ("$script:HistoryDir\" + $sevenday + "-" + $snServerNameDrop.Selected ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : OpenError: (:) [Import-Csv], FileNotFoundException
+ FullyQualifiedErrorId : FileOpenFailure,Microsoft.PowerShell.Commands.ImportCsvCommand

Import-Csv : Could not find file 'C:\mbsizehistory\-ALL Servers.csv'.
At K:\powershell\Exchange\MailBoxSizeGUI\mbsizereportv5.ps1:287 char:1
+ Import-Csv ("$script:HistoryDir\" + $onemonth + "-" + $snServerNameDrop.Selected ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : OpenError: (:) [Import-Csv], FileNotFoundException
+ FullyQualifiedErrorId : FileOpenFailure,Microsoft.PowerShell.Commands.ImportCsvCommand

Import-Csv : Could not find file 'C:\mbsizehistory\-ALL Servers.csv'.
At K:\powershell\Exchange\MailBoxSizeGUI\mbsizereportv5.ps1:290 char:1
+ Import-Csv ("$script:HistoryDir\" + $oneyear + "-" + $snServerNameDrop.SelectedI ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : OpenError: (:) [Import-Csv], FileNotFoundException
+ FullyQualifiedErrorId : FileOpenFailure,Microsoft.PowerShell.Commands.ImportCsvCommand

Chaim said...

used this all the time in or 2007 exchange environment. do you have a version for exchange 2013?

Unknown said...

It looks as though the All Server bug is still there. I see the added routine for "if selected item equals All Servers use "-ALLSERVERS.csv" but it doesn't seem to be using it.

Mike Walker said...

I can see the added routine but it is still trying to load
-All Servers.csv

Mike said...

I can see where your All Servers fix is but it doesn't appear to be working.

Mike said...

Is there a way to have this run on a schedule daily?

Anonymous said...

Is there a way to schedule this task to run?