Perl script for breaking apart large ICS files for a Zimbra migration

A migration can be really confusing and time-constrained. Since we look at the world through the soda straw of converting calendar data it's easy to sometimes forget there's this whole business of bringing in a new system on (possibly) new hardware, configuring it, provisioning users, training users.... You get the idea.

Sometimes your system is not perfectly tuned and you need to simply get the data in.

Michigan Technical University was having time-outs when trying to load large ICS files into their Zimbra calendars. By large we mean 130,000 to 150,000 lines of iCalendar data.

For a quick fix they produced a Perl script to break the ICS files into smaller chunks. Thanks to Cynthia at MTU we can publish this here for those of you who may need it.

The "splitics.pl" script:

• Splits large calendar ICS files into smaller parts.


• Adds a tag (mm) to all meetings.
  

To run this:
Step 1 - collect the files
Make the ICS files and the zcs script. Some of the ICS files are large.
Put the ics files into a directory, perhaps called "calendar-migration"

Step 2 - Use the splitics.pl script
cd calendar-migration
for i in *.ics; do ../splitics.pl $i; done

Step 3 - create the zcs file to use later
cat *.zcs > /tmp/whole.zcs

Step 4 -
move the "whole.zcs" file to the Zimbra server
suppress resource responses with this command:
zmprov mcr zimbraCalResAutoAcceptDecline FALSE
zmprov -f whole.zcs

Step 5 - undo the suppress responses when ready

NOTE: This script also inserts the tag "mm" to all migrated appointments and meetings.

#!/usr/bin/perl # for i in *.ics; do ../splitics.pl $i; done # cat *.zcs > TODO $base_name=$ARGV[0]; $base_name =~ s/.ics$//; print "$base_name.zcs\n"; open(ZCS,">$base_name.zcs") die "Couldn't open $base_name.zcs : $!\n"; print ZCS "SelectMailbox $base_name\n"; print ZCS "emptyFolder Calendar\n"; open(ICS,"<$ARGV[0]") die "Couldn't open $ARGV[0] : $!\n"; while () { last if (/BEGIN:VEVENT/); $headers .= $_; } $event_count=0; $file_number=1; $event=$_; while() { if (/BEGIN:VEVENT/) { # It's a new event! Yay! if ($event_count > 50) { # See if we should dump the output open(OUTPUT,">$ARGV[0].$file_number") die "Couldn't open $ARGV[0].$file_number for output : $!\n"; print OUTPUT $headers; print OUTPUT $event; print OUTPUT "END:VCALENDAR\n"; close(OUTPUT); print ZCS "importURLIntoFolder Calendar http://yourserver/ZIMBRA/USERID/$ARGV[0].$file_number/n"; # print ZCS "postRestURL Calendar $ARGV[0].$file_number\n"; $file_number++; $event_count=0; $event="BEGIN:VEVENT\n"; } else { # Just keep adding it on $event .= $_; $event_count++; } } else { # It's either the end, or it's another line if (/^SUMMARY:/) { # Tag so we know it's a mm meeting $_ =~ s/$/ \(MM\)/; } $event .= $_; } } # We're at the end of our run... dump what is left over open(OUTPUT,">$ARGV[0].$file_number") die "Couldn't open $ARGV[0].$file_number for output : $!\n"; print OUTPUT $headers; print OUTPUT $event; close(OUTPUT); #print ZCS "postRestURL Calendar $ARGV[0].$file_number\n"; print ZCS "importURLIntoFolder Calendar http://yourserver/ZIMBRA/USERID/$ARGV[0].$file_number/n"; print ZCS "\n"; close(ZCS);

Unresponded meeting invitations and your migration

We get this question often enough (at least three separate times from a recent client) that we thought we'd blog on it. They put the statement better than we do:

Will meetings that you have received proposals for, but not responded to
migrate? In other words, if I have been invited to a meeting in Meeting Maker but
not accepted or declined yet will that meeting be migrated?

Short answer: yes.

For completeness, I’ll describe all of the permutations.

If you are a guest, MM records your response.

There are four possible responses to meeting proposals: Accept, Decline, Decide Later, or do nothing.

• If you accept, the meeting WILL appear in your calendar. The meeting request will be removed from your inbox.
• If you decline, the meeting WILL NOT appear in your calendar. The meeting request will be removed from your inbox.
• If you decide later, the meeting WILL appear in your calendar as tentative, and the meeting request WILL remain in your inbox.
• If you do not decide (do nothing), the meeting MAY appear in your calendar as tentative, and the meeting request WILL remain in your inbox. The Sumatra tool does not apply ANY response to the proposal (the tool does not know what your intention was!) However, once you read a meeting proposal, MS Exchange actually forces the meeting to appear in your calendar as tentatively accepted. SO yes, it ‘migrates’.
  
  By the way, there could be instances were the MM exports reports an invalid guest response!! In that case, Sumatra assumes that you have ‘not decided’, and thus leaves the meeting request in the inbox.
  
  The Sumatra rule: Preserve as much information possible. It’s easier for the user to delete something after the migration, than it is to create it.

All of this is for meetings from today forward. In the interests of keeping sanity any unresponded invitations from BEFORE the date of your migration are dropped. If you haven't responded for past meetings by the time of your migration you probably don't want to take it along.

Setting the Outlook Delegate Tab Entries in a Migration

As of January 2009 it is possible to set the Delegates in the Outlook Delegate Tab in a calendar migration. This will work for both an Oracle Calendar migration and a Meeting Maker migration. As is our practice for Delegate / Designate / Proxy migrations, we've built it off existing Microsoft-released and supported utilities.

Follow the procedure for PFDAVAdmin as we have previously documented (this sets Permissions on the appropriate folders).

1. Export your Active Directory data using CSVDE
csvde -f ad.csv -s SERVER -d "OU=Clients,DC=ex2007,DC=sumatra,DC=local" -l "dn,cn,displayname,sn,givenname,objectcategory,mailnickname,mail,homeMDB,distinguishedname,altRecipient,targetAddress"
Note: change the -s and the -d to represent your server and your AD

2. Import the data into the Database table AD_ExportUserList (if you have not already)
NOTES: The CSVDE output will:
· Have more columns of data than is used in the table
· A different order of columns. You must move columns in the output file so that the data align with the table

3. Run the macro M_OutputProxyForLDIFDE

4. Open a command window (i.e., DOS) , and run LDIFDE:
ldifde.exe -f proxy_delegates.ldf -i -s SERVER -k

where -s is the name of your server.

Result will be as follows for your Delegates (In Outlook menus via Tools-Options-Delegates Tab):

For more information on using LDIFDE see:
Using LDIFDE to import and export directory objects to Active Directory

Please note: this will set READ-WRITE (MM Term) permissions only. In Outlook terms it is "Editor."

Why? Because LDIFDE does not let us set READ-ONLY (Outlook term: "Reviewer").

We do the best we can with the Proxy migration tools. In general for setting Delegates we really really really want to be using off-the-shelf Microsoft tools (less chance of an update that makes them obsolete).

Calendar-only Exchange via a server-side rule

We sometimes get asked about running Exchange in calendar-only mode.

Why would you WANT calendar-only Exchange for some users?

Let's say your legacy calendar-only system is on its way out and you need to get with the times (after Exchange, Zimbra, and Google, are there any other options?). It's easier these days to agree on a unified calendar, but there are departments who will give up their legacy email only when pried from their unconscious fingers.

There's no means in Exchange itself of using only the calendar, but some folks at Illinois State University created a server-side rule for calendar-only Exchange.

See http://www.helpdesk.ilstu.edu/downloads/dl.phtml?id=1042
and http://www.helpdesk.ilstu.edu/kb/index.phtml?kbid=1255

Once I found this I found a variety of other universities had solved the same problem.

Virginia Tech's description goes into how to do this on your own (but they kept it client-side).

And UMichigan produced an entire guide for Exchange administrators.

Kudos to all three (and the others I undoubtedly did not find)!

By the way, [MS-OXORULE]: E-Mail Rules Protocol Specification documents as much as you're likely to need to know at the low level about Rules.

The Big Idea

In general, the idea is to Delete any mail not involved with calendaring AND (this is where things get tricky) have your Exchange configured to copy-forward all email to your non-Exchange account.

Since we find enough folks looking to do this, here's our take on the methodology, with screen shots and everything.

I'm going to take you through how to do this using Outlook Web Access. This has a couple of advantages to it: it's simpler for an end-user to do if they're already Linux-enabled and it automatically makes the rule server-side (which is what you want).

1. Start Outlook Web Access, Click Options and select Rules.


2. Create a New Rule for Arriving Messages
   
   
   
3. You will see a warning. Since you want to be calendar-only I cannot see a reason you would want other rules in Outlook -- so nuke away!


4. Let's give this an obvious name like "calendar-only rule" and have it apply if the message is TO you or CC'ing you (you with me so far?):




5. If any message comes in -- we want to DELETE it




6. EXCEPT if it is a meeting invitation or update to a meeting.
   
7. Save it and we are done! (With this part at least -- remember, you need to be configured server-side to copy your email where you want it)
   
8. So NOW let's TEST THIS OUT! Let's have another user (call him "Russ") send email to the poor fellow we modified above (call him "Zyg").


9. At the same time let's have Russ send Zyg a meeting invitation:


10. Logging into OWA as "Zyg" we see that Zyg has the meeting request in his inbox,

and the email is in Deleted Items. It works.



WHAT REMAINS TO BE DONE

• You still need to have your Exchange ID set up to copy all email to where you really want it. Given the variability in configurations, which is obvious even from the university solutions given above, we do not want to start documenting how to do this in general. There are simply too many ways to skin that cat.
• You can also create a rule client-side using Outlook and then distribute it (we toyed with using Illinois State University's rule, and they kindly sent it to us and would have allowed us to put it up for downloading -- many, many thanks, Randy!). We had not seen a solution involving OWA and wanted to add some knowledge to the discussion rather than refer to someone else's.
  

Setting Default Permissions for Exchange Resources

December, 2008. The subject is migrating proxies from Meeting Maker. Among our clients in December were two sites each with 1000 users, about 100 of which were resources. One had 90,000 proxy objects, the other had 5,000.

Why the 1:18 ratio on what were otherwise similar data sets?

The one with 90,000 proxy objects in Meeting Maker wanted to give everybody in Outlook Delegate access to each resource. In round figures 100 resources times 900 people gives you 90,000 Delegates.

So we were looking at using our usual method for setting Delegates (which would take a long time).

But it's actually simpler than that.

Meeting Maker does not have a default level for giving EVERYONE access to a resource.

BUT Outlook DOES!

And PFDAVAdmin makes it easy to propagate these permissions centrally (so long as you have an Exchange Admin who's willing). In this case it's criminal not to use it.

You can set up an import file as we've documented before, using

\Everyone Reviewer

to provide read-only access.

Or (quoting liberally from the documentation for PFDAVAdmin),

The Set Calendar Permissions option enables you to perform a bulk edit specifically to change permissions of the Calendar folder so that a single operation can modify the settings of thousands of users on a server. This is useful for changing the default permissions setting of the Calendar folder of all or many users, so that team members can view other member's calendars. Using Microsoft Office Outlook® enables changing only one user at a time, so the Set Calendar Permissions option is a good solution for this kind of task. In addition to changing the permissions of the Calendar folder, by using this option, a bulk-change also occurs on the permissions of the FreeBusy Data folder (hidden folder) of all the users on a server. If the FreeBusy Data folder permissions were not changed, the users will not be able to access another user's calendar.

I was going to start taking screen shots of how to do this exactly, when I said to myself, "Self, someone must have already done this."

And so they had. Thank Amit Tank whose FAQ: Give Calendar Read Permission on all Mailboxes - PFDavAdmin admirably takes you through this.

For international users, Set calendar permissions with PFDAVADMIN goes over how to filter for translated "Calendar" folders.

CorporateTime versions and migration to Exchange / Zimbra

Usually we see Oracle Calendar Server 9.0.4 ("Coca Cola Classic") but one account in the Boston area is running an older CorporateTime server (looks like version 7) to migrate into Zimbra.

Our new version of the conversion code supports this with the following modifications (they're running on a *nix platform):

uniuser -ls -format %s\:%g\:\:%id\:%no -n 588 -p xxxx > /var/tmp/users.txt
unires -ls -n 588 -p xxxx > /var/tmp/resources.txt
unicpoutu "S=LASTNAME/G=FIRSTNAME" -f exp-lastname.txt 588

unires is the older method for getting resource lists. The format is different from OCS 9.0.4, but we've redone the logic in the OraCalReader to take care of it.

Migrating to Exchange: One Summary from the Trenches

In December 2008 we migrated the school of Veterinary Medicine at the University of Illinois.

Craig there sent us a very gracious thank you note (a rare but welcome occurrence in our line of work) that was also a good summary of what to expect in migrating into Exchange from either Meeting Maker or Oracle Calendar.

He has prepared this version for us to publish here:

With the services of Sumatra Development LLC, Illinois Veterinary Medicine has
successfully migrated 150 accounts from a local Meeting Maker server to a shared
campus-wide Exchange resource.

Interwoven scheduling among administrators and researchers elevates the data to a critical level of importance for the organization. Migrating data from one proprietary package to another, neither of which is governed by a standards protocol, is unbelievably tricky. Yet we were able to accomplish this migration in a transparent manner that our end users expected. They just wanted it to work and it just worked for us.

Looking back, I'd say the hardest parts were organizing our calendaring accounts and training our user base on Outlook. Not coincidentally, these parts took the longest. I started this project a little apprehensive about working with two organizations (outside vendors from my perspective) with me in the middle.

Sumatra was converting our data and a central campus IT unit was inserting the
data into the Exchange service. I am pleased to say it worked wonderfully. The
Sumatra instructions and data sets were easily managed by the campus Exchange
service managers. A little extra communication on the side and things worked
seamlessly. When we needed people to be there for us, they were there. My
compliments go Sumatra for their care and dedication at what they do.

For our part, the role of tirelessly training, establishing resource sharing and
answering the countless questions users come up with when they skip the training
sessions was something we were prepared to handle. The arrangement let us handle
the part we do best and let the calendaring and Exchange experts do their thing.