Reporting on Disconnected mailboxes and showing when they will be purged in Exchange 2003

One of cool things you can do in Exchange 2003 using the Exchange_Mailbox WMI class in you can show all the disconnected mailboxes by querying the DateDiscoveredAbsentInDS property which gets set after the “Cleanup Agent” has detected that there is no longer a Active Directory account associated with this mailbox. To make this information really useful in a report you need to combine it with what the Mailbox retention settings are on the mail-store where this mailbox is located. This will tell you when the mailbox is going to be deleted and how many days it has left in the cache. This information may be interesting if you have a mailbox that is quite large and you want to know when that space will be recovered into the mail-store. It can also come in handy if you need to monitor your helpdesk staff to make sure they aren’t deleting any mailboxes they shouldn’t. eg you could use it to email a warning when there is only 2-3 days left before a mailbox will be permanently deleted to really make sure you want to delete this mailbox.

To do this in a script you need to relate the mailbox stores msExchMailboxRetentionPeriod property with WMI’s DateDiscoveredAbsentInDS property and then use some of the VBS datetime functions to work out when the Mailbox will be permanently deleted. For this the ADO data shape provider again comes in handy in creating a disconnected recordset that will allow you to create a data shape that will relate mailboxes retrieved from the Exchange_Mailbox class with the store that they are in. The script outputs the results to the command window and also creates a CSV file on the c:\ drive called deletedmbrep.csv.

The msExchMailboxRetentionPeriod is an AD property that is held on each mailstore object in Active Directory and represents the number of seconds for the Mailbox retention setting. This gets converted to and from days when you view and configure it in Exchange System Manager.

How the script works
A general walkthrough of this script is it first grabs the timezone information from the registry which is necessary to convert the WMI time in DateDiscoveredAbsentInDS to the right time zone. The next part of the script takes the servername you want to run this script on as a command line parameter and then queries Active directory for this server and then queries all the Mail stores on this server. For each Mailstore it retrieves the msExchMailboxRetentionPeriod and then stores this as the Parent Datashape along with the displayname of the Mailstore. The next part of the script then queries the Exchange_Mailbox WMI class for any mailboxes that have the DateDiscoveredAbsentInDS set which would indicate they are disconnected mailboxes. This information then becomes the child recordset which is related to the parent record set based on the Mailstore name. The rest of the script is to display the data first converting the msExchMailboxRetentionPeriod into days and also the Mailboxsize in to Megabytes and the WMI datetime into a vb datetime.

To run the script you need to supply the name of the server you want to run it against as a command-line parameter

Eg cscript showdelmbs.vbs servername

The script itself looks like the following I’ve put a downloadable copy here

