Monday, December 20, 2004

Showing how much whitepace is in your database via script

Continuing on from some of my last weeks entries,

When the Information Store maintenance process runs it logs to the windows eventlogs the results of its retention and de-fragmentation operations. More importantly it tells you how much space is held in deleted item retention (and how much it just released). How much space is held in deleted mailbox retention and how much it just released. And finally how much free-space there is in the database after the online de-fragmentation has just run.

So what this script does is query AD for all the mail and public folder stores in your domain. Then it querys each servers event log for 3 specific events that contain the details of the 3 maintenance operations and then parses the result out of that text and displays the result at the commandline. Event “1221” is logged after an online de-fragmentation is run on a mailbox or public folder store. Event “1027” is logged after the deleted item retention cleanup is done and the result of starting and ending size and numbers is logged. Event “9535” is logged after the deleted mailbox cleanup is run and logs the number and size of deleted mailboxes retained and purged.

To identify each mailstore within the eventlog exchange uses a format storagegroup\mailboxstore. This format isn’t stored in Active directory anywhere (easy) so I had to build it from the Active Directory DN path. This should all work fine as long as you don’t have any “,” in your mailstore name (this is going to break the split).

I’ve post a downloadable copy of the script here

The code looks like

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")
set objmailstore = getObject(objmailstorename)
servername = mid(objmailstore.msExchOwningServer,4,instr(objmailstore.msExchOwningServer,",")-4)
dnarray = split(Rs.Fields("distinguishedName"),",",-1,1)
sgname = mid(dnarray(1),4) & "\" & mid(dnarray(0),4)
Dbfreespace = queryeventlog(servername,sgname,"1221")
Wscript.echo Rs.Fields("name") & " Freespace after Defrag : " & Dbfreespace
Dbreten = queryeventlog(servername,sgname,"1207")
mbreten = queryeventlog(servername,sgname,"9535")
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)
servername = mid(objmailstore.msExchOwningServer,4,instr(objmailstore.msExchOwningServer,",")-4)
dnarray1 = split(Rs1.Fields("distinguishedName"),",",-1,1)
sgname = mid(dnarray1(1),4) & "\" & mid(dnarray1(0),4)
Dbfreespace = queryeventlog(servername,sgname,"1221")
Wscript.echo Rs1.Fields("name") & " Freespace after Defrag : " & Dbfreespace
Dbreten = queryeventlog(servername,sgname,"1207")
wscript.echo
Rs1.MoveNext

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


function queryeventlog(servername,sgname,event2s)
SB = 0
dtmStartDate = CDate(Date) - 7
dtmStartDate = Year(dtmStartDate) & Right( "00" & Month(dtmStartDate), 2) & Right( "00" & Day(dtmStartDate), 2)
Set objWMIService = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & servername & "\root\cimv2")
Set colLoggedEvents = objWMIService.ExecQuery("Select * from Win32_NTLogEvent Where Logfile='Application' and Eventcode = '" & event2s & "' and TimeWritten >= '" & dtmStartDate & "' ",,48)
For Each objEvent in colLoggedEvents
SB = 1
Time_Written = objEvent.TimeWritten
Time_Written = left(Time_Written,(instr(Time_written,".")-1))
if instr(objEvent.Message,sgname) then
if event2s = "1221" then
queryeventlog = Mid(objEvent.Message,InStr(15,objEvent.Message,chr(34))+6,(InStr(1,objEvent.Message,"megabytes")-1)-(InStr(15,objEvent.Message,chr(34))+6))
else
if event2s = "1207" then
StartItems = Mid(objEvent.Message,InStr(82,objEvent.Message,chr(34))+13,(InStr(82,objEvent.Message,"items;")-(InStr(82,objEvent.Message,chr(34))+14)))
StartSize = Mid(objEvent.Message,(InStr(objEvent.Message,"items;")+7),InStr((InStr(objEvent.Message,"items;")+7),objEvent.Message," ")-(InStr(objEvent.Message,"items;")+7))
End_Items = Mid(objEvent.Message,(InStr(objEvent.Message,"End:")+5),InStr((InStr(objEvent.Message,"End:")+5),objEvent.Message," ")-(InStr(objEvent.Message,"End:")+5))
End_Size = Mid(objEvent.Message,(InStr((InStr(objEvent.Message,"End:")+5),objEvent.Message,"items;")+7),InStr((InStr((InStr(objEvent.Message,"End:")+5),objEvent.Message,"items;")+7),objEvent.Message," ")-(InStr((InStr(objEvent.Message,"End:")+5),objEvent.Message,"items;")+7))
Wscript.echo "Retained StartItems : " & StartItems & " StartSize : " & formatnumber(StartSize/1024,2)
Wscript.echo "Retained EndItems : " & End_Items & " EndSize : " & formatnumber(End_Size/1024,2)
else
Deleted_Number = Mid(objEvent.Message,InStr(88,objEvent.Message,".")+5,InStr(88,objEvent.Message,"deleted")-1-(InStr(88,objEvent.Message,".")+5))
Deleted_Size = Mid(objEvent.Message,(InStr(88,objEvent.Message,"deleted")+19),InStr(InStr(88,objEvent.Message,"deleted")+19,objEvent.Message," ")-(InStr(88,objEvent.Message,"deleted")+19))
Retained_Number = Mid(objEvent.Message,InStr(88,objEvent.Message,"removed.")+12,InStr(InStr(88,objEvent.Message,"removed.")+8,objEvent.Message,"deleted")-(InStr(88,objEvent.Message,"removed.")+12))
Retained_Size = Mid(objEvent.Message,InStr((InStr(88,objEvent.Message,"removed.")+8),objEvent.Message,"mailboxes")+11,InStr(InStr((InStr(88,objEvent.Message,"removed.")+8),objEvent.Message,"mailboxes")+11,objEvent.Message," ")-(InStr((InStr(88,objEvent.Message,"removed.")+8),objEvent.Message,"mailboxes")+11))
wscript.echo "Number of Deleted Mailboxs Removed : " & Deleted_Number & " Size : " & formatnumber(Deleted_Size/1024,2)
wscript.echo "Number of Deleted Mailboxs Retained : " & Retained_Number & " Size : " & formatnumber(Retained_Size/1024,2)
end if
end if
exit for
end if
next
if SB = 0 then queryeventlog = "No Backup recorded in the last 7 Days"
end function

20 comments:

Anonymous said...

Can we integrate the Last Backup into this, I see some comments in the function part...but no connection to Event 221.
"if SB = 0 then queryeventlog = "No Backup recorded in the last 7 Days""

Thanks,
AK

Anonymous said...

Another great script! Thanks for sharing them with the Exchange community.

Peter said...

Amazing script! Great work.
Referred at www.exchange-digest.com

Steve said...

Excellent script. Runs a little slow in my environment...but that is probably a network issue. The output is well formatted and easy to read. Thanks!

Anonymous said...

Glen,
I'm getting this error

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

Mailbox Stores

G:\IS\exchange migration\lexchstorewspace.vbs(61, 1) (null): 0x80041003

I'm not a developer or scripter so I'm not quite sure what this line does.

ramon-

Glen said...

This line is trying to connect to a server remotly via WMI so it can query the event log. A few things could cause it to fail what you could try first is to put a

On error resume next

as the first line of the script this means that if one server is causing problems it will skip over it and try the other servers. Other things that could cause this script to fail is that you usually need admin rights on the machine you trying to query to be able to run this script successfully

Anonymous said...

Thank you very much for your reply Glen. I added the line like you told me to and it works now.
Is it possible to just query a server instead of all the servers in the organonization?

Glen said...

You need to make follwoing changes to include a section that queries based on server name

