SOLVED

Get account name if UserPrincipalName is UserId

Copper Contributor

Sometimes the signin events in the various AAD signin logs contain the UserId as the UserPrincipalName. In some spot checks it looks to me that this often happens when the signin comes from a Teams app on an iOS device...
This requires the reader of the output to lookup the UserId e.g. in the Entra ID portal, to figure out which user this was.

Is there a way to do this dynamically in the KQL query so that I could add the name to the output?

2 Replies
best response confirmed by Ville Koch (Copper Contributor)
Solution

@Ville Koch 

Do you have AADNonInteractiveUserSignInLogs as well as SigninLogs, if so that often holds the details?  This is just an example.

 

SigninLogs
| where UserPrincipalName == UserId
| join 
    (
    AADNonInteractiveUserSignInLogs
    | project UserId, UserDisplayName, UserPrincipalName
    ) on UserId
| project UserId, UserId1, UserPrincipalName, UserPrincipalName1


e.g.

Clive_Watson_0-1713775804907.png

 

or the similar if you have IdentityInfo

SigninLogs
| where UserPrincipalName == UserId
| join 
    (
    IdentityInfo
    | project AccountUPN, AccountObjectId
    ) on $left.UserId == $right.AccountObjectId
| project UserId, UserPrincipalName,  AccountUPN



Hi Clive, thank you, that Join on AADNonInteractiveUserSignInLogs helped! 🙂
1 best response

Accepted Solutions
best response confirmed by Ville Koch (Copper Contributor)
Solution

@Ville Koch 

Do you have AADNonInteractiveUserSignInLogs as well as SigninLogs, if so that often holds the details?  This is just an example.

 

SigninLogs
| where UserPrincipalName == UserId
| join 
    (
    AADNonInteractiveUserSignInLogs
    | project UserId, UserDisplayName, UserPrincipalName
    ) on UserId
| project UserId, UserId1, UserPrincipalName, UserPrincipalName1


e.g.

Clive_Watson_0-1713775804907.png

 

or the similar if you have IdentityInfo

SigninLogs
| where UserPrincipalName == UserId
| join 
    (
    IdentityInfo
    | project AccountUPN, AccountObjectId
    ) on $left.UserId == $right.AccountObjectId
| project UserId, UserPrincipalName,  AccountUPN



View solution in original post