servername = wscript.arguments(0)
set shell = createobject("wscript.shell")
strValueName = "HKLM\SYSTEM\CurrentControlSet\Control\TimeZoneInformation\ActiveTimeBias"
minTimeOffset = shell.regread(strValueName)
toffset = datediff("h",DateAdd("n", minTimeOffset, now()),now())
set conn = createobject("ADODB.Connection")
set com = createobject("ADODB.Command")
set conn1 = createobject("ADODB.Connection")
strConnString = "Data Provider=NONE; Provider=MSDataShape"
conn1.Open strConnString
Set iAdRootDSE = GetObject("LDAP://RootDSE")
strNameingContext = iAdRootDSE.Get("configurationNamingContext")
strDefaultNamingContext = iAdRootDSE.Get("defaultNamingContext")
Set fso = CreateObject("Scripting.FileSystemObject")
set wfile = fso.opentextfile("c:\deletedmbrep.csv",2,true)
wfile.writeline("Mailbox,MailStore,Mailbox Size(MB),Date Delete Noticed,Date
when mailbox will be purged,Days left to Deletion")
set objParentRS = createobject("adodb.recordset")
set objChildRS = createobject("adodb.recordset")
strSQL = "SHAPE APPEND" & _
" NEW adVarChar(255) AS SOADDisplayName, " & _
" NEW adVarChar(255) AS SOADDistName, " & _
" NEW adVarChar(255) AS SOADmsExchMailboxRetentionPeriod, " & _
" ((SHAPE APPEND " & _
" NEW adVarChar(255) AS WMILegacyDN, " & _
" NEW adVarChar(255) AS WMIMailboxDisplayName, " & _
" NEW adVarChar(255) AS WMISize, " & _
" NEW adVarChar(255) AS WMIDateDiscoveredAbsentInDS, " & _
" NEW adVarChar(255) AS WMIStorename) " & _
" RELATE SOADDisplayName TO WMIStorename) AS MOWMI"
objParentRS.LockType = 3
objParentRS.Open strSQL, conn1
Conn.Provider = "ADsDSOObject"
Conn.Open "ADs Provider"
svcQuery = "<LDAP://" & strNameingContext & ">;(&(objectCategory=msExchExchangeServer)(cn="
& Servername & "));cn,name,distinguishedName,legacyExchangeDN;subtree"
Com.ActiveConnection = Conn
Com.CommandText = svcQuery
Set Rs = Com.Execute
while not rs.eof
sgQuery = "<LDAP://" & strNameingContext & ">;(&(objectCategory=msExchPrivateMDB)(msExchOwningServer="
& rs.fields("distinguishedName") & "));cn,name,displayname,msExchMailboxRetentionPeriod,distinguishedName,
legacyExchangeDN;subtree"
Com.CommandText = sgQuery
Set Rs1 = Com.Execute
while not rs1.eof
objParentRS.addnew
objParentRS("SOADDisplayName") = rs1.fields("displayname")
objParentRS("SOADDistName") = left(rs1.fields("distinguishedName"),255)
objParentRS("SOADmsExchMailboxRetentionPeriod") = rs1.fields("msExchMailboxRetentionPeriod")
objParentRS.update
rs1.movenext
wend
wscript.echo "finished 1st AD query Mailbox Stores"
rs.movenext
wend
Set objchild = objParentRS("MOWMI").Value
strWinMgmts ="winmgmts:{impersonationLevel=impersonate}!//"& servername
&"/root/MicrosoftExchangeV2"
Set objWMIExchange = GetObject(strWinMgmts)
Set listExchange_MailboxSizes = objWMIExchange.ExecQuery("Select * FROM
Exchange_Mailbox Where DateDiscoveredAbsentInDS IS NOT Null",,48)
For each objExchange_Mailboxs in listExchange_MailboxSizes
objchild.addnew
objchild("WMILegacyDN") = objExchange_Mailboxs.LegacyDN
objchild("WMIMailboxDisplayName") = objExchange_Mailboxs.MailboxDisplayName
objchild("WMISize") = objExchange_Mailboxs.Size
objchild("WMIDateDiscoveredAbsentInDS") =
objExchange_Mailboxs.DateDiscoveredAbsentInDS
objchild("WMIStorename") = objExchange_Mailboxs.storename
objchild.update
Next
wscript.echo "finished Exchange WMI query"
wscript.echo
objParentRS.MoveFirst
Do While Not objParentRS.EOF
Set objChildRS = objParentRS("MOWMI").Value
MSdisplayname = objParentRS("SOADDisplayName")
wscript.echo
wscript.echo "Mailbox Store : " & MSdisplayname
if objParentRS("SOADmsExchMailboxRetentionPeriod") <> 0 then
retrate = objParentRS("SOADmsExchMailboxRetentionPeriod")\24\60\60
else
retrate = 0
end if
wscript.echo "Current Retention Rate : " & retrate & " days"
Wscript.echo "Number of Deleted Mailboxes not yet purged : " &
objChildRS.recordcount
if objChildRS.recordcount <> 0 then
wscript.echo "Disconnect Mailboxes"
wscript.echo
end if
Do While Not objChildRS.EOF
mbsize = objChildRS("WMISize")
wscript.echo "Mailbox : " & objChildRS.fields("WMIMailboxDisplayName")
wscript.echo "Size of Mailbox : " & formatnumber(mbsize/1024,2) & " MB"
deldate =
dateadd("h",toffset,cdate(DateSerial(Left(objChildRS.fields("WMIDateDiscoveredAbsentInDS"),
4), Mid(objChildRS.fields("WMIDateDiscoveredAbsentInDS"), 5, 2),
Mid(objChildRS.fields("WMIDateDiscoveredAbsentInDS"), 7, 2)) & " " &
timeserial(Mid(objChildRS.fields("WMIDateDiscoveredAbsentInDS"), 9, 2),Mid(objChildRS.fields("WMIDateDiscoveredAbsentInDS"),
11, 2),Mid(objChildRS.fields("WMIDateDiscoveredAbsentInDS"),13, 2))))
wscript.echo "Date Deletetion was Detected : " & deldate
wscript.echo "Date when Mailbox will be purged : " & dateadd("d",retrate,deldate)
wscript.echo "Number of Days to Mailbox purge : " &
datediff("d",deldate,dateadd("d",retrate,deldate))
wscript.echo
wfile.writeline(replace(objChildRS.fields("WMIMailboxDisplayName"),",","") & ","
& replace(MSdisplayname,",","") & "," & replace(formatnumber(mbsize/1024,2),",","")
& "," & deldate & "," & dateadd("d",retrate,deldate) & "," &
datediff("d",deldate,dateadd("d",retrate,deldate)) )
objChildRS.MoveNext
Loop
objParentRS.MoveNext
Loop
wfile.close
Wscript.echo
Wscript.echo "CSV file created"