Hidden Treasure in your Calendar Data

We at Sumatra revel in our reputation as quantitative calendar geeks. One of the means we use to show our prowess with calendar data comes from some of the statistics we can read so readily from Meeting Maker databases, of which the following data we ran on February 8, 2008 is a sanitized example.

Some of the most interesting statistics, the number of meetings that are current is only 2.53% of the total meeting volume. Activities track almost exactly the same at 2.56%. As some of you have heard us say: Our highly advanced mathematics has proven that most events have already happened.

You can expect the same statistics from your Exchange calendar data on an ongoing basis.

Overall Stats

Item	Value	% Total
8Feb_DB_v8.8.0.7.mdb File size:	562,237,440.
Total number of meetings:	62,560.
Meeting timeframe:	1/1/1940 5:00:00 AM-12/31/2039 12:30:00 PM
Total number of meetings newer than 2/8/2008:	1,591.	2.54%
Total number of meetings older than 2/8/2008:	60,969.	97.45%
Total number of meetings without any mapped users:	62,560.	100.00%
Total number of meetings without any mapped users and newer than 2/8/2008:	1,591.	2.54%
Total number of Guests:	211468
Total number of guests attending meetings newer than 2/8/2008:	7,791.	3.68%
Total number of guests attending meetings older than 2/8/2008:	203,677.	96.31%
Total number of guests attending meetings without any mapped users:	209,603.	99.11%
Total number of guests attending meetings without any mapped users and newer than 2/8/2008:	7,791.	3.68%
Number of foreign guests	0	0.00%
Total number of activities:	1,869,557.
Activity timeframe:	1/1/1940-1/2/2040 8:00:00 AM
Total number of activities newer than 2/8/2008:	48,026.	2.56%
Total number of activities older than 2/8/2008:	1,821,531.	97.43%
Total number of Activities without any mapped users:	1,869,557.	100.00%
Total number of Activities without any mapped users and newer than 2/8/2008:	48,026.	2.56%
Overall Timeframe:	1/1/1940-1/2/2040 8:00:00 AM
Number of days:	36526

When we turn our attention to the top meeting users, we see some other interesting results. From the same server with approximately 2000 users, the top ten users account for almost 20% of the total meeting activity, account for about 15% of all guests, and about 3% of all activities. While your mileage may vary, it will not vary by much.

Top Meeting Users

User	Num Mtgs	% Total Mtgs	Num Guests	% Total Guests	Num Actvy	% Total Actvy	Grand Total	% Grand Total
AV Student Staff	3,025.	4.83%	5,939.	2.80%	563.	0.03%	3,588.	0.18%
Bela Bartok	1,488.	2.37%	5,514.	2.60%	3,134.	0.16%	4,622.	0.23%
Adam Ant	1,281.	2.04%	4,516.	2.13%	2,799.	0.14%	4,080.	0.21%
Claudette Colbert	1,084.	1.73%	3,318.	1.56%	12,446.	0.66%	13,530.	0.70%
Deanna Durbin	1,065.	1.70%	2,385.	1.12%	2,045.	0.10%	3,110.	0.16%
Erik Estrada	1,031.	1.64%	2,755.	1.30%	4,655.	0.24%	5,686.	0.29%
Felix Frankfurter	906.	1.44%	2,029.	0.95%	10,234.	0.54%	11,140.	0.57%
Research Team Conf Room	895.	1.43%	2,596.	1.22%	289.	0.01%	1,184.	0.06%
Gary Gilmore	857.	1.36%	1,845.	0.87%	3,590.	0.19%	4,447.	0.23%
Harry Hope	755.	1.20%	2,375.	1.12%	2,947.	0.15%	3,702.	0.19%
Top User Total	12,387.	19.80%	33,272.	15.73%	42,702.	2.28%	55,089.	2.85%
Grand Total	62,560.	-	211,468.	-	1,869,557.	-	1,932,117.	-

Now let's take a look at one of everyone's favorite subjects: Meetings and Recurring Meetings. As you can see, over 90% of meeting objects are one-time meetings. Of the recurring meetings, the most common are "Daily_Every_N" (which when N=7 means a weekly meeting).

Meeting Frequency Profile

Day	Num Recurring Meetings	% Total Meetings	% Total Recurring Meetings
ONCE	58,824.	94.02%	-
DAILY_EVERY_N	2,991.	4.78%	80.05%
DAILY_DAYS_OF_WEEK	364.	0.58%	9.74%
WEEKLY	315.	0.50%	8.43%
MONTHLY	66.	0.10%	1.76%
Grand Total	62560	-	5.97%

Recurring Meeting Profile

