AzureActivity Table PrincipalId + UserPrincipalName from another Table in KQL

Question

Good morning all

I am following Microsoft's official documentation for adding an alert rule that fires when a user adds another user or service principal to a privileged role assignment (e.g. Owner, Contributor). I have achieved this by streaming logs to a Log Analytics Workspace and using a Kusto query on our Azure subscription referencing the AzureActivity table.

However, I have a requirement to display the UserPrincipalName or some kind of friendly name of the PrincipalId of the user/service principal that gets added to that privileged role assignment. I can see the principalId of the user that gets added, and I know that principalId matches the objectId of the user - how can I use this information to get the user's friendly name / UserPrincipalName and display it in the alert that fires?Thanks in advance!

Answer 1

Thanks for posting your question in the Microsoft Q&A forum.

I hope this script helps you:

Get-AzureADUser -ObjectID "username@domain" | Select-Object DisplayName, UserPrincipalName

If you want to retrieve for all user you can use this script:

$tid = 'xxxxx-xxx-xxx-xxx-xxxx'  # Replace with your tenant ID
Connect-AzureAD -TenantId $tid
$allUsers = Get-AzureADUser -All $true
$userInfo = $allUsers | Select-Object DisplayName, AccountEnabled, UserPrincipalName
$userInfo | Export-Csv ./AADusers.csv -NoTypeInformation -Append

** Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful **