Tuesday, January 30, 2007

/DST switch for the Sumatra Utilities

We've added an additional flag /DST to the Sumatra Utilities as an aid to auditing how many meetings and appointments will be affected by the coming Microsoft Rebasing tool.

su /in:sutest.txt /DST

at the command prompt (where sutest.txt has user aliases zyg, riuliano, kelly, tralfax, and jam one on each line) will result in a text file like the following, outputting a total of all of each user's meetings and appointments in the DST transition zone from March 11, 2007 to April 1, 2007 and from October 28, 2007 to November 4, 2007.

--------------------------------------------------------------------------------
Timer: Start=2/1/2007 9:09:23 AM (ver v2.3.12)
--------------------------------------------------------------------------------
User Input file:sutest.txt

Alias Num Appts Num Recur Master Num Recur Instances Num Recur Exceptions Total Items Total Cross Check
zyg 12 2 19 1 34 34
riuliano 2 0 3 0 5 5
Error -1 user tralfax does not exist in the GC: GC://sumatra.local
kelly No Meetings found.

jam 1 0 0 0 1 1

So each number represents the total affected

  • Appointments (i.e., with no guests)
  • Recurring Master Meetings (i.e., which the user Owns)
  • Recurring Instances
  • Recurring Exceptions
  • Total Items
  • Total cross check

  • Recurring Master Appointments: This is the total of master meetings that originate (i.e., first instance) in the DST Delta.
  • Recurring Instances: This are the instances of ALL recurring meetings (these become exceptions after you run the MS Rebasing tool) in the DST Delta for that user (whether or not they own them). For example, a weekly recurring meeting counts for 3 instances
  • Recurring Exceptions: These are exceptions set up by the owner. I.e., the Owner could move one instance to another time. These will also be changed by the Rebaser)
  • Totals: The total Microsoft Exchange reports of all appointments and meetings in the DST Delta
  • Cross Check: This is a Sumatra count the meetings we write out in the DST Delta period
    Totals and Cross Check should equal.
  • If Totals and Cross Check don't agree it's because there are either a.) data corruptions in the calendar (which we could write a book about) or b.) someone's been operating on the calendar while you've been generating the report.
Using these numbers allows you to gauge the scope of the problem you have to deal with as the new DST transition looms forward.

Error.log will also produce information to help debug in case the Utilities do not execute on your system.

The /DST switch is completely non-modifying. It reports on which meetings, exceptions, and appointments are in the date range in question.


Thursday, January 25, 2007

Exchange and the New DST Rules for the USA and Canada

The calls have started.

Yes -- we see what a pain it is to update meetings and appointments in Microsoft Exchange 2003 for the new DST rules.

The best summary Microsoft has given of what protocol to follow is in KB Article 931667 -- parts of which read as though they were written by slackers under the influence of ganja. How can they keep a straight face when they write that tens of thousands of Exchange users should print their calendars while administrators do what Microsoft tells them to do to update the system which will not SOLVE the problem, only make it more confusing and less certain?

Come back here in a few days and we'll tell you what we're up to about it.

Wednesday, January 17, 2007

Finding Unsent Meeting Invitations in Outlook

We recently had a case where in a legacy calendar migration some meeting invitations were saved properly in the meeting proposer's calendar, but the invitations actually did not go out to guests.

To find these, Russ developed a query that was implementable in Outlook.

To implement - open Outlook and go to the Calendar.

DO Menus: View-Arrange By-Current View-Customize Current View. You'll see the following:



After clicking "Filter" select the "SQL" tab and enter the following (as above):

http://schemas.microsoft.com/mapi/id/{00062002-0000-0000-C000-000000000046}/8229000B = false

Check "Edit these criteria directly" or you won't be able to input them.

Thursday, January 11, 2007

Calendar Archive Script Using WebDAV

UPDATE: December 12, 2008: Chris graciously updated the script and a copy is here.

One of our favorite calendar-oriented developers (Christopher Quinn from Milwaukee, Wisconsin) produced this script to archive an entire year’s worth of calendar data to an Archive folder in Outlook (as opposed to a separate PST file). The advantages of this are that moving old calendar items to an archive folder speeds Outlook calendar functions and still leaves the data readily accessible to users.

This script also has the advantage of allowing an administrator to auto-archive without user intervention.

'webmailserver = Host Server for mailbox
'ArchiveYear = Year to archive before
'mailbox = mailbox
Dim itemsArchived, TotalArchivedItems

webmailserver = "9to5server"
ArchiveYear = "2006"
mailbox =
jane.doe@yourcompany.com

