Friday, February 23, 2007

Creating a Summary Email of all appointments within a Users Calendar for given period

This is another script that might come in handy for people trying to deal with DST affected appointments. Whether you choose to Rebase your appointments with the Exchange Time Zone Update Tool or not with the number of appointments and complexity of the actions you are performing you can't be 100 % sure of covering everything so you might want to send an email to users that contains a summary of what actions you have taken (or not), what problems there might be and a summary of all current appointments in their calendar that might be affected. To make things a bit easier all the appointments are hyperlinked via Outlook links so the user can click the appointment within the summary email and have that appointment or instance of the appointment open in Outlook.

I had a bit of version madness with this one for a number of reasons I started out with a CDO 1.2 version (mainly because i had most of the code already done) but I had a few problems with CDO 1.2 when trying to hyperlink to Recurring appointments. When using the EntryID from CDO it always links back to the master instance of a appointment when used in a Outlook link while not that undesirable it gets a little confusing especially if the appointment is a exception to a recurring appointment. I switched to using WebDAV which does give a EntryID that allows you to link to the instance of the recurring appointment but this only works when Outlook is being used in Online mode. If outlook is in Cached mode then the links to recurring appointments don't work. (For the Outlook links I'm using the Outlook:EntryID format)

The script works by going though all the appointments in the affected period and then compiling the start and end times as well as subject and locations and then grouping the information by the organizer of the appointment into html table rows. These table rows are then grouped by organizer using a scripting dictionary object which acts as a basic hashtable in VBS. At the end of the report a HTML email is created the first contains some verbage to explain what the email is about and then a table that first lists all the appointments and meetings the user has created as well as separate tables that lists the appointments from organizers that the user is scheduled to attend within the affected period.

The WebDAV version of the script uses the Admin virtual root directory to access the user calendars 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 ADSI query that gets the default SMTP FQDN from the default recipient policy.

The report is sent via email to do the send I've used OWA there where a few reasons for this at first I was using CDOSYS (the code is commented out if you find the OWA version doesnt work you could fall back to this method) but found that Outlook 2003 had a habit of blocking the links because it believed the message was forged so using OWA got around this problem . To cater for instances where you might be using Forms Based Authentication I created a FBA version that does a synthetic form logon and handles the OWA cookies.

The script takes the servername and mailbox alias you want to run it against as command-line parameters Its important to use the correct mailbox alias because this in turn is used to look up the users displayname and email address in Active Directory which is used to then group the appointments properly. You also need to configure one script variable with the name of the account you want to sent the message from via OWA. eg

owOwaURL ="http://" & snServername & "/exchange/" & mnMailbox & "/Drafts"

By default it tries to use the current users mailbox this may or may not succeed depending on the rights the user that is running the script has.

The FBA version needs to be configured further with details of the OWA logon to be used to send the email the following variables need to be set.

snOWAServername = "servername.com"
owaMailbox = "userName"
domain = "Domain"
strpassword = "Password"

I've put a downloadable version of cdo and webdav version here with the CDO version i put some extra logic in so i wont create outlook hyperlinks for recurring appointments. The webDAV script looks like

snServername = wscript.arguments(0)
mnMailbox = wscript.arguments(1)
Set sdMeetOrgs = CreateObject("Scripting.Dictionary")
datefrom = "2007-03-11T00:00:00Z"
dateto = "2007-04-01T00:00:00Z"

snOWAServername = "servername.com"
owaMailbox = "username"
domain = "domain"
strpassword = "password"
owOwaURL ="https://" & snOWAServername & "/exchange/" & owaMailbox & "/Drafts/"

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 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 &amp;
">;(&(objectCategory=msExchRecipientPolicy)(cn=Default
Policy));distinguishedName,gatewayProxy;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
DnameQuery = "<LDAP://" & strDefaultNamingContext &amp; ">;(mailnickname=" &
mnMailbox & ");distinguishedName,DisplayName,mail;subtree"
Com.ActiveConnection = Conn
Com.CommandText = DnameQuery
Set dsRs = Com.Execute
while not dsRs.eof
dnDisplayName = dsRs.fields("DisplayName")
emEmailaddress = dsRs.fields("mail")
dsRs.movenext
Wend
wscript.echo dnDisplayName
mbMailboxURI = "http://" & snServername &amp; "/exadmin/admin/" & dpDefaultpolicy &
"/mbx/" & mnMailbox & "/Calendar/"
wscript.echo mbMailboxURI
call procfolder(mbMailboxURI)

sub procfolder(strURL)
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"", "
strQuery = strQuery & """http://schemas.microsoft.com/mapi/proptag/0x0FFF0102""
As EntryID, "
strQuery = strQuery & """urn:schemas:httpmail:fromname"",
""urn:schemas:calendar:dtstart"", ""urn:schemas:calendar:dtend"","
strQuery = strQuery & " ""urn:schemas:calendar:location"",
""http://schemas.microsoft.com/mapi/apptstateflags"" FROM scope('shallow
traversal of """
strQuery = strQuery & strURL &amp; """') Where ""DAV:ishidden"" = False AND
""DAV:contentclass"" = 'urn:content-classes:appointment' AND "
strQuery = strQuery & " NOT ""urn:schemas:calendar:instancetype"" = 1 AND "
strQuery = strQuery & """urn:schemas:calendar:dtstart"" &lt;= CAST(""" & dateto
& """ as 'dateTime') AND "
strQuery = strQuery &amp; """urn:schemas:calendar:dtend"" &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
wscript.echo req.status
If req.status >= 500 Then
wscript.echo "Error: " & req.responsetext
ElseIf req.status = 207 Then
set oResponseDoc = req.responseXML
set oDisplayNameNodes = oResponseDoc.getElementsByTagName("a:displayname")
set oHrefNodes = oResponseDoc.getElementsByTagName("a:href")
set oSubject = oResponseDoc.getElementsByTagName("d:subject")
set oEndTime = oResponseDoc.getElementsByTagName("e:dtend")
Set oStartTime = oResponseDoc.getElementsByTagName("e:dtstart")
Set oLocation = oResponseDoc.getElementsByTagName("e:location")
Set oAppstate = oResponseDoc.getElementsByTagName("f:apptstateflags")
Set oFromname = oResponseDoc.getElementsByTagName("d:fromname")
Set oEntryID = oResponseDoc.getElementsByTagName("EntryID")
For i = 0 To (oDisplayNameNodes.length -1)
set oNode = oDisplayNameNodes.nextNode
set oNode1 = oHrefNodes.nextNode
set oNode2 = oSubject.nextNode
set oNode3 = oEndTime.nextNode
Set oNode4 = oStarttime.nextNode
Set oNode5 = oLocation.nextNode
Set oNode6 = oAppstate.nextNode
Set oNode7 = oFromname.nextNode
Set oNode8 = oEntryID.nextNode
wscript.echo oNode2.text
wscript.echo oNode3.text
wscript.echo oNode4.text
wscript.echo oNode5.text
wscript.echo oNode6.text
wscript.echo Octenttohex(oNode8.nodeTypedValue)
soOrgnizer = ""
soOrgnizer = oNode7.text
sdStartDate =
dateadd("h",toffset,DateSerial(Mid(oNode4.text,1,4),Mid(oNode4.text,6,2),Mid(oNode4.text,9,2))
&amp; " " & Mid(oNode4.text,12,8))
edEndDate =
dateadd("h",toffset,DateSerial(Mid(oNode3.text,1,4),Mid(oNode3.text,6,2),Mid(oNode3.text,9,2))
&amp; " " & Mid(oNode3.text,12,8))
wscript.echo soOrgnizer
wscript.echo
trReportBody = ""
trReportBody = trReportBody &amp; "<tr>" & vbcrlf
trReportBody = trReportBody & "<td align=""center"" width=""20%"">" &
sdStartDate &amp; " </td>" & vbcrlf
trReportBody = trReportBody & "<td align=""center"" width=""20%"">" & edEndDate
& "&nbsp;</td>" & vbcrlf
trReportBody = trReportBody & "<td align=""center"" width=""30%""><a
href=""outlook:" & Octenttohex(oNode8.nodeTypedValue) & """>" & oNode2.text &
"</a> </td>" & vbcrlf
trReportBody = trReportBody & "<td align=""center"" width=""15%"">" &
oNode5.text & "&nbsp;</td>" & vbcrlf
If oNode6.text <> 0 then
trReportBody = trReportBody & "<td align=""center"" width=""15%"">" & soOrgnizer
&amp; " </td>" & vbcrlf
trReportBody = trReportBody & "</tr>" & vbcrlf
If sdMeetOrgs.exists(soOrgnizer) Then
sdMeetOrgs(soOrgnizer) = sdMeetOrgs(soOrgnizer) & trReportBody
Else
sdMeetOrgs.Add soOrgnizer,trReportBody
End if
Else
trReportBody = trReportBody & "<td align=""center"">NA </td>" & vbcrlf
trReportBody = trReportBody & "</tr>" & vbcrlf
If sdMeetOrgs.exists(dnDisplayName) Then
sdMeetOrgs(dnDisplayName) = sdMeetOrgs(dnDisplayName) & trReportBody
Else
sdMeetOrgs.Add dnDisplayName,trReportBody
End If
End if
Next
Else
End If

