Practical Graph: Reporting Plans in a Microsoft 365 Tenant

Graph Application Permissions Make it Easier to Report Plans

Three years ago, I described some of the frustrations about using Graph APIs to report information about Planner plans. The basic problem was the lack of application permissions for the Planner Graph API. Only delegate permissions were available at the time, which meant that you could only report details of plans belonging to Microsoft 365 groups you belong to. Other plans remain inaccessible because of a lack of permissions.

Application permissions allow Azure AD apps to access data without a signed-in user. Microsoft promised to update the Planner Graph API with application permissions, but a marked lack of progress disappointed those who wanted to interact with plans for administrative purposes. For instance, Microsoft doesn’t have a backup API for Planner, and the lack of application permissions forced people to add an administrator account to every group with a plan to backup plan data – or to move plan data to another tenant.

But recently (I don’t exactly know when because I stopped checking months ago), the Tasks.Read.All and Tasks.ReadWrite.All application permissions for the Planner Graph API turned up.

The Microsoft Graph PowerShell SDK is restricted to the delegated Tasks.Read and Tasks.ReadWrite permission, which means that its cmdlets (like Get-MgGroupPlannerPlan) can only interact with plans the signed-in account can access. Any attempt to assign the application permissions is declined with an odd error. On one level, I understand the restriction because the SDK uses delegate permissions as the norm. However, the SDK also takes Azure AD administrator roles into account when calculating the set of effective permissions available in a session. It would therefore seem reasonable to be allowed to assign the application permission to the SDK so that it could be used by accounts with administrator permission. But that’s not possible (yet).

Using an Azure AD Registered App to Access Planner Data

The solution is to use an Azure AD registered app to access Planner data for every Microsoft 365 group that has a plan in its set of resources. To test the principle, I created a new Azure AD registered app and gave administrator consent for the following application permissions:

• Directory.Read.All (read directory information).
• Group.Read.All (read group information).
• Tasks.Read.All (read plan information).

I then created an application secret to use for testing. In production, it’s safer to use a certificate or use an Azure Automation runbook with a managed identity (in this case, the service principal for the automation account must be assigned the necessary permissions).

Using PowerShell to Access Planner Data

With an application identifier, tenant identifier, and application secret, we can ask Azure AD for an access token containing the app permissions and use that to make Graph API requests. There’s no mystery in this code, as it’s a well-worn road.

The basic steps in the script are:

• Find all Microsoft 365 groups in the tenant.
• For each group, check if it has any plans. Originally, a group could only have one plan, but Microsoft lifted that restriction to allow Teams to support plans in multiple channels.
• If plans are found, extract the details of the plan.
• Report the data.

To check the plans in a group, I used this code (Get-GraphData is a helper function to run the Invoke-RestRequest cmdlet and process the results):

$Uri = ("https://graph.microsoft.com/v1.0/groups/{0}/planner/plans" -f $Group.Id)
[array]$Plans = Get-GraphData -Uri $Uri -AccessToken $Token

When a group has a plan, the data returned (for a single plan) looks like this:

@odata.etag     : W/"JzEtUGxhbiAgQEBAQEBAQEBAQEBAQEBAVCc="
createdDateTime : 2020-06-09T14:45:19.2940321Z
owner           : 107fe4dd-809c-4ec9-a3a1-ab88c96e0a5e
title           : Plans
id              : N6TvImQH70KdUzZGk25B5JYAF77k
createdBy       : @{user=; application=}
container       : @{containerId=107fe4dd-809c-4ec9-a3a1-ab88c96e0a5e; type=group;
                  url=https://graph.microsoft.com/v1.0/groups/107fe4dd-809c-4ec9-a
                  3a1-ab88c96e0a5e}

The script checks that a container (the way Planner describes a plan) exists and then proceeds to fetch the tasks for each plan. Here’s an example of a call:

$Uri =  ("https://graph.microsoft.com/v1.0/planner/plans/{0}/tasks" -f $Plan.id)
[array]$Tasks = Get-GraphData -Uri $Uri -AccessToken $Token

After fetching tasks, the script computes some data such as:

• The timestamp for the newest and oldest task in the plan.
• The number of tasks for each priority (urgent, important, medium, and low). Planner uses numeric values for priorities, with the corresponding values being 1, 3, 5, and 9.
• The number of tasks at each stage of progress (complete, in progress, not started). Planner stores progress as percentage values of 100, 50, and 0.
• The buckets used to organize tasks in the plan (even if a plan holds no tasks, it has a default bucket). For each bucket, the script calculates the number of active and completed tasks and the percentage of completed tasks.

To retrieve the buckets for a plan, the script uses:

$Uri =  ("https://graph.microsoft.com/v1.0/planner/plans/{0}/buckets" -f $Plan.id)
[array]$Buckets = Get-GraphData -Uri $Uri -AccessToken $Token

The information retrieved for each plan goes into a PowerShell list for later processing.

The Microsoft documentation for the various APIs (like List Plans) provides some additional detail.

Reporting the Data

After processing all the plans linked to Microsoft 365 groups, the script processes the data to create an HTML report. The intention here is not to generate the best-looking report in the world. Instead, the script produces a report where all the plans belonging to a group are listed together. For each plan where some tasks are found, the script includes an analysis of each bucket with the number of tasks, completed tasks, active (not started or in progress) tasks, and percentage of active tasks. It’s just an example of what can be done with the data extracted for a plan.

Figure 1 shows an extract of the report from my tenant showing details of the plan the Office 365 for IT Pros eBook team use to organize potential changes published in the Microsoft 365 message center that might affect book content. The plan is called MAC Tasks, and you can see how useful the bucket analysis can be in highlighting areas where work is necessary.

Test It Yourself

The script is available to download from GitHub. Remember that the code is not a perfect solution for Planner reporting. It’s intended to be a practical demonstration of what’s possible now that the Planner Graph API supports application permissions. Feel free to suggest changes (or make them) in GitHub.

Before you can run the script, you’ll need to create an Azure AD registered app in your tenant and adjust the values for the application identifier, tenant identifier, and application secret. After that, navigating the internals of plans, tasks, and buckets should be smooth sailing.