Introducing iCalverter: Office365 Cleanup Tool

(This blog has been written as a partner blog to my presentation delivered at the London Apple Admins – 23rd June 2016 meet-up at the LSE)

Hi All. This blog details a little GUI app solution I knocked up to help us cleanup Calendars for migrations. iCalverter can be found on GitHub here.

Introduction

As it seems the days of self-hosting services are falling away (especially email, contacts and calendar services), we have found ourselves migrating a number of our customers from on-premise Kerio Connect solutions, to Microsoft’s hosted Office 365 platform.

With this, comes a number of challenges. Office 365 provides a handy migration toolkit for on-boarding email services into a hosted instance. However, this works best with other Exchange solutions as the source and, no matter how hard Kerio Connect tries, it isn’t a fully fledged Exchange Server.

What does this mean? Well, for email, not too much. We need to setup the email migration using an IMAP connection instead of any number of fancier Exchange techniques.

What about Contacts and Calendars? Well, these need to be manually exported from Kerio Connect, and manually imported into the users’ new O365 solution. This process needs to be
repeated per user.

The Issue Challenge

Generally, this isn’t too difficult or technically taxing. That is, until the Calendar imports hit something they don’t like, and you get this message:

Saving attachments to ical

There are a few variations on this message, but they all have the same result. The further bad news is that you’ll see one of these prompts for every calendar event that the import doesn’t like. For users with years and years of calendar entries, this message can appear anywhere from a handful of times, right up to hundreds of times over the course of a day. Additionally, the only sure-fire way to dismiss the message is to use the “Revert to Server” option, thereby deleting the event.

Now we can’t have one of our engineers sitting at each user’s workstation, dismissing these messages all day. In much the same way, we can’t ask the end user’s to perform this highly distracting action. What we needed was a solution to cleanse the exported calendar file, prior to import.

Step Up iCalverter

iCalverter builds on a script found on this post by Dr Drang, where he utilised a Python framework to edit his Airline-supplied ics files to have the data he required in the fields he wanted. iCalverter also utilises a Python framework called ‘icalendar‘, developed by the icalendar team, in order to manipulate the “VEVENTS”.

1. After launching iCalverter, you’ll be asked to select an .ics file.

Launching

2. Select the .ics file and the iCalverter app will go to work. Once complete you’ll be shown a message detailing the location of the source file and the name of the output file.

Finished

3. The output file will have ‘-outputfile’ appended to it and is ready to be imported into the new calendar account.

output file - trimmed

Technical Bit

Here’s the technical bit behind iCalverter:

Deep Dive

First, we needed to work on the causes of the messages.

After some trial and error, we found the following calendar events commonly caused these popups:

  • Events with file attachments.
  • Events with reoccurring events.
  • Events with other invited users.
  • Events where the user has been invited.

But how do we know what these events look like? Well, if you force an ics (calendar export file) open in a text you get a nicely formatted text file with each event headed with a “BEGIN: VEVENT” and tailed with an “END: VEVENT”:

event section

Great! Now what about identifying the ‘bad’ events listed above?

Well, these can be identified with the following keys, within each pair of “VEVENT” tags:

  • Events with file attachments.
    • “ATTACH”
  • Events with reoccurring events.
    • “RRULE”
  • Events with other invited users.
    • “ATTENDEE”
  • Events where the user has been invited.
    • “ORGANIZER”

By stripping out these four tags (and values) on every calendar event that has them, we can greatly reduce the rejected calendar event imports.

There are some limitations with this action:

  • Events with file attachments will have those attachments stripped out.
  • Events with reoccurring events will only preserve the original event and none of the reoccurring events.
  • Events with other invited users will need to have those invites resent out.
  • Events where the user has been invited will become explicit events in the user’s calendar and the user will need to request to be re-invited from the original organiser.

With end user’s made aware of these limitations, the migration can occur smoother and faster than before. However, to manually edit each file is time consuming and prone to mistakes.

Script Time

Based on the above mentioned script from Dr Drang, I wrote / modified a python script to loop through every pair of “VEVENT” tags and:

  • Set every occurrence of the “ATTACH” tag to nothing
  • Set every occurrence of the “RRULE” tag to nothing
  • Set every occurrence of the “ATTENDEE” tag to nothing
  • Set every occurrence of the “ORGANIZER” tag to nothing

After this, I then used subprocess calls to use `sed` to remove these lines entirely.

This is all handled within the “Calendar Assistant.py” script, inside the iCalVerter AppleScript application.

The final part is the AppleScript script inside the application that allows the GUI selection of the .ics file and displays some GUI messages to the end user.

Next Steps

This project was one of a few I’ve used to try to teach myself Python as well as to an attempt to solve an issue I was having. As a result, it’s still very much a work in progress and I already have the following thoughts on next possible steps:

  1. Moving the `sed` calls inside the main loop to fully support multiple file selects (firstly in the command line, followed by the GUI)
  2. Adding a progress bar to the GUI to show working on the larger .ics files.
  3. Rewrite the `sed` calls to exclusively use Python instead of bash
  4. Continue testing and tweaks to minimise those calendar import pop ups.

Summary

There you go, hopefully that’ll help other Mac Admins in similar situations, or at least provide a started script for other projects. As always, if you have any questions, queries or comments, let us know below and I’ll try to respond to and delve into as many as I can.

The usual Disclaimer:

While the author has taken care to provide our readers with accurate information, please use your discretion before acting upon information based on the blog post. Amsys will not compensate you in any way whatsoever if you ever happen to suffer a loss/inconvenience/damage because of/while making use of information in this blog.

Special Note About London Apple Admins

If you in some manner manage or administer devices with an Apple logo & work in London, or happen to be in town when a meet is happening… This is the group for you!

Each meet will start with the 1st hour (7pm-8pm) being a series 5-10 minute presentations. These presentations can be a short “Hello”, “Look at this cool thing I did” or “I really need help understanding <something>…” The idea is for these to create discussion points.

After the presentations; we eat, drink & discuss.

That’s it.

For more information, check it out here.

Leave a Reply

Your email address will not be published. Required fields are marked *