Call WriteandSendReport()

end sub

Sub WriteandSendReport()
vbVerbage = "<p><b><font face=""Arial"" color=""#000080"">Due to change blah
blah the following " _
& "Meetings and Appointments scheduled between the 11th March and 1st of April
may potential be 1" _
& "hour incorrect. The following is a list of appointments from your calender
that may be " _
& "affected its recommended blah blah</font></b></p>"
rpReport = rpReport & vbVerbage & vbcrlf
rpReport = rpReport & "<p><b><font face=""Arial"" color=""#000080"">Meeting's
and Appointments Organized by You</font></b></p>" & vbcrlf
rpReport = rpReport & "<table border=""1"" width=""100%"">" & vbcrlf
rpReport = rpReport & " <tr>" & vbcrlf
rpReport = rpReport & "<td align=""center"" bgcolor=""#000080""
width=""20%""><b><font color=""#FFFFFF"">Start Time</font></b></td>" & vbcrlf
rpReport = rpReport & "<td align=""center"" bgcolor=""#000080""
width=""20%""><b><font color=""#FFFFFF"">End time</font></b></td>" & vbcrlf
rpReport = rpReport & "<td align=""center"" bgcolor=""#000080""
width=""30%""><b><font color=""#FFFFFF"">Subject</font></b></td>" & vbcrlf
rpReport = rpReport & "<td align=""center"" bgcolor=""#000080""
width=""15%""><b><font color=""#FFFFFF"">Location</font></b></td>" & vbcrlf
rpReport = rpReport & "<td align=""center"" bgcolor=""#000080""
width=""15%""><b><font color=""#FFFFFF"">Organizer</font></b></td>" & vbcrlf
rpReport = rpReport & "</tr>" & vbcrlf
rpReport = rpReport & sdMeetOrgs(dnDisplayName)
rpReport = rpReport &amp; "</table>" & vbcrlf
rpReport = rpReport & "<p><b><font face=""Arial"" color=""#000080"">Meeting You
are Scheduled to Attended</font></b></p>" & vbcrlf

For Each kyOrg In sdMeetOrgs.Keys
If kyOrg <> dnDisplayName Then
rpReport = rpReport & "<p><b><font face=""Arial"" color=""#000080"">Organized By
: " & kyOrg & "</font></b></p>"
rpReport = rpReport & "<table border=""1"" width=""100%"">" & vbcrlf
rpReport = rpReport & sdMeetOrgs(kyOrg)
rpReport = rpReport &amp; "</table>" & vbcrlf
End if
Next


'Set objEmail = CreateObject("CDO.Message")
'objEmail.From = "user@domain"
'objEmail.To = "user@domain"
'objEmail.Subject = "Appointment Summary for DST change"
'objEmail.htmlbody = rpReport
'objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/sendusing")
= 2
'objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver")
= "servername"
'objEmail.Configuration.Fields.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport")
= 25
'objEmail.Configuration.Fields.Update
'objEmail.Send

strusername = domain &amp; "\" & owaMailbox
szXml = "destination=https://" & snOWAServername & "/exchange&flags=0&username="
& strusername
szXml = szXml &amp; "&password=" & strpassword & "&SubmitCreds=Log
On&amp;forcedownlevel=0&trusted=0"
req.Open "post", "https://" & snOWAServername & "/exchweb/bin/auth/owaauth.dll",
False
req.send szXml
reqhedrarry = split(req.GetAllResponseHeaders(), vbCrLf,-1,1)
for i = lbound(reqhedrarry) to ubound(reqhedrarry)
if instr(lcase(reqhedrarry(i)),"set-cookie: sessionid=") then reqsessionID =
right(reqhedrarry(i),len(reqhedrarry(i))-12)
if instr(lcase(reqhedrarry(i)),"set-cookie: cadata=") then reqcadata=
right(reqhedrarry(i),len(reqhedrarry(i))-12)
next

szXml = ""
szXml = szXml & "Cmd=send" & vbLf
szXml = szXml &amp; "MsgTo=" & emEmailaddress & vbLf
szXml = szXml & "MsgCc=" & vbLf
szXml = szXml &amp; "MsgBcc=" & vbLf
szXml = szXml &amp; "urn:schemas:httpmail:importance=1" & vbLf
szXml = szXml &amp; "http://schemas.microsoft.com/exchange/sensitivity-long=" & vbLf

szXml = szXml &amp; "urn:schemas:httpmail:subject=Appointment Summary for DST
change" & vbLf
szXml = szXml &amp; "urn:schemas:httpmail:htmldescription=<!DOCTYPE HTML PUBLIC " _