qdatesed = ArchiveYear & "-12-31T00:00:00Z"
boURL = "
http://" & webmailserver & "/exchange/"
CalendarURL = boURL & mailbox & "/calendar/"
ArchiveURL = boURL & mailbox & "/inbox/"& ArchiveYear & " Archive Calendar"

Call elog("Begin mailbox " & mailbox)

Call CreateFolder(ArchiveURL)

Set Rec = CreateObject("ADODB.Record")
Set Rs = CreateObject("ADODB.Recordset")
Set Conn = CreateObject("ADODB.Connection")
Conn.Provider = "ExOLEDB.DataSource"
Rec.Open CalendarURL, ,3

Do
Call archive()
TotalArchivedItems = TotalArchivedItems + itemsArchived
Loop While itemsArchived > 0

Call elog("Archived " & TotalArchivedItems & " items for " & Mailbox)

Sub archive()
itemsArchived = 0
Ssql = "SELECT ""DAV:href"", " & _
" ""urn:schemas:httpmail:subject"", " & _
" ""urn:schemas:calendar:dtstart"", " & _
" ""urn:schemas:calendar:dtend"", " & _
" ""urn:schemas:calendar:rrule"", " & _
" ""
http://schemas.microsoft.com/mapi/proptag/x81960040"", " & _
" ""DAV:contentclass"" " & _
"FROM scope('shallow traversal of """ & CalendarURL & """') " & _
"WHERE (""urn:schemas:calendar:dtend"" <>
" AND ""DAV:contentclass"" = 'urn:content-classes:appointment'" '& _

Rs.CursorLocation = 2 'adUseServer = 2, adUseClient = 3
Rs.open SSql, rec.ActiveConnection, 3

while not rs.eof
STRsubject = rs.Fields("urn:schemas:httpmail:subject").Value
STRstart = rs.Fields("urn:schemas:calendar:dtstart").Value
STRend = rs.Fields("urn:schemas:calendar:dtend").Value
RecEndDate = rs.Fields("
http://schemas.microsoft.com/mapi/proptag/x81960040").Value

recurring = rs.Fields("urn:schemas:calendar:rrule")

If IsArray(recurring) Then

If isodateit(RecEndDate) <>
Call movemessage(rs.Fields("DAV:href").Value,"/calendar/","/inbox/"& ArchiveYear & " Archive Calendar/", boURL & mailbox)
Call elog ("Archiving," & "," & STRsubject & "," & STRstart & "," & STRend & "," & RecEndDate)
itemsArchived = itemsArchived + 1 Else
Call elog ("SKIPPING--------->," & "," & STRsubject & "," & STRstart & "," & STRend & "," & RecEndDate)
End If

Else
Call movemessage(rs.Fields("DAV:href").Value,"/calendar/","/inbox/"& ArchiveYear & " Archive Calendar/", boURL & mailbox)
Call elog ("Archiving," & "," & STRsubject & "," & STRstart & "," & STRend & "," & RecEndDate)
itemsArchived = itemsArchived + 1
End If

rs.movenext
Wend

rs.Close
End Sub

'Convert Date String to ISO format
function isodateit(datetocon)
strDateTime = year(datetocon) & "-"
if (Month(datetocon) < strdatetime =" strDateTime">
strDateTime = strDateTime & Month(datetocon) & "-"
if (Day(datetocon) < strdatetime =" strDateTime">
strDateTime = strDateTime & Day(datetocon) & "T" & formatdatetime(datetocon,4) & ":00Z"
isodateit = strDateTime
end function

'Move the Item to the Archive folder
Sub movemessage(mSource,mSourceFolder,mDestFolder,mBoxURL)
On Error Resume Next
mDest = Replace(lcase(mSource),mSourceFolder,mDestFolder)
Set mRec = CreateObject("ADODB.Record")
mRec.Open mBoxURL, ,3
If Err.Number <> 0 Then
Call elog(Err.Number & vbTab & Err.Description)
Err.Clear
End If
mRec.MoveRecord mSource, mDest
If Err.Number <> 0 Then
Call elog(Err.Number & vbTab & Err.Description)
Err.Clear
End If
mRec.Close
End Sub

'Create Folder if it doesn't already exists
Sub CreateFolder(strFolderUrl)
On Error Resume Next
set nfRec = CreateObject("ADODB.Record")
nfRec.Open strFolderUrl, , 3, 8912
nfRec.Fields("DAV:contentclass") = "urn:content-classes:folder"
nfRec.Fields("
http://schemas.microsoft.com/exchange/outlookfolderclass") = "IPF.Appointment"
nfRec.Fields.Update
nfRec.Close
Set nfRec = Nothing
End Sub

Sub elog(logstring)
WScript.Echo logstring
End Sub