Create detailed mailbox reports from powershell?
Has anyone already explained how to create a detailed mailbox report via powershell? Essentially, I'd like to be able to list the number of items in each user's inbox, as well as the size. I create monthly reports for the entire mailbox, but haven't found a way to do so for just the inbox (or another folder). I did a quick search, butdidn't find anything. I'm assuming if it's possible, it's in a forum. If someoen could kindly point me to it, I'd be more appreciative. Thanks,Tim
June 12th, 2009 3:43pm

you can also create reports in different formats like html ,txt and csv. Get-MailboxStatistics | where {$_.ObjectClass -eq "Mailbox"} | Sort-Object TotalItemSize -Descending | ConvertTo-Html @{label="User";expression={$_.DisplayName}},@{label="Size" ;expression={$_.TotalItemSize.Value.ToKB()}},@{label="Items";expression={$_.ItemCount}},@{label="Storage Limit";expression={$_.StorageLimitStatus}} | out-file C:\MailboxReport.html or Get-MailboxStatistics | where {$_.ObjectClass -eq "Mailbox"} | Sort-Object TotalItemSize -Descending | ft @{label="User";expression={$_.DisplayName}},@{label="Size";expression={$_.TotalItemSize.Value.ToKB()}},@{label="Items";expression={$_.ItemCount}},@{label="Storage Limit";expression={$_.StorageLimitStatus}} -auto | out-file C:\MailboxReport.txt Regards, Laeeq Qazi
Free Windows Admin Tool Kit Click here and download it now
June 13th, 2009 7:35am

I wrote the following to put this type of information into a sql database that i could query for historical information: function New-SQLconnection { Param ([string]$server,[string]$database,[string]$connectionName = $scriptName) if (test-path variable:\conn) { $conn.close() } else { $conn = new-object ('System.Data.SqlClient.SqlConnection') } $connString = "Server=$server;Integrated Security=SSPI;Database=$database;Application Name=$connectionName" $conn.ConnectionString = $connString $conn.StatisticsEnabled = $true $conn.Open() $conn } function NonQuery-SQL { Param ($query, $conn, $CommandTimeout = 30) $sqlCmd = New-Object System.Data.SqlClient.SqlCommand $sqlCmd.CommandTimeout = $CommandTimeout $sqlCmd.CommandText = $query $sqlCmd.Connection = $conn $RowsAffected = $sqlCmd.ExecuteNonQuery() if ($? -eq $false) { $RowsAffected = -2 } $RowsAffected } function Remove-SQLconnection { Param ($connection) $connection.close() $connection = $null } ##Connect to the SQL database $scriptName = $myInvocation.MyCommand.Name $server = "SQLSERVER\INSTANCE" $database = "DatabaseName" $date = Get-Date -Format "MMM dd yyyy h:mmtt" $myConn = New-SQLconnection $server $database if($x.state -eq "closed") { "Failed to establish a connection";EXIT } $servers = Get-MailboxServer foreach($server in $servers) { $mailboxes = Get-Mailbox -server $server -ResultSize unlimited foreach ($m in $mailboxes) { $stats = Get-MailboxStatistics $m | Select ItemCount,DeletedItemCount,TotalItemSize,TotalDeletedItemSize $alias = $m.Alias $items = $stats.ItemCount $deletedItems = $stats.DeletedItemCount $size = $stats.TotalItemSize.Value.ToMB() $deletedSize = $stats.TotalDeletedItemSize.Value.ToMB() $query = "INSERT INTO wcsrmail01 (Timestamp,Alias,ItemCount,TotalItemSize,DeletedItemCount,TotalDeletedItemSize) values ('" + $date + "','" + $alias + "','" + $items + "','" + $size + "','" + $deletedItems + "','" + $deletedSize + "')" $data = NonQuery-SQL $query $myConn if($data -eq -2) { Write-Host "Creation of new record failed for MailTo" } } } ## Close the SQL connection Remove-SQLconnection $myConn
June 15th, 2009 4:15pm

All great replies. Thanks so much! I think Karl's gets me the info I need right now, but I'll be sure to investigate all the other options, for more detialed reporting. Thanks again.
Free Windows Admin Tool Kit Click here and download it now
June 15th, 2009 4:21pm

Karl, That command worked well in my lab, which has a much shorter domain name and simple AD structure. In my prod environment, the domain name and OUs take up most of the available column width. Is there a way to increase it to fit the entire output? I've been trolling around and have seen some examples, but I'm just a novice with ps, so I haven't been able to get it to work.Any other experts know? I imagine this is a real no-brainer. <sigh>-Tim
June 17th, 2009 1:24pm

Tim;Do you mean the firct column (Identity) shows the OU structure?Karl
Free Windows Admin Tool Kit Click here and download it now
June 17th, 2009 1:29pm