Day	Num Recurring Meetings	% TotalMeetings	% Total RecurringMeetings
Finite end date	3,568.	5.70%	95.50%
Ongoing (no end date)	168.	0.26%	4.49%
Grand Total	3736	5.97%	-

Trust Me (Part Four) - I see a Resource Forest thru the trees

What's the old expression -- you can't see the forest for the trees? Well, on my fourth attempt, I can.

To recap: What was the problem? I wanted to insert calendar data into Exchanged 2007 that was configured using a Exchange Resource Forest topology. The user mailboxes were linked to a accounts in a user forest. Those user mailbox accounts were disabled. You can not use Exchange Web Services to impersonate a disabled account.

What worked? Here is what I did:

• Upgraded Exchange 2007 to SP1,
• Created a service account in the resource forest
• Gave that service account DELEGATE permissions to ALL of the disabled accounts.
• Granted permission for the service account to see the user forest's AD
• Inserted calendar data!

Finding the disabled accounts, and assigning delegate rights to the service account turned out to be easy, thanks to Jian Li’s MSExchange Team Blog “How to access multiple resource mailboxes in Exchange Web Services (EWS)”. He described how it works, and (even better) created a script that has worked well for us (get the Microsoft Script.)

Oh, for the last few days the Exchange System Attendent decided to automatically start, and remain running. No idea why.

-R

Trust Me (Part Three)

Ran into two issues when configuring the resource forest and the user forest: service accounts and Address List System Service failing.

Service Account: I created two service accounts, one on each machine. The service accounts had the same name. When I went to link the resource account to the account in the user forest, accessing the linked domain controller failed.

Lesson learned - The link failed because I didn't qualify the service account with the user forest (e.g. userforest\mySvcAccount). So Exchange tried to link to the user forest domain controller with the resource service account and failed. Had I created service accounts with two different names, this problem would not have tripped me up.

Address List:

After I fixed the service account issue, the last step of in the "linked mailbox" wizard failed with an invalid address. After much head-scratching, I discovered the system attendent had failed with an MSExchangeSA event ID 1005: "Unexpected error The Local Security Authority cannot be contacted ID no: 80090304 Microsoft Exchange System Attendant occurred. "

Dave Goldman blogged about this: Creating a new mailbox in Exchange 2007 with the new-mailbox cmdlet fails with Address List Service not Available, but only addressed one aspect. My problem was choice (4) - SA stopped. I haven't figured out why it's failing, but restarting the SA allowed me to complete the mailbox link.

OK--everything is set. Next time I'll report on the results of impersonation.

Export-Mailbox -- "Exmerge" for Exchange 2007

I had to generate a five PSTs from client data inserted in our lab to help a prospect prove to his management team that the Sumatra Migration tool could read and insert their calendar data into Exchange 2007. It gave me an opportunity to test a new commandlet "export-mailbox" released with Exchange 2007 SP1. Export-Mailbox offers many of the features most of us loved in Exchange 2003's ExMerge.

Be forwarned - the commandlet comes with contraints:

• It must be run from a 32 bit client machine with Exchange Management Tools (Version Exchange 2007 SP1 or later) installed and Windows PowerShell (Download: Microsoft Exchange Server 2007 Management Tools (32-Bit));
• It requires Outlook 2003/Outlook 2007 be installed on the 32-bit client machine;
• Must be "run-as" using an account with Exchange Organization Admin or an Exchange Server Admin privs.

Here is how I exported a single mailbox to a PST:
Export-Mailbox –Identity < mailboxuser > -PSTFolderPath < pathToSavePST >

Here is how we told the client to Import the data from a PST into a client mailbox:
Import-Mailbox -Identity < mailboxuser > -PSTFolderPath < pathToSavePST >

I covered two of the other command-line variations. Ricardo Guerro's MsExchange Team blog post How to Export and Import mailboxes to PST files in Exchange 2007 SP1 details this commandlet. It comes with descriptions and screen-shots.

Yea! Another calendar migration tool for Exchange 2007.
-R

Trust Me (Part Two)

This is the second installment on my experience trying to setup a resource forest trust.

Last night I re-installed VMware's Server product on an Windows Server 2003 SP2 box that also had Exchange 2007 SP1 installed. I rebooted the box and left. I lost today's popularity contest to "Smiling Bob" after our early-morning users received "Service Unavailable" when they tried to visit our intranet, OWA, and anything that used Exchange Web Services.

What happened? The Application Event Logs show W3SVC-WP Event IDs 2268 and 2214, and System Events IDs 1002, 1039. It looks like VMware Server and Exchange 2007 are both trying to control IIS (VMware wins....)

