Tuesday, August 09, 2005

Mailbox Attachment Auditing Script

Mofidifed 15.9.2005 to include not about date format

I had a question from a customer last week about where the folder for attachments was in Exchange so they could go in and delete anything that they thought was a bit old or that they thought they didn’t need. Although these questions at times can make me cringe my explanations of how Exchange goes about storing things and what’s considered best practice when it comes to mail archiving didn’t seem to be getting though so there arose a need to produce some sort of reports so that the customer could at least see what’s going on themselves. This was a small site 10 users so this wasn’t going to be too much information overload so I set to the task of building something to do this. There where two ways I could have gone with this one way would have been to export all the information about the attachments into a database and then do some ASP pages to report on the database. Which wasn’t going to be to practicle because I needed a delivery mechinsm to the client So I decided to go with some data shaping code that would produce a CSV file for each mailbox that was scanned that could then be opened in Excel. Data shaping was good fit because it allowed me to group the data I was going to retrieve in a hierarchal fashion and after some very mundane work or working out attachment classification for each extension type I came up with some relatively useful reports. Also I made the script so it could target specific date periods like attachments more then a year old or you could target attachments from the last week if your store was showing rapid file growth. What did I learn from this well it was interesting to see how many of the same copies of a certain attachments where stored in people’s mailboxes or that one user had decided to store all the NDR’s for a large message that they tried to send (this totaled over a few hundred meg). For me this shows the future of mail storage shouldn’t be an information store or a database but some sort of intelligent content management store. In this way the content you’re storing in more relevant, less redundant and can grow and learn with the user. With everybody storing more and more email data just having a better search engine isn’t going to be good enough. At the coal face you have one person trying to use and control this so having a method where the way in which its stored is more relevant you have half a chance of making it more efficient. I’ve always wondered weather there was a correlation between the amount of data you store and accuracy and efficiency you can achieve when retrieving it. It all shows that no matter how good any system is it’s really down to how you educate the users to weather they will get the best out of it.

The script

I started out using Exoledb and ADO to do this script which did work but because the only way of getting the attachment size via Exoledb (that I know) is to use an ADO stream object which made the code very slow and memory hungry. Once I switched to using Webdav where the X-MS-ENUMATTS method can be used to scan and return all the attachment details on a message this gave a large lift in performance and I was surprised how fast it could actually process very large mailboxes. I’ve included the ADO version in the download of the post but the WebDAV version is the one I’ll talk about because this is the one that I used.

The script itself works by first setting up the data shape that will be used to create the reports. This is done by first setting up some categories for all the files and then setting up child branches for these categories for each file extension. All the extensions are tracked in one variable which is used to then identify any unknown extension types (or ones that I haven’t categorized) and then a category is dynamically created for that. After this the RecurseFolder sub is called this sub is based on the code from the mailbox size KB. With a few exceptions the main one being is that it only checks normal mail folders this was to prevent an issue where searchfolder exist in the mailbox. The ResurseFolder sub job is basically to retrieve all the folder URL’s in the mailbox and then call the procfolder sub. The profolder sub processes all the mail in a folder by looking for all email with attachments between the specified date ranges of the query. The embedattach attachment processes the attachments on each message using the X-MS-ENUMATTS method and also checks and processes any attachments that are on embedded messages attached to the primary message. Because the received time is in UTC when you retrieve it using WebDAV there’s some code to do time conversion and also some other code that cleans the values up a bit so they can be used within the data shape. The script runs pretty verbosely and outputs a lot of stuff to the command-line because this is a script that will take a long period of time to run I find this is important to let you see the progress the script is making. At the end of the script it displays some summaries of all the attachments and also creates a CSV file with the result of all the attachment scans. To make the result more meaningful I added command-line parameters so you could restrict the date range the script will look at and also the minimum size of the attachments to look at. So you can scan for all attachments that are a year old over 500 KB.

I’ve created 4 different version of this script

Mbaudit.vbs in the ADO/Exoldb Version of the script (must be run locally on the Exchange server)

Mbauditwd.vbs is the WebDAV version (using http) can be run locally or remotely

Mbauditwdfba is a WebDAV version if you are using forms based authentication on your Exchange server

