Reporting Exchange Online Meeting Room Usage Patterns

Calculating Statistics for Room Mailboxes

A Practical365.com article I wrote explaining how to extract and report statistics for room mailboxes is quite popular. The script uses Microsoft Graph API requests to fetch data about events from the calendars of the meeting rooms and analyzes the data. Apparently, many people need this data for one reason or another.

As I noted last week, when you publish a PowerShell script and make it available publicly, you’re likely to get requests for enhancements. Most of the time I don’t mind people sharing their ideas with me because I like hearing what others think and the issues they grapple with. Being forced to respond to questions also encourages research to find the right answers, and that’s a good way to acquire more knowledge.

In a minority of cases, I wonder why the person making a request doesn’t simply amend the code to do what they want. It could be that they don’t feel too confident with PowerShell or don’t know how to make a change. Basic familiarity with PowerShell and the modules used with Microsoft 365 is a core competency for administrators. At least, it is if you want to automate administrative operations.

Report Daily Usage Patterns for Room Mailboxes

In any case, this week a request came in to report the most popular days for meetings. Given that we already have the data about meetings and report statistics like the total events for a room, total minutes booked, average event duration, average attendees, and so on, it’s logical to ask when is a meeting room popular.

The information recorded for each meeting has a start and end date, so finding out the day of the week that the meeting occurred on is easily done with the PowerShell Get-Date cmdlet:

$Day = (Get-Date($MeetingStart)).DayOfWeek

Storing the day of the week for each event allows the script to analyze the information when it generates the other statistics. The basic approach I took is:

• Count the total events for each day.
• Compute the percentage of the overall events for each day.
• Build a very basic chart element for the day. The idea is to build a simple bar chart where the larger the bar, the higher the daily room usage is. I’ve no doubt that those with more artistic minds than mine can come up with a much nicer solution.
• Store the information.

After processing all room mailboxes, the script generates summary information, including the daily usage pattern for all rooms (Figure 1).

The daily usage data is stored for each room mailbox and the script outputs the same kind of chart for the individual rooms (Figure 2).

After I published the updated script, I was asked how the script aligns the bars. That’s simple. The script inserts a tab character when creating the output. That’s another old PowerShell trick. If the tab character wasn’t there, the bar chart wouldn’t line up properly.

Download Script from GitHub – But Check Article Comments

If you have issues running the script (downloadable from GitHub), check out my article about the most common errors people encounter when running PowerShell with Graph queries. Many of these issues are debated and resolved in the comments for the original article. Remember, it’s PowerShell, so the code is there to be amended. Enjoy!

---

Insight like this doesn’t come easily. You’ve got to know the technology and understand how to look behind the scenes. Benefit from the knowledge and experience of the Office 365 for IT Pros team by subscribing to the best eBook covering Office 365 and the wider Microsoft 365 ecosystem.