To restore the server, I uninstalled VMware Server and reset IIS to run in native mode using this AdsUtil command line:

cscript %systemdrive%\inetpub\AdminScripts\adsutil.vbs set w3svc/AppPools/Enable32BitAppOnWin64 0

BTW, if you want to re-enable 32 bit apps (WOW64 Mode), run this command:
cscript %systemdrive%\inetpub\AdminScripts\adsutil.vbs set w3svc/AppPools/Enable32BitAppOnWin64 1

It was suggested that VMware's WORKSTATION product would be a better choice. I just installed the evaluation version. 'Sure n b'gora' ...web services and the intranet are still available. (Thanks, Chrisopher Q., for the tip!)

Now it's back to trying to build a one-way resource forest-trust.
-R

Zimbra to Exchange Migration - First Request

It happened sooner than we thought it would: we've got our first request to migrate calendars from Zimbra into Exchange. Any other interest out there in this path?

Trust me

We are testing Exchange 2007 in our 64-bit Windows Server 2003 Exchange Resource Forest Topology using two virtual servers (with VMWare -- Microsoft Virtual Server does not work in a 64-bit environment). We created two VMs -- the "user forest" and the "Exchange forest". We set up a one-way trust between the two forests, and tried to create a "Linked" user mailbox.

After a head-banging week, we can NOT get this to work. No matter what we do, when we try to link to the mailbox we get a Event ID 2130 (MsExchangeADAccess....Exchange Active Directory Provider could not find an available domain controller). We can ping the domain, the nsLookup is fine, dcdiag /v, netdiag /v -- everything comes back without errors.

Is anybody else having this problem? How did you solve it?

One of Sumatra's clients suggested this might work if we created five VMs - one for the DNS/DHCP, a second for the user DC, a third for the resource DC, a fourth for Exchange, and a fifth for the client. We will test his theory later this week (or this weekend).

Here is an image of the topology linked from the Microsoft Exchange Resource Forest Topology article:


-R

Sumatra Migration Documentation version 9.4

Based on some feedback from the folks at the University of Pennsylvania we've revised the migration documentation set.

It's now at version 9.4.

Those of you in active migrations should have it in your ftp accounts if we haven't already emailed it to you.

Since there are a fair number of folks on what we sometimes refer to as the "ninety-nine year plan" contact us if you want the most recent version.

Client Side Migration to Exchange from Oracle Calendar

I might have mentioned sometimes that things in our office go in weird phases. December was "Pennsylvania" month because of the four migrations we had going on in that state all at once.

February seems to be "can you tell us how inexpensively we can do this, preferably without you?" Sort of the exact opposite of Valentine's Day, and not really geared to get on our good side, but it does illustrate a point.

Given that we're about moving calendars server-side and avoiding the limitations I end this with, and we're really up-front about what we can do that nobody else can, we're going to give you the safe migration advice on how to do this client-side, user-by-user, and not involve us.

As is traditional for us we use John Lennon as an OCS 9.0.4 user, migrating to Outlook.


Export Your Oracle Calendar Data

From OCS, go to the File menu, choose Export Data...


Select vCalendar file:
If you select iCalendar, you can't move tasks.

Continue and make your selections:


To pull task and calendar data into Outlook you run the same process in reverse on the Outlook side.

Here we use Outlook 2003 as the example (lots of you are still using it).


Import Your Oracle Calendar Data into Outlook
Open Outlook
On the File menu, choose Import and Export...


Select Import an iCalendar or vCalendar file (.vcs):

Browse to your export file from OCS.

John's end result looks something like this:

The limitations of this process:

• It is client-side only. Each user or someone else must do this.
• It does not handle resources well. Yes, you can bring the resource calendar over, but they are not linked to the end users who are scheduled for the resource
• Recurring appointments become individual appointments.
• Daily Notes and Day Events do not automatically become "All day" events
• Guest lists are dropped and meetings therefore all become appointments

Advantages of this method:

• It is inexpensive
• It uses only off-the-shelf functionality
• With a good help desk it is probably do-able

You can go through a similar process for importing into Zimbra with pretty much the same limitations. I'll write that up if I get any requests.

Zimbra to Yahoo to (possibly) Microsoft?

You've got to start asking yourself what would Microsoft do with an open source alternative to Exchange if they consummate a union with Yahoo.

I envision an episode of Oz or some "reeducation camps" inspired by the Chinese Cultural Revolution.

Whatever comes we're still cranking away on our migration technology -- ironically we've (almost) completed the first prototype for an Exchange to Zimbra migration.

Now, of course, we're also working on migrating Zimbra to Exchange.

