Sunday, March 1, 2020

Exchange Online Meeting Room Statistics - Graph API

A few years ago I wrote an article named "Exchange Meeting Room Statistics" about a script to gather statistics regarding Exchange meeting room usage for MSExchange.org (now techgenix.com). For this script to work, we have to give ourselves FullAccess to the meeting rooms’ mailbox, add them into our Outlook profile, and then use an Outlook COM Object to connect to Outlook and gather this information. Far from ideal, especially when trying to analyse dozens of rooms!

A couple of years after, I wrote a new version that uses Exchange Web Services to gather the same information, plus some further stats. However, this script wasn't very reliable with Exchange Online as it would work in some environments, but not in other.

I have finally written this newer version, specifically targeted at Exchange Online only, this time using Graph API!


IMPORTANT:
  • To analyze a particular meeting room, specify one or more primary SMTP addresses in the format: "room1@domain.com, room2@domain.com". Alternatively, analyze all meeting rooms by using the "-All" switch;
  • You will need to have, or create, an 'app registration' in Azure and user digital certificate for authentication (you can update the script to use 'client secret' instead);
  • The app registration will need the following API permissions to Graph API: 'User.Read.All', 'Calendars.Read', and 'Place.Read.All', all of type 'Application';
  • Maximum range to search is 1825 days (5 years);
  • You can enter the dates in the format "22/02/2020", "22/02/2020 15:00", or in ISO 8601 format such as "2020-02-22T15:00:00", or even "2020-02-22T15:00:00-08:00" to specify an offset to UTC (time zone).


The most basic way of running the script is as follows (more examples in the script itself):
.\Get-MeetingRoomStats_GraphAPI.ps1 -All -From "01/01/2020" -To "01/02/2020"


The script gathers and exports the following stats for each meeting room for the given date range:
  • RoomName: the display name of the meeting room (when using -All). When using -RoomListSMTP, this will be the room's SMTP address;
  • RoomSMTP: the SMTP address of the meeting room;
  • From: the start of the date range to search the calendar;
  • To: the end of the date range to search the calendar;
  • totalMeetings: the total number of meetings;
  • totalDuration: the total number of minutes for all meetings;
  • totalAttendees: the total number of attendees invited across all meetings;
  • totalUniqueOrganizers: the number of unique meeting organizers;
  • totalUniqueAttendees: the number of unique attendees;
  • totalReqAttendees: the total number of required attendees;
  • totalOptAttendees: the total number of optional attendees;
  • Top5Organizers: the email address of the top 5 meeting organizers, and how many meetings each scheduled;
  • Top5Attendees: the email address of the top 5 meeting attendees, and how many meetings each attended;
  • totalAllDay: the total number of 'all-day' meetings;
  • totalAM: the total number of meetings that started in the morning (this excludes all-day meetings);
  • totalPM: the total number of meetings that started in the afternoon;
  • totalRecurring: the total number of recurring meetings;
  • totalSingle: the total number of non-recurring meetings (single instance/occurrence).