Mbauditrep.vbs is the same as Mbauditwd.vbs but has some front end code added that selects all the users on a particular server and will go though each user sequentially and create a report.

To run any of these scripts requires specifying command-line parameters the usage is for (all scripts except Mbauditrep.vbs)

Cscript Mbauditwd.vbs

The start and enddate needs to be in ISO format (year-month-date) eg to scan for all attachments that are a year and old over 500 KB

Cscript Mbauditwd.vbs myserver mymailbox 500 2004-01-01 2005-01-01

The Mbauditrep.vbs script is just run with the servename and size and date parameters

Cscript Mbauditwd.vbs myserver 500 2004-01-01 2005-01-01

A Important note about the date format because this is an ISO date you need to use the date format yyyy-mm-dd (this is different from what your use to if you in the US)

A word of caution with all of these scripts the WebDAV script creates a bit of network traffic and load on the server that it is reporting against and also with the number of requests that are being made if you have logging enabled on the OWA directory it will significantly increase the size of the log file if you scan a number of mailboxes. The amount of testing that has been done with these scripts is very minimal so as always its use at your own risk and make sure you do your own testing.

I’ve put a download copy of the scripts here the script itself looks 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())
set conn1 = createobject("ADODB.Connection")
set req = createobject("microsoft.xmlhttp")
Set fso = CreateObject("Scripting.FileSystemObject")
fname = "c:\" & wscript.arguments(1) & ".csv"
set wfile = fso.opentextfile(fname,2,true)
wfile.writeline("File Group,File Type,File Extension,File Name, File Size, Email
Location,Message Subject,Sent From,Date Sent")
public exlist
public maxsize
public datefrom
public dateto
maxsize = wscript.arguments(2)
datefrom = wscript.arguments(3) & "T00:00:00Z"
dateto = wscript.arguments(4) & "T00:00:00Z"
exlist = ","
strConnString = "Data Provider=NONE; Provider=MSDataShape"
conn1.Open strConnString
set objParentRS = createobject("adodb.recordset")
set objChildRS = createobject("adodb.recordset")
set objgrandchild = createobject("adodb.recordset")
strSQL = "SHAPE APPEND" & _
" NEW adVarChar(255) AS RFileType, " & _
" ((SHAPE APPEND " & _
" NEW adVarChar(255) AS CFileext, " & _
" NEW adVarChar(255) AS CFileType, " & _
" NEW adVarChar(255) AS CFileDesc, " & _
" ((SHAPE APPEND " & _
" NEW adVarChar(255) AS GCFileext, " & _
" NEW adVarChar(255) AS GCFilename, " & _
" NEW adVarChar(255) AS GCFilesize, " & _
" NEW adVarChar(255) AS GCMessLocation, " & _
" NEW adVarChar(255) AS GCMessSubject, " & _
" NEW adVarChar(255) AS GCMessFrom, " & _
" NEW adVarChar(255) AS GCDateSent) " & _
" RELATE CFileext TO GCFileext) AS MOWMI" & _
")" & _
" RELATE RFileType TO CFileType) AS rsSOMO "
objParentRS.LockType = 3
objParentRS.Open strSQL, conn1
rem *********** Add Document Types
call adddoctype(objParentRS,"Microsoft Office Documents")
call adddoctype(objParentRS,"Compressed Files")
call adddoctype(objParentRS,"Acorbat Files")
call adddoctype(objParentRS,"Executables and Installers")
call adddoctype(objParentRS,"Sound Files")
call adddoctype(objParentRS,"Video Files")
call adddoctype(objParentRS,"Image Files")
call adddoctype(objParentRS,"Attached Email Message")
call adddoctype(objParentRS,"Unclassified Files")
rem ********************************************
rem *********** Add Document Extensions"
Set objChildRS = objParentRS("rsSOMO").Value
call addfileexts(objChildRS)
Set objgrandchild = objChildRS("MOWMI").Value
sConnString = "http://" & wscript.arguments(0) & "/exchange/" &
wscript.arguments(1) & "/NON_IPM_SUBTREE"
call RecurseFolder(sConnString,objChildRS,objgrandchild)

objParentRS.MoveFirst
Do While Not objParentRS.EOF
wscript.echo objParentRS(0)
Set objChildRS = objParentRS("rsSOMO").Value
Do While Not objChildRS.EOF
attsum = 0
attnum = 0
Set objgrandchild = objChildRS("MOWMI").Value
attnum = objgrandchild.recordcount
if objgrandchild.recordcount <> 0 then
wscript.echo " " & objChildRS(1) & " " & objChildRS(2)
wfile.writeline(objChildRS(1) & "," & objChildRS(2))
end if
Do While Not objgrandchild.EOF
wscript.echo " " & objgrandchild(1) & " " & objgrandchild(2) & " " &
objgrandchild(3) & " " & objgrandchild(4) & " "
wfile.writeline(",," & objgrandchild(0) & "," & objgrandchild(1) & "," &
objgrandchild(2) & "," & objgrandchild(3) & "," & objgrandchild(4) & "," &
objgrandchild(5) & "," & objgrandchild(6))
attsum = attsum + clng(objgrandchild("GCFilesize"))
objgrandchild.movenext
loop
if attnum <> 0 then
attachmentsumy = attachmentsumy & objChildRS(1) & "," & objChildRS(2) & "," &
objChildRS(0) & "," & attnum & "," & attsum & vbcrlf
end if
objChildRS.movenext
loop
objParentRS.MoveNext
Loop
wscript.echo
wscript.echo "Attachment Summary"
wscript.echo
wscript.echo attachmentsumy
wfile.writeline
wfile.writeline "Attachment Summary"
wfile.writeline
wfile.writeline attachmentsumy

Public Sub RecurseFolder(sUrl,objChildRS,objgrandchild)

Set oXMLHttp = CreateObject("Microsoft.xmlhttp")
oXMLHttp.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>"
oXMLHttp.setRequestHeader "Content-Type", "text/xml"
oXMLHttp.setRequestHeader "Translate", "f"
oXMLHttp.setRequestHeader "Depth", "0"
oXMLHttp.setRequestHeader "Content-Length", "" & Len(sQuery)
oXMLHttp.send sQuery
Set oXMLDoc = oXMLHttp.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,objgrandchild,objChildRS)
wscript.echo oXMLHREFNodes.Item(i).nodeTypedValue
If oXMLHasSubsNodes.Item(i).nodeTypedValue = True Then
call RecurseFolder(oXMLHREFNodes.Item(i).nodeTypedValue,objChildRS,objgrandchild)
End If
Next
End Sub


