KQL Search

Query Details

HomeAI ToolsDevice Query

Sign In Risk Analysis

Query

let ErrorLookup = externaldata(ErrorCode: long, ErrorString: string, Description: string)
[@"https://raw.githubusercontent.com/mr-r3b00t/entra_sign_in_codes/refs/heads/main/errocodes.csv"]
with (format="csv", ignoreFirstRecord=true);
EntraIdSignInEvents
| where Timestamp > ago(30d)
| where ErrorCode != 0
| where RiskLevelAggregated > 1  // Filters for low (10), medium (50), or high (100) aggregated risk
| join kind=leftouter (ErrorLookup) on $left.ErrorCode == $right.ErrorCode
| extend ResultTypeDescription = iff(isnull(Description),
    strcat("Other (", tostring(ErrorCode), ") - lookup code at https://aka.ms/AADsigninsErrorCodes"),
    strcat(ErrorString, " - ", Description))
| extend RiskLevel = case(
    RiskLevelAggregated == 100, "High",
    RiskLevelAggregated == 50,  "Medium",
    RiskLevelAggregated == 10,  "Low",
    "None/Unknown")
| project Timestamp,
          AccountUpn,
          IPAddress,
          ClientAppUsed,
          City,
          State,
          Country,
          RiskLevelAggregated,
          RiskLevel,
          ErrorCode,
          ResultTypeDescription,
          Browser,
          DeviceTrustType,
          DeviceName,
          EndpointCall,
          EntraIdDeviceId,
          ConditionalAccessStatus,
          AuthenticationRequirement,
          RiskEventTypes,
          RiskState
| sort by Timestamp desc

Explanation

This KQL query is designed to analyze sign-in events from the EntraIdSignInEvents table over the past 30 days. Here's a simplified breakdown of what the query does:

  1. Error Lookup Table: It first defines an external data source called ErrorLookup, which contains error codes, error strings, and descriptions. This data is fetched from a CSV file hosted on GitHub.

  2. Filter Sign-In Events: The query filters the sign-in events to include only those that:

    • Occurred within the last 30 days.
    • Have a non-zero error code, indicating an error occurred.
    • Have an aggregated risk level greater than 1, which corresponds to low, medium, or high risk.

  3. Join with Error Lookup: It performs a left outer join between the filtered sign-in events and the ErrorLookup table based on the error code. This enriches the sign-in events with descriptive error information.

  4. Result Type Description: It creates a new field, ResultTypeDescription, which provides a detailed description of the error. If no description is found in the lookup, it provides a link to look up the error code manually.

  5. Risk Level Labeling: It assigns a human-readable label ("High", "Medium", "Low") to the RiskLevelAggregated values for easier interpretation.

  6. Select and Sort Data: The query selects specific fields to display, such as timestamp, user account, IP address, client app used, location details, risk levels, error information, and device details. The results are sorted by the timestamp in descending order, showing the most recent events first.

Overall, this query helps in identifying and understanding recent sign-in events with errors, focusing on those with significant risk levels, and provides additional context by linking error codes to descriptions.

Details

Daniel Card profile picture

Daniel Card

Released: January 24, 2026

Tables

EntraIdSignInEvents

Keywords

EntraIdSignInEventsErrorLookupTimestampAccountUpnIPAddressClientAppUsedCityStateCountryRiskLevelAggregatedRiskLevelErrorCodeResultTypeDescriptionBrowserDeviceTrustTypeDeviceNameEndpointCallEntraIdDeviceIdConditionalAccessStatusAuthenticationRequirementRiskEventTypesRiskState

Operators

letexternaldatawithwhereago!=>joinkind=leftouteron==extendiffisnullstrcattostringcaseprojectsort bydesc

Actions