Office365 Room Calendar Displays and ScreenCloud Dashboards

2 Aug

I set up all the rooms in the head office to have their own calendar in Office365 so users can book meetings without fear of double booking etc. This is done simply in Office365 and then from within Outlook calendars for end users.

This system works well out of the box, the users book the room and if its free, they receive an email back with confirmation.

But I really wanted a combined view of all the bookings and then quite liked the idea of each room having a small display near the door showing the status of the room with some information.

I already use ScreenCloud (which is used on some LG commercial TV’, these are also locked down in Hotel mode and have On/Off timers and then into these I have Amazon Fire Sticks with the ScreenCloud app installed) which works great as our digital signage solution.

In ScreenCloud you control all the playlists and schedules form their website and so you never need to touch the TV once it’s setup. Each TV has its own schedule, which then plays different playlists at different preset times. Each playlist contains various items and apps. You can add in any of their apps (Facebook, Twitter, RSS feeds, Weather, Clock, YouTube, BBC, Dropbox, Google Docs, PowerBi and many more, they are also constantly adding to this great list) or add web pages, images, videos etc.

You then build up your own content library of these apps, or webpages and images etc. and then put these into a playlist. Each item can of course have a set time before it moves to the next one.

We also have a weekly fire alarm test and so I have the schedule set to display a fire alarm warning image 2 minutes before until 2 minutes after the test, after this it just goes back to its normal playlist. I also have an image for the Guest WiFi along with the latest password.Note, a lot of this code was done rather quickly, as such It needs a bit of work and is more a proof of concept than a final end product.

Ultimately, I wanted to get the rooms and bookings into a SQL database as I was then more comfortable writing code in C# in Visual Studio with that as a back end.

So first, get a list of the rooms and pop them in a table.

$results = Get-MailBox | where {$_.ResourceType -eq "Room"} | Select-Object DisplayName, PrimarySmtpAddress

#loop through all rooms

foreach ($room in $results){
$roomname = $room.DisplayName -replace "'","''"
$roomemail = $room.PrimarySmtpAddress

$sqlquery = "Insert into rooms.dbo.roomlist values ('" + $roomname + "', '" + $roomemail +"')"

Invoke-Sqlcmd -query $sqlquery
}

Next, let’s get all today’s booking for all the rooms. For this I popped all the bookings in a text file, this PowerShell then runs on a schedule so it’s updated frequently.

$roomlog = "c:\temp\roomlog.csv"

$startdate = Get-Date -Hour 0 -Minute 00 -Second 00
$enddate = $startdate.adddays(+1)

Invoke-RestMethod -Uri "https://outlook.office365.com/api/v1.0/users/[email protected]/calendarview?startDateTime=$startdate&endDateTime=$enddate" -Credential $cred | foreach-object{ $_.Value }| Where-Object {$_.IsAllDay -eq $False}  | Select-Object -Property  @{Name='owner';Expression={'owner'}}, Subject, start, End, @{Name='location';Expression={'Room 1'}} , iscancelled, starttimezone, @{ L = "Attendees"; E = { ($psitem.attendees.emailaddress | Select-Object -expand address) -join ',' } } | export-csv $roomlog -NoTypeInformation -force
Invoke-RestMethod -Uri "https://outlook.office365.com/api/v1.0/users/ [email protected]/calendarview?startDateTime=$startdate&endDateTime=$enddate" -Credential $cred | foreach-object{ $_.Value }| Where-Object {$_.IsAllDay -eq $False}  | Select-Object -Property  @{Name='owner';Expression={'owner'}}, Subject, start, End, @{Name='location';Expression={'Room 2'}} , iscancelled, starttimezone, @{ L = "Attendees"; E = { ($psitem.attendees.emailaddress | Select-Object -expand address) -join ',' } }  | export-csv $roomlog  -Append -NoTypeInformation -force
Invoke-RestMethod -Uri "https://outlook.office365.com/api/v1.0/users/ [email protected]/calendarview?startDateTime=$startdate&endDateTime=$enddate" -Credential $cred | foreach-object{ $_.Value }| Where-Object {$_.IsAllDay -eq $False}  | Select-Object -Property  @{Name='owner';Expression={'owner'}}, Subject, start, End, @{Name='location';Expression={'Room 3'}} , iscancelled, starttimezone, @{ L = "Attendees"; E = { ($psitem.attendees.emailaddress | Select-Object -expand address) -join ',' } }   | export-csv $roomlog  -Append -NoTypeInformation -force
Invoke-RestMethod -Uri "https://outlook.office365.com/api/v1.0/users/ [email protected]/calendarview?startDateTime=$startdate&endDateTime=$enddate" -Credential $cred | foreach-object{ $_.Value }| Where-Object {$_.IsAllDay -eq $False}  | Select-Object -Property  @{Name='owner';Expression={'owner'}}, Subject, start, End, @{Name='location';Expression={'Room 4'}} , iscancelled, starttimezone, @{ L = "Attendees"; E = { ($psitem.attendees.emailaddress | Select-Object -expand address) -join ',' } }  | export-csv $roomlog  -Append -NoTypeInformation -force

This then creates a CSV file like this:

Bob Jones,IT Meeting,2017-08-02 07:00:00,2017-08-03 16:00:00,Room 3,False,GMT Standard Time,[email protected]
Frank Spencer,DOS,2017-07-31 08:00:00,2017-08-09 16:00:00,Room 1,False,GMT Standard Time,[email protected]
James Clark,Budget Discussion,2017-08-02 08:30:00,2017-08-02 09:30:00,Room 2,False,GMT Standard Time, [email protected], [email protected], [email protected]
Bobby Alexrod,Finance Meeting,2017-08-02 13:00:00,2017-08-02 14:00:00,Room 3,False,GMT Standard Time,[email protected]

Which has the organiser, meeting title, start and end date time, room name and the last bit is the attendees, I later work out the number of attendees from the number of values here.

I then take this CSV and pop it into SQL

if (Test-Path $roomlog){

$data = Import-Csv -Delimiter "," -Header @("owner","Subject","Start","End","Location","iscancelled","starttimezone","attendees") -Path $roomlog

foreach($row in $data){
$row.'Start' = $row.'Start' -replace 'T',' '
$row.'Start' = $row.'Start' -replace 'Z',''
$row.'End' = $row.'End' -replace 'T',' '
$row.'End' = $row.'End' -replace 'Z',''
$row.'Location' = $row.'Location' -replace '@{DisplayName=',''
$row.'Location' = $row.'Location' -replace '}',''
$row.'attendees' = $row.'attendees' -replace '[email protected],',''
$row.'attendees' = $row.'attendees' -replace '[email protected],',''
$row.'attendees' = $row.'attendees' -replace '[email protected],',''
$row.'attendees' = $row.'attendees' -replace '[email protected],',''

$temp = $row.'subject'
$arr = $temp -split(' ');
$name = $arr[0] + " " + $arr[1]
$row.'subject' =  $row.'subject' -replace ($name + " "),''

$row.'owner' = $name

}

$data | ConvertTo-Csv -NoTypeInformation | % {$_.Replace('"','')} | select-object -skip 2 | Out-File $roomlog

Invoke-Sqlcmd -InputFile "C:\Tools\SQL\ImportRoomLogs.sql" -Database Rooms
}

The SQL file is as follows:

USE Rooms
GO
DELETE From log
BULK INSERT "log"
FROM 'C:\temp\roomlog.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO

So now the room booking data is all in SQL, albeit with a bit of Blu Tack and sticky tape.

I then wrote a few pages in C#. The first is essentially a view of all the bookings in all rooms for one of the large displays. There’s a bit of code to work out the duration of the meeting, the progress (in %) if it’s already started. I also have all user photos and so it displays a small thumbnail version of this on each row.

The progress column also shows a flashing “Starting soon” if the booking begins in under 8 minutes. If the booking is active, it highlights the row, if it has finished it greys it out lightly.

I then have another page that lists all the rooms and the one you select passes the room email address to the next page in the query string, so you could just go directly to the page you want.

This smaller screen then displays Booked/Available in large letters and is colour coded to make it a bit easier to see at a glance if it’s free or not. It shows a cut down version of the list of bookings, and only shows a maximum of 2. If one is in progress, it shows that one and the next, otherwise it shows the next 2.

The “Free until” time was easy to work out via SQL. The “Booked until” time, however, meant looping through all the following meetings until there was a gap between them.

The idea being that now I can use some old iPad Mini’s in mounts, hard-wired in and set to display their page 24/7. I’m currently in the process of testing a few hardware solutions for these smaller screens and looking at available mounts, something like this perhaps:

Or one of these:

Or one of these:

What I’m after is a wall mount (in white) that ideally locks in the iPad mini (or Android tablet perhaps) so that I can run the wires behind it out of view and leave it on permanently. I really need the home button covered and also a glass/perspex screen over the top so that nobody can use the touchscreen. If anyone knows or has something that would fit the bill, please get in touch! (For any suppliers that might have one, I’ll happily write you a little review!)

All in all, I’m rather happy with the result. Yes, the code needs a bit of tidying, but it’s worked well for a while now and seems like a good start.

I also wrote a few more pages which are displayed on the digital signage screens too.

If a user is set Out Of Office, it displays their first name and photo:

Another page selects a random member of staff (“ORDER BY NEWID()”) from AD and displays a short bio:

Another page lists the upcoming birthdays, and also has some JavaScript fireworks of it matches the current date!

Oh and one that works out if any member of staff currently has a work anniversary (same month) and displays this to:

All this information is stored in Active Directory, a few of the items are stored using the extension attributes.

There’s a META refresh tag in these pages too so that they update every minute. I really need to look at building it with AJAX, I’ll put that down as a phase 2 for now.

I’ll update this post once I have the hardware setup for each room.

Update:

So after quite a bit of searching and looking for the best and most cost-effective solution, I ended up moving away from a tablet idea and instead mounted a 24″ LG Monitor (1920 x 1080 so all screens are the same resolution to make managing them that bit easier) above the meeting room door. I opted for a monitor that had a USB port to Power the Amazon Fire Stick and then I only had to run a kettle cable down the wall (I already had spare ceiling sockets fitted when the office was re-fitted)

Parts List: