Free up a room in a meeting series or single event if it’s not going to be used!

Probably many people book a meeting series and invite a meeting room to that series. What happen the days people decide that today we can take the meeting over Teams, and won’t need to be in the meeting room today? People usually forget to cancel/free up the room. We end up with many rooms being empty, that could have been booked by others. This also often happen to single events

I have created a PowerShell script that gets a list of all meeting rooms any user has booked in a meeting series and single event in the coming week, then the script sends an email containing a clickable link that triggers a Logic App that cancels the room booking. All this is accomplished by using managed identities, so no need for any application secret to renewed/copied.

Updated:
* Faster, because we now use the calendarview endpoint and not events, so no need to find first instance of a series first.
* If subject is hidden in room calendar, we get the subject from the organizers calendar.

First, you need to create an Azure Automation account.

Open the account, and go to the Identity menu. Turn on System assigned identity and copy the object id.

Now go create a new blank Logic Apps, open it, and go to the Identity menu. Turn on the system assigned managed identity, and copy the object id.

Now we need to assign some Graph API permission to these two managed identities before we continue, by using these Graph API PowerShell SDK module commands.

	#Requires -Modules Microsoft.Graph
	# Install the module. (You need admin on the machine.)
	# Install-Module Microsoft.Graph

	# Set Static Variables
	$TenantID="enter here"
	$AutomationAccountDisplayname ="enter here"
	$LogicAppDisplayname = "enter here"

	# Define dynamic variables
	$ServicePrincipalFilter = "displayName eq '$($AutomationAccountDisplayname)'"
	$GraphAPIAppName = "Microsoft Graph"
	$ApiServicePrincipalFilter = "displayName eq '$($GraphAPIAppName)'"

	# Scopes needed for the managed identity (Add other scopes if needed)
	$Scopes = @(
	"Mail.Send","Place.Read.All","Calendars.read"
	)

	# Connect to MG Graph - scopes must be consented the first time you run this.
	# Connect with Global Administrator

	Connect-MgGraph -Scopes "Application.Read.All","AppRoleAssignment.ReadWrite.All" -TenantId $TenantID -UseDeviceAuthentication

	# Get the service principal for your managed identity.
	$ServicePrincipal = Get-MgServicePrincipal -Filter $ServicePrincipalFilter

	# Get the service principal for Microsoft Graph.
	# Result should be AppId 00000003-0000-0000-c000-000000000000
	$ApiServicePrincipal = Get-MgServicePrincipal -Filter "$ApiServicePrincipalFilter"

	# Apply permissions
	Foreach ($Scope in $Scopes) {
	Write-Host "`nGetting App Role '$Scope'"
	$AppRole = $ApiServicePrincipal.AppRoles \| Where-Object {$_.Value -eq $Scope -and $_.AllowedMemberTypes -contains "Application"}
	if ($null -eq $AppRole) { Write-Error "Could not find the specified App Role on the Api Service Principal"; continue; }
	if ($AppRole -is [array]) { Write-Error "Multiple App Roles found that match the request"; continue; }
	Write-Host "Found App Role, Id '$($AppRole.Id)'"

	$ExistingRoleAssignment = Get-MgServicePrincipalAppRoleAssignment -ServicePrincipalId $ServicePrincipal.Id \| Where-Object { $_.AppRoleId -eq $AppRole.Id }
	if ($null -eq $existingRoleAssignment) {
	New-MgServicePrincipalAppRoleAssignment -ServicePrincipalId $ServicePrincipal.Id -PrincipalId $ServicePrincipal.Id -ResourceId $ApiServicePrincipal.Id -AppRoleId $AppRole.Id
	} else {
	Write-Host "App Role has already been assigned, skipping"
	}
	}



	# Define dynamic variables
	$ServicePrincipalFilter = "displayName eq '$($Logicappdisplayname)'"
	$GraphAPIAppName = "Microsoft Graph"
	$ApiServicePrincipalFilter = "displayName eq '$($GraphAPIAppName)'"

	# Scopes needed for the managed identity (Add other scopes if needed)
	$Scopes = @(
	"Calendars.readwrite"
	)



	# Get the service principal for your managed identity.
	$ServicePrincipal = Get-MgServicePrincipal -Filter $ServicePrincipalFilter

	# Get the service principal for Microsoft Graph.
	# Result should be AppId 00000003-0000-0000-c000-000000000000
	$ApiServicePrincipal = Get-MgServicePrincipal -Filter "$ApiServicePrincipalFilter"

	# Apply permissions
	Foreach ($Scope in $Scopes) {
	Write-Host "`nGetting App Role '$Scope'"
	$AppRole = $ApiServicePrincipal.AppRoles \| Where-Object {$_.Value -eq $Scope -and $_.AllowedMemberTypes -contains "Application"}
	if ($null -eq $AppRole) { Write-Error "Could not find the specified App Role on the Api Service Principal"; continue; }
	if ($AppRole -is [array]) { Write-Error "Multiple App Roles found that match the request"; continue; }
	Write-Host "Found App Role, Id '$($AppRole.Id)'"

	$ExistingRoleAssignment = Get-MgServicePrincipalAppRoleAssignment -ServicePrincipalId $ServicePrincipal.Id \| Where-Object { $_.AppRoleId -eq $AppRole.Id }
	if ($null -eq $existingRoleAssignment) {
	New-MgServicePrincipalAppRoleAssignment -ServicePrincipalId $ServicePrincipal.Id -PrincipalId $ServicePrincipal.Id -ResourceId $ApiServicePrincipal.Id -AppRoleId $AppRole.Id
	} else {
	Write-Host "App Role has already been assigned, skipping"
	}
	}

view raw AssignGraphRights.ps1 hosted with ❤ by GitHub

Now you need to go into edit mode of the Logic App.

Add a When a HTTP request is received trigger.

Set it to GET and paste the following JSON Schema:

	{
	"queries": {
	"myString": "Test123"
	}
	}

view raw WebhookJSON.ps1 hosted with ❤ by GitHub

Add a Parse JSON Action with the following schema.

	{
	"type": "object",
	"properties": {
	"myString": {
	"type": "string"
	}
	}
	}

view raw Queries.ps1 hosted with ❤ by GitHub

Add another Parse JSON action with the following JSON Schema.

	{
	"properties": {
	"MeetingID": {
	"type": "string"
	},
	"MeetingRoomUPN": {
	"type": "string"
	}
	},
	"type": "object"
	}

view raw ParseJSON.ps1 hosted with ❤ by GitHub

Initialize a variable and set it.

Do the same for a second variable.

Now, create a HTTP DELETE request with the following URL and settings.

Click Save. Remember to copy the webhook url at the top after clicking save.

Go back to the automation account, go to the Runbooks menu, and click Create a Runbook.

Fill out and click Create.

Copy and paste the following script into the runback. Remember to change the email address you want to send emails from and to enter your Logic Apps webhook URL.

	#Enter email account you want to send email reminders from
	$Email = "enter email here"

	#Enter Logicapp URL
	$LogicAppURL = "enter webhook url here"


	#If subject is hidden in meeting room calendar, set this to true so we get the subject from organizers calendar instead.
	$HiddenSubject = $true

	$stopwatch = [System.Diagnostics.Stopwatch]::new()
	$stopwatch.Start()
	function AuthenticationFunction {

	Connect-AzAccount -Identity -ErrorAction Stop \| Out-Null
	$AccessToken = Get-AzAccessToken -ResourceTypeName MSGraph -ErrorAction Stop \| select -ExpandProperty Token
	$global:Header = @{"Authorization" = "Bearer $AccessToken" }



	$stopwatch = [System.Diagnostics.Stopwatch]::new()
	$Stopwatch.Start()

	}

	AuthenticationFunction

	#Gets all meetingrooms.
	$uri = "https://graph.microsoft.com/v1.0/places/microsoft.graph.room?`$top=5000"


	$Rooms = while (-not [string]::IsNullOrEmpty($uri)) {
	# API Call
	# Write-Host "`r`nQuerying $currentUri..." -ForegroundColor Yellow
	$apiCall = Invoke-WebRequest -Method "GET" -Uri $uri -ContentType "application/json" -Headers $global:Header -ErrorAction Stop -UseBasicParsing
	$nextLink = $null
	$uri = $null

	if ($apiCall.Content) {
	# Check if any data is left
	$nextLink = $apiCall.Content \| ConvertFrom-Json \| Select-Object '@odata.nextLink'
	$uri = $nextLink.'@odata.nextLink'
	$apiCall.Content \| ConvertFrom-Json
	}
	}




	$MeetingSeriesList = @()
	Foreach($room in $rooms.value){

	If($stopwatch.Elapsed.Minutes -gt 40){
	AuthenticationFunction
	}

	$RoomUPN = $room.emailaddress
	$RoomDisplayName = $room.displayName
	$Today = ((Get-Date).Date)

	$endDate = $Today.AddDays(10)

	$startDate = get-date $today -Format "yyyy-MM-dd"
	$endDate = get-date $endDate -Format "yyyy-MM-dd"
	$startDateinstance = get-date $Today -Format "yyyy-MM-dd"

	$uri = "https://graph.microsoft.com/v1.0/users/$RoomUPN/calendarview?startDateTime=$($startDate)T00:00:00.0000000&endDateTime=$($endDate)T23:59:00.0000000"

	$meetings = while (-not [string]::IsNullOrEmpty($uri)) {
	#API Call
	$apiCall = Invoke-WebRequest -Method "GET" -Uri $uri -ContentType "application/json" -Headers $global:Header -ErrorAction Stop -UseBasicParsing
	$nextLink = $null
	$uri = $null

	if ($apiCall.Content) {
	# Check if any data is left
	$nextLink = $apiCall.Content \| ConvertFrom-Json \| Select-Object '@odata.nextLink'
	$uri = $nextLink.'@odata.nextLink'
	$apiCall.Content \| ConvertFrom-Json
	}
	}



	$meetings.value \| ForEach-Object {

	$tempDateStart =(Get-Date $($_.Start.datetime)).ToUniversalTime()
	$tempDateEnd =(Get-Date $($_.end.datetime)).ToUniversalTime()
	$tzEST = [System.TimeZoneInfo]::FindSystemTimeZoneById("$($_.originalStartTimeZone)")
	$CurrentTimeStart = [System.TimeZoneInfo]::ConvertTimeFromUtc($tempDateStart, $tzEST)
	$CurrentTimeEnd = [System.TimeZoneInfo]::ConvertTimeFromUtc($tempDateEnd, $tzEST)
	$OrganizerDateStart = (Get-Date $($_.Start.datetime) -Format o).split("+")[0]
	$OrganizerDateStart = get-date $OrganizerDateStart -Format "yyyy-MM-dd"


	$iCalUId = @()
	$iCalUId = $_.iCalUId

	$uri = "https://graph.microsoft.com/v1.0/users/$($_.organizer.emailAddress.address)/calendarview?startDateTime=$($OrganizerDateStart)T00:00:00.0000000&endDateTime=$($OrganizerDateStart)T23:59:00.0000000"


	$Subject = @()

	if($HiddenSubject -eq $true){
	$Subject = ((Invoke-RestMethod -Uri $uri -Headers $global:Header).Value \| Where-Object{$_.iCalUId -like $iCalUId}).Subject
	$Subject=$Subject.Replace("'","")
	}
	else{
	$Subject = $_.Subject
	}
	#Write-Output $Subject

	$TempMeetingSeriesList = [PSCustomObject]@{
	Subject = $Subject
	Date = $(Get-Date $CurrentTimeStart -Format "yyyy-MM-dd")
	StartTime = $(Get-date $CurrentTimeStart -Format "HH:mm")
	EndTime = $(Get-date $CurrentTimeEnd -Format "HH:mm")
	ID = $_.ID
	MeetingRoomUPN = $RoomUPN
	MeetingRoomDisplayName = $RoomDisplayName
	OrganizerUPN = $_.organizer.emailAddress.address
	OrganizerName = $_.organizer.emailAddress.name
	}
	$MeetingSeriesList += $TempMeetingSeriesList
	}
	}

	$MeetingSeriesList = $MeetingSeriesList \| Group-Object ID\| %{ $_.Group \| Select 'Subject','Date', 'StartTime', 'EndTime', 'ID', 'MeetingRoomUPN', 'MeetingRoomDisplayName', 'OrganizerUPN', 'OrganizerName' -First 1}

	$Today = (Get-Date).date
	$Sunday = Get-Date ($Today.AddDays(1)) -Format yyyy-MM-dd
	$NextSunday = Get-Date ($Today.AddDays(8)) -Format yyyy-MM-dd

	#Finds all unique meeting organizers.
	$users = $MeetingSeriesList \| Select-Object OrganizerUPN,OrganizerName -Unique


	#Find all the meetingrooms a organizer have booked this week, and send email with reminder to see if any rooms can be canceled.
	foreach($user in $users){
	If($stopwatch.Elapsed.Minutes -gt 40){
	AuthenticationFunction
	}


	$EventsThisWeek = @()
	$bodyHTML = @()

	$EventsThisWeek = $MeetingSeriesList \| Where-Object{$_.date -ge $sunday -and $_.date -lt $NextSunday -and $_.organizerupn -like $user.organizerupn}

	If($EventsThisWeek){
	#$EventsThisWeek \| ft
	$Title="Here are your meeting room bookings for this week."
	$bodyHTML ='<doctype html><html><head><title>' + $Title +' </title></head><body><font face="Calibri" size="3">'
	$bodyHTML+="<p>Hi $($user.OrganizerName)!</p><p>Please cancel any meetingrooms you wont use this week<b><i></i></b>,"
	$bodyHTML+="<table border=1><tr><th>Meeting room</th><th>Booked on</th><th>at Time</th><th>Subject</th><th>Realse Meeting Room</th></tr>"

	foreach($EventThisWeek in $EventsThisWeek){
	$webhookURL = "$LogicAppURL&myString={`"MeetingRoomUPN`": `"$($EventThisWeek.MeetingRoomUPN)`" , `"MeetingID`": `"$($EventThisWeek.ID)`"}"
	$bodyHTML+="<tr><td>$($EventThisWeek.MeetingRoomDisplayName)</td><td>$($EventThisWeek.Date)</td><td>$($EventThisWeek.StartTime) to $($EventThisWeek.EndTime)</td><td>$($EventThisWeek.Subject)</td><td><a href=\'$webhookURL\'>Click here to realse meeting room</a></td></tr>"
	}</table>
	#$bodyHTML+="<p>Thanks,</p><p>MWPS COL Team</p>"
	$BodyEmail = @"
	{
	"message": {
	"subject": "$Title",
	"body": {
	"contentType": "HTML",
	"content": '$bodyHTML'
	},
	"toRecipients": [
	{
	"emailAddress": {
	"address": "$($user.OrganizerUPN)"
	}
	}
	]
	},
	"saveToSentItems": "false"
	}
	"@
	Invoke-RestMethod -Method POST -Uri "https://graph.microsoft.com/v1.0/users/$email/sendMail" -Headers $global:Header -body $BodyEmail -ContentType "application/json;charset=utf-8"
	}


	}