EWS – Office 365 – Extract Room Calendar Info with PowerShell

Home Forums Office Office 365 EWS – Office 365 – Extract Room Calendar Info with PowerShell

This topic contains 5 replies, has 3 voices, and was last updated by  Anonymous 1 year, 8 months ago.

Viewing 6 posts - 1 through 6 (of 6 total)
  • Author
    Posts

  • shadragon
    Member
    #166959

    I’ve been given what I thought to be a simple job. It’s turning out to be nightmarish and convoluted.

    We have a meeting room which has an Office 365 Room Calendar associated with it. I need to extract the following info:

    From today, to seven days from now, list all meetings that will take place in that room including:

    – Date
    – Start and end times of meeting
    – Subject

    A CSV output file would be perfect.

    I did the usual Google searches and came up with a lot of Powershell scripts including this excellent post here that uses EWS:

    http://gsexdev.blogspot.com/2015/03/export-calendar-items-to-csv-file-using.html

    Script works brilliantly, but the problem is, this script only works with user calendars and not room calendars. I also need to set this up to run autonomously several times a day to update the info as it will drive a Rasp Pi with a small display outside of the meeting room.

    I don’t have the chops to write my own script without a lot more info. Can any of you assist with examples?

    Thanks.


    wullieb1
    Moderator
    #245618

    This should probably help and then you should be able to merge both into one.

    https://social.msdn.microsoft.com/Forums/exchange/en-US/440926e7-1f1c-4f1e-8f23-7971fe3867b9/get-all-appointments-from-a-resourceroom-in-exchange-online2010-with-ews?forum=exchangesvrdevelopment

    DateTime StartDate = DateTime.Today; DateTime EndDate = DateTime.Today.AddMonths(2); CalendarView cv = new CalendarView(StartDate, EndDate); String MailboxToAccess = “[email protected]”; FolderId CalendarFolderId = new FolderId(WellKnownFolderName.Calendar, MailboxToAccess); FindItemsResults fapts = service.FindAppointments(CalendarFolderId, cv); if (fapts.Items.Count > 0) { foreach (Appointment Appoint in fapts) { Console.WriteLine(Appoint.Start); Console.WriteLine(Appoint.Subject); } } [/CODE][CODE]
    DateTime StartDate = DateTime.Today; DateTime EndDate = DateTime.Today.AddMonths(2); CalendarView cv = new CalendarView(StartDate, EndDate); String MailboxToAccess = “[email protected]”; FolderId CalendarFolderId = new FolderId(WellKnownFolderName.Calendar, MailboxToAccess); FindItemsResults fapts = service.FindAppointments(CalendarFolderId, cv); if (fapts.Items.Count > 0) { foreach (Appointment Appoint in fapts) { Console.WriteLine(Appoint.Start); Console.WriteLine(Appoint.Subject); } } [/CODE]


    Anonymous
    #369412

    Morning,

    I had great success with the original script in my original post. It runs brilliantly when I run it and type credentials in. However, I cannot get the creds to be cached so it can be run automatically.

    I hashed out the following in the script:

    #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 [/CODE]

    and replaced it with code I found on another blog page:
    [CODE]
    $username = “[email protected]
    $pwdTxt = Get-Content “C:CSVExportedPassword.txt” (This is an exported password txt file)
    $securePwd = $pwdTxt | ConvertTo-SecureString
    $credObject = New-Object System.Management.Automation.PSCredential -ArgumentList $username, $securePwd[/CODE]

    When I run the script I get:
    [CODE]
    Exception calling “Bind” with “2” argument(s): “The request failed. The remote server returned an error: (401)
    Unauthorized.”
    At C:CSVCalDumpCSV.ps1:94 char:1
    + $Calendar = [Microsoft.Exchange.WebServices.Data.Folder]::Bind($service,$folderi …
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : ServiceRequestException

    Exception calling “FindAppointments” with “2” argument(s): “The element at position 0 is invalid
    Parameter name: parentFolderIds”
    At C:CSVCalDumpCSV.ps1:111 char:1
    + $fiItems = $service.FindAppointments($Calendar.Id,$CalendarVi ew)
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : ArgumentException[/CODE]

    Why Microsoft make this so idiotically difficult is beyond me. I have tried many different blog / technet solutions to do this and none work without error.[CODE]#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 [/CODE]

    and replaced it with code I found on another blog page:

    $username = “[email protected]
    $pwdTxt = Get-Content “C:CSVExportedPassword.txt” (This is an exported password txt file)
    $securePwd = $pwdTxt | ConvertTo-SecureString
    $credObject = New-Object System.Management.Automation.PSCredential -ArgumentList $username, $securePwd[/CODE]

    When I run the script I get:
    [CODE]
    Exception calling “Bind” with “2” argument(s): “The request failed. The remote server returned an error: (401)
    Unauthorized.”
    At C:CSVCalDumpCSV.ps1:94 char:1
    + $Calendar = [Microsoft.Exchange.WebServices.Data.Folder]::Bind($service,$folderi …
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : ServiceRequestException

    Exception calling “FindAppointments” with “2” argument(s): “The element at position 0 is invalid
    Parameter name: parentFolderIds”
    At C:CSVCalDumpCSV.ps1:111 char:1
    + $fiItems = $service.FindAppointments($Calendar.Id,$CalendarVi ew)
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : ArgumentException[/CODE]

    Why Microsoft make this so idiotically difficult is beyond me. I have tried many different blog / technet solutions to do this and none work without error.[CODE]
    $username = “[email protected]
    $pwdTxt = Get-Content “C:CSVExportedPassword.txt” (This is an exported password txt file)
    $securePwd = $pwdTxt | ConvertTo-SecureString
    $credObject = New-Object System.Management.Automation.PSCredential -ArgumentList $username, $securePwd[/CODE]

    When I run the script I get:

    Exception calling “Bind” with “2” argument(s): “The request failed. The remote server returned an error: (401)
    Unauthorized.”
    At C:CSVCalDumpCSV.ps1:94 char:1
    + $Calendar = [Microsoft.Exchange.WebServices.Data.Folder]::Bind($service,$folderi …
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : ServiceRequestException

    Exception calling “FindAppointments” with “2” argument(s): “The element at position 0 is invalid
    Parameter name: parentFolderIds”
    At C:CSVCalDumpCSV.ps1:111 char:1
    + $fiItems = $service.FindAppointments($Calendar.Id,$CalendarVi ew)
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : ArgumentException[/CODE]

    Why Microsoft make this so idiotically difficult is beyond me. I have tried many different blog / technet solutions to do this and none work without error.[CODE]
    Exception calling “Bind” with “2” argument(s): “The request failed. The remote server returned an error: (401)
    Unauthorized.”
    At C:CSVCalDumpCSV.ps1:94 char:1
    + $Calendar = [Microsoft.Exchange.WebServices.Data.Folder]::Bind($service,$folderi …
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : ServiceRequestException

    Exception calling “FindAppointments” with “2” argument(s): “The element at position 0 is invalid
    Parameter name: parentFolderIds”
    At C:CSVCalDumpCSV.ps1:111 char:1
    + $fiItems = $service.FindAppointments($Calendar.Id,$CalendarVi ew)
    + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~~~~~~~~~
    + CategoryInfo : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : ArgumentException[/CODE]

    Why Microsoft make this so idiotically difficult is beyond me. I have tried many different blog / technet solutions to do this and none work without error.


    wullieb1
    Moderator
    #245627

    Write your credentials to an XML file and read that way. It will save them as encrypted.

    Get-Credential [email protected] | Export-Clixml $env:USERPROFILEDocumentsOffice365.xml

    Remember that this will fill NOT be read on any other machine than the one it was created on.


    Anonymous
    #369413

    Hi wullieb1,

    This is the verbatim result of that command, save the x’d out email. I see no password hash.

    System.Management.Automation.PSCredential
    System.Object

    System.Management.Automation.PSCredential

    [email protected]
    System.Security.SecureString

    [/CODE][CODE]

    System.Management.Automation.PSCredential
    System.Object

    System.Management.Automation.PSCredential

    [email protected]
    System.Security.SecureString

    [/CODE]


    wullieb1
    Moderator
    #245630

    You’re doing something incorrect then.
    I’ve just ran it on my home system using my 365 logon credentials and i get the following

    System.Management.Automation.PSCredential
    System.Object

    System.Management.Automation.PSCredential

    [email protected]
    01000000d08c9ddf0115d1118c7a00c04fc297eb010000009f42c30849db0345906fb5fb6ccb5c65000000000200000000001066000000010000200000004d003a21f970bab99015b2adf3ea16ad6ee8ddc81f22a47c918000dd2fe3e838000000000e80000000020000200000001caf6186a2601cae0ed0d2017d9d57cb833c95676b8309ca9e2aaf7fc9c1e85f2000000001d4976d06a4c91456d425eb5796e517dd29869b697328a9c33ea67e43593acb40000000e5c8458626d390bec64be5274d6fb98a696fa0f6efe43406ffef7bf8a746c9543e142a0dd5f06c49f847cb2e39846e5f80ee8a9f574766a29aa0d92eaafa7578

    [/CODE][CODE]

    System.Management.Automation.PSCredential
    System.Object

    System.Management.Automation.PSCredential

    [email protected]
    01000000d08c9ddf0115d1118c7a00c04fc297eb010000009f42c30849db0345906fb5fb6ccb5c65000000000200000000001066000000010000200000004d003a21f970bab99015b2adf3ea16ad6ee8ddc81f22a47c918000dd2fe3e838000000000e80000000020000200000001caf6186a2601cae0ed0d2017d9d57cb833c95676b8309ca9e2aaf7fc9c1e85f2000000001d4976d06a4c91456d425eb5796e517dd29869b697328a9c33ea67e43593acb40000000e5c8458626d390bec64be5274d6fb98a696fa0f6efe43406ffef7bf8a746c9543e142a0dd5f06c49f847cb2e39846e5f80ee8a9f574766a29aa0d92eaafa7578

    [/CODE]

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic.