After putting a stable application in production, we needed some reporting on what was being created in our environment. Our licenses included Power BI, so that choice was easy although we didn't have any previous experience building reports.
Data import
Before you can build a report, you need to import your data into Power BI. All of our data is stored in other Microsoft products so we assumed that importing it into Power BI would go very easy.
Cosmos DB was the easiest to import (although the connector is still in beta), but Microsoft Graph was giving us the most troubles. We had to work around it using an Azure Function as a proxy, as it doesn't allow connecting to it directly.
The provided reports from Microsoft Graph also have some weird quirks. We have the list of our Teams with custom metadata from the groups endpoint, and we wanted to combine it with reporting data from getOffice365GroupsActivityDetail but it doesn't provide the group id... We could get the used storage from getSharePointSiteUsageDetail (it provides site id), and with some trickery we could associate this to the correct groups.
Reports
We ended up with three reports in Power BI to help our staff better support the end users:
- a report that showed all the requests
- a report that showed all the active teams in the environment
- a report that showed all the soft-deleted teams in the environment
I'm not much of a report designer and there was limited data available when I made the screenshots, so they are just for illustrating the possibilities.
Team Requests
This report gives an overview of all the requests that happened in the system. It shows the number of requests done, spread across the different collaboration types and statuses. If all goes well, all requests should show as "Request Fulfilled" but the report gives the first indications of things going wrong during provisioning.
Filter options are limited to showing data from a specific person that made a request, or data from a specific period.
Active Teams & Soft Deleted Teams
Reports for "Active Teams" and "Soft Deleted Teams" show different data, but provide similar functionality. The former shows all teams that are still available within the system. The latter shows all teams that have been deleted, but are still in their "grace period": teams that get deleted (by an owner, an admin or due to the expiration policy) go in a 30 day grace period. During this period they can get restored.
Our support staff uses this to follow up on Teams that get deleted due to the expiration policy, where the owners might have forgotten to renew the Team.
Although both reports allow for filtering on collaboration type, creation date and renewal date, the most important functionality allows the validation of our governance rules. It helps with identifying Teams that aren't compliant with the rules, by
- showing Teams that don't have at least 2 owners
- showing Teams that aren't private (which we require)
- showing Teams with guest users as owners (which we don't allow)
- showing Teams with guest users as members (just for information)
- having the ability to sort and filter on storage used, to keep track of Teams that use a lot of storage
My report wasn't the prettiest, so one of my colleagues took a stab at making it better:
The application and the reports combined allowed us to put multiple, non-default, governance policies in place when working with Microsoft Teams.
This series of blogposts finishes with some thoughts on possible improvements and wishes for Microsoft, all in the next post!
This post is part of the series Automate governance in Microsoft Teams: