Wednesday, May 16, 2007

Unread Email report for all Folders in all Mailboxes on an Exchange Server

Unread email is such an analogy for a lot of the problems that plague email systems. Eg if you have a piece of email that is sitting unread in your inbox for a long period of time what’s the point of you receiving this email or the person who sent it sending it. Read recipients aside most people believe that when they send an email no matter how trivial the content that you will read or delete it. In a sense unread email is limbo information serving no higher purpose then clogging up your inbox kind of like pages that are stuck together in a book. Eg how will you know if you need to keep a piece of information if you have never bothered to actually read it? Having seen the inside of far too many people’s mailboxes it seems that a lot of people have developed a seventh sense for information they haven’t read but might need to know in the future. So one Interesting Report you might want to create is to look at how much space this limbo information is taking up.

The following is a script that produces a report of all the unread email in every folder on all mailboxes on a server. It’s based around the mailbox connect age script I posted a while ago but queries the folders to look at messages where the read property urn:schemas:httpmail:read is set to false. To make this a little bit more usefully the script also queries the users OOF State so you can see from the report that the reason a user may have a large number of unread email is because they are out of the office. Also the sent items folder is also queried and the date of the last email sent is also included in the report which might help indentifying any mailboxes that may not be used. The script breaks down the unread email a little further by showing the number and size of unread email that is 6 months old and the number and size of the unread email that is greater than 6 month old

Like the age content report the script produces a combined report for all mailboxes on a server as well as separate reports for each user that breaks down the unread email by folder.

The Script uses WebDAV via the Admin virtual root directory to access the user Mailboxes this gets around the need for the user running the script to have rights in the users mailbox and should be able to be run successfully using just delegated Exchange Admin rights. To work out the correct path to use for the Admin virtual root the script includes a LDAP query that gets the default SMTP FQDN from the default recipient policy.

By default the script isn’t using SSL which may mean you need to adjust the following line if you are using SSL on the ExAdmin Directory Eg change line 65

falias = "http://" & servername & "/exadmin/admin/" & dpDefaultpolicy & "/mbx/"

to

falias = "https://" & servername & "/exadmin/admin/" & dpDefaultpolicy & "/mbx/"

If you are still using Exchange 2000 you need to change line 67 in the script to

if instr(paddress,"SMTP:") then falias = falias & left(replace(paddress,"SMTP:",""),(instr(replace(paddress,"SMTP:",""),"@")-1))


The script requires one command-line parameter which is the name of the server you want to run the script against eg cscript ureadrep.vbs yourservername

I’ve put a downloadable copy of the script here the script itself look like

on error resume Next
set shell = createobject("wscript.shell")
strValueName = "HKLM\SYSTEM\CurrentControlSet\Control\TimeZoneInformation\ActiveTimeBias"
minTimeOffset = shell.regread(strValueName)
toffset = datediff("h",DateAdd("n", minTimeOffset, now()),now())