& """-//W3C//DTD HTML 4.0 Transitional//EN""><HTML DIR=ltr><HEAD><META
HTTP-EQUIV" _
& "=""Content-Type"" CONTENT=""text/html; charset=utf-8""></HEAD><BODY><DIV>" _

& "<FONT face='Arial' color=#000000 size=2>" & rpReport & "</font>" _
& "</DIV></BODY></HTML>" & vbLf

req.Open "POST", owOwaURL, False, "", ""
req.setRequestHeader "Accept-Language:", "en-us"
req.setRequestHeader "Content-type:", "application/x-www-UTF8-encoded"
req.SetRequestHeader "cookie", reqsessionID
req.SetRequestHeader "cookie", reqCadata
req.setRequestHeader "Content-Length:", Len(szXml)
req.Send szXml
Wscript.echo req.responseText

wscript.echo "Report Sent"

End Sub

Function Octenttohex(OctenArry)
ReDim aOut(UBound(OctenArry))
For i = 1 to UBound(OctenArry) + 1
if len(hex(ascb(midb(OctenArry,i,1)))) = 1 then
aOut(i-1) = "0" & hex(ascb(midb(OctenArry,i,1)))
else
aOut(i-1) = hex(ascb(midb(OctenArry,i,1)))
end if
Next
Octenttohex = join(aOUt,"")
End Function

Thursday, February 22, 2007

Creating a Report of Meeting Organizers for all appointments in all calendars on a Server via WebDAV

Euricelia Wanderley created the following script to do some reporting on User's meeting that fell withing the affected US DST period .Euricelia script uses some webDAV to do a expansion query of all calendar appointments in the affected period and then produces a csv of the results. The script uses a few cool tricks like parsing out the organizer of the meeting from the vcalendar stream of the appointment. I've taken this idea a little further and put together a version of the same type of thing that designed to send the user a HTML summary email that shows them all the appointments within the affected period im still working on this at the moment having a few issues linking to Exceptions of recurring appointments should kick it tommorow.

I've put a downloadable copy of Euricelia script here the script itself looks like

on error resume next
servername = "SERVERNAME"
public username
public password
username = "USERNAME"
password = "PASSWORD"
public datefrom
public dateto
datefrom = "2007-03-11T00:00:00Z"
dateto = "2007-04-01T00:00:00Z"

set shell = createobject("wscript.shell")
set conn1 = createobject("ADODB.Connection")
Set fso = CreateObject("Scripting.FileSystemObject")
fname = "c:\support\scripts\" & servername &amp;amp;amp;amp; ".csv"
set wfile = fso.opentextfile(fname,2,true)
wfile.writeline("Meeting,Organizer")

set conn = createobject("ADODB.Connection")
set com = createobject("ADODB.Command")
Set iAdRootDSE = GetObject("LDAP://RootDSE")
strNameingContext = iAdRootDSE.Get("configurationNamingContext")
strDefaultNamingContext = iAdRootDSE.Get("defaultNamingContext")
Conn.Provider = "ADsDSOObject"
Conn.Open "ADs Provider"
svcQuery = "<LDAP://" & strNameingContext &amp;amp;amp;amp; ">;(&(objectCategory=msExchExchangeServer)(cn="
& Servername & "));cn,name,legacyExchangeDN;subtree"
Com.ActiveConnection = Conn
Com.CommandText = svcQuery
Set Rs = Com.Execute
while not rs.eof
GALQueryFilter = "(&(&(&(&(mailnickname=*)(!msExchHideFromAddressLists=TRUE)((&(objectCategory=person)(objectClass=user)(msExchHomeServerName="
&rs.fields("legacyExchangeDN") &amp;amp;amp;amp; ")) )))))"
strQuery = "<LDAP://" & strDefaultNamingContext &amp;amp;amp;amp; ">;" & GALQueryFilter & ";distinguishedName,mail,displayname,mailnickname;subtree"
com.Properties("Page Size") = 100
Com.CommandText = strQuery
Set Rs1 = Com.Execute
while not Rs1.eof
wscript.echo "User: " & rs1.fields("displayname")
user = rs1.fields("mail")
call QueryAttendees(servername,user)
rs1.movenext
wend
rs.movenext
wend
rs.close
set conn = nothing
set com = nothing
wscript.echo "Done"


Public Sub QueryAttendees(server,mailbox)

On Error Resume Next

strURL = "http://" & server & "/exchange/" & mailbox & "/calendar/"
strQuery = "<?xml version=""1.0""?><D:searchrequest xmlns:D = ""DAV:"" >"
strQuery = strQuery & "<D:sql>SELECT DISTINCT ""DAV:href"" FROM scope('shallow
traversal of """ & strURL &amp;amp;amp;amp; """') "
strQuery = strQuery & " Where ""DAV:isfolder"" = false AND ""DAV:ishidden"" =
false AND ""urn:schemas:calendar:alldayevent"" = false "
strQuery = strQuery & "AND ""DAV:contentclass"" =
'urn:content-classes:appointment' "
strQuery = strQuery & "AND ""urn:schemas:calendar:dtend"" &amp;amp;amp;gt; CAST(""" &
datefrom & """ as 'dateTime.tz') "
strQuery = strQuery &amp; "AND ""urn:schemas:calendar:dtstart"" &lt; CAST(""" &
dateto & """ as 'dateTime.tz') "
strQuery = strQuery &amp; "</D:sql></D:searchrequest>"


wscript.echo strQuery
set req = createobject("microsoft.xmlhttp")
req.open "SEARCH", strURL, false, username, password

If Err.Number <> 0 Then
WScript.Echo "Error Opening Search"
WScript.Echo Err.Number & ": " & Err.Description
End If

req.setrequestheader "Content-Type", "text/xml"
req.setRequestHeader "Translate","f"
req.setRequestHeader "Depth", "1,noroot"
req.send strQuery

If Err.Number <> 0 Then
WScript.Echo "Error Sending Query"
WScript.Echo Err.Number & ": " & Err.Description
End If

wscript.echo req.status
wscript.echo "response" & req.responseXML

If req.status >= 500 Then
wscript.echo "Status: " & req.status
wscript.echo "Status text: An error occurred on the server."
ElseIf req.status = 207 Then
set oResponseDoc = req.responseXML
set oNodeList = oResponseDoc.getElementsByTagName("a:href")
For i = 0 To (oNodeList.length -2)
set oNode = oNodeList.nextNode
proccalmess(oNode.Text)
Next
Else
wscript.echo "Status: " & req.status
wscript.echo "Status text: " & req.statustext
wscript.echo "Response text: " & req.responsetext
End If

End Sub

public sub proccalmess(objhref)

set req = createobject("microsoft.xmlhttp")
wscript.echo objhref
wfile.write(objhref & ",")
On Error Resume Next
Req.open "GET", objhref, false,username, password
If Err.Number <> 0 Then
WScript.Echo "Error Opening GET"
WScript.Echo Err.Number & ": " & Err.Description
End If

Req.setRequestHeader "Translate","f"
Req.send

attendeearry = split(req.responsetext,"ORGANIZER;",-1,1)
for i = 1 to ubound(attendeearry)
string1 = vbcrlf & " "
stparse = replace(attendeearry(i),string1,"")
attaddress = mid(stparse,(instr(stparse,"MAILTO:")+7),instr(stparse,chr(13)))
attaddress = mid(attaddress,1,instr(attaddress,vbcrlf))
wscript.echo attaddress
wfile.writeline(attaddress)
next

end sub

Stopping and Restarting a Virtual SMTP server on Exchange 2000/2003 via Script

One thing you may need to do from time to time for a myriad of reasons is stop and start a particular instance of a Virtual SMTP server on Exchange. To do this via a script you can use the IIS provider which has a stop and start method you can use once you have connected to the object in question. A simple Example would be

Set SMTPsvc = GetObject("IIS://" & Servername & "/SMTPSVC/1")
SMTPsvc.Stop
SMTPsvc.Start

This should also work for Pop3 and IMAP4 virtual servers (as long as you use the correct path to the object). I've put a more complicated sample together that allows you to enter a servername as a commandline parameter and it will then query Active Directory to find all the SMTP Virtual Server instances on the particular server it will then connect to and query those instances on that server and restart any currently active instances. I've put a downloadable copy of the script here the script itself looks like.

snServerName = wscript.arguments(0)
set conn = createobject("ADODB.Connection")
set com = createobject("ADODB.Command")
Set iAdRootDSE = GetObject("LDAP://RootDSE")
strNameingContext = iAdRootDSE.Get("configurationNamingContext")
strDefNamingContext = iAdRootDSE.Get("defaultNamingContext")
Conn.Provider = "ADsDSOObject"
Conn.Open "ADs Provider"
Com.ActiveConnection = Conn
Com.ActiveConnection = Conn
Wscript.echo
Wscript.echo "SMTP Virtual Servers Status"
vsQuery = "<LDAP://" & strNameingContext &amp;amp;amp;amp; ">;(objectCategory=protocolCfgSMTPServer);name,distinguishedName;subtree"
Com.ActiveConnection = Conn
Com.CommandText = vsQuery
Set Rs = Com.Execute
While Not Rs.EOF
strstmsrv = "LDAP://" & rs.fields("distinguishedName")
set svsSmtpserver = getobject(strstmsrv)
crServerName =
mid(svsSmtpserver.distinguishedName,instr(svsSmtpserver.distinguishedName,"CN=Protocols,")+16,instr(svsSmtpserver.distinguishedName,",CN=Servers")-(instr(svsSmtpserver.distinguishedName,"CN=Protocols,")+16))
wscript.echo
wscript.echo "ServerName:" & crServerName
if lcase(snServerName) = lcase(crServerName) then call
getSTMPstatus(crServerName,svsSmtpserver.adminDisplayName)
rs.movenext
wend

sub getSTMPstatus(servername,vsname)
Set SMTPVSS = GetObject("IIS://" & Servername &amp;amp;amp;amp; "/SMTPSVC")
for each SMTPVS in SMTPVSS
if SMTPVS.KeyType = "IIsSmtpServer" then
if SMTPVS.ServerComment = vsname then
wscript.echo "SMTP Server : " & SMTPVS.ServerComment
select case SMTPVS.ServerState
case 1 Wscript.echo "Current State: Starting"
case 2 Wscript.echo "Current State: Started"
Wscript.echo "Will Restart"
SMTPVS.Stop
wscript.echo "Virtual server Stop"
SMTPVS.Start
wscript.echo "Virtual server Start"
case 3 Wscript.echo "Current State: Stopping"
case 4 Wscript.echo "Current State: Stopped"
case 5 Wscript.echo "Current State: Pausing"
case 6 Wscript.echo "Current State: Paused"
case 7 Wscript.echo "Current State: Continuing"
case else Wscript.echo "unknown"
end select
end if
end if
next

end sub

Thursday, February 08, 2007

Exchange 2007 Mailbox Size Powershell Form Script

This script has been superseded by Version 5 this is a much better script that doesn't require EWS and also allows you see quota usage click here to goto version 5.

With the demise of the Exchange_Mailbox WMI class in Exchange 2007 the new method of getting Mailbox sizes on Exchange 2007 has moved into the Exchange Management Shell via the new get-mailboxstatistics cmdlet. Mailbox sizes in themselves while useful can’t tell you where the space in a particular mailbox is being used. This is where you need to be able to drill down into the mailbox folders themselves and see where a user in particular is using their space eg(inbox, sent items etc). So I’ve put together a powershell script that can do just that it creates a winform that allows you to select what server you want to report on. Then it will run the get-mailboxstatistics cmdlet to retrieve all mailbox sizes for all users on this server you select and populate a ListView with these values. An event is wired to the ListView rows so when you select a row in this ListView (eg a mailbox you want to know where the space is being use). Another function runs that then performs a FindFolder operation via Exchange Web Services on the selected user which will retrieve a list of every folder in the mailbox and the size of that folder. This data is used to populate a datatable which is then used to populate another list view object that will display the names and sizes of those folders in the mailbox. To keep with the hierarchal nature of a mailbox the folder size listview first shows all the root folders and the sizes displayed are the sums of the size of the root folders plus the size of any child folders within those root folder. To see what the child folder sizes are if you click the row in the second listview for the corresponding folder another function will fire that will clear the listview and then redisplay the data for the child folder with summed totals for all these folders. To go back up one folder a back button will appear at the top of the second ListView that allows back navigation. This second operation doesn’t cause the Server to be re-queried it just uses the same data from the datatable and presents it in a different format.

To access the folders within a mailbox the EWS code relies on Impersonation to be configured the Exchange SDK has details on how to give an account Impersonation rights it involves granting two rights the first is on the Server which allows a user to submit impersonation calls and the second is either on the user account you wish to access itself or the Mailbox database if you wanted to access all mailboxes within a particular mail store for this script you want to grant the latter permission on all stores that you want to be able to get the mailbox folder sizes from. For details of how to do this see. Once you have setup impersonation you then need to configure the code with the details of the user you have setup to allow impersonation. In line 3 there are the following 4 lines that affect this.

$unUserName = "username"
$psPassword = "password$"
$dnDomainName = "domain"
$cdUsrCredentials = new-object System.Net.NetworkCredential($unUserName , $psPassword , $dnDomainName)

If you instead want to use NTLM authentication from the user that is running the script so you don’t have hard code the username and password if you comment (or delete) the lines above in the script and change the following line (124) from

$WDRequest.Credentials = $cdUsrCredentials

To

$WDRequest.UseDefaultCredentials = $True

This will mean the code will run the EWS Findfolder operation under the security context of the user executing the script. The error’s you receive if you get access denied aren’t very descriptive you usually just get a 501 error so be wary or this.

With the EWS code because it runs across SSL you need to make sure that browser trusts the certificate that’s being used (eg the code cant deal with SSL certificate errors) this is usually just a matter of importing the certificate from OWA so its trusted if you not using a registered certificate. (You’ll know what I mean if you get an error about SSL when you try to run the EWS code).

The script actually makes use of 2 Exchange cmdlet’s the first is get-mailboxstatitics and then get-user is used to get the SID of the user in question. The SID is used as part of the impersonation header within the EWS code. Because of this reliance on the Exchange cmdlet’s the script needs to be run from within the Exchange Management Shell.

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

[System.Reflection.Assembly]::LoadWithPartialName("System.Drawing")
[System.Reflection.Assembly]::LoadWithPartialName("System.windows.forms")

$unUserName = "username"
$psPassword = "password$"
$dnDomainName = "domain"
$cdUsrCredentials = new-object System.Net.NetworkCredential($unUserName , $psPassword
, $dnDomainName)

function getMailboxSizes(){

$lbListView.clear()
$lbListView.Columns.Add("UserName",150)
$lbListView.Columns.Add("# Items",70)
$lbListView.Columns.Add("MB Size(MB)",80)
$lbListView.Columns.Add("DelItems (KB)",90)

get-mailboxstatistics -Server $snServerNameDrop.SelectedItem.ToString()
ForEach-Object{
$item1 = new-object System.Windows.Forms.ListViewItem($_.DisplayName)
$item1.SubItems.Add($_.ItemCount)
$item1.SubItems.Add($_.TotalItemSize.Value.ToMB() )
$item1.SubItems.Add($_.TotalDeletedItemSize.Value.ToKB())

$lbListView.items.add($item1)
}

$form.Controls.Add($lbListView)
}

function enumFolderSizes($fsFolderIDtoSearch){
$private:enfsFilterString = "ParentID = '" + $fsFolderIDtoSearch + "'"
$private:enSubFolders = $fsTable.select($enfsFilterString)
for($fcount1 = 0;$fcount1 -le $enSubFolders.GetUpperBound(0); $fcount1++){
$global:fldSize = $global:fldSize + $enSubFolders[$fcount1][3]
$global:itemCount = $global:itemCount + $enSubFolders[$fcount1][5]
if ($enSubFolders[$fcount1][4] -ne 0){
enumFolderSizes($enSubFolders[$fcount1][1])
}

}
}

function BackFolder(){
$private:bfFilterString = "FolderID = '" + $global:LastFolder + "'"
$private:bfFolder = $fsTable.select($bfFilterString)
GetSubFolderSizes($bfFolder[0][2])

}

function GetSubFolderSizes($fiFIDToSearch){
$global:LastFolder = $fiFIDToSearch
$upButton.visible = $true
$lbFldListView.clear()
$lbFldListView.Columns.Add("Folder Name",150)
$lbFldListView.Columns.Add("# Items",80)
$lbFldListView.Columns.Add("Size(MB)",80)
$lbFldListView.Columns.Add("Has Sub",80)
$lbFldListView.Columns.Add("FID",0)
$subfsFilterString = "ParentID = '" + $fiFIDToSearch + "'"
$subFolders = $fstable.select($subfsFilterString)
for($fcount2 = 0;$fcount2 -le $subFolders.GetUpperBound(0); $fcount2++){
$global:fldSize = $subFolders[$fcount2][3]
$global:itemCount = $subFolders[$fcount2][5]
if ($subFolders[$fcount2][4] -ne 0){
enumFolderSizes($subFolders[$fcount2][1])
}
$item1 = new-object System.Windows.Forms.ListViewItem($subFolders[$fcount2][0])
$item1.SubItems.Add($global:itemCount)
$item1.SubItems.Add([math]::round(($fldsize/1mb),2))
if ($subFolders[$fcount2][4] -ne 0){
$item1.SubItems.Add("Yes")
}
else {
$item1.SubItems.Add("No")
}
$item1.SubItems.Add($subFolders[$fcount2][1])
$lbFldListView.items.add($item1)
}


}


function GetFolderSizes($siSIDToSearch){
$fsTable.clear()
$lbFldListView.clear()
$lbFldListView.Columns.Add("Folder Name",150)
$lbFldListView.Columns.Add("# Items",80)
$lbFldListView.Columns.Add("Size(MB)",80)
$lbFldListView.Columns.Add("Has Sub",80)
$lbFldListView.Columns.Add("FID",0)
$snServername = $snServerNameDrop.SelectedItem.ToString()
$siSIDToSearch = get-user $siSIDToSearch

$smSoapMessage = "<?xml version='1.0' encoding='utf-8'?>" `
+ "<soap:Envelope xmlns:soap=`"http://schemas.xmlsoap.org/soap/envelope/`" " `
+ " xmlns:xsi=`"http://www.w3.org/2001/XMLSchema-instance`"
xmlns:xsd=`"http://www.w3.org/2001/XMLSchema`"" `
+ " xmlns:t=`"http://schemas.microsoft.com/exchange/services/2006/types`" >" `
+ "<soap:Header>" `
+ "<t:ExchangeImpersonation>" `
+ "<t:ConnectingSID>" `
+ "<t:SID>" + $siSIDToSearch.SID + "</t:SID>" `
+ "</t:ConnectingSID>" `
+ "</t:ExchangeImpersonation>" `
+ "</soap:Header>" `
+ "<soap:Body>" `
+ "<FindFolder
xmlns=`"http://schemas.microsoft.com/exchange/services/2006/messages`" " `
+ "xmlns:t=`"http://schemas.microsoft.com/exchange/services/2006/types`"
Traversal=`"Deep`"> " `
+ "<FolderShape>" `
+ "<t:BaseShape>AllProperties</t:BaseShape>" `
+ "<AdditionalProperties
xmlns=""http://schemas.microsoft.com/exchange/services/2006/types"">" `
+ "<ExtendedFieldURI PropertyTag=""0x0e08"" PropertyType=""Integer"" />" `
+ "</AdditionalProperties>" `
+ "</FolderShape>" `
+ "<ParentFolderIds>" `
+ "<t:DistinguishedFolderId Id=`"root`"/>" `
+ "</ParentFolderIds>" `
+ "</FindFolder>" `
+ "</soap:Body></soap:Envelope>"

$strRootURI = "https://" + $snServername + "/ews/Exchange.asmx"
$WDRequest = [System.Net.WebRequest]::Create($strRootURI)
$WDRequest.ContentType = "text/xml"
$WDRequest.Headers.Add("Translate", "F")
$WDRequest.Method = "Post"
$WDRequest.Credentials = $cdUsrCredentials
$bytes = [System.Text.Encoding]::UTF8.GetBytes($smSoapMessage)
$WDRequest.ContentLength = $bytes.Length
$RequestStream = $WDRequest.GetRequestStream()
$RequestStream.Write($bytes, 0, $bytes.Length)
$RequestStream.Close()
$WDResponse = $WDRequest.GetResponse()
$ResponseStream = $WDResponse.GetResponseStream()
$ResponseXmlDoc = new-object System.Xml.XmlDocument
$ResponseXmlDoc.Load($ResponseStream)
$DisplayNameNodes = @($ResponseXmlDoc.getElementsByTagName("t:DisplayName"))
$ExtenedPropertyField = @($ResponseXmlDoc.getElementsByTagName("t:Value"))
$FolderIdNodes = @($ResponseXmlDoc.getElementsByTagName("t:FolderId"))
$ParentFolderIdNodes =
@($ResponseXmlDoc.getElementsByTagName("t:ParentFolderId"))
$ChildFolderCountNodes =
@($ResponseXmlDoc.getElementsByTagName("t:ChildFolderCount"))
$TotalItemCountNodes = @($ResponseXmlDoc.getElementsByTagName("t:TotalCount"))
for($i=0;$i -lt $DisplayNameNodes.Count;$i++){
if ($DisplayNameNodes[$i].'#text' -eq "Top of Information Store"){$rootFolderID
= $FolderIdNodes[$i].GetAttributeNode("Id").'#text'}
$fiFolderID = $FolderIdNodes[$i].GetAttributeNode("Id")
$pfParentFolderID = $ParentFolderIdNodes[$i].GetAttributeNode("Id")
$fsTable.Rows.Add($DisplayNameNodes[$i].'#text',$fiFolderID.'#text',$pfParentFolderID.'#text',$ExtenedPropertyField[$i].'#text',$ChildFolderCountNodes[$i].'#text',$TotalItemCountNodes[$i].'#text')
}
$fsFilterString = "ParentID = '" + $rootFolderID + "'"
$rrRootFolders = $fstable.select($fsFilterString)
for($fcount = 0;$fcount -le $rrRootFolders.GetUpperBound(0); $fcount++){
if ($rrRootFolders[$fcount][0] -ne "Top of Information Store"){
$global:fldSize = $rrRootFolders[$fcount][3]
$global:itemCount = $rrRootFolders[$fcount][5]
if ($rrRootFolders[$fcount][4] -ne 0){
enumFolderSizes($rrRootFolders[$fcount][1])
}
$item1 = new-object
System.Windows.Forms.ListViewItem($rrRootFolders[$fcount][0])
$item1.SubItems.Add($global:itemCount)
$item1.SubItems.Add([math]::round(($fldsize/1mb),2))
if ($rrRootFolders[$fcount][4] -ne 0){
$item1.SubItems.Add("Yes")
}
else {
$item1.SubItems.Add("No")
}
$item1.SubItems.Add($rrRootFolders[$fcount][1])
$lbFldListView.items.add($item1)
}
}

$form.Controls.Add($lbFldListView)
}
$form = new-object System.Windows.Forms.form
$global:LastFolder = ""
# Add DataTable

$Dataset = New-Object System.Data.DataSet
$fsTable = New-Object System.Data.DataTable
$fsTable.TableName = "Folder Sizes"
$fsTable.Columns.Add("DisplayName")
$fsTable.Columns.Add("FolderID")
$fsTable.Columns.Add("ParentID")
$fsTable.Columns.Add("Size)",[int])
$fsTable.Columns.Add("ChildFolderCount",[int])
$fsTable.Columns.Add("TotalCount",[int])
$Dataset.tables.add($fsTable)

# Add Server DropLable
$snServerNamelableBox = new-object System.Windows.Forms.Label
$snServerNamelableBox.Location = new-object System.Drawing.Size(10,20)
$snServerNamelableBox.size = new-object System.Drawing.Size(100,20)
$snServerNamelableBox.Text = "ServerName"
$form.Controls.Add($snServerNamelableBox)

# Add Server Drop Down
$snServerNameDrop = new-object System.Windows.Forms.ComboBox
$snServerNameDrop.Location = new-object System.Drawing.Size(130,20)
$snServerNameDrop.Size = new-object System.Drawing.Size(130,30)
get-mailboxserver ForEach-Object{$snServerNameDrop.Items.Add($_.Name)}
$snServerNameDrop.Add_SelectedValueChanged({getMailboxSizes})
$form.Controls.Add($snServerNameDrop)

# Add List Box to DisplayMailboxs


$lbListView = new-object System.Windows.Forms.ListView
$lbListView.Location = new-object System.Drawing.Size(10,50)
$lbListView.size = new-object System.Drawing.Size(400,500)
$lbListView.LabelEdit = $True
$lbListView.AllowColumnReorder = $True
$lbListView.CheckBoxes = $False
$lbListView.FullRowSelect = $True
$lbListView.GridLines = $True
$lbListView.View = "Details"
$lbListView.Sorting = "Ascending"
$lbListView.add_click({GetFolderSizes($this.SelectedItems.item(0).text)});


# Add List Box to Display FolderSizes


$lbFldListView = new-object System.Windows.Forms.ListView
$lbFldListView.Location = new-object System.Drawing.Size(500,50)
$lbFldListView.size = new-object System.Drawing.Size(400,500)
$lbFldListView.LabelEdit = $True
$lbFldListView.AllowColumnReorder = $True
$lbFldListView.FullRowSelect = $True
$lbFldListView.GridLines = $True
$lbFldListView.View = "Details"
$lbFldListView.Sorting = "Ascending"
$lbFldListView.add_click({GetSubFolderSizes($this.SelectedItems.item(0).subitems[4].text)});


# UP folder Button

$upButton = new-object System.Windows.Forms.Button
$upButton.Location = new-object System.Drawing.Size(500,19)
$upButton.Size = new-object System.Drawing.Size(120,23)
$upButton.Text = "Back Folder level"
$upButton.visible = $false
$upButton.Add_Click({BackFolder})
$form.Controls.Add($upButton)

$form.Text = "Exchange 2007 Mailbox Size Form"
$form.size = new-object System.Drawing.Size(1000,600)
$form.autoscroll = $true
$form.topmost = $true
$form.Add_Shown({$form.Activate()})
$form.ShowDialog()

Thursday, February 01, 2007

Creating a RSS feed of all new items in all public folders over the past 24 hours

I’m having a bit of a RSS feed week this week here’s another interesting one. A while ago I created this RSS event sink for a public folder that would generate a feed whenever a new item was posted in a Public Folder. This is useful but over a large number of folders having a separate feed for every folder is a pain and usually it’s a matter of getting across the new information that’s been added to a possible large number of folders that’s important. So what I’ve come up with is a script that will scan though every folder in the public folder tree on a server and then create a feed that has a post for each new item in each public folder within the whole public folder tree (where theres are replica available). The one problem with this script is it doesn’t really respect permissions so if you have folders that have information in them that is supposed to be restricted you might want to include some if statements so these folders are skipped.


This script use WebDAV to query each public folder it’s loosely based like a few of my scripts lately on the code from the mailbox size KB . Each folder in the public folder tree is recursively queried for items that where created in the last 24 hours and a RSS file is then built using the XMLDom com object. That script use the Admin Virtual root which means it can run using Delegated Exchange Administration rights and won’t be hampered by public folder permission. An ADSI query is used to find out what the default SMTP domain is in 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.


falias = "http://" & servername & "/exadmin/admin/" & dpDefaultpolicy & "/Public Folders/"

To customize the location where the RSS file is created modify the 2 line

feedfile = ""c:\temp\feedpubnew.xml"

The script itself takes one commandline parameter which is the servername of the server you want to run it against so to run this script you need something like

cscript pubrssnew.vbs servername

I’ve put a download copy of this script here the script itself looks like

Servername = wscript.arguments(0)
feedfile = "feedpubnew.xml"
set shell = createobject("wscript.shell")
strValueName = "HKLM\SYSTEM\CurrentControlSet\Control\TimeZoneInformation\ActiveTimeBias"
minTimeOffset = shell.regread(strValueName)
toffset = datediff("h",DateAdd("n", minTimeOffset, now()),now())
dtListFrom = DateAdd("n", minTimeOffset, now())
gmttime = dateadd("h",-toffset,now())
dateto = isodateit(gmttime)
datefrom = isodateit(DateAdd("d",-1,gmttime))
set objdom = CreateObject("MICROSOFT.XMLDOM")
set req = createobject("microsoft.xmlhttp")
rem Create Root RSS feed
Set objField = objDom.createElement("rss")
Set objattID = objDom.createAttribute("version")
objattID.Text = "2.0"
objField.setAttributeNode objattID
objDom.appendChild objField
Set objField1 = objDom.createElement("channel")
objfield.appendChild objField1
Set objField3 = objDom.createElement("link")
objfield3.text = "http://" & Servername & "/public"
objfield1.appendChild objField3
Set objField4 = objDom.createElement("title")
objfield4.text = "Public Folder Feed"
objfield1.appendChild objField4
Set objField5 = objDom.createElement("description")
objfield5.text = "New Public Folder items in the last 24 Hours"
objfield1.appendChild objField5
Set objField6 = objDom.createElement("language")
objfield6.text = "en-us"
objfield1.appendChild objField6
Set objField7 = objDom.createElement("lastBuildDate")
objfield7.text = WeekdayName(weekday(now),3) & ", " & day(now()) & " " &
Monthname(month(now()),3) & " " & year(now()) & " " & formatdatetime(now(),4) &
":00 GMT"
objfield1.appendChild objField7

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
falias = "http://" & servername & "/exadmin/admin/" & dpDefaultpolicy & "/Public
Folders/"
RecurseFolder(falias)
wscript.echo falias
set conn = nothing
set com = nothing
set wfile = nothing
set fso = Nothing
Set objPI = objDom.createProcessingInstruction("xml", "version='1.0'")
objDom.insertBefore objPI, objDom.childNodes(0)
objdom.save("c:\temp\" & feedfile)

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"",
""urn:schemas:httpmail:to"""
strQuery = strQuery & " FROM scope('shallow traversal of """
strQuery = strQuery & strURL & """') Where ""DAV:ishidden"" = False AND
""DAV:isfolder"" = False 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 oSize = oResponseDoc.getElementsByTagName("a:getcontentlength")
set odatereceived = oResponseDoc.getElementsByTagName("a:creationdate")
set fEmail = oResponseDoc.getElementsByTagName("d:fromemail")
set TEmail = oResponseDoc.getElementsByTagName("d:to")
For i = 0 To (oNodeList.length -1)
set oNode = oNodeList.nextNode
set oNode1 = oNodeList1.nextNode
set oNode2 = oSize.nextNode
set oNode3 = odatereceived.nextNode
set oNode4 = fEmail.nextNode
set oNode5 = TEmail.nextNode
wscript.echo oNode3.text
export = 0
If InStr(LCase(oNode4.text),LCase(domaintosearch))Then
export = 1
End If
if InStr(LCase(oNode5.text),LCase(domaintosearch))Then
export = 1
End If
If export = 1 Then
Call AddtoFeed(oNode1.text,oNode.text)
End if
Next
Else
End If

end sub

sub AddtoFeed(exporthref,subject)

xmlreqtxt = "<?xml version='1.0'?><a:propfind xmlns:a='DAV:'
xmlns:m='urn:schemas:httpmail:'
xmlns:mapi='http://schemas.microsoft.com/mapi/proptag/'>" _
&
"<a:prop><mapi:x6707001E/></a:prop><a:prop><a:displayname/></a:prop><a:prop><m:subject/></a:prop><a:prop><m:fromemail/>"_
&
"</a:prop><a:prop><m:htmldescription/></a:prop><a:prop><m:datereceived/></a:prop></a:propfind>"
req.open "PROPFIND", exporthref, false, "", ""
req.setRequestHeader "Content-Type", "text/xml; charset=""UTF-8"""
req.setRequestHeader "Depth", "0"
req.setRequestHeader "Translate", "f"
req.send xmlreqtxt
set oResponseDoc1 = req.responseXML
set pfParentFolder = oResponseDoc1.getElementsByTagName("d:x6707001E")
set feFromEmail = oResponseDoc1.getElementsByTagName("e:fromemail")
set sjSubject = oResponseDoc1.getElementsByTagName("e:subject")
set drDateRecieved = oResponseDoc1.getElementsByTagName("e:datereceived")
set bdHtmlBody = oResponseDoc1.getElementsByTagName("e:htmldescription")
set dnDisplayName = oResponseDoc1.getElementsByTagName("a:displayname")

