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:
- Users need to have an admin role to be able to see the reports.
- 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.
Now, before you read on and get annoyed that I’m providing a way for non-admins to see admins performing elevations and why – the intent of this solution is merely to make it available. It’s up to you who gets access to the report itself.
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!
Also published on Medium.
Discover more from Loryan Strant, Microsoft 365 MVP
Subscribe to get the latest posts sent to your email.
Great solution, worked like a charm.
How to target this for PIM group?
Do you mean to only show elevations for members of a specific group used in PIM?
Hi Loryan, when setting up the app registration, do you need delegated or application registration permissions? do I also leave the default “user.read” delegated permissions?
Many thanks
You need application permissions. And you can leave the User.Read there.
Thank you, another question, about the URL, it’s failing for me, is there a way to get the URL myself without copying it?
This is the URL for the http request?. I’m getting an authorisation failure.
Thank you
Did you consent the permissions once you added them to the app registration?
Hi Loryant, apologies, I’m having difficulty understanding this setup, I have imported the app, and the BI report, but I don’t know how to set up PIM to feed data to dataverse? it’s not clear how to use the app which I registered, and the PIM logs to get the data into dataverse, as I’m flying blind really, I need some help if you have some pointers. I have an app registered, I have a dataverse table, currently empty. I have imported your app, and your BI report, I think it’s connected ok, but it’s giving me errors because the table is empty (I think that’s the issue)
Many thanks
Ian
Hi Ian, sorry it’s taken me so long to respond – for some reason I didn’t see this comment.
Did you end up making it through, or did you give up in the end?
Hi Loryan. I am almost done with all the steps, just trying to figure out the issue I am facing while opening the template. Could you please help me with this. Below is the error message in Power BI after entering the Dataverse URL and Table Name.
Error: “Elevation History
Microsoft SQL: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server”
I’ve seen this before at a few different offices where they have some network traffic restrictions in place.
I would suggest trying from a different network and seeing if it works.
When I’ve had this on on a network, I just tether to my phone to get around it.
Hi Loryan, I am getting the error in our IVDI and other personal devices are working fine with all the settings. I wanted to know where I can find the backend technical connectivity between PowerBI Desktop Application and Dataverse.
Sorry I’m not exactly following what you mean by “IVDI” or how personal devices factor into this?
The backend connectivity between Power BI Desktop and Dataverse is using the built-in Dataverse connector.
Hey Loryan, I see flow run failure in power automate past few days, it was working fine, then got this error. This happed 2 months ago and got it fixed by itself after a week. Can you please let me know how to fix it permanently?
The error is in HTTP – Retrieve Elevations.
Error: https://graph.microsoft.com/v1.0/roleManagement/directory/roleAssignmentScheduleRequests?$filter=createdDateTime%20ge%202025-07-28T04:00:35.3409601Z&$expand=roleDefinition,principal
Sorry, I can’t see your error as I don’t have access to your environment or what might be causing it.
Application permissions are required, as I’ve noted in the blog post.
You can leave User.Read as it doesn’t interfere.
Hi Loryan. Your Solution works like a charm.
As we are using AAD groups for PIM I tried to adapt your solution for us.
The AAD groups have several roles directly assigned (e.g. Sharepoint admin + Exchange Admin). The admin user Elevates via PIM into the aad group on more or less daily basis depend to his actual tasks.
So I copied your Cloud Flow and dataverse table. The graph URL for AAD PIM groups is the following: https://graph.microsoft.com/v1.0/identityGovernance/privilegedAccess/group/assignmentScheduleRequests?$expand=principal,group
With graph explorer I get good results. The needed permissions I assigned to the app registration.
I changed the URL in the copied cloud flow and also changed the destination for the dataverse table to write into. The flow is running and successfull, but in the HTTP response, there is no data. only the header data:
{
“statusCode”: 200,
“headers”: {
“Cache-Control”: “private”,
“Transfer-Encoding”: “chunked”,
“Vary”: “Accept-Encoding”,
“Strict-Transport-Security”: “max-age=31536000”,
“request-id”: “xxxxxx”,
“client-request-id”: “xxxxx”,
“x-ms-ags-diagnostic”: “{\”ServerInfo\”:{\”DataCenter\”:\”xxxx\”,\”Slice\”:\”E\”,\”Ring\”:\”4\”,\”ScaleUnit\”:\”010\”,\”RoleInstance\”:\”xxxx\”}}”,
“OData-Version”: “4.0”,
“Date”: “Fri, 12 Sep 2025 09:47:55 GMT”,
“Content-Type”: “application/json; odata.metadata=minimal; odata.streaming=true; IEEE754Compatible=false; charset=utf-8”,
“Content-Length”: “164”
},
“body”: {
“@odata.context”: “https://graph.microsoft.com/v1.0/$metadata#identityGovernance/privilegedAccess/group/assignmentScheduleRequests(principal(),group())”,
“value”: []
}
}
as i wrote, in graph explorer i get a lot stuff in the response.
The only difference I can see is the Cache-Control in the header. above you can see private. Graph explorer has no-cache.
Do you have any idea, what could be wrong. Thanks a lot in advance.
That’s annoying.
I think I’ve experienced similar before, as HTTP actions in Power Automate do have different headers than doing it via Graph Explorer.
I’d have a look a bit deeper and see if there’s anything else that needs to be changed in the HTTP action or headers to make it work.