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"">Mailbox
Name</td>" & vbcrlf
treport = treport & "<td align=""center"" bgcolor=""#000080"" colspan=""2"">Less Than 6 Months</td>" & vbcrlf
treport = treport & "<td align=""center"" bgcolor=""#000080"" colspan=""2"">Greator Than 6 Months</td>" & vbcrlf
treport = treport & "<td align=""center"" bgcolor=""#000080"">OOF
Status</td>" & vbcrlf
treport = treport & "<td align=""center"" bgcolor=""#000080"">Last
Sent</td>" & vbcrlf
treport = treport & "</tr>" & vbcrlf
treport = treport & " <tr>" & vbcrlf
treport = treport & "<td align=""center"" bgcolor=""#000080""> </td>"
& vbcrlf
treport = treport & "<td align=""center"" bgcolor=""#000080"">#Messages</td>"
& vbcrlf
treport = treport & "<td align=""center"" bgcolor=""#000080"">Size(MB)</td>"
& vbcrlf
treport = treport & "<td align=""center"" bgcolor=""#000080"">#Messages</td>"
& vbcrlf
treport = treport & "<td align=""center"" bgcolor=""#000080"">Size(MB)</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"">Folder
Name</td>" & vbcrlf
report = report & "<td align=""center"" bgcolor=""#000080"" colspan=""2"">Less Than 6 Months</td>" & vbcrlf
report = report & "<td align=""center"" bgcolor=""#000080"" colspan=""2"">Greator Than 6 Months</td>" & vbcrlf
report = report & "</tr>" & vbcrlf
report = report & " <tr>" & vbcrlf
report = report & "<td align=""center"" bgcolor=""#000080""> </td>"
& vbcrlf
report = report & "<td align=""center"" bgcolor=""#000080"">#Messages</td>"
& vbcrlf
report = report & "<td align=""center"" bgcolor=""#000080"">Size(MB)</td>"
& vbcrlf
report = report & "<td align=""center"" bgcolor=""#000080"">#Messages</td>"
& vbcrlf
report = report & "<td align=""center"" bgcolor=""#000080"">Size(MB)</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 &
" </td>" & vbcrlf
treport = treport & "<td align=""center"">" & tresarray(0,1) & " </td>" &
vbcrlf
treport = treport & "<td align=""center"">" &
FormatNumber(tresarray(1,1)/1024/1024,2) & " </td>" & vbcrlf
treport = treport & "<td align=""center"">" & tresarray(0,2) & " </td>" &
vbcrlf
treport = treport & "<td align=""center"">" &
FormatNumber(tresarray(1,2)/1024/1024,2) & " </td>" & vbcrlf
treport = treport & "<td align=""center"">" & offstat & " </td>" & vbcrlf
treport = treport & "<td align=""center"">" & LastSent & " </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,""))
& " </td>" & vbcrlf
report = report & "<td align=""center"">" & resarray(0,1) & " </td>" &
vbcrlf
report = report & "<td align=""center"">" &
FormatNumber(resarray(1,1)/1024/1024,2) & " </td>" & vbcrlf
report = report & "<td align=""center"">" & resarray(0,2) & " </td>" &
vbcrlf
report = report & "<td align=""center"">" &
FormatNumber(resarray(1,2)/1024/1024,2) & " </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

How to test SMTP using Opportunistic TLS with Powershell and grab the public certificate a SMTP server is using

Most email services these day employ Opportunistic TLS when trying to send Messages which means that wherever possible the Messages will be encrypted rather then the plain text legacy of SMTP. This method was defined in RFC 3207 "SMTP Service Extension for Secure SMTP over Transport Layer Security" and there's a quite a good explanation of Opportunistic TLS on Wikipedia https://en.wikipedia.org/wiki/Opportunistic_TLS . This is used for both Server to Server (eg MTA to MTA) and Client to server (Eg a Message client like Outlook which acts as a MSA) the later being generally Authenticated. Basically it allows you to have a normal plain text SMTP conversation that is then upgraded to TLS using the STARTTLS verb. Not all servers will support this verb so if its not supported then a message is just sent as Plain text. TLS relies on PKI certificates and the administrative issue s that come around certificate management like expired certificates which is why I wrote th

Glen's Exchange and Office 365 Dev Blog

Search This Blog

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

Labels

Popular posts from this blog

The MailboxConcurrency limit and using Batching in the Microsoft Graph API

Sending a Message in Exchange Online via REST from an Arduino MKR1000

How to test SMTP using Opportunistic TLS with Powershell and grab the public certificate a SMTP server is using