Powershell Needful Things put that in your pipeline

3Jan/1311

Bulk Export Exchange Calendar data to Excel

Posted by Jean Louw

2013-01-03_09-45-01

The previous version of this script, proved far more popular than I could have ever imagined. It was however very complex, which caused numerous failures during execution.

I have recently spent some time streamlining the script at a client, to a point where it is now running fully automated on a schedule.

The new script has the following requirements:

  • The script assumes that it is running in C:\Scripts\CalX - if this path is not possible you have to manually update the script to reflect the location.
  • The script should be executed in the Exchange 20xx management shell.
  • The script requires that Outlook 2007 or above is installed on the computer.
  • The script requires texprof.exe to create the profiles.
  • The script requires MAPILab Advanced Outlook Security to control the security popups.
  • The script requires a file called rooms.txt in the same folder that the script is executed from.

If any exported calendar CSV data files exist from a prior runs, the script will backup these files. The script runs thought each room, in the rooms.txt file and creates / updates a profile on the local machine.  The script assigns full mailbox permissions to the current user, for the calendar to be exported. The script logs onto Outlook using that profile, and exports the calendar data to a CSV.

When it is complete, it combines all of the calendar export files into a single CSV containing all the exported data. As always, your comments and suggestions are always welcome.

The script and texprof.exe can be downloaded below:

28Oct/0926

Bulk export calendars from Exchange mailboxes

Posted by Jean Louw

I have never really had the need for a script like this, so when our catering manager at the office logged a support call, requesting an export of all calendars for all of our meeting rooms, I had to investigate the possibilities. He basically needed this information in order to determine how busy the individual meeting rooms were during the last year.
Following a quick, unsuccessful, Internet probing for tools or scripts that could do this, my initial feeling was to say “No sorry, can’t be done, or if we do it, it was going to be a manual task.”
A manual task, which involves, granting access to the room mailbox, logging onto the mailbox using Outlook, and exporting the calendar data to Excel. Sounds easy, but doing that a hundred times is very unproductive and torturous to say the least.

I decided to attempt to script it, and the result is something I am both proud of and ashamed of at the same time, as I am convinced there must be a better way.

It’s a very rough method, which involves the following process:

  • Get a list of rooms from a text file (as it was emailed to me). You could use get-mailbox instead.
  • Add-mailbox permission to the current user
  • Create an Outlook profile
  • Logon to the profile
  • Export the Calendar to CSV
  • Remove-MailboxPermission

I could automate most of the above, but creating new profiles on demand is something I’ve never had to do, and frankly, I had no idea how to get around this problem. After speaking to some of the developers at work, who promised me some dotnet code which could do it (which I am still waiting for might I add :)), I decided to use PRF files.

I have used PRF files very successfully in the past, on Terminal server deployments to automatically setup Outlook profiles.

I downloaded the ORK and created a PRF which I used as a template for the script. The blank PRF is attached to this post to save you the time and effort of using ORK.

The script finds and replaces the UserName and HomeServer in the PRF, although any Exchange server should resolve you to your mailbox server. It then creates a PRF and starts Outlook with the /importPRF switch. Some extra information, for anyone wanting to actually deploy or use the PRF file; the %HomeServer% variable in the PRF does not work the same way %UserName% works, if you want use the PRF, you need to specify one of your mailbox servers instead.

While Outlook is open on that profile, the script attaches to Outlook using a COM object and downloads the calendar for the specified date.

The calendar fields can be customised to suit your needs. In my case we simply needed the Start and End date, the duration, and the Organizer.

The export data is saved and the PRF is removed, sadly the swarm of profiles will remain, and you have to manually remove them. You could remove them from HKEY_CURRENT_USER\Software\Microsoft\Windows NT\CurrentVersion\Windows Messaging Subsystem\Profiles but I have not added that to the script.

I hope this can help you, if you ever get a freaky request like this.

The script and the PRF template can be downloaded from here: