KQL Search

Query Details

HomeAI ToolsDevice Query

Multiple Unusual User Agent From Registered Device Avoiding Conditional Access

Query

let query_frequency = 1h;
let query_period = 14d;
let _SuccessResultTypes = toscalar(
    _GetWatchlist("ResultType-SignInLogsErrorCodes")
    | where Notes has_all ("[Success]", "[Complete]") and isnotempty(ResultDescription)
    | summarize make_list(ResultType)
);
union SigninLogs, AADNonInteractiveUserSignInLogs
| where TimeGenerated > ago(query_period)
| where ResultType in (_SuccessResultTypes) and (DeviceDetail_dynamic has "trustType" or DeviceDetail_string has "trustType") and AADTenantId == ResourceTenantId
| extend DeviceDetail = coalesce(DeviceDetail_dynamic, todynamic(DeviceDetail_string))
| where isnotempty(DeviceDetail["operatingSystem"]) and isnotempty(DeviceDetail["browser"])
| extend
    OperatingSystemFamily = extract(@"^(\D+?)(\s*[Dot\.0-9]+)?$", 1, tostring(DeviceDetail["operatingSystem"])),
    BrowserFamily = extract(@"^(\D+?)(\s*[v\.0-9]+)?$", 1, tostring(DeviceDetail["browser"]))
| summarize arg_min(TimeGenerated, *) by OperatingSystemFamily, BrowserFamily
| where TimeGenerated > ago(query_frequency)
| where ConditionalAccessStatus == "notApplied"
| project
    TimeGenerated,
    CreatedDateTime,
    Type,
    UserDisplayName,
    UserPrincipalName,
    UserId,
    AlternateSignInName,
    SignInIdentifier,
    UserType,
    IPAddress,
    AutonomousSystemNumber,
    Location,
    NetworkLocationDetails,
    ResultType,
    ResultSignature,
    ResultDescription,
    ClientAppUsed,
    AppDisplayName,
    ResourceDisplayName,
    DeviceDetail,
    UserAgent,
    Status = coalesce(tostring(Status_dynamic), Status_string),
    MfaDetail = coalesce(tostring(MfaDetail_dynamic), MfaDetail_string),
    AuthenticationContextClassReferences,
    AuthenticationDetails,
    AuthenticationProcessingDetails,
    AuthenticationProtocol,
    AuthenticationRequirement,
    AuthenticationRequirementPolicies,
    SessionLifetimePolicies,
    TokenIssuerType,
    IncomingTokenType,
    TokenProtectionStatusDetails = coalesce(tostring(TokenProtectionStatusDetails_dynamic), TokenProtectionStatusDetails_string),
    ConditionalAccessStatus,
    // ConditionalAccessPolicies = coalesce(tostring(ConditionalAccessPolicies_dynamic), ConditionalAccessPolicies_string),
    SignInLogs_RiskDetail = RiskDetail,
    RiskEventTypes_V2,
    RiskLevelAggregated,
    RiskLevelDuringSignIn,
    SignInLogs_RiskState = RiskState,
    HomeTenantId,
    ResourceTenantId,
    CrossTenantAccessType,
    AppId,
    ResourceIdentity,
    UniqueTokenIdentifier,
    SessionId,
    OriginalRequestId,
    CorrelationId

Explanation

This KQL (Kusto Query Language) query is designed to analyze sign-in logs from Azure Active Directory, focusing on successful sign-ins over a specific period. Here's a simplified breakdown of what the query does:

  1. Define Time Periods:

    • query_frequency is set to 1 hour, and query_period is set to 14 days. These define the time frames for filtering the data.

  2. Identify Success Result Types:

    • It retrieves a list of result types from a watchlist named "ResultType-SignInLogsErrorCodes" that are marked as successful or complete.

  3. Combine Data Sources:

    • The query combines data from SigninLogs and AADNonInteractiveUserSignInLogs.

  4. Filter Data:

    • It filters the logs to include only those generated within the last 14 days (query_period) and matches the successful result types.
    • It further filters logs where device details contain "trustType" and the tenant IDs match.

  5. Extract Device Information:

    • It extracts and processes device details to identify the operating system and browser families.

  6. Summarize Data:

    • The query summarizes the data to find the earliest log entry for each combination of operating system and browser family.

  7. Additional Filtering:

    • It filters the summarized data to include only entries from the last hour (query_frequency) where conditional access was not applied.

  8. Select and Project Data:

    • Finally, it selects and projects a wide range of fields from the logs, including user details, device details, authentication details, and risk information.

In essence, this query is used to analyze recent successful sign-ins, focusing on device and browser information, and ensuring conditional access policies were not applied. It provides detailed insights into the sign-in activities over the specified period.

Details

Jose Sebastián Canós profile picture

Jose Sebastián Canós

Released: April 20, 2026

Tables

SigninLogsAADNonInteractiveUserSignInLogs

Keywords

DevicesUsersAuthenticationNetworkLocationRiskSessionTokenLogs

Operators

lettoscalar_GetWatchlisthas_allisnotemptysummarizemake_listunionwhereinandor==extendcoalescetodynamicextracttostringarg_minbyproject

Actions