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

Powering Real-Time Messaging at Scale with Azure Cosmos DB

Microsoft Teams, Copilot, Azure Communication Services and many other product offerings from Microsoft, rely on a unified messaging platform t...

1 day ago

Azure SQL Cryptozoology AI Embeddings Lab Now Available!

Missed out on MS Build 2025? No worries! Our lab is now available for your exploration. Dive into a unique cryptozoology experience using Azur...

2 days ago

Vector Support Public Preview now extended to Azure SQL MI

We are thrilled to announce that Azure SQL Managed Instance now supports Vector type and functions in public preview.  This builds on the mome...

2 days ago

Building Multi-Agent AI Apps in Java with Spring AI and Azure Cosmos DB!

As AI-driven apps become more sophisticated, there’s an increasing need for them to mimic collaborative problem solving – like a t...

2 days ago

What runs ChatGPT, Sora, DeepSeek & Llama on Azure? (feat. Mark Russinovich)

Build and run your AI apps and agents at scale with Azure. Orchestrate multi-agent apps and high-scale inference solutions using open-source a...

3 days ago

Azure Cosmos DB TV – Everything New in Azure Cosmos DB from Microsoft Build 2025

Microsoft Build 2025 brought major innovations to Azure Cosmos DB, and in Episode 105 of Azure Cosmos DB TV, Principal Program Manager Mark Br...

3 days ago

Azure DevOps with GitHub Repositories – Your path to Agentic AI

GitHub Copilot has evolved beyond a coding assistant in the IDE into an agentic teammate – providing actionable feedback on pull requests, fix...

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