Tuesday, October 12, 2004

Listing the file sizes of all Exchange Stores on all Exchange Servers in a Domain

One of the things that I've found missing from the Exchange Management GUI's has been the ability to get the physical size of the database files (especially when you have multiple servers to manage). None of the Exchange API's accessible with script offer this type of information either.

I've found two methods you can use to grab this information via a script, the first is when the information store performs a backup via the Exchange Backup API one of the events it logs is event code 220 which states the size of each file before it is backed up. The other method is to connect to the file using the FSO object in VBS and grab the size of the file directly.

I've expanded this second method into a script that first queries for all the msExchPrivateMDB and msExchPulicMDB objects in Active Directory using ADSI. Then using the msExchEDBFile, msExchSLVFile and msExchOwningServer AD properties constructs the URL to be able to connect to each file remotely and report on the size of the EDB and STM files for that mail or public folder store. I've also used the homeMDBBL property which contains a list of all the mailenabled accounts within a mailstore to get the number of mailboxes for each mailstore. The script itself is designed to be run from the command line using cscript and will produce a console output for every Exchange server store for the domain its runs in.

The script itself looks like this I've posted a download-able copy up here

set conn = createobject("ADODB.Connection")
set com = createobject("ADODB.Command")
Set iAdRootDSE = GetObject("LDAP://RootDSE")
strNameingContext = iAdRootDSE.Get("configurationNamingContext")
Conn.Provider = "ADsDSOObject"
Conn.Open "ADs Provider"
mbQuery = ";(objectCategory=msExchPrivateMDB);name,distinguishedName;subtree"
pfQuery = ";(objectCategory=msExchPublicMDB);name,distinguishedName;subtree"
Com.ActiveConnection = Conn
Com.CommandText = mbQuery
Set Rs = Com.Execute
Wscript.echo "Mailbox Stores"
Wscript.echo
While Not Rs.EOF
objmailstorename = "LDAP://" & Rs.Fields("distinguishedName")
set objmailstore = getObject(objmailstorename)
objmailstore.GetInfoEx Array("homeMDBBL"), 0
varReports = objmailstore.GetEx("homeMDBBL")
Set fso = CreateObject("Scripting.FileSystemObject")
edbfilespec = "\\" & mid(objmailstore.msExchOwningServer,4,instr(objmailstore.msExchOwningServer,",")-4) & "\" & left(objmailstore.msExchEDBFile,1) & "$" & mid(objmailstore.msExchEDBFile,3,len(objmailstore.msExchEDBFile)-2)
stmfilespec = "\\" & mid(objmailstore.msExchOwningServer,4,instr(objmailstore.msExchOwningServer,",")-4) & "\" & left(objmailstore.msExchSLVFile,1) & "$" & mid(objmailstore.msExchSLVFile,3,len(objmailstore.msExchSLVFile)-2)
Set efile = fso.GetFile(edbfilespec)
set sfile = fso.GetFile(stmfilespec)
edbsize = formatnumber(efile.size/1073741824,2,0,0,0)
stmsize = formatnumber(sfile.size/1073741824,2,0,0,0)
Wscript.echo Rs.Fields("name") & "# Mailboxes: " & ubound(varReports)+1 & " EDBSize(GB): " & edbsize & " STMSize(GB): " & stmsize
Rs.MoveNext

Wend
Wscript.echo
Wscript.echo "Public Folder Stores"
Wscript.echo
Com.CommandText = pfQuery
Set Rs1 = Com.Execute
While Not Rs1.EOF
objmailstorename = "LDAP://" & Rs1.Fields("distinguishedName")
set objmailstore = getObject(objmailstorename)
Set fso = CreateObject("Scripting.FileSystemObject")
edbfilespec = "\\" & mid(objmailstore.msExchOwningServer,4,instr(objmailstore.msExchOwningServer,",")-4) & "\" & left(objmailstore.msExchEDBFile,1) & "$" & mid(objmailstore.msExchEDBFile,3,len(objmailstore.msExchEDBFile)-2)
stmfilespec = "\\" & mid(objmailstore.msExchOwningServer,4,instr(objmailstore.msExchOwningServer,",")-4) & "\" & left(objmailstore.msExchSLVFile,1) & "$" & mid(objmailstore.msExchSLVFile,3,len(objmailstore.msExchSLVFile)-2)
Set efile = fso.GetFile(edbfilespec)
set sfile = fso.GetFile(stmfilespec)
edbsize = formatnumber(efile.size/1073741824,2,0,0,0)
stmsize = formatnumber(sfile.size/1073741824,2,0,0,0)
Wscript.echo Rs1.Fields("name") & " EDBSize(GB): " & edbsize & " STMSize(GB): " & stmsize
Rs1.MoveNext

