A better way to report administrator role elevations in Privileged Identity Management

Privileged Identity Management (PIM) in Entra ID (formerly known as Azure Active Directory) is a fantastic solution for helping reduce overuse of administrative rights by IT admins in the course of administering Microsoft 365 environments.

All too-often, IT admins will perform functions using their admin account using the Global Administrator because, quite frankly, it’s easier.

However in this day and age we should be using the principle of least privilege — where effectively IT admins should only be selecting the relevant admin roles they need to perform the task.

The Challenge

While PIM does a good job and provides some reports, there are fundamentally two main issues I see:

1. Users need to have an admin role to be able to see the reports.
2. The history of elevations takes an extra click to see the justificaiton reason, which seems minor — but adds up when you have to do it multiple times.

While challenge 1 is somewhat manageable — it does seem silly to give people an admin role so that they can look at report of… admin role elevations.

Additionally, there are people outside of IT admin functions that may need to see this information — such as IT management, executives, auditors, etc.

So while we can give them admin roles, the user interfaces of Entra ID and PIM aren’t exactly that friendly for non-technical folks who may be unfamiliar with the terms and menus.

The Solution

Utilising Microsoft Graph with Entra ID, Power Automate, Dataverse, and Power BI, we are able to get the data we need on a regular basis and make it available in a more friendly version.

App Registration

The first thing we need is an App Registration in Privileged Identity Manager with only the following permission: RoleManagement.ReadWrite.Directory.

Now, I’m never a fan of using “Write” permissions in something that is only reading information from an API, however according to the Microsoft Graph documentation for the list RoleAssignmentScheduleRequests action — that is the least privileged permission required when using application permissions.

Dataverse table

The second thing we’ll need is a Dataverse table with really only two fields:

• Elevation ID
• Elevation JSON

You could probably argue that the Elevation ID column isn’t needed, but I chose to create it in case I needed a relationship at a future point and wanted to have something to correlate against.

(If you really want, you can store this in a SharePoint list or something else.)

Here’s an example of what gets stored in our two columns:

Power Automate workflow

The workflow is relatively straightforward:

I’ve set the workflow to run once a day, and to therefore retrieve all the elevations from the previous day:

Within the query I’m also expanding both the role definition as well as the principal in the elevation, which gives me a display name for both the role that was elevated, as well as the person performing the elevation.

This saves me having to perform additional queries or lookups to match that information from either another API call or another table.

Then, for every elevation found within my query, we store this as a new row in our table:

With other workflows I may parse the JSON and record every required value into a separate column in the table — however Dataverse can be a bit picky if it doesn’t get things in the way format it expects, and sometimes even if there’s nothing to record for that field.

Also, Power BI has the ability to parse JSON — so we may as well do the heavy lifting over there.

Power BI report

I’m not going to dive into the transformations performed within Power BI report itself, because for that I’ve made it into a template for you to import and start using yourself.

When opening the template file you’ll be prompted for the Dataverse URL (without the “https://” part), as well as the table name to use. Once you’ve connected to the table and data is coming in, there are two pages used to display the data.

The first is the overview page:

This page shows the elevations by admin account, as well as statistics on the elevations by role.

Additionally there are some slicers at the top allowing you to filter by:

• Admin name
• Admin role
• Elevation date

The history page in the report provides a table with more details including:

• Date & time of elevation
• Admin name
• Admin role
• Justification

Back on the front page, the graphs allow for you to select items which then update the statistics being shown for them.

For example, if you select an admin name it will filter the pie chart to show the breakdown of admin role elevations as well as the bar chart to give you a further breakdown of percentages:

Additionally, both of the bar charts have drillthrough capabilities which allows you to hover over an admin name and drill into the history for that admin:

Or drill into an admin role:

And there you have it!

Something to be aware of is that my report doesn’t display the ticket reference for any elevation, but if that’s something you use in PIM — simply modify the JSON parsing in the Power BI report to expose the field. Once you’ve done that it’ll be available for display in the history tables.

Getting the solution

You can download both the Power Platform solution as well as Power BI template file from my GitHub repository.

Simply import the Power Platform solution and provide it with:

• Tenant ID
• App ID (from your app registration)
• Secret value (from your app registration

Then open the Power BI report template, connect it as mentioned above, publish it somewhere, set it to refresh every day, and enjoy!

Originally published at Loryan Strant, Microsoft 365 MVP.