Other Entra ID / Azure AD SignIn errors

The challenge

Most of us analyzing Azure AD SignIn logs have been there. You come across a failed sign-in, but the ResultDescription is not really helpful, but only shows “Other”.

When using the Entra ID portal UI most of those error codes will perfectly translate to a more helpful error message.

So how can you have the same experience when using KQL?

First things first, let’s analyze which unresolved error types are in your environment.

union isfuzzy=true SigninLogs, AADNonInteractiveUserSignInLogs
| where ResultType != 0 and ResultDescription == "Other"
| summarize by ResultType

Based on this data you can do two things:

1. Lookup the error code in the UI
2. Use the official reference Azure AD Authentication and authorization error codes to lookup the error code
3. Lookup the error code using the sign-in error lookup tool

Option #3 is the best of the three, because I don’t want to lookup hundreds of error codes manually and the official website does not cover everything you are looking for.

The way to the solution

Since the error code website by Microsoft has a super simple interface which is easy to parse, I created a small script that does the heavy lifting for me. Since I also didn’t want to miss any error codes I might not have in my initial list I also don’t just check the exact error message id, but also 20 error codes up and down the original error id. This way I checked a total of 2143 possible error messages. Of course some are invalid and others might already be resolved in the Sign-In logs. Of those 2143 checked error codes I was able to get 764 unique error messages which I saved as a JSON file.

And voila, now I can lookup the missing values right in your kusto query using the externaldata function.

Since I want to keep the original format of the Sign-In logs I added a another line to replace the original ResultDescription if the value is “Other” and the lookup table contains a better description.

let ResolvedErrorCodes = externaldata(code: string, Message: string)['https://raw.githubusercontent.com/f-bader/EntraID-ErrorCodes/main/EntraIDErrorCodes.json'] with (format='multijson');
UnifiedSignInLogs
| where ResultType != 0
| lookup ResolvedErrorCodes on $left.ResultType == $right.code
| extend ResultDescription = iff(ResultDescription == "Other",iff(isempty(Message),"Other",Message),ResultDescription)
| project-away Message
| project-reorder TimeGenerated, ResultType, ResultDescription

The solution

But always querying external data might not be the most performant way of doing this. A Sentinel watchlist should be the better solution for this.

UnifiedSignInLogs
| where ResultType != 0
| lookup _GetWatchlist('EntraIDErrorCodes') on $left.ResultType == $right.SearchKey
| extend ResultDescription = iff(ResultDescription == "Other", iff(isempty(Message), "Other", Message), ResultDescription)
| project-away Message, *1
| project-reorder TimeGenerated, ResultType, ResultDescription

For your convenience, I created a ARM template you can use to deploy a watchlist.

I uploaded all this data to a new GitHub repository where you can consume it as JSON, CSV or find the ARM template to deploy the watchlist directly.

Entra ID - Azure AD Authentication and authorization error codes

References

• externaldata operator
• SigninLogs reference
• Sign-in logs in Azure Active Directory
• Sign-in error lookup tool
• Use watchlists in Microsoft Sentinel