Finding Unused mailboxes             Download Scripts

One problem that medium to large Exchange networks may face especially as these systems grow older is you may end up with a large number of mailboxes that are no longer used but still sit their and receive email. This can happen when staff come and go in a organization and the IT department isn't informed also where generic mailboxes have been setup but their purpose and use has been long forgotten. Finding these mailboxes can be very hard and sometimes a random process of opening suspect mailboxes. This script has been written to try and help in this situation by querying the number of items in a mailbox received in the last 50 days and the comparing this figure against a count of the number of unread emails in a  mailbox over the last 50 days. If these two figures are equal then its a good chance that this mailbox is not currently in use and its time to chase people up to see if it needs to be deleted.

How it works

set WshShell = CreateObject("WScript.Shell")
Set fso = CreateObject("Scripting.FileSystemObject")
set wfile = fso.opentextfile("c:\temp\Unreadmailreport.txt",2,true)
wfile.writeline("Username,Unread-Emails,Total-Emails")

This first section of code sets up a text file to wright the results to by default this is to a hard-coded text file in the c:\temp of your server

DomainName = "your.domain.com.au"    '  Use the Active Directory Domain Name
strDefaultNamingContext = "DC=your,D=domain,DC=com,DC=au"  ' Use the Active Directory Domain Name

GALQueryFilter = "(&(&(&(& (mailnickname=*)(!msExchHideFromAddressLists=TRUE) (| (&(objectCategory=person)(objectClass=user)(msExchHomeServerName=/o=exch-dev/ou=First Administrative Group/cn=Configuration/cn=Servers/cn=Yourserver)) )))))"
strQuery = "<LDAP://" & DomainName & "/" & strDefaultNamingContext & ">;" & GALQueryFilter & ";samaccountname;subtree"

Set oConn = CreateObject("ADODB.Connection") 'Create an ADO Connection
oConn.Provider = "ADsDSOOBJECT" ' ADSI OLE-DB provider
oConn.Open "ADs Provider"

Set oComm = CreateObject("ADODB.Command") ' Create an ADO Command
oComm.ActiveConnection = oConn
oComm.Properties("Page Size") = 1000
oComm.CommandText = strQuery
oComm.Properties("Sort on") = "givenname"

Set rs = oComm.Execute
while not rs.eof
    getunreadcount(rs.fields("Samaccountname"))
    rs.movenext
wend

The above code works the same way as I have explained in this article, the main difference here is in the GALQueryfilter I'm using. For this script I've included 2 new parameters the first is the server name (this is so only mailboxes on this server are included) and the second is a parameter to exclude and hidden mailboxes. From experience I've found hidden mailboxes tend to cause a lot of problems with these type of scripts because you tend to pickup the system mailboxes and antivirus mailboxes whose structures differ and cause the script to crash.  The sections that I've highlighted in Red are the parts that you need to change to get it to work in your environment, the hardest one of these to get is the DN name of the server for the GalQueryFilter their are a few ways to get this the easiest way I've found is to use ADSIedit. If you are using ADSIedit just look at the msExchHomeServerName property of 1 of the user accounts that has a mailbox on the server you wish to query. The other method if you don't have ADSIedit installed (or just don't like using it) is to use the Exchange System Manager I've written a quick walkthrough of this method here.

Once this query has executed a record set is built the next part of the code executes a function called getunreadcount.

function getunreadcount(accountname)
tyear = year(now()-1)
tmonth = month(now()-1)
if tmonth < 10 then
tmonth = 0 & tmonth
end if
stday = day(now()-1)
if stday < 10 then
stday = 0 & stday
end if
qdatest = tyear & "-" & tmonth & "-" & stday & "T01:00:00Z"
inbstr = accountname
tmailbox = inbstr
inbstr = "file://./backofficestorage/your.domain.com.au/MBX/" & inbstr   ' Use the Primary Exchange SMTP domain name
set WshShell = CreateObject("WScript.Shell")
Set Conn = CreateObject("ADODB.Connection")
Set Rec = CreateObject("ADODB.Record")
Set Rs1 = CreateObject("ADODB.Recordset")
Conn.Provider = "ExOLEDB.DataSource"
Rec.Open inbstr
strView = "SELECT ""DAV:displayname """
strView = strView & "FROM scope ('Shallow traversal of """& inbstr & "/inbox""') "
strview = strview & "WHERE ""urn:schemas:httpmail:read"" = false and (""urn:schemas:httpmail:datereceived"" > CAST(""" & qdatest & """ as 'dateTime'))"
rs1.CursorLocation = 3 'adUseServer = 2, adUseClient = 3
rs1.CursorType = 3
Rs1.Open strView, Rec.ActiveConnection, 2
unreadcount = rs1.recordcount
rs1.close
strView = "SELECT ""DAV:displayname """
strView = strView & "FROM scope ('Shallow traversal of """& inbstr & "/inbox""') "
strview = strview & "WHERE (""urn:schemas:httpmail:datereceived"" > CAST(""" & qdatest & """ as 'dateTime'))"
Rs1.Open strView, Rec.ActiveConnection, 2
mailcount = rs1.recordcount
if mailcount = unreadcount and unreadcount > 10 then
wfile.writeline(accountname & "," & unreadcount & "," & mailcount)
end if
set rs1 = nothing
set rec = nothing
set conn = nothing
getunreadcount = unreadcount

end function
 

The above function opens up a Exoledb connection to the mailbox based on the Samaccount name (which should be the same as the Exchange alias name if not your in trouble and your going to have to do some mods to the query). Once the Exoldeb connection is opened to the mailbox a query is done for all the unread items in the mailbox over the past 50 days and the record-count of this record-set is then stored in a variable.  Another query is then performed to get the total number of emails in the inbox over the past 50 days. These two values are compared if they are the same then the name of the account, number of items and number of unread email is written to the text file. To guard against false triggers by empty mailboxes the mailbox has to have 10 or more unread emails to qualify to be written to the report file (this can be adjusted to what ever value you need)  So once the script has finished running you end up with a text file containing the accounts that are collecting email that nobody is reading. One caveat for this script is if somebody has a inbox rule that fires on all email that is moving the email into another folder that isn't the inbox then this script may be ineffective.

Installing and using the script

As I mentioned above before you use this script you need to modify the sections highlighted in red to reflect you own Active Directory domain name  and Exoledb path URL for your Exchange server. I've use the file:// url which means this script needs to run on the Exchange server where the mailboxes your reporting on are located. You need to modify this part of the code to include your full mailbox domain name (if your not sure what it is have a look at the M: drive on your server).
The other thing to consider with this script is it must be run with an account that has full rights to all users mailboxes( this used to be referred to as service level access in Exchange 5.5). In Exchange 2000 no accounts have these rights by default and administrative accounts are specifically denied this right. Have a look at Microsoft Qbase article which discus's various methods that can be used to grant these rights.

Once this is done you should be able to execute or schedule this script, the only other thing that I've added in the environments where I have used this script is some simple CDO code to email the results of the file to a mailbox when its finished.

Download Scripts