sub procfolder(strURL,objgrandchild,objChildRS)

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 & """urn:schemas:httpmail:datereceived"", ""urn:schemas:httpmail:fromname"",
"
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 "
strQuery = strQuery & """urn:schemas:httpmail:hasattachment"" = True AND "
strQuery = strQuery & """urn:schemas:httpmail:datereceived"" &lt; CAST(""" &
dateto & """ as 'dateTime') AND "
strQuery = strQuery & """urn:schemas:httpmail:datereceived"" &gt; CAST(""" &
datefrom & """ as 'dateTime')</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 oSubject = oResponseDoc.getElementsByTagName("d:subject")
set odatereceived = oResponseDoc.getElementsByTagName("d:datereceived")
set ofromemail = oResponseDoc.getElementsByTagName("d:fromemail")
For i = 0 To (oNodeList.length -1)
set oNode = oNodeList.nextNode
set oNode1 = oNodeList1.nextNode
set oNode2 = oSubject.nextNode
set oNode3 = odatereceived.nextNode
set oNode4 = ofromemail.nextNode
call
embedattach(oNode1.Text,oNode2.Text,oNode3.Text,oNode4.Text,oNode.Text,objgrandchild,objChildRS)
Next
Else
End If

end sub

sub
embedattach(objhref,
subject,daterecieved,recievedfrom,davdisplay,objgrandchild,objChildRS)
req.open "X-MS-ENUMATTS", objhref, false, "", ""
req.send
If req.status > 207 Or req.status < 207 Then
Else
set resDoc1 = req.responseXML
Set objHrefNodeList = resDoc1.getElementsByTagName("a:href")
Set objattachmethod = resDoc1.getElementsByTagName("d:x37050003")
Set objcnval = resDoc1.getElementsByTagName("f:cn")
set objattachname = resDoc1.getElementsByTagName("d:x3704001f")
set objattachfilename = resDoc1.getElementsByTagName("e:attachmentfilename")
set objattsize = resDoc1.getElementsByTagName("d:x0e200003")
If objHrefNodeList.length > 0 Then
For f = 0 To (objHrefNodeList.length -1)
set objHrefNode1 = objHrefNodeList.nextNode
set objNodef = objattachmethod.nextnode
if objattachmethod.length <> 0 then
if objNodef.Text = 5 then
call
embedattach(objHrefNode1.Text,subject,daterecieved,recievedfrom,davdisplay,
objgrandchild,objChildRS)
else
settodav = 0
set objNode1f = objattachfilename.nextNode
if objattachfilename.length = 0 then
if objattachname.length = 0 then
set objNode1f = objcnval.nextNode
if objcnval.length = 0 then settodav = 1
else
set objNode1f = objattachname.nextNode
end if
end if
fnFileName = objNode1f.Text
if err.number <> 0 then wscript.echo "error" & objHrefNode1.Text
set objNode3f = objattsize.nextnode
attsize = objNode3f.Text

