Hello,
I'm trying to modify a PowerShell script that reads by the EWS API information from a calendar and exported this to a CSV file.
I found a script that my request almost completely solves (Source: http://gsexdev.blogspot.de/2015/03/export-calendar-items-to-csv-file-using.html).
The only problem is I need some additional information, such as "Creation Time" (dispid 0x3007), "LastModificationTime (dispid 0x3008) and the name of the person who has made the last change to the item. However, I cannot find the dispid for the field "LastModificationBy" but that's not the real problem.
I have added some lines to the script (77, 80, 117) and added the string CreationTime to line 110, which are intended to show me the value for "Creation Time" but the export shows no values.
##most of this code was from Glen Scales on http://social.msdn.microsoft.com/Forums/exchange/en-US/e8af16ac-845d-4afb-a14a-3c373a80e932/how-to-export-calendar-items-##to-csvfile-with-powershell?forum=exchangesvrdevelopment&prof=required
##I also used some from http://msgdev.mvps.org/exdevblog/expapt.zip - also by Glen Scales. The zipped file has a PS script.
## Get the Mailbox to Access from the 1st commandline argument, put in the target email address here (make sure you have permissions)
$MailboxName = "carsten@test.de"
## Load Managed API dll
Add-Type -Path "C:\Program Files\Microsoft\Exchange\Web Services\2.0\Microsoft.Exchange.WebServices.dll"
## Set Exchange Version
$ExchangeVersion = [Microsoft.Exchange.WebServices.Data.ExchangeVersion]::Exchange2010_SP2
## Create Exchange Service Object
$service = New-Object Microsoft.Exchange.WebServices.Data.ExchangeService($ExchangeVersion)
$windowsIdentity = [System.Security.Principal.WindowsIdentity]::GetCurrent()
$sidbind = "LDAP://<SID=" + $windowsIdentity.user.Value.ToString() + ">"
$aceuser = [ADSI]$sidbind
$service.AutodiscoverUrl($aceuser.mail.ToString())
## 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://cas.test.intra/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 Calendar
$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);
$CreationTime = new-object Microsoft.Exchange.WebServices.Data.ExtendedPropertyDefinition([Microsoft.Exchange.WebServices.Data.DefaultExtendedPropertySet]::Appointment, 0x3007,[Microsoft.Exchange.WebServices.Data.MapiPropertyType]::SystemTime);
$psPropset= new-object Microsoft.Exchange.WebServices.Data.PropertySet([Microsoft.Exchange.WebServices.Data.BasePropertySet]::FirstClassProperties)
$psPropset.Add($Recurring)
$psPropset.Add($CreationTime)
$psPropset.RequestedBodyType = [Microsoft.Exchange.WebServices.Data.BodyType]::Text;
$AppointmentState = @{0 = "None" ; 1 = "Meeting" ; 2 = "Received" ;4 = "Canceled" ; }
#Define Date to Query - this involves manually entering the start & end date
# $StartDate = $(Read-Host "Enter Start day of Calendar in format YYYY-MM-DD")
# $EndDate = $(Read-Host "Enter End day of Calendar in format YYYY-MM-DD")
#Define Date to Query programmatically
$ts = New-TimeSpan -Days 30
$StartDate = (get-date) - $ts
$EndDate = (get-date) + $ts
$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,Type,Subject,Location,Organizer,CreationTime,Attendees,Resources,AppointmentState
$rptObj.StartTime = $Item.Start
$rptObj.EndTime = $Item.End
$rptObj.Subject = $Item.Subject
$rptObj.Type = $Item.AppointmentType
$rptObj.Location = $Item.Location
$rptObj.Organizer = $Item.Organizer.Address
$rptObj.CreationTime = $Item.CreationTime
$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
}
}
$RptCollection | Export-Csv -NoTypeInformation -Path "c:\temp\$MailboxName-CalendarCSV.csv"
I am doing something wrong with the query of extended properties, but I cannot find my mistake.
The extended property "IsReccuring" is shown in the export, just my additional field "Creation Time" is not shown.
Your help is greatly appreciated
Carsten
Other recent topics
