Creating and Deleting Meetings: Adding and removing meetings in Outlook using PowerShell part 3

In the previous post Getting PowerShell ready to work with Exchange: Adding and removing meetings in Outlook using PowerShell part 2 we looked at preparing the script to create meetings in an Exchange calendar. Now that the connection has been created to the appropriate calendar we can start creating meetings (which is the easy bit) and deleting them when necessary (which is not so easy).

There are a number of tutorials on using PowerShell to create a meeting in an Exchange calendar, and include the necessary participants.  Two I found particularly helpful were Aman Dhally’s Powershell and Outlook: Create Calendar Meetings using Powershell function and Mike Pfeiffer’s Creating Calendar Items with PowerShell and the EWS Managed API (which includes an interactive script that is great for testing).

In our case the details of the appointment were captured through a separate system and held in a SQL Server table.  The script used a view of that table to select the appointments that had not yet been processed and read them into an ADO.net data table. This is not really the focus of this series of posts so I have not included the detail of accessing the data from the database not of constructing the body of the meeting record using token substitutions. Instead we will focus on the mechanics of creating a meeting record.

Meeting Parameters

A meeting record consists of 5 parameters added to the Exchange Appointment object ($appointment):

  • The title of the meeting record: $appointment.Subject
  • The body of the meeting record: $appointment.Body
    • Note: this accepts HTML content by default (unlike a regular email record)
  • The meeting start date/time value: $appointment.Start
  • The meeting end date/time value: $appointment.End
  • And what makes it a meeting rather than an appointment is the inclusion of participants. In our case required participants: $appointment.RequiredAttendees

Typically these values will be generated by the PowerShell script, and in our case the values come from the SQL Server table. The following code snippet assumes that these values are in the $row object.

Notes:

The $exchService is the Exchange connection, see the previous post on creating this object.

Because RequiredAttendees is a multivalue construct it needs to be assigned with a pipe rather than a simple assignment as in the other parameters:

The meeting record is created, and Required Attendees notified, when the $appointment.Save command is executed.

Deleting Meeting Record

Most tutorials stop with the creation of the meeting (or appointment), and in many cases this is enough.  Unfortunately in our case not only were meetings initiated in the online booking system queried by this script, meetings could also be deleted in the same online booking system. We therefore needed a mechanism for the script to delete them from the Exchange calendar.

Designing a view in SQL Server to identify the meetings that had been cancelled was trivial. The real problem was at the Exchange end. In order for the script to delete the meetings we needed to be able to find which meeting records had been created in Exchange for each booking in the online system. This is where advice on the internet started to run very thin.

Step 1: Identify the Meeting Created in Exchange and store the identifier

There are a couple of different ways to identify a meeting in Exchange, but the one described here should retrieve a globally unique identifier that will persist even if a meeting is edited in Exchange. it also retrieves a text string that can easily be stored in whatever system underpins the script, in our case the table of bookings in SQL Server.

To capture the GUID we first need to extend the Exchange connection object with an extended property set

With this in place we can then capture the GUID after the meeting is created

The variable $CalIdVal64 is a base 64 encoded text string which represents to binary GUID. The text string can now be stored with the booking information and can be used to find and delete the meeting if necessary.

The full code snippet is available at the end of this post.

Step 2: Using the GUID to delete a meeting from Exchange

As with creating meetings, the script uses a database view to access details of meetings that need to be deleted from Exchange. In the code snippet these details are in the $row object

  • $CalIdVal64 is the base 64 encoded GUID captured when the meeting was created
  • $CleanGlobalObjectId is the extended property object created when defining the Exchange connection
  • $Calendar is the Exchange calendar object the script binds to

The meeting is cancelled when the $a.CancelMeeting() command executes and all Required Attendees are notified by email automatically.

The Full Script Skeleton

Because the script relies on an external store of meeting details it is difficult to provide a drop in and run script.  It would be possible to write the commands to create and delete a meeting as functions, and this is what we have done in production, but it does not make the code significantly more reusable and it is more difficult to follow. So what follows should be seen as a skeleton that can be fleshed out with your own external data sources, or dismembered and put into separate scripts to create and delete meetings.

 

 

 

 

 

Getting PowerShell ready to work with Exchange: Adding and removing meetings in Outlook using PowerShell part 2

In the previous post, Adding and removing meetings in Outlook using PowerShell,  I discussed why we had chosen to use PowerShell to manage appointments in Outlook.  In this post we will look how to set up PowerShell to perform these tasks, specifically:

  • enabling the EWS Managed API
  • using encrypted passwords with EWS
  • working with the calendar

The full script is listed at the bottom of the post.

Enabling the EWS Managed API

Microsoft’s EWS Managed API is the core mechanism for interacting with the Exchange Server. The code is now available on the OfficeDev github account which links to the Windows Installer downloads.  There is quite a lot of documentation on MSDN and other Microsoft outlets as well as third party sources.

Installing the package on the server where the PowerShell code will run was straightforward: download the MSI file and run the installer. The only thing to ensure is that the chosen version matches the Exchange server you are connecting to.

Once installed in the server, the EWS Managed API needs to be made available to PowerShell.  While it could be added to the profile, it is simpler to include it in the script

Once the Import-Module cmdlet has run all the APi features are available to PowerShell.

Using Encrypted Passwords with EWS

The aim of the script is to create appointments in the primary calendar of an Exchange account accessed by service administrators.  In order to add  appointments into the calendar the script needs to authenticate with the correct identity. We did consider running the script under that identity, but that would have meant adding permissions to other machines, databases and services that did not make sense.

Fortunately the EWS API allows the script to create a connection to an account by passing a plain text username and password.  Clearly this is not really acceptable, so we have used the technique for creating an encrypted password file described by Todd Klindt (the PowerShell v2 version). Once the encrypted password file was on the disk the trick was getting EWS to accept it.

