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