At our offices, we suspect that rooms are being booked but go unused when the booker claimed to need it. All of our rooms are equipped with movement (PIR) sensors, so we can detect movement in those rooms. We can count the number of people who pass the sensor. I went on a journey to find out how to do something with the data that I’ve got, and perhaps show our office manager, so that she could then think of some consequences or a rewarding system. And to solve my personal problem; all rooms are always booked and in real life we suspect the booked rooms are not always used.
Outlook REST API
Outlook has a REST API, documentation on that can be found here. https://docs.microsoft.com/en-us/previous-versions/office/office-365-api/api/version-2.0/calendar-rest-operations Using this API, we can find calendar events and meeting times that correspond with an email address. A room has an email address, which can be found in outlook properties. We can find these rooms using the Outlook scheduling assistant. Not that we cannot read the organizers of that particular meeting, but we can receive a subject.
Using Azure timed functions, I can set up a serverless function which connects to the outlook API, authorizes as a user with the proper permissions to view the availability and reservations of a certain room in outlook. These room reservations can be sent to the InfluxDB using simple POST queries over HTTP. In my case, I send a 1 to the server with the subject and the start time. In the subject, we find the subject of the meeting as well as the user who booked it. Then, at the end of an appointment, I send a 0. With InfluxDB I can then use fill(previous) to see the current state at any point in time.
SELECT max("SensorValue"), "Description" FROM "RoomName" WHERE ("SensorType" = '"OutlookReservatie"') AND $timeFilter GROUP BY time($interval) fill(previous)
Visualization dashboard
In Grafana, we can then graph the reservations and find correlations with the detected movement in that room. In the end, this should be done automatically by the timed function which can send the subject of the appointment at that time to the office manager in case no one was seen in that room. First, we explore the data manually. We plot the movement in a certain room and combine that with the reservations we just inserted into the database. Grafana can also act when we spot this and send an alert.
The PIR sensors just detect movement, I’ve set them up so I get a single bit message each time someone passes the sensor. This way, I can read the movement in a certain room, or at least the amount pass by’s. Our sensors talk to the things network, which then sends it to the Azure cloud.
We can then color the reservations without movement, making it easier to spot and also alert on it. We simply find the times where there is a reservation without movement and it’s accompanying reservation subject and we know who to blame for the room occupancy.
Yellow is reserved, yet unused room occupancy.
In this graph, you can already see that there are some unused reservations! We have success! ?
Then, to do this automatically; if the room is reserved and the time has passed for the time slot, count the amount of passing in that time frame and area. If we do not see any movement in the last half an hour, but we do see a reservation, we should send an alert.
Great! Now that we can blame people, we should also praise the people who do use their reservation! Let the blaming and praising begin!