Servername = wscript.arguments(0)
treport = "<table border=""1"" width=""100%"">" & vbcrlf
treport = treport & " <tr>" & vbcrlf
treport = treport & "<td align=""center"" bgcolor=""#000080""><b><font color=""#FFFFFF"">Mailbox
Name</font></b></td>" & vbcrlf
treport = treport & "<td align=""center"" bgcolor=""#000080"" colspan=""2""><b><font
color=""#FFFFFF"">Less Than 6 Months</font></b></td>" & vbcrlf
treport = treport & "<td align=""center"" bgcolor=""#000080"" colspan=""2""><b><font
color=""#FFFFFF"">Greator Than 6 Months</font></b></td>" & vbcrlf
treport = treport & "<td align=""center"" bgcolor=""#000080""><b><font color=""#FFFFFF"">OOF
Status</font></b></td>" & vbcrlf
treport = treport & "<td align=""center"" bgcolor=""#000080""><b><font color=""#FFFFFF"">Last
Sent</font></b></td>" & vbcrlf
treport = treport & "</tr>" & vbcrlf
treport = treport & " <tr>" & vbcrlf
treport = treport & "<td align=""center"" bgcolor=""#000080""><b><font color=""#FFFFFF"">&nbsp;</font></b></td>"
& vbcrlf
treport = treport & "<td align=""center"" bgcolor=""#000080""><b><font color=""#FFFFFF"">#Messages</font></b></td>"
& vbcrlf
treport = treport & "<td align=""center"" bgcolor=""#000080""><b><font color=""#FFFFFF"">Size(MB)</font></b></td>"
& vbcrlf
treport = treport & "<td align=""center"" bgcolor=""#000080""><b><font color=""#FFFFFF"">#Messages</font></b></td>"
& vbcrlf
treport = treport & "<td align=""center"" bgcolor=""#000080""><b><font color=""#FFFFFF"">Size(MB)</font></b></td>"
& vbcrlf
treport = treport & "</tr>" & vbcrlf
set req = createobject("microsoft.xmlhttp")
set com = createobject("ADODB.Command")
set conn = createobject("ADODB.Connection")
Set iAdRootDSE = GetObject("LDAP://RootDSE")
strNameingContext = iAdRootDSE.Get("configurationNamingContext")
strDefaultNamingContext = iAdRootDSE.Get("defaultNamingContext")
Conn.Provider = "ADsDSOObject"
Conn.Open "ADs Provider"
polQuery = "<LDAP://" & strNameingContext & ">;(&(objectCategory=msExchRecipientPolicy)(cn=Default
Policy));distinguishedName,gatewayProxy;subtree"
svcQuery = "<LDAP://" & strNameingContext & ">;(&(objectCategory=msExchExchangeServer)(cn="
& Servername & "));cn,name,legacyExchangeDN;subtree"
Com.ActiveConnection = Conn
Com.CommandText = polQuery
Set plRs = Com.Execute
while not plRs.eof
for each adrobj in plrs.fields("gatewayProxy").value
if instr(adrobj,"SMTP:") then dpDefaultpolicy =
right(adrobj,(len(adrobj)-instr(adrobj,"@")))
next
plrs.movenext
wend
wscript.echo dpDefaultpolicy
Com.CommandText = svcQuery
Set Rs = Com.Execute
while not rs.eof
GALQueryFilter = "(&(&(&(& (mailnickname=*)(!msExchHideFromAddressLists=TRUE)(|
(&(objectCategory=person)(objectClass=user)(msExchHomeServerName=" &
rs.fields("legacyExchangeDN") & ")) )))))"
strQuery = "<LDAP://" & strDefaultNamingContext & ">;" & GALQueryFilter & ";displayname,mail,distinguishedName,mailnickname,proxyaddresses;subtree"
com.Properties("Page Size") = 100
Com.CommandText = strQuery
Set Rs1 = Com.Execute
while not Rs1.eof
report = "<table border=""1"" width=""100%"">" & vbcrlf
report = report & " <tr>" & vbcrlf
report = report & "<td align=""center"" bgcolor=""#000080""><b><font color=""#FFFFFF"">Folder
Name</font></b></td>" & vbcrlf
report = report & "<td align=""center"" bgcolor=""#000080"" colspan=""2""><b><font
color=""#FFFFFF"">Less Than 6 Months</font></b></td>" & vbcrlf
report = report & "<td align=""center"" bgcolor=""#000080"" colspan=""2""><b><font
color=""#FFFFFF"">Greator Than 6 Months</font></b></td>" & vbcrlf
report = report & "</tr>" & vbcrlf
report = report & " <tr>" & vbcrlf
report = report & "<td align=""center"" bgcolor=""#000080""><b><font color=""#FFFFFF"">&nbsp;</font></b></td>"
& vbcrlf
report = report & "<td align=""center"" bgcolor=""#000080""><b><font color=""#FFFFFF"">#Messages</font></b></td>"
& vbcrlf
report = report & "<td align=""center"" bgcolor=""#000080""><b><font color=""#FFFFFF"">Size(MB)</font></b></td>"
& vbcrlf
report = report & "<td align=""center"" bgcolor=""#000080""><b><font color=""#FFFFFF"">#Messages</font></b></td>"
& vbcrlf
report = report & "<td align=""center"" bgcolor=""#000080""><b><font color=""#FFFFFF"">Size(MB)</font></b></td>"
& vbcrlf
report = report & "</tr>" & vbcrlf
falias = "http://" & servername & "/exadmin/admin/" & dpDefaultpolicy & "/mbx/"
for each paddress in rs1.fields("proxyaddresses").value
if instr(paddress,"SMTP:") then falias = falias & left(replace(paddress,"SMTP:",""),(instr(replace(paddress,"SMTP:",""),"@")-1))
next
ReDim tresarray(1,6)
wscript.echo falias
offstat = chkoof(falias & "/" & "non_ipm_subtree/")
call RecurseFolder(falias)
LastSent = getlastsent(falias)
report = report & "</table>" & vbcrlf
Set fso = CreateObject("Scripting.FileSystemObject")
set wfile = fso.opentextfile("c:\temp\" & rs1.fields("mail").value &
".htm",2,true)
wfile.write report
wfile.close
set wfile = nothing
treport = treport & "<tr>" & vbcrlf
treport = treport & "<td align=""center"">" & rs1.fields("mail").value &
"&nbsp;</td>" & vbcrlf
treport = treport & "<td align=""center"">" & tresarray(0,1) & "&nbsp;</td>" &
vbcrlf
treport = treport & "<td align=""center"">" &
FormatNumber(tresarray(1,1)/1024/1024,2) & "&nbsp;</td>" & vbcrlf
treport = treport & "<td align=""center"">" & tresarray(0,2) & "&nbsp;</td>" &
vbcrlf
treport = treport & "<td align=""center"">" &
FormatNumber(tresarray(1,2)/1024/1024,2) & "&nbsp;</td>" & vbcrlf
treport = treport & "<td align=""center"">" & offstat & "&nbsp;</td>" & vbcrlf
treport = treport & "<td align=""center"">" & LastSent & "&nbsp;</td>" & vbcrlf
treport = treport & "</tr>" & vbcrlf
rs1.movenext
wend
rs.movenext
wend
rs.close
set conn = nothing
set com = nothing
treport = treport & "</table>" & vbcrlf
Set fso = CreateObject("Scripting.FileSystemObject")
set wfile = fso.opentextfile("c:\temp\Server-" & Servername &
"-UnreadReport.htm",2,true)
wfile.write treport
wfile.close
set wfile = nothing
set fso = nothing

Public Sub RecurseFolder(sUrl)

req.open "SEARCH", sUrl, False, "", ""
sQuery = "<?xml version=""1.0""?>"
sQuery = sQuery & "<g:searchrequest xmlns:g=""DAV:"">"
sQuery = sQuery & "<g:sql>SELECT ""http://schemas.microsoft.com/"
sQuery = sQuery & "mapi/proptag/x0e080003"", ""DAV:hassubs"" FROM SCOPE "
sQuery = sQuery & "('SHALLOW TRAVERSAL OF """ & sUrl & """') "
sQuery = sQuery & "WHERE ""DAV:isfolder"" = true and ""DAV:ishidden"" = false
and ""http://schemas.microsoft.com/mapi/proptag/x36010003"" = 1"
sQuery = sQuery & "</g:sql>"
sQuery = sQuery & "</g:searchrequest>"
req.setRequestHeader "Content-Type", "text/xml"
req.setRequestHeader "Translate", "f"
req.setRequestHeader "Depth", "0"
req.setRequestHeader "Content-Length", "" & Len(sQuery)
req.send sQuery
Set oXMLDoc = req.responseXML
Set oXMLSizeNodes = oXMLDoc.getElementsByTagName("d:x0e080003")
Set oXMLHREFNodes = oXMLDoc.getElementsByTagName("a:href")
Set oXMLHasSubsNodes = oXMLDoc.getElementsByTagName("a:hassubs")
For i = 0 to oXMLSizeNodes.length - 1
call procfolder(oXMLHREFNodes.Item(i).nodeTypedValue,sUrl)
wscript.echo oXMLHREFNodes.Item(i).nodeTypedValue
If oXMLHasSubsNodes.Item(i).nodeTypedValue = True Then
call RecurseFolder(oXMLHREFNodes.Item(i).nodeTypedValue)
End If
Next
End Sub

sub procfolder(strURL,pfname)
wscript.echo strURL
ReDim resarray(1,6)
strQuery = "<?xml version=""1.0""?><D:searchrequest xmlns:D = ""DAV:""
xmlns:b=""urn:uuid:c2f41010-65b3-11d1-a29f-00aa00c14882/"">"
strQuery = strQuery & "<D:sql>SELECT ""DAV:displayname"",
""urn:schemas:httpmail:subject"", "
strQuery = strQuery & """DAV:creationdate"", ""DAV:getcontentlength"", "
strQuery = strQuery & """urn:schemas:httpmail:fromemail"""
strQuery = strQuery & " FROM scope('shallow traversal of """
strQuery = strQuery & strURL & """') Where ""DAV:ishidden"" = False AND
""DAV:isfolder"" = False AND ""urn:schemas:httpmail:read"" =
False</D:sql></D:searchrequest>"
req.open "SEARCH", strURL, false
req.setrequestheader "Content-Type", "text/xml"
req.setRequestHeader "Translate","f"
req.send strQuery
If req.status >= 500 Then
ElseIf req.status = 207 Then
set oResponseDoc = req.responseXML
set oNodeList = oResponseDoc.getElementsByTagName("a:displayname")
set oNodeList1 = oResponseDoc.getElementsByTagName("a:href")
set oSize = oResponseDoc.getElementsByTagName("a:getcontentlength")
set odatereceived = oResponseDoc.getElementsByTagName("a:creationdate")
For i = 0 To (oNodeList.length -1)
set oNode = oNodeList.nextNode
set oNode1 = oNodeList1.nextNode
set oNode2 = oSize.nextNode
set oNode3 = odatereceived.nextNode
wscript.echo oNode3.text
If CDate(DateSerial(mid(oNode3.text,1,4),
mid(oNode3.text,6,2),mid(oNode3.text,9,2))) > dateadd("m",-6,now()) Then
resarray(0,1) = resarray(0,1) + 1
resarray(1,1) = resarray(1,1) + Int(oNode2.text)
End if
If CDate(DateSerial(mid(oNode3.text,1,4),
mid(oNode3.text,6,2),mid(oNode3.text,9,2))) < dateadd("m",-6,now()) Then
resarray(0,2) = resarray(0,2) + 1
resarray(1,2) = resarray(1,2) + Int(oNode2.text)
End if
Next
Else
End If
tresarray(0,1) = tresarray(0,1) + resarray(0,1)
tresarray(1,1) = tresarray(1,1) + resarray(1,1)
tresarray(0,2) = tresarray(0,2) + resarray(0,2)
tresarray(1,2) = tresarray(1,2) + resarray(1,2)
report = report & "<tr>" & vbcrlf
report = report & "<td align=""center"">" & unescape(Replace(strURL,falias,""))
& "&nbsp;</td>" & vbcrlf
report = report & "<td align=""center"">" & resarray(0,1) & "&nbsp;</td>" &
vbcrlf
report = report & "<td align=""center"">" &
FormatNumber(resarray(1,1)/1024/1024,2) & "&nbsp;</td>" & vbcrlf
report = report & "<td align=""center"">" & resarray(0,2) & "&nbsp;</td>" &
vbcrlf
report = report & "<td align=""center"">" &
FormatNumber(resarray(1,2)/1024/1024,2) & "&nbsp;</td>" & vbcrlf
report = report & "</tr>" & vbcrlf
end sub

Function chkoof(mburl)

xmlstr = "<?xml version='1.0' encoding='UTF-8' ?>" _
& "<a:propfind xmlns:a='DAV:' xmlns:b='urn:schemas-microsoft-com:datatypes'>" _

& "<a:prop xmlns:d='http://schemas.microsoft.com/exchange/'>" _
& "<d:oof-state/>" _
& "</a:prop>" _
& "</a:propfind>"
req.open "PROPFIND", mburl, false
req.setRequestHeader "Content-Type", "text/xml; charset=""UTF-8"""
req.setRequestHeader "Depth", "0"
req.setRequestHeader "Translate", "f"
req.send xmlstr
set oResponseDoc = req.responseXML
set osubNodeList = oResponseDoc.getElementsByTagName("d:oof-state/")
ntar = osubNodeList.length -1
wscript.Echo "********** OOf-State :" & osubNodeList.Item(ntar).text
chkoof = osubNodeList.Item(ntar).text
End Function

function getlastsent(mburl)
getlastsent = "N/A"
strURL = mburl & "/Sent Items/"
strQuery = "<?xml version=""1.0""?><D:searchrequest xmlns:D = ""DAV:""
xmlns:b=""urn:uuid:c2f41010-65b3-11d1-a29f-00aa00c14882/"">"
strQuery = strQuery & "<D:sql>SELECT ""DAV:displayname"",
""urn:schemas:httpmail:subject"", "
strQuery = strQuery & """DAV:creationdate"", ""DAV:getcontentlength"", "
strQuery = strQuery & """urn:schemas:httpmail:fromemail"",
""urn:schemas:mailheader:date"""
strQuery = strQuery & " FROM scope('shallow traversal of """
strQuery = strQuery & strURL & """') Where ""DAV:ishidden"" = False AND
""DAV:isfolder"" = False</D:sql></D:searchrequest>"
req.open "SEARCH", strURL, false
req.setrequestheader "Content-Type", "text/xml"
req.setRequestHeader "Translate","f"
req.setRequestHeader "Range", "rows=0-0"
req.send strQuery
If req.status >= 500 Then
ElseIf req.status = 207 Then
set oResponseDoc = req.responseXML
set oNodeList = oResponseDoc.getElementsByTagName("e:date")
For i = 0 To (oNodeList.length -1)
set oNode = oNodeList.nextNode
wscript.echo "*******Last Sent :" & oNode.text
getlastsent =
dateadd("h",toffset,DateSerial(Mid(oNode.text,1,4),Mid(oNode.text,6,2),Mid(oNode.text,9,2))
& " " & Mid(oNode.text,12,8))
Next
Else
End If
end function

31 comments:

Neill T. said...

Another fine script Glen. Now I just have to work out how to hack it to do a CSV rather than an HTM file to do data sorting. :-)

Marlon D said...

Nice One!
For those too lazy to read the script (like me!) you'll find the reports in the C:\TEMP folder!!
to modify this location, modify the 2 instances of
set wfile = fso.opentextfile("c:\temp\" ...

Jam said...

Glen,

This script would be a life saver if I could get it to work fully. I'm having problems getting the reports to populate with data. The script creates the HTML files without any problems, listing all active mailboxes within the domain etc. so it looks as though its accessing the server succesfully. It's just not reporting the unread mail items. Any ideas ?

Glen said...

Try to Rem out the

On error resume next

lines this will allow you to see what errors are being generated.

Cheers
Glen

Jam said...

Thanks Glen,
Looks like an exchange permissions problem. Error message generated as shown below. Any ideas on what permissions its trying to use so as I can review these on my Exchange box ? Thanks in advance.
James
===============================

>cscript ureadrep.vbs ExchServer

Microsoft (R) Windows Script Host Version 5.6 Copyright (C) Microsoft Corporation 1996-2001. All rights reserved.

domain.com

http://ExchServer/exadmin/admin/domain.com/mbx/Administrator@domain.com

>ureadrep.vbs(195, 1) msxml3.dll: Access is denied.

Glen said...

This script uses the Admin virtual Root so you need to be using an account that has been delegated Exchange Admin rights (eg an account that has rights to use Exchange System Manager).

Cheers
Glen

Jam said...

Thanks Glen. I've actually been running directly on the Exchange box logged in as domain admin which has full access to Exchange System manager. Perhpas exchange permissions is a red herring, as the error refers to and XML DLL file - could permissions be missing on that file, or perhaps something that DLL calls ?

Glen said...

Try changing the URL from http to https. Its possible that you are only allowing https to this Virtual directory.

Cheers
Glen

Jam said...

Apologies Glen. Yes I should have tried this as per your original post. Applying this change for SSL, the error message has now changed as shown below. Any ideas why the download would fail (access of some sort again I imagine - but where to look ?).
===================================
>ureadrep.vbs(195, 1) msxml3.dll: The download of the specified resource has failed.

Glen said...

That error usually means that you are getting a SSL popup error because you dont trust the certificate being used. The object the script is using cant deal with SSL errors so will error out. Generally to fix this you just need to trust the certificate being used for OWA by importing it in Internet Explorer.

Jam said...

Thanks Glen. We are indeed using SSL for OMA and OWA, with a Cert issued for the external name of our server 'mail.domain.com'. however this does not match the internal hostname of our server 'ExchSvr'. Hence IE always pops the usual certificate warning when you try to access OWA using the hostname (instead of the public DNS name 'mail.domain.com'). I've tried running the script using this 'mail.domain.com' name (which is mapped on our internal DNS servers to the 'ExchSvr' host name ok) but without success. I assume this script relies upon refering to the physical hostname of the server as opposed to the public facing DNS name which our SSL Cert has been issued to? Is there a way around this at all?

Anonymous said...

Can you tell me why I have to hit ok hundreds of times to run this script?

Glen said...

When you run the script use cscript eg cscript scriptname.vbs . This means it will just output to the cmdline instead of poping up everything.

Cheers
Glen

Anonymous said...

Is there a way to use this script but only check directly in the inbox and exclude all subfolders?

Sam said...

Can this output be done in CSV if so can you let me know asap.

HTML output i received shows there are no unread emails which is incorrect anyidea ?

smith.chris said...

getting a msxm13.dll: The system cannot locate the resource specified. I'v registered the dll successfully however the error still comes up. ANy ideas?

Glen said...

This indicates that your using a Self signed certificate or you certificate is invalid. If its self signed try to import this into you browser so its trusted.

Cheers
Glen

Nelly said...

Here is the error that I am getting:

Microsoft VBScript runtime error:
Object required: 'osubNodeList.Item(...)'

Any ideas?

Nelly said...

We imported the self signed certificate from IIS to the IE certificate store and also tried testing for SSL errors by browsing to the url that the script uses:

https:\\servername\exadmin

We did not receive any SSL errors but continue to receive the error mentioned before when running the script. Any ideas?

rubeus said...

I get the error msxm13.dll: System error: -2146697208 any idea what this one means.

Anonymous said...

Second here with 'C:\ureadrep.vbs(199, 1) Microsoft VBScript runtime error: Object required: 'osub
NodeList.Item(...)'
Does it work for Exchange 2007?

Glen said...

If your using 2007 have a look at http://gsexdev.blogspot.com/2008/11/find-unused-mailbox-powershell-gui.html

Cheers
Glen

David said...

Glen,
how to make the script only query read emails in Inbox and Deleted items under the root, excluding subfoldes?

Only report unread emails older than xxx days. I think you have defined age in the script but can't find it.

the most difficult part for me is to remove "read recepient" header in any unread emails if found. Any idea how to do this? Any samples?

Thanks.

Glen said...

What version of Exchange are you using ? 2003 or 2007/2010

Cheers
Glen

Anonymous said...

Glen,

Error message "msxml3.dll: Access is denied" is prmopted. I am using domain administrator account to execute the script.

Any advice? Thanks

Tim said...

Tried to get it to work, but no luck. HTML with headers is created. cscript cscript.echoes our mail domain name, and then exits. Removing on error line and running it again showed no errors.

Anonymous said...

Hi Glen,

Having some issues with running this against a 2010 exchange server. The account i am logging in and running this with is an enterprise admin and has full access rights to the mailboxes but yet no data is returned in any of the html documents.
I have tried to run this in powershell analyzer and it returns a
datediff("h",DateAdd("n", minTimeOffset, now()),now()) Missing Expression after caracter 24 (,)
This is the only error i see. Am i missing something sorry kind of new to all this

Thanks
Matt

Robert Tyler said...

Glenn, I am trying to use your script and have removed the on error and still just get the domain.com the html document still has no data in it

Glen Scales said...

Can you see it processing the Mailboxes ? What version of Exchange are you using ?

Cheers
Glen

Anonymous said...

Hi Glen, nice post. Wondering, can we do it only for a specific list of mailboxes rather than all the mailboxes on a server or the organization. We have the requirement to perform this only for a few mailboxes.

Glen Scales said...

This is very old and will only work on 2007 and older Exchange servers what version are you running ? You would need to rewrite the script to exclude the ADSI query and maybe just read a csv file etc