I had a request from a user to find out the meeting room utilization across our head office, but unfortunately I couldn’t find an easy way to do this with Exchange 2010 cmdlets. Here are some instructions to do so with COM objects, PowerShell and Excel.
First create a mailbox that will have the meeting room rights assigned to it, then grant the user access to the required mailboxes so that they show in Outlook:
Get-Mailbox meetingroomprefix* | Add-MailboxPermission -AccessRights FullAccess -User temporarymailbox
Open Outlook and add the meeting room mailboxes, then run the PowerShell script below with the appropriate date range. Mine shows the last financial year.
Next, open the CSV output file in Excel then:
- Create new sheet called Report
- Insert a pivot table containing all the data from the raw data sheet
- Drag “MeetingRoom” to Rows
- Drag “Duration” to Values. Edit the field settings to be “Count” and rename to “Number of Meetings”
- Drag “Duration” to Values. Edit the field settings to be “Sum” and rename to “Total Duration”
- Create two new fields – one with the amount of work weeks per year (48.775 for mine) and the amount of hours per week (40 for mine).
- Create a new table for utilization percentages. Put the formula as
Comment and share