if fnFileName <> "" and clng(attsize/1024) > clng(maxsize) then
wscript.echo fnFileName
fatt1 = len(fnFileName)
lcMaloc = replace(objhref,"%E2%80%99S%20"," ")
lcMaloc = replace(unescape(lcMaloc),davdisplay,"")
lcMaloc = replace(lcMaloc,"http://" & wscript.arguments(0) & "/exchange/" &
wscript.arguments(1) & "/","")
wscript.echo lcMaloc
fatt2 = fatt1 - 2
attname = UCASE(fnFileName)
rtime =
dateserial(mid(daterecieved,1,4),mid(daterecieved,6,2),mid(daterecieved,9,2)) &
" " & mid(daterecieved,12,8)
objgrandchild.addnew
objgrandchild("GCFileext") = mid(attname,fatt2,3)
objgrandchild("GCFilename") = right(replace(fnFileName,",",""),254)
objgrandchild("GCFilesize") = replace(formatnumber(attsize/1024,2),",","")
objgrandchild("GCMessLocation") = right(replace(lcMaloc,",",""),254)
objgrandchild("GCMessSubject") = right(replace(subject,",",""),254)
objgrandchild("GCMessFrom") = recievedfrom
objgrandchild("GCDateSent") = dateadd("h",toffset,formatdatetime(rtime,0))
objgrandchild.update
elistchk = "," & mid(attname,fatt2,3) & ","
if instr(exlist,elistchk) = 0 then
call adddocext(objChildRS,"Unclassified Files",mid(attname,fatt2,3),"Unknown")

end if
end if
end if
end if
next
Else
End If
End If
end sub

sub adddoctype(objParentRS,doctype)

objParentRS.addnew
objParentRS("RFileType") = doctype
objParentRS.update

end sub


sub adddocext(objChildRS,doctype,ext,fdesc)

objChildRS.addnew
objChildRS("CFileType") = doctype
objChildRS("CFileext") = ext
objChildRS("CFileDesc") = fdesc
exlist = exlist & "," & ext & ","
objChildRS.update

end sub