Unfortunately this took a fair bit of trial and error.  Searching the web offered a number of different ways to create a connection, but most of them did not work. The solution was ultimately to create a credentials object using the secure string and then extract the password as a string variable that is then used to create the WebCredentials object:

  • get the contents of the encrypted password file (referenced by the $passfile variable) and turn it into a secure string

  • create a credentials object and extract the password

  • create a Exchange Service object (note this is for Exchange 2010)

  • use the password variable (and a username variable) to create credentials for the new Exchange Service object

Now that the Exchange connection is established we can bind to the calendar.

Working with the Calendar

The script then binds to the default calendar for the nominated account.  We have used this approach because most users are not too familiar with multiple calendars, and on this service account this default calendar is used primarily for managing these appointments.

An enhancement would be to check for the successful creation of the $folderid object, but as this is binding to the default calendar there is no real danger that the calendar will not be found as long as the credentials authenticate successfully.

We are now ready to create (and delete) entries in the calendar; which will be the subject of the next post; Creating and Deleting Meetings: Adding and removing meetings in Outlook using PowerShell part 3.

The script so far

 

Adding and removing meetings in Outlook using PowerShell

Over a couple of posts I plan to describe how I have used PowerShell to add and, more importantly, cancel meetings in a third party’s Outlook Calendar using PowerShell.

As is typically the case for these sorts of posts, it represents a distillation of and extension to a number of blog posts and forum answers that I have trawled through over a number of days working on this problem.  Unusually, I did not find any one source that provided the basis for the solution. Instead there are a number of sources that address various aspects of the problem, often several contributors saying pretty much the same thing. However, I have been struck by the relative paucity of PowerShell specific guidance on this sort of thing, and have found myself having to translate C# code into PowerShell on more than one occasion. Perhaps this indicates that this approach to managing meetings in Exchange is not best practice, but as PowerShell is starting to pervade all aspects of the Microsoft stack I thought it might be interesting to pass on my experience of stitching together hints and pointers from may sources.

The scenario

This development is part of a much larger service management system. As part of the service clients can book a range of appointments with relevant practitioners either directly online or through reception staff who will take the details and enter them into the online system on the client’s behalf. The online system handles the allocation of time slots among practitioners and appointment types, but here the requirement was that the practitioners wanted their appointments to appear in their personal Outlook calendar, and for cancelled appointments to be properly flagged.

In other settings it might have been appropriate to use a shared calendar or for practitioners to add a SharePoint calendar. Alternatively a separate interface within the online system could have provided personalised information to each practitioner. However these practitioners were often accessing their workload data remotely, and they have considerable autonomy in their choice of technologies to support their work, so in our setting it was necessary for the practitioners to have appointments in their own personal Exchange calendar without using any additional or added calendars.

The solution, inspired by a similar system developed by colleagues at the University of St. Andrews, was to create all the Exchange appointments in a central calendar managed by the admin team. The practitioner would then be added as a required attendee at the meeting. This would trigger the appointment to appear in the practitioner’s calendar. If the practitioner deleted or edited the appointment this would be flagged in the central calendar for the admin team to investigate. When appointments were cancelled in the central calendar the practitioner would see that the appointment had been cancelled in their calendar and could remove the entry.

Why PowerShell?

Given that the bookings that need to be synchronised with an Outlook calendar are made through an online form PowerShell is not the only, or even the most obvious, option, however in our setting it was an attractive one for various reasons:

1 PowerShell is self-contained

As we shall see it does required some additions, but fundamentally it does not rely on the CMS or any other system to perform these tasks; all it needs is access to the source data and the Exchange Web Services. While we run the script on the same host as the CMS it is not necessary and we could, in theory, have a dedicated VM just running our scheduled PS scripts.

2 it is easy to schedule

While the CMS has support for scheduling actions, as we have access to the host OS it is simpler to set up scheduled tasks in Windows than to create something that would run from the CMS. There are already other PS scripts scheduled so it also makes it simpler to choreograph them when they are all in the one place. There is an argument for making the setting and cancelling of appointments a real time push from the online forms to Outlook, and this would be possible if we developed something within the CMS. However, in our environment there are disadvantages to a real-time sync. Very often the booking is part of a discussion or at least deliberation where the client might be exploring several options. In this case bookings can be made and then cancelled almost immediately as the client weights up the implications of the booking. The appointment being booked is also at least one and often several days in advance for a fixed number of time slots, so the practitioners do not need to know immediately who is attending a particular session, they just need to know in time. There is no danger of the delay in synchronisation causing problems with bookings as all bookings are handled through the CMS.

3 it is easy to write

The relative ease or difficulty of any scripting task is primarily a function of the developer’s skill and experience in a particular language and environment. Working with the Microsoft stack there are a wide range of languages and frameworks to choose from. When you add the additional complexity of working within a software platform like SharePoint or DNN developers experienced in the underlying language often find the environment makes life even more complex; just ask .NET developer what it is like to work inside SharePoint for the first time.

In this enviroment PowerShell can play the role as common denominator or lingua franca. In a team where developers and devops work in different languages and on different software platforms on the Microsoft stack, it can be difficult to write custom code that can be supported by anyone in the team. As PowerShell becomes ever more ubiquitous across the stack, most professionals have some exposure to the language and can quickly get to grips with a self-contained script.

The anatomy of the script

The essence of the script is quite simple, performing two basic actions

  • Read the CMS database for appointments that have yet to be created in Exchange and create them
  • Read the CMS database for appointments that have been created in Exchange and have subsequently been cancelled and cancel them

(in our production environment these two actions are actually separate and embedded in larger scripts that perform a number of other actions; I will highlight the code that is common to both)

In the following posts I will describe