Saturday, May 09, 2009

Mailbox Size Summary reporting Gui for Exchange 2007

Picking up again on the topic i touched on in January about creating different summary reports of how size and resources are consummed within an Exchange enviroment he's a script that takes it in another direction (and dont we need one of those). A number of people have asked questions about how they can report on mailbox usage not just based on particular users but usage by particular OU or other User property eg Department,Office etc. An here's the script to do it

How it works


This script first combines 3 different Exchange cmdlets to build a displayable and redisplayble dataset that is stored in hashtable. The Get-User cmdlet is used to get all the user information and a number of properties such as the ExchangeGUID,MailboxSize and what OU a users is in is addded to the standard Get-User object and then the object is stored in a hashtable indexed by the Active Directory GUI. Because you can't link directly Get-User and Get-Mailboxstatistics directly without using Pipeline composition which will do a search for each user making this script a little to slow for enviroments with a large number of users. Get-Mailbox is used to create a hashtable that allows the linking of the information that is returned by the Get-User cmdlet and what is returned by the Get-Mailboxstatistics.

To build display elements all the properties from Get-User are added to the Dropdown for Groupby type. When this is selected it fires a function that causes the data stored in the results hashtable to be regrouped using the selected value which then rebuilds the data in the datatable which in tern refreshs what you see in the datagrid. To spice things up a bit I've added some extra functionality like being able to get the actally mailboxes that are being included in the grouping in a seperate datatable. Also the ability to export both grids to a csv file. The final things to give this script some visual spark by adding a few Google charts to add a visual dimension to the summariesed data

The result is a script that produces a lot of quite interesting and at times slightly amsuing results. Eg being able to see grouped by first name who uses the most mailbox resources is kind of funny. You could go take this little further use the persons birthday to figure their star sign and then show mailbox size usage by signs of the zodiac.

(If your looking for OU its added as the last item in the Drop down list)

I've put a download of the script here the script itself looks like.

[System.Reflection.Assembly]::LoadWithPartialName("System.Drawing")
[System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms")
$form = new-object System.Windows.Forms.form

$usrinfo = @{ }
$ctab = @{ }
$agDepartment = @{ }
$agOffice = @{ }
$htAg = @{ }
$ExcomCollection = @()
function Aggresults{
$htAg.clear()
if ($mtTypeDrop.SelectedItem -eq $null){
$agMethod = "LastName"
}
else{
$agMethod = $mtTypeDrop.SelectedItem.ToString()
}
foreach ($row in $usrinfo.Values){
if ($row.$agMethod.ToString() -eq ""){
if ($htAg.containskey("Not Set")){
$htAg["Not Set"].Number = $htAg["Not Set"].Number + 1
$htAg["Not Set"].Size = $htAg["Not Set"].Size + [INT]$row.MailboxSize.ToString()
}
else{
$mbcnt = "" | select Name,Number,Size
$mbcnt.Name = "Not Set"
$mbcnt.Number = 1
$mbcnt.Size = [INT]$row.MailboxSize.ToString()
$htAg.Add("Not Set",$mbcnt)

}
}
else{
if ($htAg.containskey($row.$agMethod.ToString())){
$htAg[$row.$agMethod.ToString()].Number = $htAg[$row.$agMethod.ToString()].Number + 1
$htAg[$row.$agMethod.ToString()].Size = $htAg[$row.$agMethod.ToString()].Size + [INT]$row.MailboxSize.ToString()
}
else{
$row.$agMethod.ToString()
$mbcnt = "" | select Name,Number,Size
$mbcnt.Name = $row.$agMethod.ToString()
$mbcnt.Number = 1
$mbcnt.Size = [INT]$row.MailboxSize.ToString()
$htAg.Add($row.$agMethod.ToString(),$mbcnt)
}
}
}
$agtable.Clear()
$valueBlock = ""
$TitleBlock = ""
$TitleBlock1 = ""
foreach ($row in $htAg.Values){
$agtable.rows.add($row.Name,$row.Number,$row.Size)
if ($valueBlock -eq ""){$valueBlock = $row.Size.ToString()
$lval = [INT]$row.Size
}
else {
$valueBlock = $valueBlock + "," + $row.Size.ToString()
if ($lval -lt [INT]$row.Size){$lval = [INT]$row.Size}
}
if ($TitleBlock -eq ""){$TitleBlock = $row.Name.ToString().Replace("&","-")
$TitleBlock1 = $row.Name.ToString().Replace("&","-")
}
else {$TitleBlock = $TitleBlock + "|" + $row.Name.ToString().Replace("&","-")
$TitleBlock1 = $row.Name.ToString().Replace("&","-") + "|" + $TitleBlock1
}
}
$csString = "http://chart.apis.google.com/chart?cht=p3&chs=430x160&chds=0," + $lval + "&chd=t:" + $valueBlock + "&chl=" + $TitleBlock + "&chco=0000ff,00ff00,ff0000,FFFFFF,000000"
$pbox.ImageLocation = $csString
$csString1 = "http://chart.apis.google.com/chart?cht=bhs&chs=530x300&chd=t:" + $valueBlock + "&chds=0," + ($lval+20) + "&chxt=x,y&chbh=a&chxr=" + "&chxr=0,0," + ($lval+20) + "&chxl=1:|" + $TitleBlock1 + "&chco=0000ff,00ff00,ff0000,FFFFFF,000000"
$pbox1.ImageLocation = $csString1
$dgDataGrid.DataSource = $agtable
}

function getMailboxSizes(){

$mbcombCollection = @()
$usrinfo.clear()
$ctab.clear()
$agDepartment.clear()
$agOffice.clear()
$ExcomCollection = @()
$agtable.clear()

$serverName = $snServerNameDrop.SelectedItem.ToString()
if ($snServerNameDrop.SelectedItem.ToString() -eq "ALL Servers"){
$mailboxes = get-mailbox -ResultSize Unlimited
}
else{
$mailboxes = get-mailbox -server $snServerNameDrop.SelectedItem.ToString() -ResultSize Unlimited
}
$mailboxes | foreach-object{
$ctab.add($_.Guid.ToString(),$_.ExchangeGuid)
}
"Finished Get Mailbox"
Get-User -recipienttype UserMailbox -ResultSize Unlimited | foreach-object{
if ($ctab.ContainsKey($_.Guid.ToString())){
$usrobj = $_ | select *,MailboxSize,ExchangeGUID,OU
$oustring = ""
$idarry = $_.Identity.ToString().Split("/")
for($i=1;$i-lt ($idarry.length-1);$i++){
$oustring = $oustring + "\" + $idarry[$i]
}
$usrobj.OU = $oustring
$usrobj.ExchangeGUID = $ctab[$_.Guid.ToString()]
$usrinfo.add($ctab[$_.Guid.ToString()].ToString(),$usrobj)
}

}
$mbServers = get-mailboxserver
if ($snServerNameDrop.SelectedItem.ToString() -eq "ALL Servers"){
$mbServers | foreach-object{

$mscombCollection += get-mailboxstatistics -server $_.Name | Where {$_.DisconnectDate -eq $null}

}
}
else{
$mscombCollection += get-mailboxstatistics -server $snServerNameDrop.SelectedItem.ToString() | Where {$_.DisconnectDate -eq $null}
}

$mscombCollection | ForEach-Object{
$usrobj = $usrinfo[$_.MailboxGuid.ToString()]
if ($usrinfo.ContainsKey($_.MailboxGuid.ToString())){
if ($_.TotalItemSize.Value.ToMB() -ne $null){
$usrobj.MailboxSize = $_.TotalItemSize.Value.ToMB()
}
else
{
$usrobj.MailboxSize = 0
}
}

}
Aggresults
}

function ShowMailboxes(){
if ($mtTypeDrop.SelectedItem -eq $null){
$agMethod = "LastName"
}
else{
$agMethod = $mtTypeDrop.SelectedItem.ToString()
}
$mbtable = New-Object System.Data.DataTable
$mbtable.TableName = "Mailboxes"
$mbtable.Columns.Add("Display Name")
$mbtable.Columns.Add($agMethod)
$mbtable.Columns.Add("Mailbox Size(MB)",[int64])
foreach ($row in $usrinfo.Values){
if($row.$agMethod -eq $agtable.DefaultView[$dgDataGrid.CurrentCell.RowIndex][0]){
$mbtable.rows.add($row.DisplayName,$row.$agMethod,$row.MailboxSize)
}
else{
if($row.$agMethod -eq ""){
if("Not Set" -eq $agtable.DefaultView[$dgDataGrid.CurrentCell.RowIndex][0]){
$mbtable.rows.add($row.DisplayName,$row.$agMethod,$row.MailboxSize)
}
}
}
}
$dgDataGrid1.DataSource = $mbtable
}

function ExportGrid1csv{

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

function ExportGrid2csv{

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

$agtable = New-Object System.Data.DataTable
$agtable.TableName = "Mailbox Sizes"
$agtable.Columns.Add("Name")
$agtable.Columns.Add("# Mailboxes",[int64])
$agtable.Columns.Add("Mailbox Size(MB)",[int64])




# 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.Items.Add("ALL Servers")
$snServerNameDrop.Add_SelectedValueChanged({getMailboxSizes})

$form.Controls.Add($snServerNameDrop)

# Add Agregate 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(70,20)
$mtTypeDroplableBox.Text = "Group By"
$form.Controls.Add($mtTypeDroplableBox)

# Add Mailbox Type Drop Down
$mtTypeDrop = new-object System.Windows.Forms.ComboBox
$mtTypeDrop.Location = new-object System.Drawing.Size(270,20)
$mtTypeDrop.Size = new-object System.Drawing.Size(135,30)
$properties = get-user -resultsize 1 | get-member -membertype Property
foreach ($prop in $properties){
$mtTypeDrop.Items.Add($prop.Name)
}
$mtTypeDrop.Items.Add("OU")
$mtTypeDrop.Add_SelectedValueChanged({Aggresults})
$form.Controls.Add($mtTypeDrop)

# Show Mailboxs Button

$shMailboxes = new-object System.Windows.Forms.Button
$shMailboxes.Location = new-object System.Drawing.Size(600,19)
$shMailboxes.Size = new-object System.Drawing.Size(120,23)
$shMailboxes.Text = "Show Mailboxes"
$shMailboxes.visible = $True
$shMailboxes.Add_Click({ShowMailboxes})
$form.Controls.Add($shMailboxes)

# Add Export Grid Button

$exButton1 = new-object System.Windows.Forms.Button
$exButton1.Location = new-object System.Drawing.Size(430,19)
$exButton1.Size = new-object System.Drawing.Size(90,23)
$exButton1.Text = "Export Grid"
$exButton1.Add_Click({ExportGrid1csv})
$form.Controls.Add($exButton1)

# Add Export Grid Button 2

$exButton2 = new-object System.Windows.Forms.Button
$exButton2.Location = new-object System.Drawing.Size(750,19)
$exButton2.Size = new-object System.Drawing.Size(125,23)
$exButton2.Text = "Export Grid"
$exButton2.Add_Click({ExportGrid2csv})
$form.Controls.Add($exButton2)

#add Picture box

$pbox = new-object System.Windows.Forms.PictureBox
$pbox.Location = new-object System.Drawing.Size(550,360)
$pbox.Size = new-object System.Drawing.Size(500,220)
$form.Controls.Add($pbox)

$pbox1 = new-object System.Windows.Forms.PictureBox
$pbox1.Location = new-object System.Drawing.Size(10,360)
$pbox1.Size = new-object System.Drawing.Size(550,370)
$form.Controls.Add($pbox1)

# 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(530,300)
$dgDataGrid.AutoSizeRowsMode = "AllHeaders"
$form.Controls.Add($dgDataGrid)

$dgDataGrid1 = new-object System.windows.forms.DataGridView
$dgDataGrid1.Location = new-object System.Drawing.Size(550,50)
$dgDataGrid1.size = new-object System.Drawing.Size(450,300)
$dgDataGrid1.AutoSizeRowsMode = "AllHeaders"
$form.Controls.Add($dgDataGrid1)

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

11 comments:

Anonymous said...

It is showing people in the main OU but does not show people in other OU's that use our exchange servers.

Glen said...

Its sorts not limits the result by OU so im not quite sure what your getting at ?

Cheers
Glen

Anonymous said...

We have a resource domain for some users who use forest trusts to have access to their linked mailboxes. Almost half of the other users access their mailboxes normally. When I run this, I get:

"WARNING: There are more results than are currently displayed. To view all of
the results, increase the maximum number of recipients to display."

I tried adding the $AdminSessionADSettings.ViewEntireForest=$true to the script, but that did not work. All the -ResultSize parameters seem to use "Unlimited", so I'm not sure what is going on. There are about 102,000 mail objects, with perhaps 90 percent of that user mailboxes.

Thanks for any insight.

John said...

Glen,

I know it has been a while since you posted this but I am wondering if it is possible to view linked mailboxes with this script? This may be what the previous poster was eluding to.

Anonymous said...

thanks for this great script!!!!!

greets from black forest

Anonymous said...

Glen,
I realize I am late to the party, but wanted to share a curious result while assessing an E2K7 environment I am now responsible for.

I used your sumby and mbsizereportv5 scripts. When querying for mailbox count with each tool I found that the result sets where very different.

With sumby I selected server and grouped by displayname and exported to .csv. With mbsizereportv5 I selected server and exported .csv I have accounted for disconnected mailboxes, but it is not enough to explain the variance.

I used a simple (get-mailbox -resultsize unlimited).count and found that the result was consistent with sumby.

I realize you likely have little time of your own, but if you have an idea of where I should start to look I would appreciate it. In any case I will let you know what I find.

Thanks!

Anonymous said...

You cannot call a method on a null-valued expression.
At C:\Temp\sumby.ps1:37 char:115
+ $htAg[$row.$agMethod.ToString()].Size = $htAg[$row.$agMethod.
ToString()].Size + [INT]$row.MailboxSize.ToString( <<<< )

Glen said...

Sounds like you may not have configured the variable for the servername and such in the script ?

Cheers
Glen

Brian Kerhin said...

Amazing! Glen, you the MAN!

Tested on Exchange 2010

abhi said...

Hello Glen,

Really a good artcile :-).
I wanted to do build some similar kind of report but that using VBScript and LDAP/AD.
Do you have any idea how can I do this?
In simple words, I want to get the same information as Get-MailBoxStatistics does, but want to used LDAP/AD and VBScript.
If you can tell me the object name, properties or any reference book where I can get those details.
It will be really great if you can help me.

Regards,
Pim

p.s. : I want specifically for MS Exchange Server 2010

Anonymous said...

A script from heaven... Great job!