Friday, November 24, 2006

Reporting on the age of content in mailbox folders and across an Exchange Server via a script

Like many of us the age of the content in mailboxes is constantly aging the usefulness of aged content on expensive storage is an always a elemental question for those people who are grasping at trying to manage mail-store usage. If your still running Exchange 2000 then you maybe battling with a very finite amount of storage asking your service provider to please make it last another six months (why me!!). So if this is the case you need to arm your users (and yourself) with maybe a little more information about where the storage in your mailboxes is being used and where the growth curves have happened over the years they have been commanding the storage in their mailbox.

So enter this script, what this script does is scans every single item in a mailbox and looks at the creation date of this item. It then aggregates the size of the item and the item count into one of 3 categories 0-1 year old , 1-2 years old and over 2 years old. The script then produces a little html report that shows for each folder what this size of and total number of items are in this folder over these three time periods.

The time it takes to scan every item in a mailbox means that if you have large mailboxes this script will take a very long time to run. On a server with a large number of mailboxes it probably not practical. But it can be handy to get a farily detail report on a single mailbox and it can be good on a small mail server with not too many mailboxes if you have time to run it. The script itself uses WebDAV to search firstly the folder hierarchy and then search each individual folder. I’ve come up with two versions of the script the first Is a version that uses Form Based Authentication and just scans one configured mailbox. The other version takes the servername of a sever you want to run it against as a commandline parameter and then querys for all mailboxes that are visible in the GAL on this server. It connects to and scans every mailbox using the Admin virtual root which means the script can run with delegated Exchange admin rights.

The FBA version of the script creates a htm report for the user it’s configured to run against in the c:\temp directory of the server. The server version creates a separate report for each user it runs against and creates one report that shows the totals for every mailbox across each time period.

To use the FBA version you need to configure the authentication details to use for the synthetic form logon and the mailbox and server you want to connect to in the following line

snServername = "servername.com"
mnMailboxname = "mailboxname"
username = "username"
domain = "domain"
strpassword = "password"


To run the ageautserver.vbs script it takes one command line parameter which is the name of the server you want to run it against (eg cscript ageautserver.vbs servername). 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

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

to

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

The script is pretty verbose which could be cut down because it was such a long running script I kind of preferred that it at least told me what it was doing while it was taking so long.

I’ve put a downloadable version of the code here the server version looks like

on error resume next
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"">Over 2 Years Old</font></b></td>" & vbcrlf
treport = treport & "<td align=""center"" bgcolor=""#000080"" colspan=""2""><b><font
color=""#FFFFFF"">1-2 Years Old</font></b></td>" & vbcrlf
treport = treport & "<td align=""center"" bgcolor=""#000080"" colspan=""2""><b><font
color=""#FFFFFF"">0-1 Years Old</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 & "<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"">Over 2 Years Old</font></b></td>" &
vbcrlf
report = report & "<td align=""center"" bgcolor=""#000080""
colspan=""2""><b><font color=""#FFFFFF"">1-2 Years Old</font></b></td>" & vbcrlf
report = report & "<td align=""center"" bgcolor=""#000080""
colspan=""2""><b><font color=""#FFFFFF"">0-1 Years Old</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 & "<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 & replace(paddress,"SMTP:","")

next
ReDim tresarray(1,6)
wscript.echo falias
call RecurseFolder(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"">" & tresarray(0,3) & "&nbsp;</td>" &
vbcrlf
treport = treport & "<td align=""center"">" &
FormatNumber(tresarray(1,3)/1024/1024,2) & "&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\mailboxage.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</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",-24,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",-24,now()) And
CDate(DateSerial(mid(oNode3.text,1,4),
mid(oNode3.text,6,2),mid(oNode3.text,9,2))) < dateadd("m",-12,now()) Then
resarray(0,2) = resarray(0,2) + 1
resarray(1,2) = resarray(1,2) + 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",-12,now()) And
CDate(DateSerial(mid(oNode3.text,1,4),
mid(oNode3.text,6,2),mid(oNode3.text,9,2))) < now() Then
resarray(0,3) = resarray(0,3) + 1
resarray(1,3) = resarray(1,3) + 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)
tresarray(0,3) = tresarray(0,3) + resarray(0,3)
tresarray(1,3) = tresarray(1,3) + resarray(1,3)
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 & "<td align=""center"">" & resarray(0,3) & "&nbsp;</td>" &
vbcrlf
report = report & "<td align=""center"">" &
FormatNumber(resarray(1,3)/1024/1024,2) & "&nbsp;</td>" & vbcrlf
report = report & "</tr>" & vbcrlf
end sub
 

24 comments:

Briam said...

Very cool script. This will be great for helping keep our 'special' users in check. Is there a quick way to set a total for each user on their individual page?

Glen said...

Depends what you want to total. Eg do you want to total each year, all years or all folders for all years

Cheers
Glen

Briam said...

Actually, once the script finished the final summary page showed me exactly what I needed.

I tried running the solo mailbox, but I get the following.

C:\temp\MailboxItemAge\SOLO.vbs(61, 4) msxml3.dll: The download of the specified
resource has failed.

...Any thoughts? Here are the vars I am using....

snServername = "atlexmb01" (also tried fully qualified)
mnMailboxname = "ub8240j" (yeah...our ids/mailbox names suck)
username = "ub8240j"
domain = "CORP"
strpassword = "***********"

...any thoughts? (Forms Based is ON, on this server)

Glen said...

