Azure Role Assignments Audit Report

Overview:

Azure Administrators often come across challenges while tracking multiple Azure role assignments and removals. At present Azure provides Activity Logs but they make less sense to non-techsavy stakeholders. For example it includes Role Id, Principal Id but doesn't indicate Role names and Principal names which can make the report more readable. To ensure proper tracking and accountability, we need a comprehensive report that includes the following details:

Initiator and Timestamp
User/Group/Principal assigned/removed
Role assigned/removed
Scope of the Attempt

Pre-Requisites:

Export subscription level Activity Logs to a Log Analytics Workspace. For this navigate to Subscription > Activity log > Export Activity Log > Add Diagnostic Setting

Add Diagnostic Setting to export Administrative logs to a Log Analytic Workspace of your choice and hit the save button:

Navigate to the Workspace and Retrieve the Workspace ID from the overview section, we'll require this in our script.

Solution:

We have created a solution that retrieves and refines information from the Log Analytic Workspace stored Activity Logs and creates a readable CSV report.

Sample Output:

PowerShell Script:

Please replace with appropriate workspace ID(line 32,33) and output CSV file path(line 57, 78). You can provide same values for both at multiple places. Based on the requirement and Log Analytics Retention the no. of days can also be edited(line 6,20)

#Login Azure Account Add-AzAccount #Log Analytics query for retrieving Role Assignment addition activities for the past 2 days $addqr = 'AzureActivity | where TimeGenerated > ago(2d) | where CategoryValue =~ "Administrative" and OperationNameValue =~ "Microsoft.Authorization/roleAssignments/write" and ActivityStatusValue =~ "Start" | extend RoleDefinition = extractjson("$.Properties.RoleDefinitionId",tostring(Properties_d.requestbody),typeof(string)) | extend PrincipalId = extractjson("$.Properties.PrincipalId",tostring(Properties_d.requestbody),typeof(string)) | extend PrincipalType = extractjson("$.Properties.PrincipalType",tostring(Properties_d.requestbody),typeof(string)) | extend Scope = extractjson("$.Properties.Scope",tostring(Properties_d.requestbody),typeof(string)) | extend RoleId = split(RoleDefinition,"/") | extend InitiatedBy = Caller | extend Operation = split(OperationNameValue,"/") | project TimeGenerated,InitiatedBy,Scope,PrincipalId,PrincipalType,RoleID=RoleId[4],Operation= Operation[2]' #Log Analytics query for retrieving Role Assignment removal activities for the past 2 days $rmqr = 'AzureActivity | where TimeGenerated > ago(2d) | where CategoryValue =~ "Administrative" and OperationNameValue =~ "Microsoft.Authorization/roleAssignments/delete" and (ActivityStatusValue =~ "Success") | extend RoleDefinition = extractjson("$.properties.roleDefinitionId",tostring(Properties_d.responseBody),typeof(string)) | extend PrincipalId = extractjson("$.properties.principalId",tostring(Properties_d.responseBody),typeof(string)) | extend PrincipalType = extractjson("$.properties.principalType",tostring(Properties_d.responseBody),typeof(string)) | extend Scope = extractjson("$.properties.scope",tostring(Properties_d.responseBody),typeof(string)) | extend RoleId = split(RoleDefinition,"/") | extend InitiatedBy = Caller | extend Operation = split(OperationNameValue,"/") | project TimeGenerated,InitiatedBy,Scope,PrincipalId,PrincipalType,RoleID=RoleId[6],Operation= Operation[2]' #Please replace with appropriate workspace ID $addqueryResults = Invoke-AzOperationalInsightsQuery -WorkspaceId "<replace with Workspace ID>" -Query $addqr $rmqueryResults = Invoke-AzOperationalInsightsQuery -WorkspaceId "<replace with Workspace ID>" -Query $rmqr #Isolating Log Analytics query results $addqrs = $addqueryResults.Results $rmqrs = $rmqueryResults.Results #For each add query result find user/group name and role name to append into the CSV report foreach ($qr in $addqrs) { $rd = Get-AzRoleDefinition -Id $qr.RoleID if($qr.PrincipalType -eq 'User') { $prncpl = Get-AzADUser -ObjectId $qr.PrincipalId } elseif($qr.PrincipalType -eq 'Group'){ $prncpl = Get-AzADGroup -ObjectId $qr.PrincipalId } else{ $prncpl = Get-AzADServicePrincipal -ObjectId $qr.PrincipalId } $qr | Add-Member -MemberType NoteProperty -Name 'Role' -Value $rd.Name $qr | Add-Member -MemberType NoteProperty -Name 'PrincipalName' -Value $prncpl.DisplayName #Replace with appropriate path $qr | Export-Csv -Path "<Replace Path>\<FileName.csv>" -NoTypeInformation -Append } #For each remove query result find user/group name and role name to append into the CSV report foreach ($qr in $rmqrs) { $rd = Get-AzRoleDefinition -Id $qr.RoleID if($qr.PrincipalType -eq 'User') { $prncpl = Get-AzADUser -ObjectId $qr.PrincipalId } elseif($qr.PrincipalType -eq 'Group'){ $prncpl = Get-AzADGroup -ObjectId $qr.PrincipalId } else{ $prncpl = Get-AzADServicePrincipal -ObjectId $qr.PrincipalId } $qr | Add-Member -MemberType NoteProperty -Name 'Role' -Value $rd.Name $qr | Add-Member -MemberType NoteProperty -Name 'PrincipalName' -Value $prncpl.DisplayName #Replace with appropriate path $qr | Export-Csv -Path "<Replace Path>\<FileName.csv>" -NoTypeInformation -Append } # End of Script

Hope this helps!

Published on: August 07, 2024

Learn more

Azure Infrastructure Blog articles

Azure Security: Private Vs. Service Endpoints

When connecting securely to a platform service such as a key vault or an Azure storage account, Microsoft recommends using a private endpoint ...

7 hours ago

Give your Foundry Agent Custom Tools with MCP Servers on Azure Functions

Learn how to connect your MCP server hosted on Azure Functions to Microsoft Foundry agents. This post covers authentication options and setup ...

1 day ago

Azure Data Factory Tips for Reliable Microsoft Dynamics 365 CE and Dataverse Integrations

Reliable integrations between Microsoft Dynamics 365 Customer Engagement and external systems can become challenging. This is especially true ...

1 day ago

Scalable AI with Azure Cosmos DB: Tredence Intelligent Document Processing (IDP) | March 2026

Azure Cosmos DB enables scalable AI-driven document processing, addressing one of the biggest barriers to operational scale in today’s enterpr...

2 days ago

Banco Bradesco Scales Generative AI on Azure Red Hat OpenShift

3 days ago

Announcing the end of support for Node.js 20.x in the Azure SDK for JavaScript

After July 9, 2026, the Azure SDK for JavaScript will no longer support Node.js 20.x. Upgrade to an Active Node.js Long Term Support (LTS) ver...

3 days ago

MCP Apps on Azure Functions: Quickstart with TypeScript

Learn how to build and deploy MCP (Model Context Protocol) apps on Azure Functions using TypeScript. This guide covers MCP tools, resources, l...

3 days ago

Setting up Power BI Version Control with Azure Dev Ops

In this blog post is a way set up version control for Power BI semantic models (and reports) using the PBIP (Power BI Project) format, Azure D...

9 days ago

Azure Developer CLI (azd) – March 2026: Run and Debug AI Agents Locally, GitHub Copilot Integration, & Container App Jobs

Run, invoke, and monitor AI agents locally or in Microsoft Foundry with the new azd AI agent extension commands. Plus GitHub Copilot-powered p...

10 days ago

Writing Azure service-related unit tests with Docker using Spring Cloud Azure

This post shows how to write Azure service-related unit tests with Docker using Spring Cloud Azure. The post Writing Azure service-related uni...

10 days ago

Blog image

Azure Infrastructure Blog articles

Learn more

Azure Role Assignments Audit Report

Overview:

Pre-Requisites:

Solution:

Sample Output:

PowerShell Script:Please replace with appropriate workspace ID(line 32,33) and output CSV file path(line 57, 78). You can provide same values for both at multiple places. Based on the requirement and Log Analytics Retention the no. of days can also be edited(line 6,20)

Related posts

PowerShell Script:

Please replace with appropriate workspace ID(line 32,33) and output CSV file path(line 57, 78). You can provide same values for both at multiple places. Based on the requirement and Log Analytics Retention the no. of days can also be edited(line 6,20)