This is not a surprise to those of you who have dealt with us in the past. Our decision process on which projects to work on is based on simple free market economics: who is asking us to do things and are they willing to pay for it?

Terminated Users, Broken Meetings, and Exchange 2007

Given the current economic situation we expect that many US corporations will soon be filled with a "right-sized" group of users. This will of course leave rotting corpses throughout the organization and some of them are going to be festering away in your Exchange 2007 store.

We've heard it referred to as the "terminated user" problem and it works like this:

Yorick, a diligent young exec just fresh from his MBA with little experience other than managing his BMW and his credit cards, has been busily setting up meetings with people across all departments. He is now gone but his meetings live on. (Note: We realize this is an idealized scenario. In the real world Yorick would be promoted while people with far more experience but higher salaries and more benefits would be nuked wholesale, but we try not to be bitter.)

So recurring meetings managed by Yorick, which might or might not be relevant anymore, are sitting in calendars across the company.

You the Exchange Administrator would like to get rid of them wholesale, but there's no way of doing this except for logging in as Yorick and manually finding and cancelling all meetings, making sure to send updates. Multiply this hassle by the body count in your reorg.

But it does not need to be like this.

Since as part of our calendar migrations we're creating and deleting meetings ALL THE TIME, we've started experimenting with some tools based off our insertion code that will go through a user's calendar, cancel all meetings they proposed, and automatically send updates to guests.

Problem solved.

We've started with a congruent problem: finding orphan conference rooms from cancelled meetings and built it into our main code via the "Test" button (which those of you in the midst of E2K7 migrations have gotten used to)

To check for orphan conference rooms:

Why are we telling you all of this? Because we're looking for folks who really want the problem solved and are willing to work with some early code to help us work out the best way to invoke and use this in the Exchange environment.

You're going to need to set Permissions for a service account to run this app as you would for a migration -- so be advised of that. But we're more than happy to share this with folks who contact us directly looking to try it out.

Outlook Meeting Reminders: OL2003 + E2K7 = Confusing

We have some folks migrating into Exchange 2007 keeping their Outlook 2003 calendars, who started to notice some odd behavior with their reminders.

15 Minutes seems to be the magic number for a meeting reminder default when you put Outlook 2003 in an Exchange 2007 environment.

Expect that under a variety of conditions of meetings being proposed to and from OL2003 that the default will be set to 15 minutes on the ATTENDEE's calendar, regardless of where it was set in the meeting ORGANIZER's, and regardless of the ATTENDEE's Default reminder setting.

The situation arises with default settings and reminders if Outlook 2003 is the origination point. We haven't (yet) seen problems with OWA or Outlook 2007.