That error is permissions related it can also happen when you are using Self signed certificates on your server and you are get a warning popups when you normally access OWA (eg certificate not issue by a trusted provider etc) because the object cant deal with these type of popup messages that code will usually through this type of error. Importing the certificate can sometimes fix this if it is permission related have a look in the event log on the server this will usually tell you why a logon is failing.

Cheers
Glen

Anonymous said...

Can you change the report values to say.. 2months, 4 months, etc.?

thanks

Anonymous said...

I am running the script as instructed.

cscript ageautserver.vbs "servername"

I get the html output files of the mailbox but they are empty.

how can i fix this?

Great idea by the way.

Anonymous said...

I also running the server based script as detailed but getting empty results files.

Any ideas?

Exchange 2000 SP3 on Win2000 SP4 script running from an XP SP2 Client

User running script has full mailbox rights.

btw great work on this site. You've made my job soooo much easier. ;-)

Glen said...

To change the Date and Time groups all you need to do is change the 3 lines that contain the following

dateadd("m",-24,now())

There are two line like this and one line

dateadd("m",-12,now())

This creates a range the logic statement looks at to determine what it will start to argregate. To change it to 2 or 4 months just change 24 to 4.

dateadd("m",-4,now())

Glen said...

To fix the problems with no output the first thing to do would be remove or rem the first line

on error resume next (this is a bit of lazy coding technique)

After you get rid of this line you should see any errors that get generated. There are a few possiblities what might be happening. Watch out for permissions because it uses the Admin virtual root you do need to use an account that has at least veiw only admin Exchange rights. (the account shouldn't need to have full access to all the mailboxes the admin virtual root allows you to bypass this). If you running Exchange 2000 you also need to be carefull because i used the Email address to connect to the mailboxes which wont work in 2000. In 2000 you should just use the mailnickname instead. I've moded a version for 2000 and posted it http://msgdev.mvps.org/exdevblog/ageautserver2000.zip.

Hope this helps please let me know how it goes

Cheers
Glen

Anonymous said...

Have tried the alternative version of the script for Exchange 2000. I get data for some users now but none for others. Strange.

Tried turning off the "..resume next" to look for an error but none shows.

Can't see any difference in the user setup.

IanE

Sean said...

This script is great, but I'm after a way to find out how old unread items are within a users inbox. Basically its to keep track of who is checking their mail and whose not replying to their email..

any ideas?

thanks

Sean

Glen said...

Have a look at http://gsexdev.blogspot.com/2004/06/finding-unused-mailboxes-with-cdo-12.html this sounds like what you want to do.

Cheers
Glen

Adam said...

This is what I have been looking for.

But, I need >1 year, >5 years and >10 years.

How would this be done?

Thanks
Adam

Glen said...

You need to change the filtering section to match the logic of the dates you want to search for example

If CDate(DateSerial(mid(oNode3.text,1,4), mid(oNode3.text,6,2),mid(oNode3.text,9,2))) < dateadd("m",-120,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",-60,now()) And CDate(DateSerial(mid(oNode3.text,1,4), mid(oNode3.text,6,2),mid(oNode3.text,9,2))) < dateadd("m",-12,now()) Then
resarray(0,2) = resarray(0,2) + 1
resarray(1,2) = resarray(1,2) + 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",-12,now()) And CDate(DateSerial(mid(oNode3.text,1,4), mid(oNode3.text,6,2),mid(oNode3.text,9,2))) < now() Then
resarray(0,3) = resarray(0,3) + 1
resarray(1,3) = resarray(1,3) + Int(oNode2.text)
End if

Adam said...

Can this be changed to look at the Sent date? In my enviroment the Modified date has been changed due to a migration from Domino.

You have it counting the IPM.Note message class, how can the IPM.Note.EnterpriseVault.Shortcut message class be added?

dnacool1 said...

I am trying to use this script on Exchange 2010 SP1, and it runs fine, but it only shows "0"'s for the users in that it listed. Any ideas where to start looking ? I'm not a strong vb scripter by any means, but any assistance or direction you could offer would be great !

Zeek said...

This would be great, but I too am getting just 0s for the Size column, and blank for #Messages when running it on an individual mailbox.

Zeek said...

Oh, guess details would be good. Exchange 2010 SP3 on Windows server 2008 R2

Anonymous said...

Any Response on this - I too get all 0 returns, Exchange 2010 SP3 2008R2. When I rem the first line I get the following error -


Script: c:\temp\ageautserver.vbs
Line: 2
Character: 1
Error: Subscript out of range
Code: 800A0009
Source: Miscrosoft VBScript runtime error

Glen Scales said...

To run the ageautserver.vbs script it takes one command line parameter which is the name of the server you want to run it against (eg cscript ageautserver.vbs servername)

Cheers
Glen

Internetdeity said...

Hi Glen! Love the script! Thx! Like some of the other users, i am running the script via cscript ageautserver.vbs and i am getting the html file, but they are all empty with the exception of the header information. I have already removed the 'on error resume next' and i am not getting any errors. I have full access to all mailboxes in our EX2010 environment.

Any additional details? Thanks in advance!

Glen Scales said...

That script use WebDAV so it won't work in Exchange 2010, I have pleny of EWS examples for doing the same type of thing see http://gsexdev.blogspot.com.au/search?q=age

Cheers
Glen

Internetdeity said...

Ah right! Thx Glen! Basically what i am trying to do is find out the data size of all mail in the 'Sent Items' folder which is over 2 years old. We currently have 27,000 mailboxes - any ideas?

Glen Scales said...

Develop your own EWS script eg something like https://www.dropbox.com/s/olie0t2hu59om6l/SentItemsex.txt?dl=0