Servername = "yourservername"
Conn.Open "ADs Provider"
svcQuery = "<LDAP://" & strNameingContext & ">;(&(objectCategory=msExchExchangeServer)(cn="
& Servername & "));cn,name,distinguishedName;subtree"
Com.ActiveConnection = Conn
Com.CommandText = svcQuery
Set snrs = Com.Execute
mbQuery = "<LDAP://" & strNameingContext & ">;(&(objectCategory=msExchPrivateMDB)(msExchOwningServer="
& snrs.fields("distinguishedName") & "));name,distinguishedName;subtree"
pfQuery = "<LDAP://" & strNameingContext &
">;(&(objectCategory=msExchPublicMDB)(msExchOwningServer=" &
snrs.fields("distinguishedName") & "));name,distinguishedName;subtree"

Anonymous said...

I apologize Glen, but I'm not good at scripting yet. Could you please tell me where in the code I could add those lines? Thanks again for all your help.

Ramon-

Glen said...

I've posted up a modified version of the script have a look at http://bluetack.aspxconnection.com/exdevblog/lexchstorewspacesn.zip

Anonymous said...

Hello Glen,
sorry for being such a pain. I tried the new script and getting this error:

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

D:\lexchstorewspacesn.vbs(15, 1) (null): Exception occurred.


Thank you again Happy New Year to you!
Ramon

Glen said...

This sounds like the query for the servername is failing. Have you put the servername you want to search for in the first line. When you did this make sure you use the servers short netbios name not the fqdn

Anonymous said...

Hi Glen,
Hope your New Year was great. yes, I did put the server name (rldms01) here is what i get:
P:\>cscript lexchstores.vbs rldms01
Microsoft (R) Windows Script Host Version 5.6
Copyright (C) Microsoft Corporation 1996-2001. All rights reserved.

Mailbox Stores

P:\lexchstores.vbs(22, 3) Microsoft VBScript runtime error: File not found

Glen said...

Im guessing you modified the first line of the script like

Servername = wscript.arguments(0)

This script works fine for me what version of Exchange is the server your trying to run it against have.(these queries wont work on a Exchange 5.5 server) Try putting in a on error resume next at the top of the script and see if you can get any output. The other thing to do is to start putting in Wscript.echo statements and echo out the varibles and query results which might help track down whats going wrong

Anonymous said...

is there a way to display teh results in a nice fancy html with attachment...."you know managers and their darn documentation"

Anonymous said...

Great Script.Great work.

i faced problems running the script which was resolved by adding a
"on error resume next" .

maybe you could incorporate this or some form of error handling.

Thanks !

Ludvig F. Aarstad said...

Just downloaded your script. However, I am running a script where I have a list of all servers and storagegroups and mailstores in a separate database. How do I run your script towards a named server, storagegroup and mailstore?

Glen said...

Have a look at http://msgdev.mvps.org/exdevblog/lexchstorewspacesn.zip
which is a version of this script that allows you to target it towards a particular server via the servername. It you really only what to look at one store then if you pull the Distingished name via ADSI edit you can then use this directly by calling the function with the right parameters. An easy way to work it out would be just to use a echo statement that echo'd the parameters that this script uses at the moment and then create you own script that passes in these parameters insteads.

Coupland said...

Great script! Although, I've noticed what I think is one snag with it. I think it only shows only retained deleted items from the "Deleted Items" folder and not other folders. I've got the "DumpsterAlwaysOn" value set on all my machines because Cached Exchanged Mode accounts can have their deleted files skip the "Deleted Items" folder because while syncing the server could potentially never know that the a file was moved to "Deleted Items "before it was removed from there.

Of course, this isn't a huge issue because these retained deleted items would only show up on my laptop users or users that delete and clear "Deleted Items" before a sync. I just figured I'd let everyone know.

Thanks for all the great scripts!

Anonymous said...

Just a quick note - the Event ID that appears in the logs after cleanup of items past retention date isn't 1027. It's 1207.