There are in the support database a variety of problems listed with meeting reminders in Outlook (appointments seem to be working OK, but maybe that's just our eyes glazing over).

Among the classics:

The reminder for the meeting is removed when you accept a meeting request in Outlook 2003 which is based on Rules removing reminders

A reminder for an all day event meeting request is reset to 18 hours when you send the meeting request in Outlook 2003 which is self-explanatory.

And Meeting reminder settings in a meeting request may be cleared or may take precedence over the meeting reminder settings in Outlook 2003 which says there should not be a problem (but which we cannot reproduce).

Try just hitting the support.microsoft.com and searching for "Outlook reminders".

findItem and restrictions in EWS code

For those of you Exchange 2007 migrators who wondered why Sumatra needed to put categories in the subject line -- We ran into a bug in Exchange 2007 RTM (Microsoft confirmed fixed in SP1). The same problem showed up in this post involving contacts: http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1849174&SiteID=17

Basically: you can't use FindItem with restrictions that use SUBSTRING in an EWS XML query:

<restriction>
<t:contains containmentmode="Substring"
ContainmentComparison="IgnoreCaseAndNonSpacingCharacters">
<t:ExtendedFieldURI PropertySetId="00062008-0000-0000-C000-000000000046"
PropertyId="34100" PropertyType="String"/>
<t:Constant Value="mmConv102659080256Z"/>
</t:Contains>
</restriction>

So in contrast to almost every other release Microsoft has done for Exchange -- SP1 actually IMPROVES the migration process! I feel like Fred Sanford expecting to join Elizabeth imminently.

Exchange 2007: to sp1 or not to sp1?

One of the features clients love about the Sumatra migration technology is our back out strategy: if something goes wrong during a calendar insertion we can selectively remove all the data we put in and do it more quickly than a full Exchange restore.

We do this using keywords.

For Exchange 2007 pre-sp1 because we were under the gun to complete the code for a migration in July, we took a quick path that placed the keyword in the "Subject" line (and created an option to remove these).

For sp1 we no longer insert our keywords into the Subject: line (meaning you get all the benefits of our back out capability without the annoyance of having to remove the keywords).

Bottom line: either pre-sp1 or sp1 will support a calendar migration, but each method requires a different version of the insertion code.

Consult with Sumatra if you need to decide which version to use during your migration.
Update: January 19, 2008: We've confirmed there were some bugs with EWS that the sp1 service pack fixes. We'll blog on it soon.

Rate Limiting Steps in an Exchange 2007 migration

We've started to get the question "What's the choke point in a calendar migration into Exchange 2007?"

Keeping in mind that in 2007 we use EWS, you've got to think of the migration as running via HTML. So network efficiency and I/O are the crucial factors.

Russ produced this list of limiting factors (in priority order)

• Speed of the SAN (Mostly it's I/O bound)


• Speed of the NETWORK (Next, it's related to how fast HTTPS requests are
  sent/received)


• Speed of the SERVERS (CAS, then MBX) (Third, how fast the Web Services Requests are PROCESSED)


• Speed of the 32-bit (Fourth, how fast those requests are GENERATED)

In Exchange 2000/2003, where we used CDO, the horsepower of the individual back end Exchange server played a more crucial role.

Categories and Extended MAPI Properties in Exchange 2007

Sumatra used categories and extended MAPI properties to "tag" items that it inserts into Exchange. Sumatra uses this tagging strategy to find (and update) specific meetings. This works well in Exchange 2003, and doesn't work at all in Exchange 2007.



We set these items on messages in the meeting organizer's calendar. But wait--those categories and extended MAPI properties do not get transmitted with a messages in Exchange 2007! A PSS call yesterday confirmed that this behavior is possibly a bug.!?! I'll re-post if we find a solution



Here's the most relevant part of the code that adds categories (using XML):

<t:CalendarItem>
<t:ItemClass>IPM.Appointment</t:ItemClass>
<t:Subject>Russ and Nancy 1 on 1</t:Subject>
<t:Categories><t:String>mmConv102659080256Z!9</t:String></t:Categories>
<t:ExtendedProperty> <t:ExtendedFieldURI PropertySetId="00020329-0000-0000-C000-000000000046" PropertyName="SumatraKeys" PropertyType="String"/> <t:Value>E1D5E39F08206011E39F9218</t:Value></t:ExtendedProperty>

Zimbra and Private Activities / To-Dos

In the several Meeting Maker to Zimbra migrations we've done, the issue of what to do with "Private" activities and to-dos always come up.

Since Zimbra pre-version 5 does not have Private capability at the meeting level we've done all of these options:

• Delete "Private" Activities / To-Dos before they are inserted (clean, easy to understand, infuriating for some users)
• Pre-pend "PRIVATE:" to the relevant activities (easy to find and the user can deal with them as they will)
• Separate the "Private" items into a separate database and insert into separate calendars. A little time-consuming all around, but it worked this weekend at a migration in Pennsylvania.

In Zimbra 5 this all becomes moot -- but we want to make sure current clients contemplating a Zimbra migration know their options.

Exchange 2007 sp1

Our preliminary read on how sp1 has an impact on our insertion code: No problems.

Russ ran an insertion in the test lab with no problems.

We have a field test coming up this weekend and we'll let you know the real world results.

Contacts Migration into Zimbra

For soon-to-be former Meeting Maker sites, you can now put your MM contacts into Zimbra.

Contacts get exported as CSV files which you can upload with curl or zmprov.

To create them:

1. Create a directory called c:\sumatra\contacts
2. Open the Database and go to "Macros"
3. Double click the macro entitled "M_OutputZimbraContacts"
4. Walk away from the machine for about a half hour. We haven't done a good interface for telling where things are in the process, but if you look at the directory you created you'll see the contacts files being created.
5. They'll be in the form LOGIN_ID.csv so it's hard to confuse them with the calendar files

Right now this only works for Meeting Maker contacts because we can read them server-side.

For Oracle Calendar Server contacts, which we can only read client-side, we'd need to do something different. Which we will if we hear sufficient demand.

Accessing Exchange Resource Mailboxes

Microsoft confirmed Exchange Web Services (EWS) does not support to "impersonation" access to a mailbox if the account is disabled. (see: http://msexchangeteam.com/archive/2007/12/13/447731.aspx) This requires Sumatra customers enable resources before an insertion.

That Microsoft post did say the only way to access disabled-account mailboxes with EWS is to use Delegate Access. Sumatra's support team suggests an additional use for the script as an easier way to set up access to the resources, particularly when an end user "manages" one or more resources.

http://msexchangeteam.com/files/12/attachments/entry447730.aspx