For i = 0 To (sjSubject.length -1)
set pfnode = sjSubject.nextNode
set pfnode1 = feFromEmail.nextNode
set pfnode2 = drDateRecieved.nextNode
set pfnode3 = bdHtmlBody.nextNode
Set pfnode4 = pfParentFolder.nextNode
Set pfnode5 = dnDisplayName.nextNode
wscript.echo pfnode.text
wscript.echo pfnode1.text
wscript.echo pfnode2.text
rem wscript.echo pfnode3.text
wscript.echo pfnode4.text
wscript.echo pfnode5.text
wscript.echo left(Replace(pfnode2.text,"T"," "),19)
Set objField2 = objDom.createElement("item")
objfield1.appendChild objField2
Set objField8 = objDom.createElement("guid")
Set objattID8 = objDom.createAttribute("isPermaLink")
objattID8.Text = "false"
objField8.setAttributeNode objattID8
objfield8.text = exporthref
objfield2.appendChild objField8
Set objField9 = objDom.createElement("title")
objfield9.text = pfnode.text
if objfield9.text = "" then objfield9.text = "Blank"
objfield2.appendChild objField9
Set objField10 = objDom.createElement("link")
objfield10.text = "http://" & Servername & "/public" & pfnode4.text
objfield2.appendChild objField10
Set objField11 = objDom.createElement("description")
objfield11.text = pfnode3.text
if objfield11.text = "" then objfield11.text = "Blank"
objfield2.appendChild objField11
Set objField12 = objDom.createElement("author")
objfield12.text = pfnode1.text
objfield2.appendChild objField12
Set objField13 = objDom.createElement("pubDate")
objfield13.text = WeekdayName(weekday(left(Replace(pfnode2.text,"T"," "),19)),3)
& ", " & day(left(Replace(pfnode2.text,"T"," "),19)) & " " &
Monthname(month(left(Replace(pfnode2.text,"T"," "),19)),3) & " " &
year(left(Replace(pfnode2.text,"T"," "),19)) & " " &
formatdatetime(left(Replace(pfnode2.text,"T"," "),19),4) & ":00 GMT"
objfield2.appendChild objField13
Set objField14 = objDom.createElement("category")
objfield14.text = unescape(Replace(LCase(pfnode4.text),LCase(pfnode5.text),""))
objfield2.appendChild objField14
set objfield2 = nothing
set objfield8 = nothing
set objfield9 = nothing
set objfield10 = nothing
set objfield11 = nothing
set objfield12 = nothing
set objfield13 = nothing
next

