locked
Problem with query multiple extended porperties from calendar entries with powershell and EWS RRS feed

  • Question

  • 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

    Wednesday, June 24, 2015 10:18 AM

Answers

  • This

     $rptObj.CreationTime = $Item.CreationTime

    Will never work because there is no Strongly typed property called CreationTime if you want to use the Extended property you defined then you need to use something like

    $CreateTimeVal = $null
    if($Item.TryGetProperty($CreationTime,[ref]$CreateTimeVal)){
            $rptObj.CreationTime = $CreateTimeVal 
    }

    However you don't really need to do that because you can use the

    $Item.DateTimeCreated

    and

    $Item.LastModifiedTime

    and

    $Item.LastModifiedName

    eg

    $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,LastModifiedTime,LastModifiedName,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.DateTimeCreated
     $rptObj.LastModifiedTime = $Item.LastModifiedTime
     $rptObj.LastModifiedName = $Item.LastModifiedName
     $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"

    Cheers
    Glen

    DateTimeCreated


    • Marked as answer by NewCybertec Friday, June 26, 2015 7:48 AM
    Thursday, June 25, 2015 2:28 AM

All replies

  • Just guessing, and I don't know if it makes any difference to your results, but perhaps creation time belongs to the common extended property set, rather than the appointment one?

    OWA For SmartPhone

    Wednesday, June 24, 2015 11:20 AM
  • This

     $rptObj.CreationTime = $Item.CreationTime

    Will never work because there is no Strongly typed property called CreationTime if you want to use the Extended property you defined then you need to use something like

    $CreateTimeVal = $null
    if($Item.TryGetProperty($CreationTime,[ref]$CreateTimeVal)){
            $rptObj.CreationTime = $CreateTimeVal 
    }

    However you don't really need to do that because you can use the

    $Item.DateTimeCreated

    and

    $Item.LastModifiedTime

    and

    $Item.LastModifiedName

    eg

    $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,LastModifiedTime,LastModifiedName,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.DateTimeCreated
     $rptObj.LastModifiedTime = $Item.LastModifiedTime
     $rptObj.LastModifiedName = $Item.LastModifiedName
     $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"

    Cheers
    Glen

    DateTimeCreated


    • Marked as answer by NewCybertec Friday, June 26, 2015 7:48 AM
    Thursday, June 25, 2015 2:28 AM
  • Hello Glen,

    thank you very much  for your very helpful advice!!!

    Using 

     $rptObj.CreationTime = $Item.DateTimeCreated
     $rptObj.LastModifiedTime = $Item.LastModifiedTime
     $rptObj.LastModifiedName = $Item.LastModifiedName

    works.

    Best Regards,

    Carsten

    Thursday, June 25, 2015 9:20 AM
  • Many thanks for your  consideration.
    Thursday, June 25, 2015 10:26 AM