by

Export calendar Objects to a CSV file utilizing EWS and Powershell

Export calendar Objects to a CSV file utilizing EWS and Powershell Any person requested about this final week and whereas I’ve quite a lot of EWS scripts that do entry the Calendar I did not have a easy instance that simply exported an inventory of the Calendar occasions with related data to a CSV file so right here it’s.

I’ve talked on this one earlier than on this howto  however while you question the calendar folder utilizing EWS you’ll want to use a CalendarView which is able to broaden any recurring appointments in a calendar. There are some limits while you use a calendarview in which you could solely return a most of 2 years of appointments at a time and paging will restrict the max variety of gadgets to 1000 per name. So you probably have a calendar with a really giant variety of appointments you’ll want to break your question into small date time blocks. On this instance script I am simply grabbing the subsequent 7 days of appointments if you wish to question an extended interval you’ll want to modify the next traces (conserving in thoughts what I simply talked about)

#Outline Date to Question
$StartDate = (Get-Date)
$EndDate = (Get-Date).AddDays(7) 

The AppointmentState worth being reporting on is that this property I’ve put some code in that converts the Bitwise worth again to its Enumeration Flags worth (or values) so it makes it extra readable.

With the Attendees I’ve additionally embody the acceptance standing if that is saved, observe the acceptance standing of all of the attendees and assets will solely be out there on the Organizer copy of an appointment (which will likely be saved within the Organizers calendar).  The script produces a CSV that appears like

I’ve put a download of the script right here the code itself seems 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 Entry from the first 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_MACHINESOFTWAREMicrosoftExchangeWeb Providers'|Type-Object Title -Descending| Choose-Object -First 1 -ExpandProperty Title)).'Set up Listing') + "Microsoft.Exchange.WebServices.dll")
if (Check-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 Trade Model
$ExchangeVersion = [Microsoft.Exchange.WebServices.Data.ExchangeVersion]::Trade2010_SP1

## Create Trade Service Object
$service = New-Object Microsoft.Trade.WebServices.Knowledge.ExchangeService($ExchangeVersion)

## Set Credentials to make use of two choices are availible Option1 to make use of explict credentials or Choice 2 use the Default (logged On) credentials

#Credentials Choice 1 utilizing UPN for the home windows Account
$psCred = Get-Credential
$creds = New-Object System.Web.NetworkCredential($psCred.UserName.ToString(),$psCred.GetNetworkCredential().password.ToString())
$service.Credentials = $creds
#Credentials Choice 2
#service.UseDefaultCredentials = $true

## Select to disregard any SSL Warning points brought on by Self Signed Certificates

## Code From http://poshcode.org/624
## Create a compilation atmosphere
$Supplier=New-Object Microsoft.CSharp.CSharpCodeProvider
$Compiler=$Supplier.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 Native.ToolkitExtensions.Web.CertificatePolicy{
public class TrustAll : System.Web.ICertificatePolicy {
public TrustAll() {
}
public bool CheckValidationResult(System.Web.ServicePoint sp,
System.Safety.Cryptography.X509Certificates.X509Certificates cert,
System.Web.WebRequest req, int downside) {
return true;
}
}
}
'@
$TAResults=$Supplier.CompileAssemblyFromSource($Params,$TASource)
$TAAssembly=$TAResults.CompiledAssembly

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

## finish code from http://poshcode.org/624

## Set the URL of the CAS (Consumer Entry Server) to make use of two choices are availbe to make use of Autodiscover to seek out the CAS URL or Hardcode the CAS to make use of

#CAS URL Choice 1 Autodiscover
$service.AutodiscoverUrl($MailboxName,{$true})
"Using CAS Server : " + $Service.url

#CAS URL Choice 2 Hardcoded

#$uri=[system.URI] "https://casservername/ews/exchange.asmx"
#$service.Url = $uri

## Non-obligatory part for Trade Impersonation

#$service.ImpersonatedUserId = new-object Microsoft.Trade.WebServices.Knowledge.ImpersonatedUserId([Microsoft.Exchange.WebServices.Data.ConnectingIdType]::SmtpAddress, $MailboxName)

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

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

#Outline Date to Question
$StartDate = (Get-Date)
$EndDate = (Get-Date).AddDays(7)

$RptCollection = @()


#Outline the calendar view
$CalendarView = New-Object Microsoft.Trade.WebServices.Knowledge.CalendarView($StartDate,$EndDate,1000)
$fiItems = $service.FindAppointments($Calendar.Id,$CalendarView)
if($fiItems.Objects.Depend -gt 0){
 $sort = ("System.Collections.Generic.List"+'`'+"1") -as "Type"
 $sort = $sort.MakeGenericType("Microsoft.Exchange.WebServices.Data.Item" -as "Type")
 $ItemColl = [Activator]::CreateInstance($sort)
 foreach($Merchandise in $fiItems.Objects){
  $ItemColl.Add($Merchandise)
 } 
 [Void]$service.LoadPropertiesForItems($ItemColl,$psPropset) 
}

foreach($Merchandise in $fiItems.Objects){
$rptObj = "" | Choose StartTime,EndTime,Length,Sort,Topic,Location,Organizer,Attendees,Sources,AppointmentState,Notes,HasAttachments,IsReminderSet,ReminderDueBy
$rptObj.StartTime = $Merchandise.Begin
$rptObj.EndTime = $Merchandise.Finish
$rptObj.Length = $Merchandise.Length
$rptObj.Topic = $Merchandise.Topic
$rptObj.Sort = $Merchandise.AppointmentType
$rptObj.Location = $Merchandise.Location
$rptObj.Organizer = $Merchandise.Organizer.Tackle
$rptObj.HasAttachments = $Merchandise.HasAttachments
$rptObj.IsReminderSet = $Merchandise.IsReminderSet
$rptObj.ReminderDueBy = $Merchandise.ReminderDueBy
$aptStat = "";
$AppointmentState.Keys | the place { $_ -band $Merchandise.AppointmentState } | foreach { $aptStat += $AppointmentState.Get_Item($_) + " "}
$rptObj.AppointmentState = $aptStat
$RptCollection += $rptObj
foreach($attendee in $Merchandise.RequiredAttendees){
$atn = $attendee.Tackle + " Required "
if($attendee.ResponseType -ne $null){
$atn += $attendee.ResponseType.ToString() + "; "
}
else{
$atn += "; "
}
$rptObj.Attendees += $atn
}
foreach($attendee in $Merchandise.OptionalAttendees){
$atn = $attendee.Tackle + " Optional "
if($attendee.ResponseType -ne $null){
$atn += $attendee.ResponseType.ToString() + "; "
}
else{
$atn += "; "
}
$rptObj.Attendees += $atn
}
foreach($attendee in $Merchandise.Sources){
$atn = $attendee.Tackle + " Resource "
if($attendee.ResponseType -ne $null){
$atn += $attendee.ResponseType.ToString() + "; "
}
else{
$atn += "; "
}
$rptObj.Sources += $atn
}
$rptObj.Notes = $Merchandise.Physique.Textual content
"Start : " + $Merchandise.Begin
"Subject : " + $Merchandise.Topic

}
$RptCollection | Export-Csv -NoTypeInformation -Path "c:temp$MailboxName-CalendarCSV.csv"