AzureActivity Table PrincipalId + UserPrincipalName from another Table in KQL

Daniel Gareth Davies 0

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!

1 answer

Vahid Ghafarpour 22,445 Reputation points

2024-07-18T17:53:39.3566667+00:00
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 **
Please sign in to rate this answer.

1 person found this answer helpful.
Daniel Gareth Davies 0 Reputation points

2024-07-18T18:00:34.48+00:00

Hello Vahid

I already know how to get the information in PowerShell using the AzureAD module or Graph API. I need to get the same information in Kusto by joining another table with the AzureActivity table.

SadiqhAhmed-MSFT 48,351 Reputation points Microsoft Employee

2024-09-05T14:51:54.8533333+00:00

@Daniel Gareth Davies To display the UserPrincipalName or a friendly name of the PrincipalId in your alert, you can join the AzureActivity table with the AzureADUsers table in your Kusto query. This will allow you to map the PrincipalId to the corresponding UserPrincipalName. Here's an example of how you can achieve this:

In this query:

The AzureActivity table is filtered to include only the operations where a member is added to a role.

The PrincipalId is extracted from the Properties field.

The AzureADUsers table is joined with the AzureActivity table on the PrincipalId and ObjectId.

The UserPrincipalName is then projected along with other relevant fields.

This will allow you to display the UserPrincipalName in your alert when a user or service principal is added to a privileged role assignment.

Hope this helps. If you need further assistance or have any other questions, please reply to this post.

If the response helped, do "Accept Answer" and up-vote it

Daniel Gareth Davies 0 Reputation points

2024-09-05T15:48:34.91+00:00

Hi @SadiqhAhmed-MSFT

Thank you for the reply. One problem - I do not have the AzureADUsers table available to me in my Azure tenant. Is there some log I haven't got streamed in to my Log Analytics Workspace that contains the table? I couldn't find any mention of the AzureADUsers table online or in my Azure tenant.

Current diagnostic settings:

Thanks in advance.

SadiqhAhmed-MSFT 48,351 Reputation points Microsoft Employee

2024-09-06T14:31:59.1566667+00:00

@Daniel Gareth Davies AzureADUsers table is not a standard log that is streamed into Log Analytics Workspace by default.

To achieve your goal of displaying the UserPrincipalName or a friendly name of the PrincipalId in your alert, you can use the Azure Active Directory (AAD) audit logs. These logs contain information about user activities and can be streamed into your Log Analytics Workspace. Here's how you can do it:

Make that Azure Active Directory audit logs are enabled and being sent to your Log Analytics Workspace. You can do this by navigating to the Azure Active Directory in the Azure portal, selecting "Audit logs," and configuring the diagnostic settings to send the logs to your Log Analytics Workspace.

Once the logs are being streamed, you can query the AAD audit logs to get the UserPrincipalName. Here's an example Kusto query:

AuditLogs | where ActivityDisplayName == "Add member to role" | extend PrincipalId = tostring(parse_json(TargetResources).id) | join kind=inner ( IdentityInfo | project UserPrincipalName, Id ) on $left.PrincipalId == $right.Id | project TimeGenerated, ActivityDisplayName, UserPrincipalName, Result, ResourceGroup, SubscriptionId

In this query:

The AuditLogs table is filtered to include only the operations where a member is added to a role.

The PrincipalId is extracted from the TargetResources field.

The IdentityInfo table is joined with the AuditLogs table on the PrincipalId and Id.

The UserPrincipalName is then projected along with other relevant fields.

This approach should help you display the UserPrincipalName in your alert when a user or service principal is added to a privileged role assignment table is not a standard log that is streamed into Log Analytics Workspace by default.

Daniel Gareth Davies 0 Reputation points

2024-09-10T21:21:14.0033333+00:00

Hi Sadiqh

Sorry, perhaps I am confused here, but I also do not have the IdentityInfo table.

SadiqhAhmed-MSFT 48,351 Reputation points Microsoft Employee

2024-09-11T07:51:28.63+00:00

@Daniel Gareth Davies IMO, this can be better handled through Azure technical support channel. Please reach out to Azure technical support team by raising a ticket. The ticket enables you to work closely with the support engineers and get a quick resolution to your issue.

Here is the link - https://docs.microsoft.com/en-us/azure/azure-portal/supportability/how-to-create-azure-support-request to create support case.

Fahim Thanawala 0 Reputation points

2025-02-19T16:17:10.5666667+00:00

Hello All,

I am also looking for the same and stuck at same problem.

Regards,

Fahim

SadiqhAhmed-MSFT 48,351 Reputation points Microsoft Employee

2025-02-20T14:06:49.65+00:00

@Fahim Thanawala Sorry to hear that you are experiencing similar issues. But we did not get any update from Daniel if he had reached out to support for further assistance. If this issue is impacting your production environment, I would suggest contacting Azure technical support team by raising a support ticket for 1:1 support.

Daniel Gareth Davies 0 Reputation points

2025-02-20T16:31:34.15+00:00

Hello, I did reach out to a Support Engineer in the Azure: Monitoring and Automation department at Microsoft Support. He researched the question and came back with the following:

After doing some additional research, we found a possible way to add the Display Name of a PrincipalID to the results the query, to then display it on the alert message as a dimension. First, you need to enable User and Entity Behavior Analytics (UEBA) in Microsoft Sentinel. This will feed the "IdentityInfo" table in the Log Analytics Workspace associated to Microsoft Sentinel. You can find more information in the link below:

Once this is done, you can then create a query that pulls data from that Log Analytics Workspace to cross-reference results from "AzureActivity" and "IdentityInfo", allowing you to compare "PrincipalID" values between them and pull the user's data.

I was not however able to get this to work, as we do not use Microsoft Sentinel in our environment, so we closed the request out as unresolved.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

AzureActivity Table PrincipalId + UserPrincipalName from another Table in KQL

1 answer

Your answer