Get account name if UserPrincipalName is UserId

[Ville Koch]

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 · Accepted 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.

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

[#3]

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