End Sub


function isodateit(datetocon)
strDateTime = year(datetocon) & "-"
if (Month(datetocon) < 10) then strDateTime = strDateTime & "0"
strDateTime = strDateTime & Month(datetocon) & "-"
if (Day(datetocon) < 10) then strDateTime = strDateTime & "0"
strDateTime = strDateTime & Day(datetocon) & "T" & formatdatetime(datetocon,4)
&":00Z"
isodateit = strDateTime
end function

Creating a RSS feed of an Exchange 2007 Mailbox Folder using Exchange Web Services C# and Powershell

I’m a real fan of RSS it tends to by my preferred method of reading and aggregating information so I though I’d see how easy it would be to generate a RSS feed using the new Exchange Web Services in Exchange 2007. Fortunately the XmlTextWriter class in .NET makes creating XML very easy when compared with the old XMLDOM Com object. To create a feed of items from a folder using EWS you need to first use a FindItem request which will return a list of items in a folder I used a restriction so it would only return items that where less then 7 days old. One of the restictions with the FindItem operation as stated in the SDK “FindItem returns only the first 512 bytes of any streamable property. For Unicode, it returns the first 255 characters by using a null-terminated Unicode string. It does not return any of the message body formats or the recipient lists. FindItem will return a recipient summary. You can use the GetItem Operation to get the details of an item.” ref This means you would only be able to return a small percentage of body of a message which if you’re creating a summary feed is okay because this will be enough characters to give you a decent summary. I wanted a full feed so I went with using a separate GetItem Operation on each of the EntryIDs that are returned from the finditem request.