123 comments:

  1. Where do I run this PS? Do I use Exchange online PS or AzureAD PS?

    ReplyDelete
    Replies
    1. Hi Steve,

      You can run it from a "normal" PowerShell console. The script uses Graph API, so it doesn't need to be connected to Exchange Online or Azure AD :)

      Best regards,
      Nuno

      Delete
    2. \GetMeetingRoomStats_GraphAPI_v0.2.ps1 : Cannot process argument transformation on parameter 'All'. Cannot convert value "System.String" to type
      "System.Management.Automation.SwitchParameter". Boolean parameters accept only Boolean values and numbers, such as $True, $False, 1 or 0.

      Delete
    3. Can you show me exactly how are you calling the script?

      Delete
  2. I am trying to collect the information about each meeting which have been booked rather than just the overall number based on the room. I have taken the # out from the part about getting the information about each meeting but the output is only giving me 1 result per room rather than every meeting in the room

    ReplyDelete
    Replies
    1. Hi. Did you export that PSCustomObject to a file for example?

      Delete
  3. can you confirm if we can store the email addresses in a csv and import the csv as at the moment we are having to run a query on 400+ calendars

    ReplyDelete
    Replies
    1. I'm afraid you can't with the script as is...

      Delete
    2. export all of your resource mailboxes onto a csv, extract all of their smtp's to a simple file and run this command.

      import-csv c:\user\username\desktop\smtp.csv | foreach { .\getmeetingroomstats_graphapi.ps1 -roomlistsmtp $_.PrimarySmtpAddress}

      Delete
  4. Hi, I read that there is a new Places api that is supposed to replace findRooms or findRoomLists. It can list more than 100 rooms https://docs.microsoft.com/en-us/graph/api/resources/place?view=graph-rest-beta
    Can you please suggest how to modify your script in order to use the new Places api?

    ReplyDelete
    Replies
    1. Hi Agnese,

      Thank you for letting me know! I just tested it and it seems to work great! :) Unfortunately I don't have a tenant with more than 100 meeting rooms, but it worked fine in a smaller tenant.

      All you have to do is ensure your app registration has the required permissions as per the article and update the following line from:

      $allRooms = Query-GraphAPI -URI "https://graph.microsoft.com/beta/users/$user/findRooms" -Token $token
      To:
      $allRooms = Query-GraphAPI -URI "https://graph.microsoft.com/beta/places/microsoft.graph.room" -Token $token

      Then, find and replace all "$room.address" with "$room.emailAddress", and "$room.name" with "$room.nickname"

      That should do it! :)

      Regards,
      Nuno

      Delete
  5. Hi Nuno,
    nice work.
    Where i can find your script. Microsoft delete all of the scirpts :(
    https://gallery.technet.microsoft.com/scriptcenter/Exchange-Online-Meeting-4894b38f is not working anymore.

    Kind regards,
    Neon

    ReplyDelete
    Replies
    1. Thanks! I know, I need to move these scripts to GitHub or something... :( I will update the post once I do

      Delete
    2. Hi Steve, too I am in urgent neefnedyou r script od you upload it ploan it plel yo..e. pelase?

      Delete
    3. Please let us know when you do, this looks perfect!

      Delete
    4. This would be great!

      Delete
    5. Here it is! https://github.com/NunoFilipeMota/PublicScripts/blob/main/GetMeetingRoomStats_GraphAPI.ps1

      Delete
  6. Hi Nuno,

    Hope your well, would appreciate if you can share the script.

    Many Thanks

    ReplyDelete
    Replies
    1. Hi! Sorry for the delay. Here it is! https://github.com/NunoFilipeMota/PublicScripts/blob/main/GetMeetingRoomStats_GraphAPI.ps1

      Delete
  7. Do you still have the script for on prem .. the scripts looks good but I need for exchange 2016 on prem

    ReplyDelete
    Replies
    1. Hi Brijesh,

      Sure, you can find it here: https://github.com/NunoFilipeMota/PublicScripts/blob/main/GetMeetingRoomStats_EWS.ps1

      Regards,
      Nuno

      Delete
  8. Hi Nuno mota can you help me ? do oyu have script for exchange on premise ? i need room occupancy per hour some help ?

    ReplyDelete
    Replies
    1. Sure! You can find it here: https://letsexchange.blogspot.com/2017/05/exchange-meeting-room-statistics.html

      Delete
  9. Hi Nuno,
    Thank you for publishing the script. I am receiving the following error while running it:
    Unable to get token: 'The remote server returned an error: (401) Unauthorized.'

    .\GetMeetingRoomStats_GraphAPI.ps1 -All -From "01/05/2021" -To "18/05/2021"

    Can you please suggest. Please note I have registered the app in azure and provide the client ID and client serest details in the script.

    ReplyDelete
    Replies
    1. Hi!

      Are you sure the script has the correct information about the app registration? The error seems to be when you are trying to get the auth token. Also, please double-check the app registration has the correct API permissions!

      Regards, Nuno

      Delete
    2. I can second this error. Script works when I specify a meeting room but not when I use -All parameter. Is there any additional API permission required to pull the list of rooms in the first place?

      Delete
    3. Apologies, I updated the code to use "list places" instead of "findRooms" and didn't update the description... You will also need "Place.Read.All" application permissions. Apologies for the error. I will update GitHub soon!

      Delete
  10. Hi Nuno,

    Thank-you for this script - just what I need, but cant get it to work. It seems EWS Managed API isn't available anymore. Has anyone been able to download it from somewhere? I see reference to nuget, but have no idea how to use it!

    ReplyDelete
    Replies
    1. Hi! Aren't you able to use the Graph API version?

      Delete
  11. Hi Nuno, i am receiving the following error... Retrieved OAuth Token
    Unable to query Graph API: 'Der Remoteserver hat einen Fehler zurückgegeben: (401) Nicht autorisiert.' I run this script from my local client without globaladmin rights.

    ReplyDelete
    Replies
    1. Hi. It seems to me the app registration is not configured correctly. Does the app registration have the required permissions, and do you have the correct app registration details in the script?

      Delete
  12. Hi Nuno, thanks for creating this.
    I get this error when running the script: You must use the -ClientID -ClientSecret AND -TenantID parameters. Exiting Script.

    ReplyDelete
    Replies
    1. Hi Lynn. Are you using those parameters when running the script? If not, please update them with the corresponding Azure application details at the beginning of the script.

      Delete
    2. Hi Nino, Thanks for your response, I have fixed the settings in the script but I am getting a new error: Unable to get token: 'The remote server returned an error: (400) Bad Request.' -- Did I miss a setting in the script?

      Delete
    3. Hi Lynn. Have you updated all three parameters and is the app registration configured correctly?

      Delete
  13. Hi Nuno, Thank you for this.
    When I run the script with the -All parameter, The output shows that it retrieved 6 meeting rooms but the output shows stats for only 1 meeting room. can you please advise?

    ReplyDelete
    Replies
    1. Hi. Are you running the latest version that uses Graph API?

      Delete
  14. Hi Nuno. I've tried to use updated the script, but it seems retrieving only 100 rooms. Could you please advise how to fix it?

    ReplyDelete
    Replies
    1. Hi Russel. Are you using the latest version with Graph API?

      Delete
    2. Hello Nuno. Yes, the version of Graph API 1.6.1

      Delete
    3. Hi Russel,

      Apologies. Please use the following instead:

      $allRooms = Query-GraphAPI -URI "https://graph.microsoft.com/beta/places/microsoft.graph.room?top=999" -Token $token

      I've updated the code on GitHub with this change.

      Regards,
      Nuno

      Delete
  15. Hello,
    Thank you very much for your script which was really helpful for our need. The only problem we had was about limitation of first 100Th room mailboxes despite switching to -All and using pagination through https://docs.microsoft.com/en-us/graph/paging.
    Still helpful anyway.

    ReplyDelete
    Replies
    1. Hi,

      Apologies. Please use the following instead:

      $allRooms = Query-GraphAPI -URI "https://graph.microsoft.com/beta/places/microsoft.graph.room?top=999" -Token $token

      I've updated the code on GitHub with this change.

      Regards,
      Nuno

      Delete
  16. im getting this error

    Please use -All or -RoomListSMTP parameters. Exiting Script.

    HELP!

    ReplyDelete
    Replies
    1. Hi Abmiester,

      You have to use one of those parameters when running the scripy.

      Regards,
      Nuno

      Delete
  17. hi Nuno..can you assist. i double checked the creds and this is what i'm getting
    Retrieved OAuth Token
    Unable to query Graph API: 'The remote server returned an error: (401) Unauthorized.'
    END.

    ReplyDelete
    Replies
    1. Hi Abmiester. That means the app registration you created does not have the required permissions. Have you assigned it 'User.Read.All', 'Calendars.Read', and 'Place.Read.All' (all of type 'Application')?

      Delete
    2. Hi, I'm getting the same, the app registration has the following permissions 'User.Read.All', 'Calendars.Read', and 'Place.Read.All' all of them type Application.

      I am using the secret ID, Tennant ID and Application ID parameters.

      Delete
  18. Hi Nuno, I have one doubt in your script. It is working fine. I booked a meeting room on 26th Sep. When I run the script today by passing only Sep month date, it is not giving me the details about the meeting which I have booked on 26th Sep.
    Can you please assist?

    ReplyDelete
  19. Hi Nuno,

    After using this '$allRooms = Query-GraphAPI -URI "https://graph.microsoft.com/beta/places/microsoft.graph.room" -Token $token' also, powershell script is displaying only 100 meeting rooms.
    Can you please assist ?

    Thanks.

    ReplyDelete
    Replies
    1. Hi Vidhya,

      Apologies. Please use the following instead:

      $allRooms = Query-GraphAPI -URI "https://graph.microsoft.com/beta/places/microsoft.graph.room?top=999" -Token $token

      I've updated the code on GitHub.

      Regards,
      Nuno

      Delete
    2. Thank you so much Nuno for your help. It worked :)

      Delete
    3. Excellent! :) Thank you for the update.

      Delete
  20. Great script and thank you so much this has helped me.
    I want to see all the attendees in a certain timeframe not top 5, how can i change this?

    Thank you

    ReplyDelete
    Replies
    1. Thank you! :)
      Simply update the following line which selects only the top 5:
      Top5Attendees = If ($topAttendees) {($topAttendees.GetEnumerator() | Sort -Property Value -Descending | Select -First 5 | % {"$($_.Key) ($($_.Value))"}) -Join ", "} Else {""}

      If you want more than 5, simply update the Select statement, or remove it completely to get ALL of the attendees.

      Regards,
      Nuno

      Delete
  21. I continue to get Unable to get token: 'The remote server returned an error: (401) Unauthorized.' I have made sure application permissions are assigned

    ReplyDelete
  22. Just an fyi if anyone else is in a GCC High environment like myself make sure to change all .com to .us throughout the script.

    example

    https://login.microsoftonline.us/$TenantID/oauth2/v2.0/token
    https://graph.microsoft.us/

    ReplyDelete
  23. Hi Nuno, great work! All objects returned are ResourceType 'Room' (understandable as that's what is targeted by the Graph API). Do you know if a similar report can be produced for objects with ResourceType 'Workspace'?
    Thanks.

    ReplyDelete
    Replies
    1. Thanks! I don't I'm afraid, I never looked into that specifically...

      Delete
  24. Ok here is a SIMPLE GUIDE if you have problems running the script

    in the end you should have something like this

    syntax

    .\Get-MeetingRoomStats_GraphAPI.ps1 -ClientID randomid-generated-from-azure-ad -ClientSecret generatedpasswordfromazuread -TenantID generated-idf-from-azure-id -RoomListSMTP "yourroomname@yourdomainname.com" -From "01/10/2021" -To "01/12/2021"

    get -ClientID from

    https://portal.azure.com/#blade/Microsoft_AAD_IAM/ActiveDirectoryMenuBlade/RegisteredApps

    app registrations
    new registration

    clientid

    and tenant id are gonna show up here

    then go to certificates and secrets get new client secret
    copy the secret/password because it will be hidden later.

    -ClientSecret

    this will expire after set time


    go to the app and view permissions
    add application permission for

    Calendars.Read
    Place.Read
    Place.Read.All
    User.Read
    User.Read.All


    and allow access/grant admin consent

    hopefully it will work


    ReplyDelete
  25. Hello. Does this work on workspaces?

    ReplyDelete
  26. Hello Nuno,

    Thank you very much for making this script. I am using it on a tenant with more than 300 room mailboxes. One thing I can't really understand is that it stops processing far before it reaches the total number of room mailboxes. It also doesn;t show any mailboxes with 0 meetings. Could it be that it skips these and also doesn't count it in the total number of processed mailboxes?

    ReplyDelete
    Replies
    1. Hi Stefan,

      Yes, at is, it will skip the room if there are no meetings. You will find that in the following part of the code:
      $totalMeetings = ($allMeetings | Measure).Count
      If ($totalMeetings -eq 0) {
      Write-Log -Type "WRN" -Message "0 meetings retrieved from '$($room.nickname)'"
      Continue
      } Else {
      Write-Log -Type "INF" -Message "$totalMeetings meetings retrieved from '$($room.nickname)'"
      }

      When this happens, you should see a WRN entry in the log for that particular meeting room as per the code above.

      As to the number of rooms, yes, it should return all of them.

      I've just started working on a version of this script that uses the Graph API SDK instead, which should greatly simplify the script! Hopefully it will be published soon :)

      Regards,
      Nuno

      Delete
    2. Hi Nuno,

      Thank you for the very quick response. For some reason when I use it, it shows that there are 335 room mailboxes in the status bar, but when it reaches about 175 it stops with the "END." message. This is wile using From: 1-02-2022 and To: 15-02-2022. So I only get 175 mailboxes in the export and none of them has 0 meetings. When I use From: 1-11-2019 and To: 30-11-2019 it also shows 335 mailboxes in the status bar but stops at 115. Do you maybe know a reason why this could happen?

      Regards,
      Stefan

      Delete
    3. Are you sure those missing ones are not rooms with 0 meetings? Have you checked the log for "0 meetings retrieved from" to see how many entries there are?
      You can try commenting the "Continue" statement, but nothing will be added to the export if there are no meetings for the room...

      Delete
    4. I am experiencing same issue where not all meeting rooms data is retrieved even though meeting rooms definitely have meetings, is there a fix for this yet?

      Delete
    5. So is it actually possible to export also rooms with 0 meetings?

      Delete
    6. In order to see warning messages in the log and in the export file you need to comment out both "Continue" statements. Thank you Nuno for the perfect script. Really appreciate it.

      Delete
  27. The script is great. Thanks for doing this. I see a place to uncomment lines and get individual meeting details as well. I did that and it puts that out to screen, but does not append to the csv output. Is there a way to get it to put the meeting details in the csv as well?

    ReplyDelete
    Replies
    1. Thank you! :) Yes, it will output it to the screen. If you want it into a CSV, simply add something like " | Export-CSV file.csv -NoType -Append" after that block's }

      Delete
  28. Trying your script for the first time, and I am also seeing "Unable to get token: 'The remote server returned an error: (401) Unauthorized.'". Verified that the application has the following rights:
    Calendars.Read,
    Place.Read.All
    User.Read
    User.Read.All

    any thoughts?

    ReplyDelete
    Replies
    1. Have you checked that the permissions have actually been granted for the organisation? They will require admin consent.

      Delete
  29. First of all I'd like to thank you for taking your time to write the script. It's really nice to have an adaptation which now works via Graph API.

    Second, do you know how recurring calendar elements are read by Graph API/your script ? I'm running an export right now and I'm getting several timeouts. When investigating further I see mailboxes with 22 items in the calendar cause 30+ min delays where it simply says "Processed (0 / 1). Current calendar: ''". 22 items shouldn't take that long. Checking the calendar I see some Outlook super users (not) creating 17 recurring events on a single day (due to mailbox full access thus skipping calendarprocessing limitations) and not setting an end date.

    For this particular mailbox it takes about 80 seconds to export data for 1 month. So it should take about 16 minutes for 2021 but that's not what I'm experiencing. Seems like I'm hitting some hidden timeout/retry not being printed to screen until it times out.

    ReplyDelete
  30. Thnx for the script and for providing it to us, it really helps alot.

    If I run the script called 'GetMeetingRoomStats_GraphAPI_v0.1.ps1' everything runs fine but when I use the script called 'Update GetMeetingRoomStats_GraphAPI.ps1' there is a problem.

    If I use the -RoomListSMTP parameter it runs fine and I can run it on all rooms, but when I use the -All parameter I just get 'Unable to query Graph API: The remote server returned an error: (401) No permission'

    I haven't made any changes to the script. What am I missing here?

    ReplyDelete
    Replies
    1. Hi Richard. Glad to hear that! :)

      What do you mean when you run it like 'Update GetMeetingRoomStats_GraphAPI.ps1'? Did you rename it and added a space to the script name?

      As to using "-All", please make sure the app registration has all the permissions, as that parameter requires 'Place.Read.All' permissions.

      Delete
  31. Hello Nuno,

    Sorry for the lame problem, but I see the EWS Managed API is no longer available. What can i do? How can i install the api? I have already checked the github but I cannot see any installable version of the API.

    I just would like to know what is the usage of the meeting rooms (percentage of free-reserved times) in my workplace. Your ps script seems to be the best solution for this but in this case I cannot use it :(

    ReplyDelete
    Replies
    1. Hi Zoltan. You can get v2.2 from here: https://www.nuget.org/packages/Microsoft.Exchange.WebServices/

      However, I suggest you use the script I wrote that uses Graph API (the one in this post) instead of EWS.

      Delete
  32. Hey
    I tried running the script and also gave permissions of API permissions but I still encountered such an error message:
    "You must use the -ClientID -ClientSecret AND -TenantID parameters. Exiting Script."

    Would appreciate help. Thanks!

    ReplyDelete
    Replies
    1. Hi. How exactly are you calling/running the script?

      Delete
  33. Hi,

    for AM/PM meeting counters, i changed to this
    If ($meeting.isAllDay) {$totalAllDay++} Else {If ((Get-Date $meeting.start.dateTime -format "HH") -lt "12") {$totalAM++} Else {$totalPM++}}

    for country having 24h format, also it wont count meetings in the mornings

    ReplyDelete
    Replies
    1. That's a great approach as well! But my one works fine for me, even with 24h format, so that's strange...

      Delete
  34. Nuno!
    You are a scholar and a gentleman! Thank you for creating this script.
    Quick question,
    Is it possible to use this script with Certificate Based Authentication?
    If so, can you point me in the direction of how I can incorporate that with your script instead of using the client secret?
    Much thanks and keep up the great work!

    ReplyDelete
    Replies
    1. Hi Tom,

      Thank you for the kind comment! :)

      Yes! Just replace the Get-OAuthToken function with the following, which uses a cert installed under the machine or user scope (update accordingly with the details of your cert):

      Function Get-OAuthToken {
      Param ($ClientID, $TenantID, $CertThumprint)

      Try {
      Import-Module MSAL.PS -ErrorAction Stop
      } Catch {
      Write-Log -Type "ERR" -Message "Unable to import MSAL PowerShell module: '$($_.Exception.Message)'"
      Return $False
      }

      # Get OAuth Token
      Try {
      $ClientCertificate = Get-Item "Cert:\CurrentUser\My\$CertThumprint"
      $token = Get-MsalToken -ClientId $ClientID -TenantId $TenantID -ClientCertificate $ClientCertificate
      Return $token.AccessToken
      } Catch {
      Write-Log -Type "ERR" -Message "Unable to get OAuth token using MSAL: '$($_.Exception.Message)'"
      }
      }

      Hope it helps!

      Regards,
      Nuno

      Delete
  35. Nuno - Great script and so useful. It ran successfully on the first try. Thank you so much for writing and sharing this.

    I also ran into the issue of rooms with 0 meetings being completely omitted, both from the log file and the csv file and had to comment out the line "If (!$allMeetings) {Continue}" since that caused those rooms to be skipped for both.

    What I was not able to accomplish, was to add other attributes to the export. For example, the rooms' DisplayName, city, address, etc., which would be very helpful. Could you help with that?

    ReplyDelete
    Replies
    1. Thank you for the feedback :) Please check the latest version of the script I have just uploaded. It now exports meeting rooms with 0 meetings :)

      Delete
  36. when i run the script to get the meeting room calendar events information for 10 to 15 room mailboxes , i am not getting all events information(issue number 1) and for some events i am not getting meeting room name

    ReplyDelete
    Replies
    1. Hi. Please try the newer version I have just uploaded as it now uses MSAL to retrieve the token. Please note that it requires a certificate for authentication, but you can change the function to use a Client Secret instead if you want.

      Delete
  37. Hi! I would like to use this script with an imported csv file with the meeting rooms, is this possible?

    The thing is we have over 600 meeting rooms and i would just like to use it on 60-80 meeting rooms, it takes too long to write with "roomlistSMTP". I Could ofc use it on all and just filter it out to the ones i want but it would be lovely to use it with imported CSV file instead.

    ReplyDelete
    Replies
    1. Hi Markus. I'm afraid you'd need to update the code yourself in order to work that way. I'll try to incorporate that in the next version, but not sure when that will be...

      Delete
  38. This still seemingly even with the current changes to $allRooms = Query-GraphAPI -URI "https://graph.microsoft.com/beta/places/microsoft.graph.room?top=999"

    immediately stops processing whenever it hits the 100th mailbox no matter how you shake it out.

    Did you ever figure out what was causing that?

    ReplyDelete
    Replies
    1. That is strange... For me, it is exporting over 700 meetings in one go in my environment. Please try the latest version I have just uploaded (but that part of the code is exactly the same...)

      Delete
  39. Hey Nuno, When i run the script it does not error out, i have added the options for -ClientID, -TenantID, -ClientSecret. (I created the app in azure and granted permisisons). However the script only reports 1 meeting.

    If i set the from/to on the script to "2020-01-01" and "2022-08-17" - It reports the SINGLE Meeting as a from/date of:

    From : 1/1/2020 12:00:00 AM To : 8/17/2022 12:00:00 AM

    If i set start date to different date the above results change to that date as well. Also if i run the scripts with no to/from parameters, then it reports nothing.

    Any ideas?

    ReplyDelete
    Replies
    1. That's very strange... Have you tried the date in a different format? You can enter the dates in the format "22/02/2020", "22/02/2020 15:00", or in ISO 8601 format such as "2020-02-22T15:00:00", or even "2020-02-22T15:00:00-08:00" to specify an offset to UTC (time zone).

      Also, please try the newer version I have just uploaded.

      Delete
  40. Hey Nuno, We followed the directions to set the script up, but when we run it, it just returns 1 meeting. For conference rooms that i know have more than 1 meeting.

    any ideas?

    Robert

    ReplyDelete
    Replies
    1. That's very strange, never had that happening to me... Are those other meetings within the timespan chosen?

      Also, please try the newer version I have just uploaded. Please note it requires a certificate for authentication, but you can change the function to use a Client Secret instead if you want.

      Delete
  41. Hi Nuno,
    I have been able to successfully run the script however when I run using the -all switch it shows "retrieving 116 meeting rooms" however it is only exporting 87 room data results and I have gone through the log and cannot see any lines that state "0 meetings retrieved from"

    ReplyDelete
    Replies
    1. That's strange... Probably those meeting rooms don't have any meetings, but it still should highlight that. Please try the newer version I have just uploaded as it now exports meeting rooms with 0 meetings.

      Delete
  42. Hi I am having issues where the script just ENDs halfway through exporting data from 116 meetings rooms. Only does approx 60 meetings, have verified meeting rooms have bookings aswell.

    ReplyDelete
    Replies
    1. Hi. Please try the newer version I have just uploaded as it now uses MSAL to retrieve the token. Please note that it requires a certificate for authentication, but you can change the function to use a Client Secret instead if you want.

      Delete
  43. thanks you,. Trying your script for the first time, and I am also seeing "Unable to get token: 'The remote server returned an error: (401) Unauthorized.'". Verified that the application has the following rights:
    Calendars.Read,
    Place.Read.All
    User.Read
    User.Read.All

    and Admin consent is OK .
    any thoughts?

    ReplyDelete
    Replies
    1. Please try the newer version I have just uploaded as it now uses MSAL to retrieve the token. Please note that it requires a certificate for authentication, but you can change the function to use a Client Secret instead if you want.

      Delete
  44. I am also seeing "Unable to get token: 'The remote server returned an error: (401) Unauthorized.'". Verified that the application has the following rights:
    Calendars.Read,
    Place.Read.All
    User.Read
    User.Read.All

    I have confirmed they have all been consented by the Admin?

    ReplyDelete
    Replies
    1. Please try the newer version I have just uploaded as it now uses MSAL to retrieve the token. Please note that it requires a certificate for authentication, but you can change the function to use a Client Secret instead if you want.

      Delete
  45. Hi there, the script and these comments now seem to only reference using a certificate. Are you able to share the code required to use clientsecret?

    ReplyDelete
    Replies
    1. Hi. You need to update all references of the variable "CertThumprint" to something like "Secret" and then update the Get-MsalToken cmdlet inside the "Get-OAuthToken" function to use a secret instead of a certificate.

      Delete
  46. Hi, great script.. Now that you have updated to use certificate I got clientsecret working by referencing the old script via github. This is more a general question rather than a request. Do you think its possible to report on which days are more popular. Or to say another way what % of bookings fall on each workday, for a given room, for a given timeframe?

    ReplyDelete
    Replies
    1. Hi Jono. Thank you! :)

      To use a certificate, you need to update all references of the variable "CertThumprint" to something like "Secret" and then update the Get-MsalToken cmdlet inside the "Get-OAuthToken" function to use a secret instead of a certificate.

      Yes, it is technically possible. You'd need to keep track of all days (days of the month, week days, whetever you prefer) and then use that for your stats (I suggest an array or hashtable). It might be tricky, but it's doable!

      Regards, Nuno

      Delete
  47. How do you I use this script with client secret instead of certificate

    ReplyDelete
    Replies
    1. You need to update all references of the variable "CertThumprint" to something like "Secret" and then update the Get-MsalToken cmdlet inside the "Get-OAuthToken" function to use a secret instead of a certificate.

      Delete
    2. Hello Nuno, Thank you for this script. I have replaced all "CertThumprint" with "Secret" and updated these; [Parameter(Mandatory = $False)]
      [String] $ClientID = "xxxxxxxxxxxxxxxxxxx",

      [Parameter(Mandatory = $False)]
      [String] $Secret = "xxxxxxxxxxx",

      [Parameter(Mandatory = $False)]
      [String] $TenantID = "xxxxxxxxxxxxxxxxx"
      )

      I guess I am not understanding how to update this section; Function Get-OAuthToken {
      Param ($ClientID, $TenantID, $Secret)

      Try {
      Import-Module MSAL.PS -ErrorAction Stop
      } Catch {
      Write-Log -Type "ERR" -Message "Unable to import MSAL PowerShell module: '$($_.Exception.Message)'. Exiting script."
      Exit
      }

      Try {
      $ClientCertificate = Get-Item "Cert:\CurrentUser\My\$Secret"
      $token = Get-MsalToken -ClientId $ClientID -TenantId $TenantID -ClientCertificate $ClientCertificate -ErrorAction Stop

      # Get token expiration date and time so we can renew it 2 minutes before it expires
      $global:tokenExpireDateTime = (Get-Date $token.ExpiresOn.DateTime).AddSeconds(-120)

      Return $token.AccessToken
      } Catch {
      Write-Log -Type "ERR" -Message "Unable to get OAuth token using MSAL: '$($_.Exception.Message)'. Exiting script."
      Exit
      }
      }

      I know I need to update the ClientCertificate with Secret in some way, but I am not sure exactly how to do that?

      Delete
    3. The following should do it :)

      Remove the following line:
      $ClientCertificate = Get-Item "Cert:\CurrentUser\My\$Secret"

      And update this line:
      $token = Get-MsalToken -ClientId $ClientID -TenantId $TenantID -ClientCertificate $ClientCertificate -ErrorAction Stop

      To this:
      $token = Get-MsalToken -ClientId $ClientID -TenantId $TenantID -ClientSecret $Secret -ErrorAction Stop

      Delete
  48. 0 meetings retrieved from 'WE Conference Room 1'
    Unable to query Graph API: 'The remote server returned an error: (400) Bad Request.'. The mailbox could be inactive, soft-deleted, or hosted on-premise
    s.
    This is what i am getting for all Conference Rooms any idea what the request is ??

    ReplyDelete
    Replies
    1. Hi. Is that meeting room in Exchange Online? Does it work for other meeting rooms? Try printing the value of $uri at the start of the Query-GraphAPI function.

      Delete
  49. Can you help with this error I am getting what result is it looking for??

    Unable to get OAuth token using MSAL: 'Exception calling "GetResult" with "0" argument(s): "Invalid provider type specified."'. Exiting script.

    ReplyDelete
    Replies
    1. Which version of PowerShell are you using? Are you able to "manually" (outside of the script) use the MSAL module to retrieve a token?

      Delete
  50. I am getting this error see below I am using ,net 4.8 a self signed cert and have change the cert to a RSA cert instead of CNG still same error any help would be appreciated

    Unable to get OAuth token using MSAL: 'Could not use the certificate for signing. See inner exception for details. Possible cause: this may be a known issue with apps build against .NET Desktop 4.6 or lower. Either target a higher version of .NET desktop - 4.6.1 and above, or use a different certificate type (non-CNG) or sign your own assertion as described at https://aka.ms/msal-net-signed-assertion. '. Exiting script.

    ReplyDelete
  51. Unable to get OAuth token using MSAL: 'Cannot process argument transformation on parameter 'ClientSecret'. Cannot convert the "#########" value of type "System.String" to type "System.Security.SecureString".'. Exiting script. After chaning the certthumprint to Secret. Can you please suggest.

    ReplyDelete
  52. I wanted to let you know that your script saved me a bunch of time. Thank you very much for taking the time to create such a useful script.

    ReplyDelete
    Replies
    1. Glad to hear it helped! :) Thank you for the feedback!!

      Delete