Enrich AADSTS Error Code Description
AADSTS Errorcodes KQL
Query
let ErrorCodes = externaldata(ErrorCode:string, Description:string)
[@"https://raw.githubusercontent.com/benscha/KQLAdvancedHunting/refs/heads/main/MISC/AADSTS_errorcodes.csv"]
with(format="csv", ignoreFirstRecord=true);
AADSignInEventsBeta
| extend ErrorCode = tostring(ErrorCode)
| lookup kind=leftouter ErrorCodes on ErrorCode
| project-rename ErrorDescription = DescriptionAbout this query
Enrich AADSTS Error Code Description
Query Information
Description
This KQL query enriches Azure AD sign-in events with human-readable AADSTS error descriptions by looking up error codes from an external CSV (https://github.com/benscha/KQLAdvancedHunting/blob/main/MISC/AADSTS_errorcodes.csv) file.
Author <Optional>
- Name: Benjamin Zulliger
- Github: https://github.com/benscha/KQLAdvancedHunting
- LinkedIn: https://www.linkedin.com/in/benjamin-zulliger/
Defender XDR
Explanation
This KQL query is designed to enhance Azure Active Directory (AAD) sign-in event data by adding human-readable descriptions for error codes. Here's a simple breakdown of what the query does:
-
Load Error Codes: It starts by loading an external CSV file containing AADSTS error codes and their corresponding descriptions. This file is hosted on GitHub.
-
Convert Error Code to String: It ensures that the error codes in the AAD sign-in events are treated as strings.
-
Join Data: The query performs a left outer join between the AAD sign-in events and the error codes from the CSV file. This means it matches each sign-in event's error code with its description from the CSV file, if available.
-
Rename Column: Finally, it renames the column containing the error descriptions to "ErrorDescription" for clarity.
In summary, this query enriches AAD sign-in event data by adding readable descriptions for error codes, making it easier to understand the nature of any errors that occur during sign-ins.
Details
Benjamin Zulliger
Released: December 30, 2025
Tables
Keywords
Operators