Wend
Rs.Close
Rs1.Close
Conn.Close
Set Rs = Nothing
set Rs1 = Nothing
Set Com = Nothing
Set Conn = Nothing

21 comments:

Anonymous said...

Great Script!!!

Is it possible to add the servername before the Mailbox Stores?

Thanks!

Anonymous said...

c:\>cscript lexchstores.vbs
Microsoft (R) Windows Script Host Version 5.6 Copyright(C) Microsoft Corporation 1996-2001. All rights reserved.

Mailbox Stores

C:\lexchstores.vbs(20, 3) Microsoft VBScript runtime error: Inval id procedure call or argument: 'mid'

FYI
Getting this error.

Glen said...

I've only really tested this with the names of the stores are set to default (which allways includes the server name). If you did want to put the server name then something like mid(objmailstore.msExchOwningServer,4,instr(objmailstore.msExchOwningServer,",")-4)

With the mid error There are a few things you could try to track down what is happening. The first thing would be to place a couple of echo statement before the mid
statement runs so you can see the strings its trying
to work with. eg put

wscript.echo objmailstore.msExchOwningServer
wscript.echo objmailstore.msExchEDBFile

before the

edbfilespec = "\\" &
mid(objmailstore.msExchOwningServer,4,instr(objmailstore.msExchOwningServer,",")-4)
& "\" & left(objmailstore.msExchEDBFile,1) & "$" &
mid(objmailstore.msExchEDBFile,3,len(objmailstore.msExchEDBFile)-2)

You should get the DN name of the server for
msExchOwningServer and the file path for msExchEDBFile
. A len could fail like that if those properties where
missing. You could also place a wscript.echo
Rs.Fields("distinguishedName") to see which mailstore
it is trying to work with. Maybe its finding an old
mailstore object for a Exchange server that been
removed from your network . Could also be having problems with the way you names being used

The other thing you could try is to put a "on error resume next" at the start of the script to see if it will resume after the error.

Anonymous said...

Excellent, Thank you for your response!

One more thing if the Stores has more then 1000 Mailboxes, it only shows 1000.
Is there any way of setting that property value?

I tested with Com.Properties("Page Size") = 5000
but it doesn't work maybe wrong place...

Thanks again!

Glen said...

That's actually a problem with getinfoex when you need to retrieve more then a 1000 values you need to do it ranges. Theres a explanation of this here http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adsi/adsi/using_iads__getinfoex_for_range_retrieval.asp

I've changed the sample i posted in this blog to use this ranged query and posted it here http://bluetack.aspxconnection.com/exdevblog/lexchstores1.zip. I haven't got any really large servers to try it on so consider it untested

Cheers
Glen

Anonymous said...

First, thank you. This looks like a nice piece of code.

I am also getting the MID error and the echos helped as I think I know the issue. We are in an Exchange 5.5 mixed migration right now and the first server chosen is an Exchange 5.5 server.

My objective is to list the count of the Mailbox stores on Exchange 2003 only. Is there a simple way to do this with the script?

Anonymous said...

:-) Ok, NOW I really like your script.

I added the On Error Resume Next to the start and it of course ignores the 5.5 servers and dumps the Exchange 2003.

So, where do I send the popcorn?

Thanks

Anonymous said...

Feedback about the 1000 limit issue, it takes the first value and moves on, running your big store script, see below:

C:\>cscript lexchstores1.vbs
Microsoft (R) Windows Script Host Version 5.6
Copyright (C) Microsoft Corporation 1996-2001. All rights reserved.

Mailbox Stores

