Tuesday, December 14, 2004

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

[updated fixed new bug when there's more then one mailbox store on a server]

I've had a bit of feedback about the first version of the script that i posted here so I decided to give it a quick revise and add some additional functionality. The additional functionality added to this script is that instead of just returning the size of each store file it also now returns the freespaces left of the disk where the file is located and the percentage of the diskspace that is free. Also there are some fixes for it to allow the script to work when there are more then 1000 mailboxes. I've posted a copy of the new code up here

set conn = createobject("ADODB.Connection")
set com = createobject("ADODB.Command")
Set iAdRootDSE = GetObject("LDAP://RootDSE")
strNameingContext = iAdRootDSE.Get("configurationNamingContext")
rangeStep = 999
lowRange = 0
highRange = lowRange + rangeStep
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")
mbnum = 0
quit = false
rangeStep = 999
lowRange = 0
highRange = lowRange + rangeStep
set objmailstore = getObject(objmailstorename)
Do until quit = true
on error resume next
strCommandText = "homeMDBBL;range=" & lowRange & "-" & highRange
objmailstore.GetInfoEx Array(strCommandText), 0
if err.number <> 0 then quit = true
varReports = objmailstore.GetEx("homeMDBBL")
if quit <> true then mbnum = mbnum + ubound(varReports)+1
lowRange = highRange + 1
highRange = lowRange + rangeStep
loop
err.clear
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)
set edrive = fso.GetDrive("\\" & mid(objmailstore.msExchOwningServer,4,instr(objmailstore.msExchOwningServer,",")-4) & "\" & left(objmailstore.msExchEDBFile,1) & "$")
set sdrive = fso.GetDrive("\\" & mid(objmailstore.msExchOwningServer,4,instr(objmailstore.msExchOwningServer,",")-4) & "\" & left(objmailstore.msExchSLVFile,1) & "$")
edbtotalspace = round(edrive.totalsize/1073741824)
edbfreespace = round(edrive.FreeSpace/1073741824)
edbpercentleft = FormatNumber((edbfreespace/edbtotalspace)*100, 0)
stmtotalspace = round(sdrive.totalsize/1073741824)
stmfreespace = round(sdrive.FreeSpace/1073741824)
stmpercentleft = FormatNumber((stmfreespace/stmtotalspace)*100, 0)
Wscript.echo Rs.Fields("name") & "# Mailboxes: " & mbnum & " EDBSize(GB): " & edbsize & " STMSize(GB): " & stmsize
wscript.echo "Freespace on EDB Drive (" & left(objmailstore.msExchEDBFile,1) & ":) :" & edbfreespace & " GB Percent Left :" & edbpercentleft & " %"
wscript.echo "Freespace on STM Drive (" & left(objmailstore.msExchSLVFile,1) & ":) :" & stmfreespace & " GB Percent Left :" & stmpercentleft & " %"
wscript.echo
Rs.MoveNext

Wend
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)
set edrive = fso.GetDrive("\\" & mid(objmailstore.msExchOwningServer,4,instr(objmailstore.msExchOwningServer,",")-4) & "\" & left(objmailstore.msExchEDBFile,1) & "$")
set sdrive = fso.GetDrive("\\" & mid(objmailstore.msExchOwningServer,4,instr(objmailstore.msExchOwningServer,",")-4) & "\" & left(objmailstore.msExchSLVFile,1) & "$")
edbtotalspace = round(edrive.totalsize/1073741824)
edbfreespace = round(edrive.FreeSpace/1073741824)
edbpercentleft = FormatNumber((edbfreespace/edbtotalspace)*100, 0)
stmtotalspace = round(sdrive.totalsize/1073741824)
stmfreespace = round(sdrive.FreeSpace/1073741824)
stmpercentleft = FormatNumber((stmfreespace/stmtotalspace)*100, 0)
Wscript.echo Rs1.Fields("name") & " EDBSize(GB): " & edbsize & " STMSize(GB): " & stmsize
wscript.echo "Freespace on EDB Drive (" & left(objmailstore.msExchEDBFile,1) & ":) :" & edbfreespace & " GB Percent Left :" & edbpercentleft & " %"
wscript.echo "Freespace on STM Drive (" & left(objmailstore.msExchSLVFile,1) & ":) :" & stmfreespace & " GB Percent Left :" & stmpercentleft & " %"
wscript.echo
Rs1.MoveNext

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

27 comments:

Anonymous said...

Great development, I'm really using your scripts, some feedback.
There must be some error in this one because it counts correct on the first Exchange server which is 5.5 (still in mix mode) but doesn't count the others which are EX2000 and the > 1000 mailboxes (still) doesn't work.

C:\>lexchstoresv2.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: 418 EDBSize(GB): STMSize
(GB):
Freespace on EDB Drive (:) : GB Percent Left : %
Freespace on STM Drive (:) : GB Percent Left : %

Mailbox Store (XXXXXXX)# Mailboxes: 0 EDBSize(GB): 2.31 STMSize(GB): 13.38

Freespace on EDB Drive (E:) :18 GB Percent Left :53 %
Freespace on STM Drive (E:) :18 GB Percent Left :53 %

Private Information Store (XXXXXXX)# Mailboxes: 0 EDBSize(GB): 2.31 STMSi
ze(GB): 13.38
Freespace on EDB Drive (:) :18 GB Percent Left :53 %
Freespace on STM Drive (:) :18 GB Percent Left :53 %

Private Information Store (XXXXXXX)# Mailboxes: 0 EDBSize(GB): 2.31 STMSi
ze(GB): 13.38
Freespace on EDB Drive (:) :18 GB Percent Left :53 %
Freespace on STM Drive (:) :18 GB Percent Left :53 %

Private Information Store (XXXXXXX)# Mailboxes: 0 EDBSize(GB): 2.31 STMSi
ze(GB): 13.38
Freespace on EDB Drive (:) :18 GB Percent Left :53 %
Freespace on STM Drive (:) :18 GB Percent Left :53 %

Mailbox Store SG1 IS1 XXX01# Mailboxes: 0 EDBSize(GB): .06 STMSize(GB): .01
Freespace on EDB Drive (D:) :8 GB Percent Left :33 %
Freespace on STM Drive (D:) :8 GB Percent Left :33 %

Mailbox Store SG1 IS1 XXX31# Mailboxes: 0 EDBSize(GB): 8.39 STMSize(GB): 1.19
Freespace on EDB Drive (N:) :161 GB Percent Left :81 %
Freespace on STM Drive (N:) :161 GB Percent Left :81 %

Mailbox Store SG1 IS1 XXX30# Mailboxes: 0 EDBSize(GB): 8.94 STMSize(GB): 1.30
Freespace on EDB Drive (N:) :133 GB Percent Left :67 %
Freespace on STM Drive (N:) :133 GB Percent Left :67 %

Mailbox Store SG1 IS1 XXX32# Mailboxes: 0 EDBSize(GB): 9.26 STMSize(GB): 1.37
Freespace on EDB Drive (N:) :135 GB Percent Left :68 %
Freespace on STM Drive (N:) :135 GB Percent Left :68 %

Glen said...

Okay I've managed to get a version of Enterprise Server to test with and i think i found the issue for over 1000. I wasn't reseting the range values so when it would query the next server it would be looking look to high in the range values. I've update the entry and zip files.

With the Exchange 5.5 issue im not sure with this one i dont have any 5.5 boxes around to test with. On a 5.5 box your only going to have EDB files which is going to cause some parts of the scipt to error out. You may just need to add a if statment in there to detect when you have a 5.5 box and get it to skip over the stm stuff.

Anonymous said...

It's GOLD now, working perfect :)
Great job, Thank you!

Looks like this.....
Mailbox Store SG1 IS1 XXX31# Mailboxes: 3342 EDBSize(GB): 8.39 STMSize(GB): 1.19

Freespace on EDB Drive (N:) :161 GB Percent Left :81 %
Freespace on STM Drive (N:) :161 GB Percent Left :81 %

Mailbox Store SG1 IS1 XXX30# Mailboxes: 3359 EDBSize(GB): 8.94 STMSize(GB): 1.30

Freespace on EDB Drive (N:) :133 GB Percent Left :67 %
Freespace on STM Drive (N:) :133 GB Percent Left :67 %

Mailbox Store SG1 IS1 XXX32# Mailboxes: 3573 EDBSize(GB): 9.26 STMSize(GB): 1.37

Freespace on EDB Drive (N:) :135 GB Percent Left :68 %
Freespace on STM Drive (N:) :135 GB Percent Left :68 %

Anonymous said...

I'm going to test this as soon as I return to the office on Thursday. What about reporting on white-space in these databases? That would be a great addition

Michel

Glen said...

Im working on Whitespace what this space

Anonymous said...

Great Stuff....

My org uses a lot of simular store names (MS01 MS02 ..etc). Is there a quick way to add the server name to display on the same line as the Store name?

Thanks for all your work.

Briam

Glen said...

Yes you can,
I get the servername with the following statement mid(objmailstore.msExchOwningServer,4,instr(objmailstore.msExchOwningServer,",")-4)

So one way to include the servername first would be assign it to a varible and then add this to the output line (51) eg

strservername =(objmailstore.msExchOwningServer,4,instr(objmailstore.msExchOwningServer,",")-4)
Wscript.echo strservername & " " & Rs.Fields("name") & "# Mailboxes: " & mbnum & " EDBSize(GB): " & edbsize
& " STMSize(GB): " & stmsize

You need to do this for the mailbox and public folder section i've posted up a sample at http://bluetack.aspxconnection.com/temp/lexchstoresv2sname.zip

Anonymous said...

SWEET!! That did the trick... That would have taken me forever to figure out. Thank you very much. With over 80 stores in our org.... this is a much needed tool.

Briam

Anonymous said...

Glen, Great work. I've wrapped your connections into a larger "admin" type app using .NET (C#). Your connections return every store in the site or forest. Is there a way to only search the domain the user is logged into? Something like this:

Set iAdRootDSE = GetObject("LDAP://dc=myDomainName, dc=com")
strNameingContext = iAdRootDSE.Get("configurationNamingContext")

The above code does not work, but am I on the right track? Your thoughts....

Glen said...

This script queries the configuration partition which is where all the Exchange configuration information is held. In a forest there is only one configuration partition that is replicated amoung all the DC's in the forest from MSDN
" Configuration Partition: The configuration partition contains replication topology and other configuration data that must be replicated throughout the forest. Every domain controller in the forest has a replica of the same configuration partition. "

So to limit it to only servers in a particular domain is a little tricky. Theres no domain property on the store object itself so modifying the query string isn't going to work either. You may just need to look at doing it at the display end. Eg you can grab the servername and then do a search in the domain partiation for that user's domain to see if there is a computer object for the mail server.

Martin said...

I most say this is a great script.
I am not a programmer so I'm wondering if it would be hard to create a webpage from which you can run this script and get the results presented in a webpage?

.Martin

Glen said...

You could do it in a asp or asp.net web page then main issue is getting impersonation to work correctly because the user making these querys will need to have rights to query Active Directory and the file locations on the server. You would probably be better using WMI is a ASP page instead of FSO object to get the file sizes.

Anonymous said...

Nice Script... Can any one help me modify the script to get one or two server rathar than entire organization.

Anonymous said...

I'm trying to execute this script but it is generating an error in the following line

mbQuery = "pfQuery = "Com.ActiveConnection = Conn

Why?

Glen said...

Looks like a cut and paste issue blogger sometime interprates parts of the script as html and doesn't show them properly. For this reason i allways provide a downloadable version of the code try downloading http://bluetack.aspxconnection.com/exdevblog/lexchstoresv2.zip

Cheryl said...

This script is exactly what I've been looking for, but I get an error "file not found" for edbfilespec (22,3).

Any help is appreciated!

Glen said...

To start with i would put a

On error resume next

at the top of the script this will mean it will skip over any error it gets and continue on. It could be just one server in you network thats causing issues. Or could be you have a Exchange 5.5 server which this script wont work on. The other thing that could be affecting this is if you have disabled the administrative shares on your servers. To cater for this i created a WMI version which would be worth giving a try see http://gsexdev.blogspot.com/2005/01/listing-file-sizes-with-wmi.html

Cheryl said...

Thanks, Glen, I'm sure that's it - these are mountable shares from RAID. I'll give this a try.

Anonymous said...

This is an absolutely invaluable bit of code Glen. Thanks a million for making it available. It's just what I was looking for.

Any chance of getting it to include transaction log info if you ever do a v3? - number of logs, space used and free would be quite useful to include.

In the meantimem if I can find the LDAP query for the log drives I may try and adapt it myself :o)

Glen said...

Have a look at http://gsexdev.blogspot.com/2005/10/displaying-details-about-transaction.html which has most of the code you should need to do this.

Cheers
Glen

Anonymous said...

Nice script, works fine for storage groups which are local but does not return the size of the storage groups which are on our NAS.

Any ideas?

Sureen said...

Hi Glen,

Awsome Script, I changed it to get the CSV output, but the issues is the environment I'm Managing is very big say-200,000 mailboxes with 1500 Mailstores,

So my problem is I'm not able to get the output lines more than 1000, even i changed the rangestep = 9999, its not working , please help me to get the full funcanality of the script, to get all the servers Mail stores data in a single output!!!

Thank you very much for the help

Have a great moments

:)
Sureen

Glen said...

Did you use the code in the download or just cut and paste from the blog. When i made the bug fixes i tend to just update the download and not the code in the post itself. Im pretty sure i fixed the 1000 mailbox issue and other people have used this okay. But the fix is only relfected in the download http://msgdev.mvps.org/exdevblog/lexchstoresv2.zip

Cheers
Glen

Sureen said...

HI Glen,

I Downloaded the Code form the site, and now also I tried with the code link you give but I 'll be having the same issue, and I alos tried the code running from Scriptomatic V2, still the same issue,

I disregard the free space and percentage results from ur script and taken in consideration of volume space of EDB and STM, and # of mailboxes, in the CSV file colum wise then I'll be gettimg more stores but still 1000 lines, If i include the free space then the # of stores displayed is less in the format you give (Display results in CMD)

I belive I experssed the issues

:)
Sureen

Sureen said...

Glen,

Any updates

Thanks
Sureen

Glen said...

Sorry its probably the code but i can't reproduce your problem. Maybe have a look at http://www.petri.co.il/reporting_storage_size_in_exchange.htm instead to see if this works better for you.

Rich said...

Thanks Glen, a very big help.