Loading...

Azure Role Assignments Audit Report

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 

SarthakAgarwal_1-1723036783571.png

 

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

SarthakAgarwal_2-1723036783572.png

 

  • 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.

 

SarthakAgarwal_3-1723036783576.png

 

Sample Output:

 

SarthakAgarwal_4-1723036783577.png

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:

Learn more
Azure Infrastructure Blog articles
Azure Infrastructure Blog articles

Azure Infrastructure Blog articles

Share post:

Related posts

How Azure CLI handles your tokens and what you might be ignoring

Running az login feels like magic. A browser pops up, you pick an account, and from then on, everything just works. No more passwords, no more...

10 hours ago

Boost your Azure Cosmos DB Efficiency with Azure Advisor Insights

Azure Cosmos DB is Microsoft’s globally distributed, multi-model database service, trusted for mission-critical workloads that demand high ava...

2 days ago

Microsoft Azure Fundamentals #5: Complex Error Handling Patterns for High-Volume Microsoft Dataverse Integrations in Azure

🚀 1. Problem Context When integrating Microsoft Dataverse with Azure services (e.g., Azure Service Bus, Azure Functions, Logic Apps, Azure SQ...

2 days ago

Using the Secret Management PowerShell Module with Azure Key Vault and Azure Automation

Automation account credential resources are the easiest way to manage credentials for Azure Automation runbooks. The Secret Management module ...

3 days ago

Microsoft Azure Fundamentals #4: Azure Service Bus Topics and Subscriptions for multi-system CRM workflows in Microsoft Dataverse / Dynamics 365

🚀 1. Scenario Overview In modern enterprise environments, a single business event in Microsoft Dataverse (CRM) can trigger workflows across m...

3 days ago

Easily connect AI workloads to Azure Blob Storage with adlfs

Microsoft works with the fsspec open-source community to enhance adlfs. This update delivers faster file operations and improved reliability f...

4 days ago

Microsoft Azure Fundamentals #3: Maximizing Event-Driven Architecture in Microsoft Power Platform

🧩 1. Overview Event-driven architecture (EDA) transforms how systems communicate.Instead of traditional request–response or batch integration...

4 days ago

Azure Developer CLI (azd) – October 2025

This post announces the October release of the Azure Developer CLI (`azd`). The post Azure Developer CLI (azd) – October 2025 appeared f...

5 days ago

Microsoft Azure Fundamentals #2: Designing Real-Time Bi-Directional Sync Between Dataverse and Azure SQL for Multi-Region Deployments

Here’s a detailed technical breakdown of designing a real-time bi-directional sync between Dataverse and Azure SQL for multi-region deployment...

5 days ago

Azure DevOps local MCP Server is generally available

Today we are excited to take our local MCP Server for Azure DevOps out of preview 🥳. Since the initial preview announcement, we’ve work...

6 days ago
Stay up to date with latest Microsoft Dynamics 365 and Power Platform news!
* Yes, I agree to the privacy policy