Private Information Store (XXXXXXX)# Mailboxes: 415 EDBSize(GB): STMSize(GB):
Mailbox Store (XXXXXXXXXXXXXX)# Mailboxes: 415 EDBSize(GB): 2.31 STMSize(GB): 13.38
Private Information Store (XXXXXXXXXX)# Mailboxes: 415 EDBSize(GB): 2.31 STMSize(GB): 13.38
Private Information Store (XXXXXXXXXX)# Mailboxes: 415 EDBSize(GB): 2.31 STMSize(GB): 13.38
Private Information Store (XXXXXXXXXX)# Mailboxes: 415 EDBSize(GB): 2.31 STMSize(GB): 13.38
Mailbox Store SG1 IS1 XXX01# Mailboxes: 415 EDBSize(GB): .06 STMSize(GB): .01
Mailbox Store SG1 IS3 XXX01# Mailboxes: 415 EDBSize(GB): .00 STMSize(GB): .00
Mailbox Store SG2 IS1 XXX01# Mailboxes: 415 EDBSize(GB): 1.31 STMSize(GB): .15
Mailbox Store SG2 IS2 XXX01# Mailboxes: 415 EDBSize(GB): 2.35 STMSize(GB): .47
Mailbox Store SG2 IS3 XXX01# Mailboxes: 415 EDBSize(GB): 2.46 STMSize(GB): .53
.....> etc.

Any suggestions?
Thanks for your time...

Glen said...

Okay i've had a look and i think i found the problem I wasn't reseting the quit varible which would have caused the problem you where experiencing with multiple stores. I've updated the zip file on http://bluetack.aspxconnection.com/exdevblog/lexchstores1.zip

Anonymous said...

Hi Glen,

I have a quick question re: you script "Listing the file sizes of all Exchange Stores on all Exchange Servers in a Domain (10/12/04)." In my Exchange org the exchange stores are not shared and do not have default admin shares. So the attempt to get \\servername\G$\Program Files\... fails. Do you know of any other way to get to or address the actual edb file? Any help would be appreciated.

Thanks.
Dave

Glen said...

Instead of using FSO you can use WMI to list the size of the files on Exchange. The only catch with this is if you running windows 2000 then you need to apply the following hotfix to fix a bug where you get null values when the files get over 2 GB. http://support.microsoft.com/?id=820608

I've created a version of this script that uses WMI instead of FSO and it seems to work okay. I've posted it up on http://bluetack.aspxconnection.com/exdevblog/lexchstoreswmi.zip

Anonymous said...

Nice script. How can i get this to read from a txt file (and report echange store\limits\database path\ and especially the number of mailboxes per storage group....

Glen said...

Somebody has asked a simular question before have a look at http://msgdev.mvps.org/exdevblog/tempbin/lexchstoresv2snsg.zip which is a script that does something simular to what your asking it takes a command line parameter of the server you want to run it against. What it doesn't do is stroage limits you can do something like this with CDOEXM http://gsexdev.blogspot.com/2005/08/iterating-though-storage-groups-with.html

Anonymous said...

nice...is there a way to convert this so it can report data in XML or similar...and maybe add edb\ stm sizes as well

Glen said...

You would need to put your own code into format the result as XML and then output it to a file just use the XMLDOM object should work find for this. The script list edb/stm sizes not quite sure where you going with that that question

Anonymous said...

glenn..please assist...trying to work on this code to send to my HelpDesk each morning..Need size of storage group and number of mailboxes within each store... I would appreciate it if u can assist..




Dim strFilename
strFilename = "C:\computers.txt"

Sub DoObject(strName)

set theServer = CreateObject("CDOEXM.ExchangeServer")
Set theSG = CreateObject("CDOEXM.StorageGroup")
Set thePF = CreateObject("CDOEXM.PublicStoreDB")
Set theMB = CreateObject("CDOEXM.MailboxStoreDB")

theServer.DataSource.Open strName

'return mailstore name and DN for each mailstore in the storage group separated by |
'each set of name and DN is separated by ;
strMailStores=GetMailStores(objConfiguration.ADSpath,strName,strSG)

'split into an array
MailStoreArray=Split(strMailStores,";")

WScript.Echo strSG & " mailbox store(s)"

'loop through array
For m=0 To UBound(MailStoreArray)-1
'split each entry into it's name and DN components
MailStoreNameArray=Split(MailStoreArray(m),"|")
strMailStoreName=MailStoreNameArray(0)
strMailStoreDN=MailStoreNameArray(1)

WScript.Echo " " & strMailStoreName

'return EDB and STM filepath separated by ;
strMailFiles=GetMailStoreFiles(objConfiguration.ADSpath,strMailStoreDN)
FileArray=Split(strMailFiles,";")
strEDBPath=FileArray(0)
strSTMPath=FileArray(1)

'get filesize for EDB and STM file
iEDBSize=GetFileSize(strName,strEDBPath,strUsername,strPassword)
iSTMSize=GetFileSize(strName,strSTMPath,strUsername,strPassword)
'replace \\ back with \ in filepaths
strEDBPath=Replace(strEDBPath,"\\","\")
strSTMPath=Replace(strSTMPath,"\\","\")

'display file results
wscript.Echo " " & strEDBPath & " = " &_
FormatNumber((iEDBSize/1048756),2) & " MB"
wscript.Echo " " & strSTMPath & " = " &_
FormatNumber((iSTMSize/1048756),2) & " MB"
Next


End Sub

Function GetMailStores(strPath,strName,strSG)
On Error Resume Next
strQuery="Select distinguishedname,name from '" & strPath &_
"' WHERE objectclass='msExchPrivateMDB' "

strResults=""
Set cat=GetObject("GC:")
For Each obj In cat
Set GC=obj
Next

Set conn=CreateObject("ADODB.Connection")
Set cmd=CreateObject("ADODB.Command")
conn.Provider="ADSDSOObject"
conn.Open

Set cmd.ActiveConnection=conn
Set RS=conn.Execute(strQuery)
Do While Not RS.EOF

If InStr(rs.Fields("distinguishedname"),strSG) And _
InStr(rs.Fields("distinguishedname"),strName) Then
strResults=strResults & rs.Fields("name") & "|" &_
rs.Fields("distinguishedname") & ";"
End If
rs.movenext
Loop
rs.Close
conn.Close

GetMailStores=strResults

End Function


Function GetMailStoreFiles(strConfigPath,strDN)
On Error Resume Next

strQuery="Select distinguishedName,msExchEDBfile,msExchSLVFile,name FROM '" &_
strConfigPath & "' WHERE objectclass='msExchPrivateMDB' AND distinguishedname='" &_
strDN & "'"

Set cat=GetObject("GC:")
For Each obj In cat
Set GC=obj
Next

Set conn=CreateObject("ADODB.Connection")
Set cmd=CreateObject("ADODB.Command")
conn.Provider="ADSDSOObject"
conn.Open

Set cmd.ActiveConnection=conn
Set RS=conn.Execute(strQuery)

Do While Not RS.EOF
strResults=RS.fields("msExchEDBfile") & ";" & RS.fields("msExchSLVFile")
rs.movenext
Loop
rs.Close
conn.Close

GetMailStoreFiles=strResults

End Function

Function GetFileSize(strName,strFile,UserName,Password)
On Error Resume Next

strFile=Replace(strFile,"\","\\") 'filepath must use \\ instead of \

Set SWBemlocator = CreateObject("WbemScripting.SWbemLocator")
SWBemlocator.Security_.AuthenticationLevel=WbemAuthenticationLevelPktPrivacy
Set objWMIService = SWBemlocator.ConnectServer(strName,"\root\cimV2",UserName,Password)
If Err.Number<>0 Then
strData="Error connecting to " & strName & ". Error #" & Hex(err.Number) &_
" " & Err.Description
GetFileSize=strData
Err.Clear
Else
strQuery="Select name,filesize from CIM_DATAFILE where name='" & strFile & "'"
Set colItems = objWMIService.ExecQuery(strQuery,,48)
For Each file In colItems
strResults=file.filesize
Next
GetFileSize=strResults

End If

End Function


Dim objFSO, objTS, strName
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTS = objFSO.OpenTextFile(strFilename)
Do Until objTS.AtEndOfStream
strName = objTS.ReadLine
WScript.Echo "Read " & strName & " from file..."
DoObject strName
Loop
objTS.Close

Glen said...

Whats your actually question/problem all you've done is post a code fragment and a very vauge explaination. There's a script for getting the number of mailbox in a storage group http://msgdev.mvps.org/exdevblog/tempbin/lexchstoresv2snsg.zip

Anonymous said...

do u have anything i can use to get all exchange servers and number of mailboxes per storage group to an excel spreadsheet??

Anonymous said...

When I try to run the script for finding the # of mailboxes in a storage group I get a error
" Script out of range ". Please advise

Glen said...

That script requires that you enter the servername you want to run it against as a commandline parameter eg

cscript lexchstoresv2snsg.vbs servername

Otherwise you can hardcode the servername in the first line eg replace

Servername = wscript.arguments(0)

with

Servername = "yourservername"

Cheers
Glen

Anonymous said...

Was just about to write this myself. Fixed it up with a few tweaks and viola! Many thanks.

Please remember, this was made to help all of you, not for the poster to be your personal vb script writer.

Go look up tweaks errors etc yourself.