Tuesday, August 14, 2007

Event ID 1029 Audit Report for failed Folder Access script

A couple of weeks back someone asked about a script to get details of a folder from the FID (folder ID) that is captured as part of a Event 1029 if your doing audit logging as described on msexchange.org. PFDavadmin does a good job of querying and presenting this information in a format that is compatible with what you can retrieve from the event logs but when you do turn up logging to this level the number of event that gets logged can be a bit overwhelming to use this method. So I thought I’d put together a few scripts that could automate this process and produce an htm report at the end showing which folders where accessed and by whom.

Instead of searching the Exchange store for each different event to find the related Folder what I did was come up with a script that would first query every folder in every mailbox in a mail store and then build and XML file that could then be queried by another script when it came time to produce a report. The report script when it runs uses WMI to query the event log for any 1029 events that happened in the time period you specify it then finds the related folder information by using the FID retrieved from the data section of the event log and then produces a HTML report of this information

The script to build the XML file that contains the FID information works similar to PFdavadmin in that it uses the admin virtual root. The advantage here is you can run the script with an account that has delegated Exchange admin rights and it will be able to access every mailbox. One difference is that instead of using the ptagFID : 0x67480014 I used the http://schemas.microsoft.com/exchange/permanenturl property and parsed the FID from this as per http://support.microsoft.com/kb/320749/en-us

By default the script uses http if your server is set to require https on the virtual admin root then you need to change the following line

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


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

The fiddb.vbs script which is the script that builds the XML file needs to be run with 1 command line parameter which is the name of the server you want to report against. An example commandline is

Cscript fiddb.vbs yourservername

The ElogFidReport first does a conversion of the current time to UTC to be able to query the event log for any 1029 events logged in the time period you specify as a command line parameter. It then queries the eventlog of the server you specify as another commandline parameter and goes though each of the 1029 events and first parsers the email address of the requestor and exchangelegdn of the mailbox being accessed. It then searches Active directory for this legDN value to find the mailbox in question so the DisplayName can be used to make the report more readable. The FID information is retrieved from the data section of the event log and converted from a Byte array into the FID format that is used in the XML file. A search is then done on the XML for that FID and finally a report is built. To make the report a little more readable the results are grouped by Mailbox and sorted by access date.

To run the EventLog report script you need to pass in two commandline parameters the first is the servername of the server you want to report against and the second is how many hours you want to look back in the logs. So to do a report of the last 4 hours you would use something like

Cscript ElogFidReport.vbs youservername 4

All the reports and XML files are stored in the c:\temp directory if this folder doesn’t exist or you want to change it to something else you need to search and configure the variables in both scripts that use this directory.

I’ve put a download of both script here the script itself looks like.

Servername = wscript.arguments(0)
csCurrentdbFileName = "c:\temp\fiddb.xml"

Set fso = CreateObject("Scripting.FileSystemObject")
set wfile = fso.opentextfile(csCurrentdbFileName,2,true)
wfile.writeline("<?xml version=""1.0""?>")
wfile.writeline("<SnappedFIDS SnapDate=""" & WeekdayName(weekday(now),3) & ", "
& day(now()) & " " & Monthname(month(now()),3) & " " & year(now()) & " " &
formatdatetime(now(),4) & ":00" & """>")

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
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 =
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
falias = "http://" & servername & "/exadmin/admin/" & dpDefaultpolicy & "/mbx/"
wfile.writeline("<Mailbox displayName=""" & rs1.fields("mail").value & """>")
for each paddress in rs1.fields("proxyaddresses").value
if instr(paddress,"SMTP:") then falias = falias & replace(paddress,"SMTP:","") &
Call GetRootFolder(falias)
call RecurseFolder(falias)
set conn = nothing
set com = nothing

Public Sub GetRootFolder(sUrl)

xmlreqtxt = "<?xml version='1.0'?><a:propfind xmlns:a='DAV:' xmlns:e='http://schemas.microsoft.com/exchange/'><a:prop><e:permanenturl/></a:prop></a:propfind>"
req.open "PROPFIND", sUrl, false , "", ""
req.setRequestHeader "Content-Type", "text/xml; charset=""UTF-8"""
req.setRequestHeader "Depth", "0"
req.setRequestHeader "Translate", "f"
req.send xmlreqtxt
set oResponseDoc = req.responseXML
set oNodeList = oResponseDoc.getElementsByTagName("d:permanenturl")
For i = 0 To (oNodeList.length -1)
set oNode = oNodeList.nextNode
wfile.writeline("<Folder Name=""NON_IPM_SUBTREE/Root"" Path=""Root"" fid=""1"&
Mid(oNode.text,InStr(Len(oNode.text)-8,oNode.text,"-"),10) & """></Folder>")

End sub

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 ""DAV:displayname"",
sQuery = sQuery & "mapi/proptag/x6707001E"",
""http://schemas.microsoft.com/exchange/permanenturl"", ""DAV:hassubs"" FROM
sQuery = sQuery & "('SHALLOW TRAVERSAL OF """ & sUrl & """') "
sQuery = sQuery & "WHERE ""DAV:isfolder"" = true and NOT
""http://schemas.microsoft.com/mapi/proptag/x36010003"" = 3"
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 oXMLDavDisplayName = oXMLDoc.getElementsByTagName("a:displayname")
Set oXMLHREFNodes = oXMLDoc.getElementsByTagName("a:href")
Set oXMLHasSubsNodes = oXMLDoc.getElementsByTagName("a:hassubs")
Set oXMLFIDNodes = oXMLDoc.getElementsByTagName("e:permanenturl")
Set oXMLPathNodes = oXMLDoc.getElementsByTagName("d:x6707001E")
For i = 0 to oXMLHREFNodes.length - 1
wscript.echo oXMLHREFNodes.Item(i).nodeTypedValue
wscript.echo oXMLDavDisplayName(i).nodeTypedValue & " " &
if oXMLPathNodes(i).nodeTypedValue = "/" then
strDispName = "root"
strDispName = oXMLDavDisplayName(i).nodeTypedValue
end if
wfile.writeline("<Folder Name=""" & escape(strDispName) & """ Path=""" &
escape(oXMLPathNodes(i).nodeTypedValue) & """ fid=""1"&
& """></Folder>")
If oXMLHasSubsNodes.Item(i).nodeTypedValue = True Then
call RecurseFolder(oXMLHREFNodes.Item(i).nodeTypedValue)
End If
End Sub