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

Tuesday, December 14, 2004

Finding when a Exchange Store last backed up via script

There are a couple of ways you can determine when the last time a particular exchange store was backed up if you're using a program that backs up via the Exchange Backup API (Veritas,Brightstore etc).

With Exchange 2003 a new function was added into System Manager to allow you to see the "Time of Last Full Backup" on the database tab. This property is also available in CDOEXM (if you have the 2003 version of system manager loaded on the machine). The ".LastFullBackupTime" property was added to the IMailStoreDB and IPublicStoreDB interfaces. Using this is pretty easy all you have to do is to open the mailstore using the LDAP DN path and access the property eg

set mdbobj = createobject("CDOEXM.MailboxStoreDB")
mdbobj.datasource.open "LDAP://mbdDNpath"
Wscript.echo "Last Backed Up : " & mdbobj.LastFullBackupTime

I've put this together with a similar query from my previous post and this script selects all the exchange stores in a domain and reports when the last time they where backed up. I've posted the code here (this will on work on Exchange 2003)

Another method you can use to do this is to query the eventlog on each of your servers and look for each of the 221 completion events for each of the database files. When the backup runs on a Exchange server you get a bunch of really useful events that tell you when the backup started, how much was backed up and when it finished. In this script im focusing on the finished events which include the path to the database file. So if I combine it with the script from my previous post which was already getting the server name and file path of each of the database files. You end up with a script that will query Active directory for every mail and public folder store then query the event log via WMI to work out when the last time a backup occurred of each file and display the result on the commandline. Note I've limited the search range to the last 7 days so if a server hasn't backed up in the last 7 days you receive a no backup recorded message. This was done to keep the time it takes to run the script to a minumn as searching through a really large event log can be very time consuming. I've posted a copy of the script here The code look 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)
slvlastbackuped = queryeventlog(servername,objmailstore.msExchSLVFile)
edblastbackuped = queryeventlog(servername,objmailstore.msExchEDBFile)
Wscript.echo Rs.Fields("name") & " Last Backed up EDB : " & edblastbackuped
Wscript.echo Rs.Fields("name") & " Last Backed up STM : " & slvlastbackuped
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)
slvlastbackuped = queryeventlog(servername,objmailstore.msExchSLVFile)
edblastbackuped = queryeventlog(servername,objmailstore.msExchEDBFile)
Wscript.echo Rs1.Fields("name") & " Last Backed up EDB : " & edblastbackuped
Wscript.echo Rs1.Fields("name") & " Last Backed up STM : " & slvlastbackuped
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,filename)
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 = '221' 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,filename) then
queryeventlog = dateserial(mid(Time_Written,1,4),mid(Time_Written,5,2),mid(Time_Written,7,2)) & " " & timeserial(mid(Time_Written,9,2),mid(Time_Written,11,2),mid(Time_Written,13,2))
exit for
end if
next
if SB = 0 then queryeventlog = "No Backup recorded in the last 7 Days"
end function

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

Monday, December 06, 2004

Setting Outlook 2003 Junk Email Options Programmatically

[If you are trying to do this using Exchange 2007 please see http://gsexdev.blogspot.com/2007/07/turning-on-filter-junk-email-in.html ]


The Outlook 2003 Junk email filter has a number of different options that can be configured through (tools-options) that determine how the filter will treat email it detects as spam in your inbox. The configuration settings are stored on a hidden extendedrule message in a user’s inbox. The options settings are held in two MAPI properties on this hidden message the first one being

http://schemas.microsoft.com/mapi/proptag/0x61010003 which stores a long value that sets that level of junk email protection you want the long values for each of the setting are

No Automatic filtering = -1
Low = 6
High = 3
Safe Lists only = -2147483648

The “Permanently delete suspected junk e-mail instead of moving it to the Junk E-mail folder” is stored in the http://schemas.microsoft.com/mapi/proptag/0x61020003 as

Disabled = 0
Enabled = 1

On a new mailbox or a mailbox where Outlook 2003 is not being used (and this setting hasn’t been turned on with OWA2003) this hidden message won’t exist in the user inbox (also the junk e-mail folder won't have been created). One way to create this rule (and folder) is to turn on “filter junk email” in OWA2003. You can do this programmatically using a Webdav post and some OWA commands. Eg

xmlstr = ""
xmlstr = xmlstr & "Cmd=options" & vbLf
xmlstr = xmlstr & "junkemailstate=1" & vbLf
xmlstr = xmlstr & "cmd=savejunkemailrule" & vbLf
Set ObjxmlHttp = CreateObject("Microsoft.XMLHTTP")
ObjxmlHttp.Open "POST", "http://server/exchange/mailbox/", False,
"domain\user", "password"
ObjxmlHttp.setRequestHeader "Accept-Language:","en-us"
ObjxmlHttp.setRequestHeader "Content-type:","application/x-www-UTF8-encoded"
ObjxmlHttp.setRequestHeader "Content-Length:", Len(xmlstr)
ObjxmlHttp.Send xmlstr
Wscript.echo ObjxmlHttp.responseText

Once you know for sure that the rule message is going to be there you can go about using a script to modify the Outlook Junk-email settings. The script I use to do this is based on Exoledb (you could also use Webdav if you wanted to do it remotely). What the script does is searches the users inbox for any IPM.ExtendedRule.Message messages that has the http://schemas.microsoft.com/mapi/proptag/0x65EB001E property (which I’m not really sure does) set to JunkEmailRule. Once this message is identified it’s opened up using ADO/Exoledb and the two properties I mentioned above are modified. In the sample script it sets the junk email option to high. I’ve posted a copy of the two scripts in this article here

The code for the above script looks like this

mailbox = "yourmailbox"
Set Rs = CreateObject("ADODB.Recordset")
Set msgobj = CreateObject("CDO.Message")
set Rec = CreateObject("ADODB.Record")
set Rec1 = CreateObject("ADODB.Record")
Set Conn = CreateObject("ADODB.Connection")
mailboxurl = "file://./backofficestorage/yourdomain.com/MBX/" & mailbox & "/"
Conn.Provider = "ExOLEDB.DataSource"
Rec.Open mailboxurl, ,3
mailboxurl = "file://./backofficestorage/ yourdomain.com /MBX/" & mailbox & "/inbox/"
SSql = "SELECT ""DAV:href"", ""DAV:uid"", ""DAV:contentclass"" FROM scope('shallow traversal of """ & mailboxurl & """') "
SSql = SSql & " WHERE ""http://schemas.microsoft.com/mapi/proptag/0x65EB001E"" = 'JunkEmailRule' and ""http://schemas.microsoft.com/exchange/outlookmessageclass"" = 'IPM.ExtendedRule.Message' "
Rs.CursorLocation = 3 'adUseServer = 2, adUseClient = 3
Rs.CursorType = 3
rs.open SSql, rec.ActiveConnection, 3
if Rs.recordcount <> 0 then
Rs.movefirst
while not rs.eof
wscript.echo Rs.Fields("DAV:href").Value
rec1.open Rs.Fields("DAV:href").Value,,3
wscript.echo rec1.fields("http://schemas.microsoft.com/mapi/proptag/0x61010003").Value
wscript.echo rec1.fields("http://schemas.microsoft.com/mapi/proptag/0x61020003").Value
rec1.fields("http://schemas.microsoft.com/mapi/proptag/0x61010003").Value = 3
rec1.fields("http://schemas.microsoft.com/mapi/proptag/0x61020003").Value = 0
rec1.fields.update
rec1.close
rs.movenext
wend
end if
rs.close