Monday, March 16, 2015

Export calendar Items to a CSV file using EWS and Powershell

Somebody asked about this last week and while I have a lot of EWS scripts that do access the Calendar I didn't have a simple example that just exported a list of the Calendar events with relevant information to a CSV file so here it is.

I've talked on this one before in this howto  but when you query the calendar folder using EWS you need to use a CalendarView which will expand any recurring appointments in a calendar. There are some limits when you use a calendarview in that you can only return a maximum of 2 years of appointments at a time and paging will limit the max number of items to 1000 per call. So if you have a calendar with a very large number of appointments you need to break your query into small date time blocks. In this example script I'm just grabbing the next 7 days of appointments if you want to query a longer period you need to adjust the following lines (keeping in mind what I just mentioned)

#Define Date to Query
$StartDate = (Get-Date)
$EndDate = (Get-Date).AddDays(7) 

The AppointmentState value being reporting on is this property I've put some code in that converts the Bitwise value back to its Enumeration Flags value (or values) so it makes it more readable.

With the Attendees I've also include the acceptance status if this is stored, note the acceptance status of all the attendees and resources will only be available on the Organizer copy of an appointment (which will be stored in the Organizers calendar).  The script produces a CSV that looks like



I've put a download of the script here the code itself looks like

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
## Get the Mailbox to Access from the 1st commandline argument

$MailboxName = $args[0]

## Load Managed API dll  
###CHECK FOR EWS MANAGED API, IF PRESENT IMPORT THE HIGHEST VERSION EWS DLL, ELSE EXIT
$EWSDLL = (($(Get-ItemProperty -ErrorAction SilentlyContinue -Path Registry::$(Get-ChildItem -ErrorAction SilentlyContinue -Path 'Registry::HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Exchange\Web Services'|Sort-Object Name -Descending| Select-Object -First 1 -ExpandProperty Name)).'Install Directory') + "Microsoft.Exchange.WebServices.dll")
if (Test-Path $EWSDLL)
    {
    Import-Module $EWSDLL
    }
else
    {
    "$(get-date -format yyyyMMddHHmmss):"
    "This script requires the EWS Managed API 1.2 or later."
    "Please download and install the current version of the EWS Managed API from"
    "http://go.microsoft.com/fwlink/?LinkId=255472"
    ""
    "Exiting Script."
    exit
    } 
  
## Set Exchange Version  
$ExchangeVersion = [Microsoft.Exchange.WebServices.Data.ExchangeVersion]::Exchange2010_SP1  
  
## Create Exchange Service Object  
$service = New-Object Microsoft.Exchange.WebServices.Data.ExchangeService($ExchangeVersion)  
  
## Set Credentials to use two options are availible Option1 to use explict credentials or Option 2 use the Default (logged On) credentials  
  
#Credentials Option 1 using UPN for the windows Account  
$psCred = Get-Credential  
$creds = New-Object System.Net.NetworkCredential($psCred.UserName.ToString(),$psCred.GetNetworkCredential().password.ToString())  
$service.Credentials = $creds      
#Credentials Option 2  
#service.UseDefaultCredentials = $true  
  
## Choose to ignore any SSL Warning issues caused by Self Signed Certificates  
  
## Code From http://poshcode.org/624
## Create a compilation environment
$Provider=New-Object Microsoft.CSharp.CSharpCodeProvider
$Compiler=$Provider.CreateCompiler()
$Params=New-Object System.CodeDom.Compiler.CompilerParameters
$Params.GenerateExecutable=$False
$Params.GenerateInMemory=$True
$Params.IncludeDebugInformation=$False
$Params.ReferencedAssemblies.Add("System.DLL") | Out-Null

$TASource=@'
  namespace Local.ToolkitExtensions.Net.CertificatePolicy{
    public class TrustAll : System.Net.ICertificatePolicy {
      public TrustAll() { 
      }
      public bool CheckValidationResult(System.Net.ServicePoint sp,
        System.Security.Cryptography.X509Certificates.X509Certificate cert, 
        System.Net.WebRequest req, int problem) {
        return true;
      }
    }
  }
'@ 
$TAResults=$Provider.CompileAssemblyFromSource($Params,$TASource)
$TAAssembly=$TAResults.CompiledAssembly

## We now create an instance of the TrustAll and attach it to the ServicePointManager
$TrustAll=$TAAssembly.CreateInstance("Local.ToolkitExtensions.Net.CertificatePolicy.TrustAll")
[System.Net.ServicePointManager]::CertificatePolicy=$TrustAll

## end code from http://poshcode.org/624
  
## Set the URL of the CAS (Client Access Server) to use two options are availbe to use Autodiscover to find the CAS URL or Hardcode the CAS to use  
  
