AADSTS Errorcodes KQL

# *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 ```KQL 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 = Description ```

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.

KQL Search

AADSTS Errorcodes KQL

Query

Explanation

Details

Actions