Script to Dump Exchange Mailbox Info to Spreadsheet (CSV)

I found another useful script I wrote last year on my hard drive this evening. It's pasted in below. This script will dump quite a bit of useful information about each mailbox on a particular server or set of servers to a CSV file which you can in turn import into Excel and create a spreadsheet from. I typically would import data into a SQL Server table using DTS (Data Transformation Services) if I needed to do alot of computation or data mining. Excel gets very slow when doing tasks that really require an index over a lot of data.

Note: The script uses WMI to get this information so Exchange 2003 is required. Only Exchange View Only level permissions are required in Active Directory, however you will likely need local Administrator privleges on each Exchange server. I don't have an Exchange 2003 server readily available to test and I was running as an Exchange Full Admin when I originally wrote this script.

There are a few properties which I did not export as I did not need them at the time. The specific meaning of each property available is available on MSDN. Adding these properties to the script should be self explanatory (especially given a very similar script at the bottom of the MSDN article).

There are two things you must edit in order for this script to function within your organization:

Line 28:
"Const TOTAL_SERVERS = 3"

You should put the total number of servers you plan to inventory in TOTAL_SERVERS.

Lines 36 - 37:
strComputer(0) = "xmb01"
strComputer(1) = "xmb02"
strComputer(2) = "xmb03"

You should create or remove additional lines for each server name in the strComputer() array. Note that the array starts with index 0. The script has been tested with twelve servers and sixty thousand mailboxes.

Here is the code for the script. Use this at your own risk, it's not my fault if anything happens.

 

'==========================================================================
' NAME   : Exchange Mailbox Stats Dumper
' AUTHOR : Brian Desmond, brian@briandesmond.com
' DATE   : 12/28/2005
' COMMENT: This script requires Exchange 2003. It will dump information
'			about each mailbox on the mailbox servers specified
'
'	Version		Date		Author			Note
'	-----------------------------------------------------------------
'	1.0			28Nov05		Brian Desmond	Initial Version
'	1.1			03Sep06		Brian Desmond	
'	1.2			13Dec08		Brian Desmond	Fixed array sizing bug,
'											Added error handling note
'											Added TODOs
'											Moved configurable items up
'==========================================================================
Option Explicit

' Note this script currently uses On Error Resume Next
' this isn't best practice - in reality this should be tightly 
' wrapped around the WMI connection logic in the loop rather
' than up here.
On Error Resume Next

' TODO: Configure this
' This is the total number of servers which you
' will specify for inventory
Const TOTAL_SERVERS = 3

Dim strComputer()
ReDim strComputer(TOTAL_SERVERS)

' TODO: Populate this array
' Enter each server name below as an entry in the array
' starting with zero
strComputer(0) = "xmb01"
strComputer(1) = "xmb02"
strComputer(2) = "xmb03"

'==========================================================================

Dim objWMIService
Dim colItems

Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")

Dim fil
Set fil = fso.CreateTextFile("mailboxes.txt")

Dim objItem
Dim line
Dim i

' Write a header row to the CSV
fil.WriteLine """Server"",""Storage Group"",""Mail Store"",""Mailbox GUID"",""Display Name"",""LegacyDN"",""Size"",""Item Count"",""Associated Content Count"",""Deleted Message Size"",""Date Absent"",""Storage Limit Level"""

For i = 0 To TOTAL_SERVERS - 1
	Set objWMIService = GetObject("winmgmts:" _
	    & "{impersonationLevel=impersonate}!\\" & strComputer(i) & _
	        "\ROOT\MicrosoftExchangeV2")
	
	Set colItems = objWMIService.ExecQuery _
	    ("Select * from Exchange_Mailbox")
	
	For Each objItem in colItems
		line = """" & objItem.ServerName & """"
		line = line & ","
		line = line & """" & objItem.StorageGroupName & """"
		line = line & ","
		line = line & """" & objItem.StoreName & """"
		line = line & ","
		line = line & """" & objItem.MailboxGUID & """"
		line = line & ","
		line = line & """" & objItem.MailboxDisplayName & """"    
		line = line & ","
		line = line & """" & objItem.LegacyDN & """"
		line = line & ","
		line = line & """" & objItem.Size & """"
		line = line & ","
		line = line & """" & objItem.TotalItems & """"
		line = line & ","
	    line = line & """" & objItem.AssocContentCount & """"
	    line = line & ","
	    line = line & """" & objItem.DeletedMessageSizeExtended & """"
	    line = line & ","
	    line = line & """" & objItem.DateDiscoveredAbsentInDS & """"
	    line = line & ","
	    line = line & """" & objItem.StorageLimitInfo & """"
	    
	    fil.WriteLine line 
	    'WScript.Echo line 
	Next
Next

fil.Close
Set fso = Nothing
Set objWMIService = Nothing

Posted Monday, September 04 2006 1:10 PM by Brian Desmond | 22 Comments
Tagged as: , ,

Comments, Trackbacks, & Pingbacks

#1 re: Script to Dump Exchange Mailbox Info to Spreadsheet (CSV)

Wednesday, September 06 2006 2:49 AM by Victor

Brian,

I tried out this script, looks quite nice.

Victor

#2 re: Script to Dump Exchange Mailbox Info to Spreadsheet (CSV)

Wednesday, November 14 2007 6:14 PM by Nolan

Brian,

i've been going through the nightmare that is export each IS to CSV and import into Excel. i've found this very useful

#3 re: Script to Dump Exchange Mailbox Info to Spreadsheet (CSV)

Wednesday, December 12 2007 7:35 AM by Kris Vrancken

I alwats get an error on Line 26 character 17 :

Expected Integer Constant.

Please mail me at kris.vranckenATgmail.com about a possible solution.

Thanks

#4 re: Script to Dump Exchange Mailbox Info to Spreadsheet (CSV)

Wednesday, January 09 2008 10:06 PM by tjtres

I am getting the same error on line 26. How can we fix it?

#5 re: Script to Dump Exchange Mailbox Info to Spreadsheet (CSV)

Wednesday, January 09 2008 10:14 PM by tjtres

I plugged in the actual integer instead of "TOTALSERVERS" and the script seems to run ok. Temporary solution since I am not yet a scripter :)

#6 re: Script to Dump Exchange Mailbox Info to Spreadsheet (CSV)

Wednesday, January 23 2008 10:49 AM by Kevin

This is a great script!  And fast!  Thank you Brian!

To fix the error on line 26 change it from:

Dim strComputer(TOTALSERVERS)  to:

ReDim strComputer(TOTALSERVERS)

Kevin

#7 re: Script to Dump Exchange Mailbox Info to Spreadsheet (CSV)

Wednesday, June 04 2008 8:15 AM by Donovan Colbert

Excellent script. Did exactly what I needed. Thanks for posting this!

#8 re: Script to Dump Exchange Mailbox Info to Spreadsheet (CSV)

Tuesday, July 01 2008 5:00 PM by Aaron Perrault

Great script, any way you could update it so it works with Exchange 2007?

app

#9 re: Script to Dump Exchange Mailbox Info to Spreadsheet (CSV)

Sunday, October 26 2008 1:58 AM by Nazir

Please send me the link for this script.

Thanks.

#10 re: Script to Dump Exchange Mailbox Info to Spreadsheet (CSV)

Thursday, October 30 2008 10:40 AM by Lakshmi Narayana

Hi,

Cant find the link to script. Pls share the same

thanks

Lakshmi Narayana

#11 re: Script to Dump Exchange Mailbox Info to Spreadsheet (CSV)

Saturday, November 15 2008 9:41 PM by brian desmond

I added the missing link. I will work on fixing the bug identified in the comments soon.

#12 re: Script to Dump Exchange Mailbox Info to Spreadsheet (CSV)

Saturday, December 13 2008 11:17 PM by Brian Desmond

I fixed the array size bug in the script. Let me know of any new issues.

#13 re: Script to Dump Exchange Mailbox Info to Spreadsheet (CSV)

Friday, December 19 2008 5:23 AM by David Leif

If you're not into scripting at all I can recommend using a tool called <a href="http://www.scriptlogic.com/products/message-stats">http://www.questmessagestats.com</a> that is a part of scriptlogic's <a href="http://www.scriptlogic.com/Solutions/exchange-management-tools.asp">exchange management soluton</a>.

This tool provides a wide range of exchange related reports including, for example, mailbox configuration, usage and email quotas.

#14 re: Script to Dump Exchange Mailbox Info to Spreadsheet (CSV)

Tuesday, January 13 2009 1:43 PM by Brian

Thanks so much for posting this! Worked like a charm

#15 re: Script to Dump Exchange Mailbox Info to Spreadsheet (CSV)

Wednesday, September 02 2009 6:01 PM by JP

Thank you for the posting. This works like a champ.

#16 re: Script to Dump Exchange Mailbox Info to Spreadsheet (CSV)

Tuesday, September 15 2009 6:07 AM by Kris Vrancken

I am looking to add variables to the script (proxy addresses,...) but not sure where to find the exact names as these are not the ones from ADSIedit ?

#17 re: Script to Dump Exchange Mailbox Info to Spreadsheet (CSV)

Thursday, September 24 2009 10:38 PM by Don

Is there a way to break down and get a message count for each folder in the users mailbox. For example, sub folder of inbox and the total number of items in that folder?

BTW...this Script Rocks!

#18 re: Script to Dump Exchange Mailbox Info to Spreadsheet (CSV)

Friday, November 20 2009 11:05 AM by Simon

This script just saved my life!

Thanks!

Simon

#19 re: Script to Dump Exchange Mailbox Info to Spreadsheet (CSV)

Tuesday, December 22 2009 10:23 AM by Rad Gears

Great script, was really looking for something that had mailbox creation date. As this company continues to grow we are running into license not being purchased for the mailbox created. Would be nice to be able to look at the date created to estimate growth also..

#20 re: Script to Dump Exchange Mailbox Info to Spreadsheet (CSV)

Friday, February 05 2010 9:43 AM by Raju

Great script, it saved my time, along with this can i export Last Logon time, if so pls provide me the line

#21 re: Script to Dump Exchange Mailbox Info to Spreadsheet (CSV)

Wednesday, February 17 2010 4:06 PM by Justin Cooksey

Great script, I have added some code to email me the resulting CSV file. My problem is, that the script doesn't get any data if I am not logged in to the server.

Is there a way around this? I would like to run this once a month and have it email me the details. I don't want to have to log in and run it manually.

#22 re: Script to Dump Exchange Mailbox Info to Spreadsheet (CSV)

Wednesday, July 28 2010 10:18 AM by James

Brilliant thanks for making us look good :-)

Leave a comment