#CAS URL Option 1 Autodiscover  
$service.AutodiscoverUrl($MailboxName,{$true})  
"Using CAS Server : " + $Service.url   
   
#CAS URL Option 2 Hardcoded  
  
#$uri=[system.URI] "https://casservername/ews/exchange.asmx"  
#$service.Url = $uri    
  
## Optional section for Exchange Impersonation  
  
#$service.ImpersonatedUserId = new-object Microsoft.Exchange.WebServices.Data.ImpersonatedUserId([Microsoft.Exchange.WebServices.Data.ConnectingIdType]::SmtpAddress, $MailboxName) 

# Bind to the Calendar Folder
$folderid= new-object Microsoft.Exchange.WebServices.Data.FolderId([Microsoft.Exchange.WebServices.Data.WellKnownFolderName]::Calendar,$MailboxName)   
$Calendar = [Microsoft.Exchange.WebServices.Data.Folder]::Bind($service,$folderid)
$Recurring = new-object Microsoft.Exchange.WebServices.Data.ExtendedPropertyDefinition([Microsoft.Exchange.WebServices.Data.DefaultExtendedPropertySet]::Appointment, 0x8223,[Microsoft.Exchange.WebServices.Data.MapiPropertyType]::Boolean); 
$psPropset= new-object Microsoft.Exchange.WebServices.Data.PropertySet([Microsoft.Exchange.WebServices.Data.BasePropertySet]::FirstClassProperties)  
$psPropset.Add($Recurring)
$psPropset.RequestedBodyType = [Microsoft.Exchange.WebServices.Data.BodyType]::Text;

$AppointmentState = @{0 = "None" ; 1 = "Meeting" ; 2 = "Received" ;4 = "Canceled" ; }

#Define Date to Query 
$StartDate = (Get-Date)
$EndDate = (Get-Date).AddDays(7)  

$RptCollection = @()

  
#Define the calendar view  
$CalendarView = New-Object Microsoft.Exchange.WebServices.Data.CalendarView($StartDate,$EndDate,1000)    
$fiItems = $service.FindAppointments($Calendar.Id,$CalendarView)
if($fiItems.Items.Count -gt 0){
 $type = ("System.Collections.Generic.List"+'`'+"1") -as "Type"
 $type = $type.MakeGenericType("Microsoft.Exchange.WebServices.Data.Item" -as "Type")
 $ItemColl = [Activator]::CreateInstance($type)
 foreach($Item in $fiItems.Items){
  $ItemColl.Add($Item)
 } 
 [Void]$service.LoadPropertiesForItems($ItemColl,$psPropset)  
}
foreach($Item in $fiItems.Items){      
 $rptObj = "" | Select StartTime,EndTime,Duration,Type,Subject,Location,Organizer,Attendees,Resources,AppointmentState,Notes,HasAttachments,IsReminderSet,ReminderDueBy
 $rptObj.StartTime = $Item.Start  
 $rptObj.EndTime = $Item.End  
 $rptObj.Duration = $Item.Duration
 $rptObj.Subject  = $Item.Subject   
 $rptObj.Type = $Item.AppointmentType
 $rptObj.Location = $Item.Location
 $rptObj.Organizer = $Item.Organizer.Address
 $rptObj.HasAttachments = $Item.HasAttachments
 $rptObj.IsReminderSet = $Item.IsReminderSet
 $rptObj.ReminderDueBy = $Item.ReminderDueBy
 $aptStat = "";
 $AppointmentState.Keys | where { $_ -band $Item.AppointmentState } | foreach { $aptStat += $AppointmentState.Get_Item($_) + " "}
 $rptObj.AppointmentState = $aptStat 
 $RptCollection += $rptObj
    foreach($attendee in $Item.RequiredAttendees){
  $atn = $attendee.Address + " Required "  
  if($attendee.ResponseType -ne $null){
   $atn += $attendee.ResponseType.ToString() + "; "
  }
  else{
   $atn += "; "
  }
  $rptObj.Attendees += $atn
 }
 foreach($attendee in $Item.OptionalAttendees){
  $atn = $attendee.Address + " Optional "  
  if($attendee.ResponseType -ne $null){
   $atn += $attendee.ResponseType.ToString() + "; "
  }
  else{
   $atn += "; "
  }
  $rptObj.Attendees += $atn
 }
 foreach($attendee in $Item.Resources){
  $atn = $attendee.Address + " Resource "  
  if($attendee.ResponseType -ne $null){
   $atn += $attendee.ResponseType.ToString() + "; "
  }
  else{
   $atn += "; "
  }
  $rptObj.Resources += $atn
 }
 $rptObj.Notes = $Item.Body.Text
 "Start    : " + $Item.Start  
    "Subject     : " + $Item.Subject 
 
}   
$RptCollection | Export-Csv -NoTypeInformation -Path "c:\temp\$MailboxName-CalendarCSV.csv"

27 comments:

Arnold said...

Thank you Glen! This works great!
Is it possible to export also the calendar items from Room (Ressource) Mailboxes? Thanks!

Glen Scales said...

Yes but the information you get will be different eg you won't get attendees etc from a room mailbox and the information will vary depending on the Room Mailbox settings

Arnold said...

I used your How-To http://gsexdev.blogspot.co.at/2012/03/ews-managed-api-and-powershell-how-to_27.html to get the Room Mailbox appointments and combined it with this skript. Work perfect. Thank you very much for this blog!

mike d said...

Thank you so much for this! Looked all over for something like this. This works like a charm! Question though we have a large number of Room Mailboxes is there any way to edit this to $MailboxName = $args[0] search through a txt file with all of the rooms listed? Trying to make this as automated as possible. Thank you.

Balakumar K said...

when i have the date rage for more than 90 days am getting following error @ [Void]$service.LoadPropertiesForItems($ItemColl,$psPropset)

"Exchange Web Services are not currently available for this request because none of the Client Access Servers in the destination site could process"

Glen Scales said...

You need to add the anchor header in eg

92. #check Anchor header for Exchange 2013/Office365
93. if($service.HttpHeaders.ContainsKey("X-AnchorMailbox")){
94. $service.HttpHeaders["X-AnchorMailbox"] = $MailboxName
95. }else{
96. $service.HttpHeaders.Add("X-AnchorMailbox", $MailboxName);
97. }
98. "AnchorMailbox : " + $service.HttpHeaders["X-AnchorMailbox"]

Anonymous said...

Thank you!!!!!

Unknown said...

Question, how would I export all the appointments within a conference room calendar that attendees accepted?

Glen Scales said...

You can't that data isn't available in the Conference room Mailbox you need to go back to the Organizer Mailbox to get that data.

Cesar Parra said...

I have this error in this line

Exception calling "FindAppointments" with "2" argument(s): "The element at posi
tion 0 is invalid
Parameter name: parentFolderIds"
At C:\Temp\CalendarDumpToCSV.ps1:107 char:37
+ $fiItems = $service.FindAppointments <<<< ($Calendar.Id,$CalendarView)
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : DotNetMethodException

Glen Scales said...

That error mean the script has failed to bind to the calendar folder so you need to make sure you have Full rights on the Mailbox you trying to access (Grant them via Add-MailboxPermission) and also that you passing in the Full SMTPAddress of the Mailbox you trying to access.

SI_Exch2010 said...

Do you have a script for the below?

How would I export all the appointments within a conference room calendar that attendees accepted?

Glen Scales said...

No you can't do that the only way to see if a attendee has accepted is to query the organizers mailbox

SI_Exch2010 said...

Thanks Glen!

In regards to the "export all the appointments within a conference room calendar that attendees accepted" is that do able via powershell? Can you give some guidance?

Glen Scales said...

You will need to query the organizers calendar for each appointment (using the GOID of the appointment Id) to work out that somebody has accepted the meeting. EWS Impersonation may help solve the authentication part of this

Anonymous said...

how can i do this for office 365 exchange online user calendars

Unknown said...

Hi! Is there anyway to do this in Office 365?

Unknown said...

Hi! Is there anyway to do this in Office 365?

Anonymous said...

Is there any way to get the field that are greater than 255 characters as only first 255 chars are displayed in location subject etc..

Anonymous said...

Hi Glen,

I am getting the below error though i have full access on target mailbox. Will you be able to help?


Exception calling "Bind" with "2" argument(s): "The request failed. The remote server returned an error: (401) Unauthorized."
At C:\O365\Caldata.ps1:14 char:1
+ $Calendar = [Microsoft.Exchange.WebServices.Data.Folder]::Bind($servi ...
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : ServiceRequestException

Exception calling "FindAppointments" with "2" argument(s): "The element at position 0 is invalid
Parameter name: parentFolderIds"
At C:\O365\Caldata.ps1:26 char:1
+ $findItems = $service.FindAppointments($Calendar.Id,$CalendarView)
+ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : ArgumentException

Glen Scales said...

It sounds like the way you entering the credentials is the problem. I would advise you use the UPN as the username your entering.

Nuno Miguel Lopes said...

Do you have the soluction for this error? I'm also with this errror

Glen Scales said...

You entering the credentials in the wrong format

Rob said...
This comment has been removed by the author.
Kaz Szydlo said...

Thanks for this script, Glenn. The autodetect portion didn't work in my environment, but I was able to use the hardcoded option to work. Great foresight adding it in.

Anonymous said...

will this work on Exchangeonline? or is there an updated version?

Anonymous said...

Will it work for exchange online user ?