Yes, that's the one. Doesn't display the structure, per se, but where the object is. Here's a sample:Identity ItemsInFolder FolderSize-------- -----------------------DOMAIN.COM/Users/ExAdm... 3 1728BIdeally, I'd like the first column to display the alias or DisplayName.
June 17th, 2009 1:57pm

Ahhhh....The Get-Mailbox cmdlet passes that information to the Get-MailboxFolderStatistics cmdlet.So, we need to explicitly pass the alias;Try this: Get-Mailbox |Select-Object alias |ForEach-Object{Get-MailboxFolderStatistics -Identity $_.alias -FolderScope inbox|Select-Object Identity,ItemsInFolder,FolderSize} Karl
Free Windows Admin Tool Kit Click here and download it now
June 17th, 2009 3:32pm

That helps so much. I was able to produce the report the boss(es) wanted. Thanks so much. Just curious if there was a way to specify the width of output columns. I can see someone asking me what the sizes and item counts are for all folders in user mailboxes. Since we're only looking at inboxes right now, the output below works. If I need to provide info on the subfolders of the inbox or another folder, I woudn't be able to do it. Is that possible? Identity ItemsInFolder FolderSize ------------------------------- user\Inbox 114 28405696B user\Inbox\Accounts 47 226392Buser\Inbox\New York ... 171 16079334Buser\Inbox\Boston... 0 0Buser\Inbox\Miami ...39 2823424Buser\Inbox\Chicago ... 5 21284Buser\Inbox\Houston ... 28 788848Buser\Inbox\LA ... 8 113168B
June 18th, 2009 2:22pm

Try this: $FolderStats = Get-Mailbox |Select-Object alias |ForEach-Object{Get-MailboxFolderStatistics -Identity $_.alias |Select-Object Identity,ItemsInFolder,FolderSize} $FolderStats | Export-Csv c:\folderstats.csv -NoTypeInformation Karl
Free Windows Admin Tool Kit Click here and download it now
June 18th, 2009 2:54pm

For width problem try FT (Format-Table) like thisGet-Mailbox |Get-MailboxFolderStatistics -FolderScope inbox | FT Identity,ItemsInFolder,FolderSize -atuoSizeRegards,Laeeq Qazi
June 19th, 2009 2:57am

Laeeq, Where would you insert the autosize switch into the following command? $FolderStats = Get-Mailbox |Select-Object alias |ForEach-Object{Get-MailboxFolderStatistics -Identity $_.alias |Select-Object Identity,ItemsInFolder,FolderSize} $FolderStats | Export-Csv F:\folderstats.csv -NoTypeInformation(The above command works spectacularly, btw. Many thanks to Karl.)I tried it after FolderSize, but received the error below. Sorry I'm so terrible at this. Select-Object : A parameter cannot be found that matches parameter name 'AutoSize'.-Tim
Free Windows Admin Tool Kit Click here and download it now
June 23rd, 2009 10:57am

$FolderStats = Get-Mailbox |Select-Object alias |ForEach-Object{Get-MailboxFolderStatistics -Identity $_.alias |Select-Object Identity,ItemsInFolder,FolderSize} $FolderStats | Export-Csv F:\folderstats.csv -NoTypeInformation$FolderStats | Format-Table -AutoSizeSince you have $FolderStats as a variable you can just modify that code at the end to export to different outputs.
June 23rd, 2009 11:04am

That's awesome. Thanks to everyone for all the help.
Free Windows Admin Tool Kit Click here and download it now
June 23rd, 2009 11:16am

I need a similar commandlet to check Items older that 30days in users mailboxes
September 3rd, 2009 4:00am

Hey Karl,Please can you assist i need command to get how much of users with ITems older that 30days in their mailboxes?This willdetermine how much of storage we will save if if add thme to our 30 day archive policy...Let me know
Free Windows Admin Tool Kit Click here and download it now
September 3rd, 2009 4:06am

Sheldon;You should start a new question.I am researching this though.I don't think it will be trivial, but if you post a new question more people will look at it.Karl
September 3rd, 2009 10:07am

Sheldon;Thinking about this more, you may want to cruise over to the development forum, and ask there:
Free Windows Admin Tool Kit Click here and download it now
September 3rd, 2009 10:45am

Hi We are using exchange 2010 SP1. I need to prepare every month full report. I found your script very helpful. I just want to know whether we can include OU information as well. Because I need add which user belong to which OU. So I am manually exporting recipients list from EMS and past into excel sheet then I am comparing both excel sheet and merging. This is hectic job. Is there a script to get a report including OU? If so please let me know. Regards, Sundar
May 7th, 2012 8:16am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics