KQL Search

Query Details

HomeAI ToolsDevice Query

Auth Methods Token Bounded Cae

Query

union SigninLogs, AADNonInteractiveUserSignInLogs
    | where TimeGenerated >ago(90d)
    | where isnotempty(SessionId)
    | where ResultType == "0" and HomeTenantId == ResourceTenantId
    | extend MfaDetail = coalesce(todynamic(MfaDetail_string), MfaDetail_dynamic)
    | extend DeviceDetail = coalesce(todynamic(DeviceDetail_string), DeviceDetail_dynamic)
    | extend TokenProtectionStatusDetails = coalesce(todynamic(TokenProtectionStatusDetails_string), TokenProtectionStatusDetails_dynamic)
    | extend SignInSessionStatus = tostring(parse_json(TokenProtectionStatusDetails)["signInSessionStatus"])
    | extend AuthProcessDetails = replace_string(AuthenticationProcessingDetails, " " , "")
    | extend AuthProcessDetails = replace_string(AuthProcessDetails, "\r\n" , "")
    | parse AuthProcessDetails with * "IsCAEToken\",\"value\":\"" IsCaeToken "\"" *
    | mv-expand parse_json(AuthenticationDetails)
    | project AuthMethod = tostring(parse_json(AuthenticationDetails).authenticationMethod), SessionId, UniqueTokenIdentifier, UserPrincipalName, AppDisplayName, SignInSessionStatus, IsCaeToken, CreatedDateTime, DeviceDetail
    | join kind=leftouter (
        union SigninLogs, AADNonInteractiveUserSignInLogs
            | where TimeGenerated >ago(91d)
            | summarize arg_min(CreatedDateTime, *) by SessionId
            | mv-expand parse_json(AuthenticationDetails)
            | extend AuthMethodStepDetail = tostring(parse_json(AuthenticationDetails).authenticationStepResultDetail)
            | extend AuthMethod = iff(isnotempty(tostring(parse_json(AuthenticationDetails).authenticationMethod)), tostring(parse_json(AuthenticationDetails).authenticationMethod), "Unknown")
            | project SessionId, AuthMethod, AuthMethodStepDetail, parse_json(AuthenticationDetails), InitialSignIn = CreatedDateTime, InitialRequestId = OriginalRequestId
        ) on SessionId
    | project-rename InitialAuthMethod = AuthMethod1
    | where UserPrincipalName != @"[email protected]"
    | extend DeviceName = tostring(parse_json(DeviceDetail)["displayName"])
    | extend DeviceTrustType = tostring(parse_json(DeviceDetail)["trustType"])
    | extend DeviceCompliant = tostring(parse_json(DeviceDetail)["isCompliant"])
    | extend DeviceDetails = bag_pack_columns(DeviceName, DeviceTrustType, DeviceCompliant)
    | extend TokenDetails = bag_pack_columns(UniqueTokenIdentifier, IsCaeToken, SignInSessionStatus, DeviceCompliant)
    | summarize SessionStartTime = min(CreatedDateTime), SessionEndTime = max(CreatedDateTime), NumberOfTokens=countif(isnotempty(UniqueTokenIdentifier)), NumberOfTokensWithComplianceState=countif(DeviceCompliant == 'true'), TokenDetails = make_list(TokenDetails), NumberOfBoundedTokens=countif(SignInSessionStatus == 'bound'), NumberOfCaeTokens=countif(IsCaeToken == 'True') by SessionId, InitialAuthMethod, AuthMethodStepDetail, tostring(parse_json(AuthenticationDetails)), InitialSignIn, InitialRequestId, UserPrincipalName, DeviceName
    | project InitialSignIn, InitialAuthMethod, UserPrincipalName, DeviceName, SessionStartTime, SessionEndTime, SessionId, NumberOfTokens, NumberOfBoundedTokens, NumberOfCaeTokens, NumberOfTokensWithComplianceState, TokenDetails
    // Summarize by users
    //| extend Session = bag_pack_columns(SessionId, TokenDetails, NumberOfBoundedTokens, NumberOfCaeTokens, NumberOfTokens)
    //| summarize InitialAuthMethod = make_set(InitialAuthMethod), Sessions = make_set(Session), NumberOfSessions = dcount(NumberOfTokens) by UserPrincipalName

Explanation

This query is designed to analyze and summarize user sign-in sessions from two log sources: SigninLogs and AADNonInteractiveUserSignInLogs. Here's a simplified breakdown of what the query does:

  1. Data Union and Filtering:

    • Combines data from SigninLogs and AADNonInteractiveUserSignInLogs.
    • Filters the data to include only records from the last 90 days where the SessionId is not empty, the result type indicates success (ResultType == "0"), and the home tenant matches the resource tenant.

  2. Data Transformation:

    • Extracts and processes details about multi-factor authentication (MFA), device, and token protection status.
    • Cleans up and parses the authentication processing details to extract specific information like whether the token is a CAE token.

  3. Session Details Extraction:

    • Expands authentication details to extract the authentication method used.
    • Projects relevant fields such as AuthMethod, SessionId, UserPrincipalName, AppDisplayName, and device details.

  4. Joining with Historical Data:

    • Joins the current session data with historical data (older than 91 days) to get the initial authentication method and other details for each session.

  5. Device and Token Details:

    • Extracts and organizes device details like device name, trust type, and compliance status.
    • Compiles token details including unique identifiers and compliance status.

  6. Session Summarization:

    • Summarizes sessions by calculating the start and end times, counting tokens, and identifying tokens with specific attributes (e.g., compliance state, bounded status, CAE tokens).
    • Groups the summarized data by session ID and other key attributes.

  7. Final Projection:

    • Projects a final set of fields for each session, including initial sign-in time, authentication method, user principal name, device name, session start and end times, and token details.

The commented-out section at the end suggests an additional summarization step by user, which would aggregate sessions and authentication methods for each user.

Details

Thomas Naunheim profile picture

Thomas Naunheim

Released: June 11, 2025

Tables

SigninLogsAADNonInteractiveUserSignInLogs

Keywords

SigninLogsAADNonInteractiveUserSignInLogsSessionIdMfaDetailDeviceDetailTokenProtectionStatusDetailsSignInSessionStatusAuthProcessDetailsAuthenticationDetailsAuthMethodUniqueTokenIdentifierUserPrincipalNameAppDisplayNameCreatedDateTimeDeviceNameDeviceTrustTypeDeviceCompliantTokenDetailsInitialAuthMethodAuthMethodStepDetailInitialSignInInitialRequestId

Operators

unionwhereagoisnotempty==extendcoalescetodynamictostringparse_jsonreplace_stringparsemv-expandprojectjoinkind=leftoutersummarizearg_miniffproject-rename!=bag_pack_columnscountifmake_listdcountmake_set

Actions