One of the other new things I’ve used in this Code is one of the cooler new features of Exchange Web Services which is Impersonation. Previously if you wanted to write code that was going to run against a mailbox that was being run under the security context of an account that wasn’t the owner of that mailbox that the account in question would need to be given rights to this mailbox eg via delegation or AD Users and Computers etc. With Impersonation you still have to grant rights for another account to access a mailbox other then its own but these rights are just specific to EWS impersonation so granting rights in this way means that the account can use EWS to access a mailbox but it cant use OWA or Outlook (or any other API). So from a security standpoint this is pretty desirable and gives you much more leverage in your applications (and keeps those auditors happy). The Exchange SDK has details on how to give an account Impersonation rights it involves granting two rights the first is on the Server which allows a user to submit impersonation calls and the second is either on the user account you wish to access itself or the Mailbox database if you wanted to access all mailboxes within a particular mail store see. Using Impersonation is relatively easy you can use the UPN, SID or SMTP address of the account you want to access I went for the SMTP address. The impersonation information is added to the SOAP header eg in the powershell script the following represents adding the SOAP header for impersation

+ "<soap:Header>"`
+ "<t:ExchangeImpersonation>" `
+ "<t:ConnectingSID>" `
+ "<t:PrimarySmtpAddress>" + $mbMailboxToAccess + "</t:PrimarySmtpAddress>" `
+ "</t:ConnectingSID>" `
+ "</t:ExchangeImpersonation>" `
+ "</soap:Header>" `
+ "<soap:Body>" `