sub addfileexts(objChildRS)
call adddocext(objChildRS,"Microsoft Office Documents","DOC","Microsoft Word
Document")
call adddocext(objChildRS,"Microsoft Office Documents","DOT","Microsoft Word
Template")
call adddocext(objChildRS,"Microsoft Office Documents","XLS","Microsoft Excel
Spreedsheet")
call adddocext(objChildRS,"Microsoft Office Documents","PPT","Microsoft
Powerpoint Presentation")
call adddocext(objChildRS,"Microsoft Office Documents","PPS","Microsoft
Powerpoint Slide Show")
call adddocext(objChildRS,"Microsoft Office Documents","MDB","Microsoft Access
Database")
call adddocext(objChildRS,"Microsoft Office Documents","ADP","Microsoft Access
Project")
call adddocext(objChildRS,"Microsoft Office Documents","VSD","Microsoft Visio
Diagram")
call adddocext(objChildRS,"Microsoft Office Documents","ONE","Microsoft OneNote
Note")
call adddocext(objChildRS,"Microsoft Office Documents","RTF","Rich Text Format
file")
call adddocext(objChildRS,"Microsoft Office Documents","TXT","Text File")
call adddocext(objChildRS,"Microsoft Office Documents","CSV","Comma seperated
File")
call adddocext(objChildRS,"Compressed Files","ZIP","Zip Compressed File")
call adddocext(objChildRS,"Compressed Files","TAR","nix Tar Compressed File")
call adddocext(objChildRS,"Compressed Files","ARG","Arg Compressed File")
call adddocext(objChildRS,"Compressed Files","RAR","RAR Compressed File")
call adddocext(objChildRS,"Compressed Files","ACE","ACE Compressed File")
call adddocext(objChildRS,"Compressed Files","BHX","Binary Hex Compressed File")
call adddocext(objChildRS,"Acorbat Files","PDF","Adobe Acrobat File")
call adddocext(objChildRS,"Executables and Installers","EXE","Executable File")
call adddocext(objChildRS,"Executables and Installers","MSI","Windows
Installer")
call adddocext(objChildRS,"Sound Files","WAV","Wave File")
call adddocext(objChildRS,"Sound Files","MP3","MPeg3 Sound file")
call adddocext(objChildRS,"Sound Files","WMA","Windows Media file")
call adddocext(objChildRS,"Sound Files","WMV","Windows Media file")
call adddocext(objChildRS,"Sound Files","SND","Windows Sound File")
call adddocext(objChildRS,"Sound Files",".AU","AU Sound File")
call adddocext(objChildRS,"Sound Files","RPM","Real Audio Sound File")
call adddocext(objChildRS,"Sound Files","MID","MIDI Audio Sound File")
call adddocext(objChildRS,"Sound Files",".RM","Real Audio Sound File")
call adddocext(objChildRS,"Sound Files",".RA","Real Audio Sound File")
call adddocext(objChildRS,"Sound Files","ASF","Advanced Streaming format File")
call adddocext(objChildRS,"Video Files","AVI","AVI Video format file")
call adddocext(objChildRS,"Video Files","MPG","MPG Video format file")
call adddocext(objChildRS,"Video Files","MOV","MOV Video format file")
call adddocext(objChildRS,"Video Files","IVX","DIVX Video format file")
call adddocext(objChildRS,"Video Files","PG4","MPG4 Video format file")
call adddocext(objChildRS,"Video Files","SWF","Shockwave format file")
call adddocext(objChildRS,"Image Files","JPG","JPG picture file")
call adddocext(objChildRS,"Image Files","BMP","Bit Map picture file")
call adddocext(objChildRS,"Image Files","GIF","Gif picture file")
call adddocext(objChildRS,"Image Files","PNG","Portable Network graphics picture
file")
call adddocext(objChildRS,"Image Files","TIF","Tag Image picture file")
call adddocext(objChildRS,"Image Files","IFF","Tag Image picture file")
call adddocext(objChildRS,"Image Files","WMF","Windows Metafile file")
call adddocext(objChildRS,"Image Files","EMF","Enhanced Metafile file")
call adddocext(objChildRS,"Image Files","PEG","Enhanced Metafile file")
call adddocext(objChildRS,"Attached Email Message","EML","Attached Email
Message")
call adddocext(objChildRS,"Attached Email Message","ICS","Attached Calendar
file")
end sub

27 comments:

Techguru69 said...

Thanks for the scripts Glen. It doesn't appear to work in my environment for some reason though. I am running EX2003 and attempting to run the scripts against the store. They run, with IE security warnings when it hits a mailbox, and then the output is empty. There are only the header lines in the .CSV files, nothing else. I'm running them on a XPSP2 laptop.
I've run the Mbauditwd.vbs with servername mymailbox 2003-01-01 2005-06-01 and it shows nothing, even though I have placed test attachments in that mailbox to see if it actually worked. I also ran the Mbauditrep.vbs, and after about 100 security warnings (one for each mailbox), it produced empty .CSV files for each mailbox on the server.
Not sure what's up - maybe I'm doing something wrong?

Teo Heras said...

Are you sure you have appropriate permissions? If you're running it through IE you'll definately have to turn up security so that you log on to the web page. Then, you're account may need 'send as' and 'receive as' rights.

Glen said...

Teo is correct in regards to permissions you need to full right to the mailbox your reporting on. From the commandline you posted you are missing one of the commandline parameters the min size of attahments to look for eg
cscript servername mymailbox 10 2003-01-01 2005-06-01.

The other thing i would suggest doing is rem the on error resume next statement on the first line of the script. This will mean you will recieve any errors that script is having.

Ryan said...

After running for a quite a bit of time on a large mailbox I recieved the following error.

mbaudit.vbs(159, 1) Microsoft VBScript runtime error: Overflow: 'cint'

Any ideas?

Glen said...

Ah sorry about that that was one of the fixs i included in the WebDAV version what you need to do is change the cint to clng. Basically the numbers are overflowing because of the size of the attachment so you need to use the long datatype instead of intergers. I've updated the code in the download. Thanks

Anonymous said...

i have the same problem as Techguru69, i dont get any atachmenets listed, i have run the code in debug mode and no errors are reported. but the code never enumerates the atachenments.
oNodeList.length is always 0. any ideas why ?

Glen said...

The first thing i would do is double check that you do have rights to the mailbox you are trying to report on (eg just try and using OWA against the mailbox and see if it works okay). The other thing you could do to debug is to get it to echo out the reponsetext for the webdav queries. This output can get a bit verbose at times but this is what i use to track down problems. eg

wscript.echo oXMLHttp.responsetext

Other things worth checking are your Internet Log files maybe URLScan has locked down one of the WebDAV verbs. Another tool for diagnoisis would be to use something like Etheral to do a network capture of the script running which would also be usefull to point to what might be cuase the problem

Anonymous said...

I have the required rights, as for one its my mailbox, and my account is a memeber of enterprise admins and exchange admins. i will check out you other sugestions and post back..

Anonymous said...

where is the bestplace to put wscript.echo oXMLHttp.responsetext in the script and what am i looking for in the response ?

Glen said...

You need to put this after the the send statement

eg oXMLHttp.send sQuery

What you will get back is the results of the query. So say you query a folder for all the email with attachments you should get back the results of this query (in a XML format). If you dont or get something else such as error this will help work out whats going wrong. What might be happening is that the namespace you are getting back in query is different from the what i recieved when creating the script. For instance if your properties are being returned as e:x0e080003 instead of d:x0e080003 then this script would fail with the type of error your getting. The easy fix here is just to change the following line to reflect the result of the query you are getting.

Set oXMLSizeNodes = oXMLDoc.getElementsByTagName("d:x0e080003")

change to

Set oXMLSizeNodes = oXMLDoc.getElementsByTagName("e:x0e080003")

You would need to do this for all the elements that might be different.

Anonymous said...

Nice code, thanks - however I really want to audit who's sending and who's receiving (i.e. the To and CC lists) - so I went to add displayto and displaycc (thought they were the right attributes). Unfortunately I seem to be strugling with the formatting of the output (I guess the attributes are multi-valued and your code doesn't handle that).

Any ideas - or perhaps another piece of code? (All I need to do is check if anyone on the server has mailed a particular domain in the last 28 days - and I can't use the audit logs) ... Thanks!?

Glen said...

Do you have Message Tracking enabled on your Server. This is what i would use to do this if you have the log file you could have a look at using something like http://www.outlookexchange.com/articles/glenscales/mtrackrs.asp which can report on this type of thing (and more). The SMTP log files are also another place you can get this imformation from i dont have any scripts for this but something like sawmill can do this. Trawling mailboxes for this type of information is a little cumberson and not very accurate (eg someone could have deleted the message already) it is doable the header properties aren't mulitvalued but usually require a bit of parsing to get them in a workable format. You can use something like Exchange Explorer to look at format of the properties you want to retrieve

simon said...

Using mbauduitrep I get a done response and no data. Using mbauditwb i get C:\Temp>cscript mbauditwd.vbs myserver mymailbox 10 2006-12-0
1 2007-01-01
Microsoft (R) Windows Script Host Version 5.6
Copyright (C) Microsoft Corporation 1996-2001. All rights reserved.

Microsoft Office Documents
Compressed Files
Acorbat Files
Executables and Installers
Sound Files
Video Files
Image Files
Attached Email Message
Unclassified Files

Attachment Summary
and again no data. I am domain admin and running it locally on the ex server Any suggestions? thanks

Glen said...

If your running as Domain admin then you will be specifically denied access to any other account other then the administrators mailbox. Have a read of http://www.petri.co.il/grant_full_mailbox_rights_on_exchange_2000_2003.htm which give some methods to assign an account full access to everyone mailbox.

Also to diagnose what might be going wrong try reming out the "on error resume next" at the top of the script

cheers
Glen

Simon said...

No luck, I get access denied with the local admin, domain admin, exchange admin. I even set up a brand new account and game it server admin and full mailbox rights to the one I want to report on and I get mbauditwd.vbs(113, 4) msxml3.dll: Access is denied.

Glen said...

Again if you give the user administration rights you will be specifically denied access see http://support.microsoft.com/kb/821897

What type of authentication are you using if you are using basic authentication then you may need to hard code the username and password in the code in the following line

oXMLHttp.open "SEARCH", sUrl, False, "domain\user", "password"

The other thing you could do is try using the admin virtual root which means you should be able to run the code with just delegated Exchange admin rights see http://msdn2.microsoft.com/en-us/library/aa123676.aspx

Cheers
Glen

Simon said...

Hi Gregg, thanks for the suggestion, the hard coding of ther logon credentials has resolved the problem.
Just oine more question if you don't mind, mbauditrep.vbs goes through all users but does it create a collated report? Or do I need pipe the screen output and format it myself? There's a lot of info in there! thanks again

simon said...

any idea how I can get past; C:\Temp\mbauditwd.vbs(194, 6) Microsoft VBScript runtime error: Object required: 'objNode1f'?
thanks

Simon said...

Please disregard the last post, i still had the REM in the script. thanks

Glen said...

If you want a collated report your best bet you just be to join the CSV file together. You could also do something like import them into a access or SQL db and then you could use something like Crystal reports to do some advanced Grouping etc.

Cheers
Glen

Insinr8 said...

Glen,
Thanks for the script, it works great. I was just wondering if there is a way to turn off the message boxes for each file and directory I am getting. It makes going through a 2Gb mail box a bit of a chore. (mbauditwb.vbs)

Awesome stuff, thanks

Glen said...

If you run it from the command prompt using cscript you shouldn't get any msgboxs (just echos out to the command prompt. Otherwise you would need to rem out all the wscript.echo line eg you could just do a replace in notepad of wscript.echo with rem wscript.echo

Cheers
Glen

Allen said...

Thanks for this glen
Your script has already saved me a lot of work.
I have however encountered a small issue when runnuing the script.

A lot of people in the workplace have folders for sorting their mail on the same level as their inbox and the script appears to be missing them completely. I am far from a scripting person and was wondering if you could please assist me in adding this functionality

Glen said...

It should enumerate all folder no matter what level they are at. What you might find is that its erroring out for some reason on those folders. You could try to removing or reming the first line

on error resume next

You should then see any error that occur. Hopefully you should be able to work out what the problem is by the error that is returned

Cheers
Glen

Ben_13 said...

Hey Glen, may be a silly question but will these work with exchange 2007?

Glen said...

WebDAV will work on Exchange 2007 a better method would be to rewrite the script to use EWS.

Cheers
Glen

Barry said...

This seems to be a great script - trying it now. I was unable to run any of it from the Exchange server. I got mbauditwd to work remotely by hard coding the login as mentioned.
I'm using it to do multiple accounts by passing mailbox aliases to the command via a script.

REM Audit.bat - with limit of 1000
set QueryCMD=dsquery * "dc=my,dc=local" -scope subtree -Filter "(&(objectCategory=*)(homeMDB=*)(msExchHomeServerName=*MyExch*))" -attr mailnickname -l -limit 1000

FOR /F "tokens=*" %%i IN ('%QueryCMD%') Do cscript mbauditwd.vbs MyExch01.ops.local "%%i" 800 2004-01-01 2010-11-01