Another thing to note in this script is in the Link node of each RSS item I put a link into so if you double clicked on the item it would open the original email in OWA. Now with Exchange 2003/2000 you could just use the Href value of the email in question and OWA would render the item okay. With OWA on 2007 this doesn’t work instead you need to use the EntryID of the Item which you can get from the FindItem request. The one thing I did find is that this itemID cant be used as is from the FindItem request and needed to be transposed a bit to make it work. I’m not sure if this will vary between servers or not and there’s no documentation on this type of thing so its really a best guess. What I used did work for me on multiple mailboxes on the same server.

I stared out writing this as a WebService in C# using the EWS proxy objects but I decided I’d much rather have it as a powershell script as it would be easy to schedule and run so I ended up creating a C# version and a Powershell version. Both versions require 5 variables to be set

$snServername = "Servername"

Self Explanatory
$unUserName = "Username"
$psPassword = "password"
$dnDomainName = "domain"


This is the Username and Password of the account you will be using to do the Impersonation. (You don’t have to hardcode them if you can use NTLM)


$mbMailboxToAccess = user@smtpdomain.com

This is the smtp address of the user you want to aggregate

The code aggregates the last 7 days worth or items in the inbox this could be changed to another folder other then the inbox (if you wanted) or the number of days to aggregate can be changed by modifying the following line.

+ "<t:Constant Value=`"" +
$datetimetoquery.ToUniversalTime().AddDays(-7).ToString("yyyy-MM-ddThh:mm:ssZ")
+ "`"/>"`

I’ve put a download of the C# code and powershell script here the script itself looks like.

function
GetItem($smSoapMessage){
$bdBodytext = ""
$WDRequest1 = [System.Net.WebRequest]::Create($strRootURI)
$WDRequest1.ContentType = "text/xml"
$WDRequest1.Headers.Add("Translate", "F")
$WDRequest1.Method = "Post"
$WDRequest1.Credentials = $cdUsrCredentials
$bytes1 = [System.Text.Encoding]::UTF8.GetBytes($smSoapMessage)
$WDRequest1.ContentLength = $bytes1.Length
$RequestStream1 = $WDRequest1.GetRequestStream()
[void]$RequestStream1.Write($bytes1, 0, $bytes1.Length)
[void]$RequestStream1.Close()
$WDResponse1 = $WDRequest1.GetResponse()
$ResponseStream1 = $WDResponse1.GetResponseStream()
$ResponseXmlDoc1 = new-object System.Xml.XmlDocument
$ResponseXmlDoc1.Load($ResponseStream1)
$tbBodyNodes = @($ResponseXmlDoc1.getElementsByTagName("t:Body"))
for($itemNums=0;$itemNums -lt $tbBodyNodes.Count;$itemNums++){
$bdBodytext = $tbBodyNodes[$itemNums].'#text'.ToString()
}
return $bdBodytext
}


$snServername = "servername"
$unUserName = "user"
$psPassword = "password"
$dnDomainName = "domain"
$mbMailboxToAccess = "user@smtpdomain.com"
$cdUsrCredentials = new-object System.Net.NetworkCredential($unUserName , $psPassword
, $dnDomainName)
$xsXmlFileName = "c:\feedname.xml"
[System.Reflection.Assembly]::LoadWithPartialName("System.Web") > $null
$xrXmlWritter = new-object
System.Xml.XmlTextWriter($xsXmlFileName,[System.Text.Encoding]::UTF8)
$xrXmlWritter.WriteStartDocument()
$xrXmlWritter.WriteStartElement("rss")
$xrXmlWritter.WriteAttributeString("version", "2.0")
$xrXmlWritter.WriteStartElement("channel")
$xrXmlWritter.WriteElementString("title", "Inbox Feed For " + $mbMailboxToAccess)
$xrXmlWritter.WriteElementString("link", "https://" + $snServerName + "/owa/")
$xrXmlWritter.WriteElementString("description", "Exchange Inbox Feed For" +
$mbMailboxToAccess)
$datetimetoquery = get-date
$smSoapMessage = "<?xml version='1.0' encoding='utf-8'?>" `
+ "<soap:Envelope xmlns:soap=`"http://schemas.xmlsoap.org/soap/envelope/`" " `
+ " xmlns:xsi=`"http://www.w3.org/2001/XMLSchema-instance`"
xmlns:xsd=`"http://www.w3.org/2001/XMLSchema`"" `
+ " xmlns:t=`"http://schemas.microsoft.com/exchange/services/2006/types`" >" `
+ "<soap:Header>" `
+ "<t:ExchangeImpersonation>" `
+ "<t:ConnectingSID>" `
+ "<t:PrimarySmtpAddress>" + $mbMailboxToAccess + "</t:PrimarySmtpAddress>" `
+ "</t:ConnectingSID>" `
+ "</t:ExchangeImpersonation>" `
+ "</soap:Header>" `
+ "<soap:Body>" `
+ "<FindItem
xmlns=`"http://schemas.microsoft.com/exchange/services/2006/messages`" " `
+ "xmlns:t=`"http://schemas.microsoft.com/exchange/services/2006/types`"
Traversal=`"Shallow`"> " `
+ "<ItemShape>" `
+ "<t:BaseShape>AllProperties</t:BaseShape>" `
+ "<AdditionalProperties
xmlns=""http://schemas.microsoft.com/exchange/services/2006/types"">" `
+ "<ExtendedFieldURI PropertyTag=""0x3FD9"" PropertyType=""String"" />" `
+ "<ExtendedFieldURI PropertyTag=""0x10F3"" PropertyType=""String"" />" `
+ "<ExtendedFieldURI PropertyTag=""0x0C1A"" PropertyType=""String"" />" `
+ "</AdditionalProperties>" `
+ "</ItemShape>" `
+ "<Restriction>" `
+ "<t:IsGreaterThanOrEqualTo>" `
+ "<t:FieldURI FieldURI=`"item:DateTimeSent`"/>"`
+ "<t:FieldURIOrConstant>" `
+ "<t:Constant Value=`"" +
$datetimetoquery.ToUniversalTime().AddDays(-7).ToString("yyyy-MM-ddThh:mm:ssZ")
+ "`"/>"`
+ "</t:FieldURIOrConstant>"`
+ "</t:IsGreaterThanOrEqualTo>"`
+ "</Restriction>"`
+ "<ParentFolderIds>" `
+ "<t:DistinguishedFolderId Id=`"inbox`"/>" `
+ "</ParentFolderIds>" `
+ "</FindItem>" `
+ "</soap:Body></soap:Envelope>"

$strRootURI = "https://" + $snServername + "/ews/Exchange.asmx"
$WDRequest = [System.Net.WebRequest]::Create($strRootURI)
$WDRequest.ContentType = "text/xml"
$WDRequest.Headers.Add("Translate", "F")
$WDRequest.Method = "Post"
$WDRequest.Credentials = $cdUsrCredentials
$bytes = [System.Text.Encoding]::UTF8.GetBytes($smSoapMessage)
$WDRequest.ContentLength = $bytes.Length
$RequestStream = $WDRequest.GetRequestStream()
$RequestStream.Write($bytes, 0, $bytes.Length)
$RequestStream.Close()
$WDResponse = $WDRequest.GetResponse()
$ResponseStream = $WDResponse.GetResponseStream()
$ResponseXmlDoc = new-object System.Xml.XmlDocument
$ResponseXmlDoc.Load($ResponseStream)
$subjectnodes = @($ResponseXmlDoc.getElementsByTagName("t:Subject"))
$FromNodes = @($ResponseXmlDoc.getElementsByTagName("t:Name"))
$SentNodes = @($ResponseXmlDoc.getElementsByTagName("t:DateTimeSent"))
$SizeNodes = @($ResponseXmlDoc.getElementsByTagName("t:Size"))
$IDNodes = @($ResponseXmlDoc.getElementsByTagName("t:ItemId"))
$dsDescription = @($ResponseXmlDoc.getElementsByTagName("t:Value"))
for($i=0;$i -lt $subjectnodes.Count;$i++){
$Senttime = [System.Convert]::ToDateTime($SentNodes[$i].'#text'.ToString())
$Senttime.ToString() + " " + $FromNodes[$i].'#text' + " " +
$subjectnodes[$i].'#text' + " " + $SizeNodes[$i].'#text'
$IdNodeID = $IDNodes[$i].GetAttributeNode("Id")
$ckChangeKey = $IDNodes[$i].GetAttributeNode("ChangeKey")
$smSoapMessage = "<?xml version='1.0' encoding='utf-8'?>" `
+ "<soap:Envelope xmlns:soap=`"http://schemas.xmlsoap.org/soap/envelope/`" "
`
+ " xmlns:xsi=`"http://www.w3.org/2001/XMLSchema-instance`"
xmlns:xsd=`"http://www.w3.org/2001/XMLSchema`"" `
+ " xmlns:t=`"http://schemas.microsoft.com/exchange/services/2006/types`" >"
`
+ "<soap:Header>" `
+ "<t:ExchangeImpersonation>" `
+ "<t:ConnectingSID>" `
+ "<t:PrimarySmtpAddress>" + $mbMailboxToAccess + "</t:PrimarySmtpAddress>"
`
+ "</t:ConnectingSID>" `
+ "</t:ExchangeImpersonation>" `
+ "</soap:Header>" `
+ "<soap:Body><GetItem
xmlns=`"http://schemas.microsoft.com/exchange/services/2006/messages`"><ItemShape>"
`
+ "<BaseShape
xmlns=`"http://schemas.microsoft.com/exchange/services/2006/types`">Default</BaseShape></ItemShape>"
`
+ "<ItemIds><ItemId Id=`"" + $IdNodeID.'#text' + "`"" `
+ " xmlns=`"http://schemas.microsoft.com/exchange/services/2006/types`"
/></ItemIds></GetItem></soap:Body>" `
+ "</soap:Envelope>"
$xrXmlWritter.WriteStartElement("item")
$xrXmlWritter.WriteElementString("title", $subjectnodes[$i].'#text')
$xrXmlWritter.WriteElementString("link", "https://" + $snServername +
"/owa/?ae=Item&t=IPM.Note&id=Rg" +
[System.Web.HttpUtility]::UrlEncode($IdNodeID.'#text').Substring(58).Replace("%3d","J"))
$xrXmlWritter.WriteElementString("author", $FromNodes[$i].'#text')
$xrXmlWritter.WriteStartElement("description")
$xrXmlWritter.WriteRaw("<![CDATA[")
$bdBodytext = GetItem($smSoapMessage)
$xrXmlWritter.WriteRaw($bdBodytext)
$xrXmlWritter.WriteRaw("]]>")
$xrXmlWritter.WriteEndElement()
$xrXmlWritter.WriteElementString("pubDate", $Senttime.ToString("r"))
$xrXmlWritter.WriteElementString("guid", $IdNodeID.'#text')
$xrXmlWritter.WriteEndElement()

}
$xrXmlWritter.WriteEndElement()
$xrXmlWritter.WriteEndElement()
$xrXmlWritter.WriteEndDocument()
$xrXmlWritter.Close()
"Done"