KQL Search

Query Details

HomeAI ToolsDevice Query

Consent Fix Hunting Confidence On Token And Network Signals

Query

let Lookback = 2d;
let AffectedApps = dynamic([
    "Microsoft Azure CLI",
    "Microsoft Azure PowerShell",
    "Microsoft Graph Command Line Tools"
    ]);
let BrowserProcesses = dynamic([
    "msedge.exe",
    "chrome.exe",
    "iexplore.exe",
    "firefox.exe",
    "com.apple.Safari",
    "com.microsoft.edgemac",
    "com.microsoft.edgemac.helper"
    ]);
union SigninLogs, AADNonInteractiveUserSignInLogs
| where CreatedDateTime > ago(Lookback)
| where AppDisplayName in~ (AffectedApps)
| extend TokenProtectionStatus = iff(isempty(TokenProtectionStatusDetails_dynamic), todynamic(TokenProtectionStatusDetails_string), TokenProtectionStatusDetails_dynamic)
| extend
    SignInSessionStatusCode = TokenProtectionStatus.signInSessionStatus
| project-rename UniqueTokenId = UniqueTokenIdentifier
| where ResultType == "0"
| join kind = leftouter (NetworkAccessTraffic
    | project UniqueTokenId, InitiatingProcessName)
    on UniqueTokenId
| project
    InitialSignInTime = CreatedDateTime,
    AppDisplayName,
    InitialResource = ResourceDisplayName,
    UserPrincipalName,
    InitiatingProcessName,
    SessionId,
    InitialSignInUti = UniqueTokenId,
    InitialIpAddress = IPAddress,
    InitialAsn = AutonomousSystemNumber,
    InitialSignInSessionStatus = tostring(SignInSessionStatusCode),
    InitialIsGsa = IsThroughGlobalSecureAccess,
    InitialTokenType = IncomingTokenType
| join kind = inner (
    union SigninLogs, AADNonInteractiveUserSignInLogs
    | where Lookback == (Lookback)
    | where AppDisplayName in~ (AffectedApps)
    | where ResultType == "0"
    | extend TokenProtectionStatus = iff(isempty(TokenProtectionStatusDetails_dynamic), todynamic(TokenProtectionStatusDetails_string), TokenProtectionStatusDetails_dynamic)
    | extend
        TokenProtection = TokenProtectionStatus.signInSessionStatus,
        SignInSessionStatusCode = TokenProtectionStatus.signInSessionStatus
    | project
        SignInTime = CreatedDateTime,
        ResourceDisplayName,
        SignInSessionStatus = tostring(SignInSessionStatusCode),
        SessionId,
        UniqueTokenIdentifier,
        IPAddress,
        Asn = AutonomousSystemNumber,
        IsGsa = IsThroughGlobalSecureAccess,
        TokenType = IncomingTokenType
    )
    on SessionId
| where SignInTime > InitialSignInTime
| project-away SessionId1
| project-reorder
    InitialSignInTime,
    SignInTime,
    SessionId,
    UserPrincipalName,
    AppDisplayName,
    InitialResource,
    ResourceDisplayName,
    InitialSignInSessionStatus,
    SignInSessionStatus,
    InitialIpAddress,
    IPAddress,
    InitialAsn,
    Asn,
    InitialIsGsa,
    IsGsa
| extend WithinAuthCodeExpirationTime = SignInTime - InitialSignInTime > 12m
| extend ConfidenceScore = case(
                               InitiatingProcessName in~ (BrowserProcesses) and InitialSignInSessionStatus != SignInSessionStatus,
                               "Very high",
                               (InitialSignInSessionStatus != SignInSessionStatus and InitialAsn != Asn) or (InitialIsGsa != IsGsa and SignInSessionStatus != "bound"),
                               "High",
                               (InitialAsn != Asn and WithinAuthCodeExpirationTime == true) and (InitialSignInSessionStatus != SignInSessionStatus and InitialTokenType != TokenType),
                               "Medium",
                                (SignInTime - InitialSignInTime > 5s) and (SignInTime - InitialSignInTime < 720s),
                                "Low",
                               "None"
                           )
| extend UseCaseDescription = case(
                               InitiatingProcessName in~ (BrowserProcesses) and InitialSignInSessionStatus != SignInSessionStatus,
                               "Browser Auth Code Flow used instead of WAM based on GSA logs",
                               (InitialSignInSessionStatus != SignInSessionStatus and InitialAsn != Asn),
                               "Auth Code Flow used instead of WAM and ASN changed between sign-ins",
                               (InitialIsGsa != IsGsa and SignInSessionStatus != "bound"),
                               "Change from compliant network (GSA) during authentication and initial token unbound",
                               (InitialAsn != Asn and WithinAuthCodeExpirationTime == true) and (InitialSignInSessionStatus != SignInSessionStatus and InitialTokenType != TokenType),
                               "ASN changed during authentication, seconds authentication within 10 minutes, token bound status changed",
                               ""
                           )
| extend SignIn = bag_pack_columns(SignInTime, SignInSessionStatus, ResourceDisplayName, Asn, IPAddress, IsGsa, UniqueTokenIdentifier, TokenType)
| summarize SignIns = make_set(SignIn)
    by
    InitialSignInTime,
    UserPrincipalName,
    AppDisplayName,
    InitialSignInSessionStatus,
    SessionId,
    ConfidenceScore,
    UseCaseDescription,
    InitialIsGsa,
    Asn,
    IPAddress,
    InitialTokenType,
    InitiatingProcessName
| sort by InitialSignInTime

Explanation

This KQL query is designed to analyze sign-in activities for specific applications over the past two days. Here's a simplified breakdown of what the query does:

  1. Define Variables:

    • Lookback: Sets the time frame to the last 2 days.
    • AffectedApps: Lists the applications of interest, specifically Microsoft Azure CLI, Microsoft Azure PowerShell, and Microsoft Graph Command Line Tools.
    • BrowserProcesses: Lists common browser processes like Edge, Chrome, Firefox, etc.

  2. Data Collection:

    • Combines data from SigninLogs and AADNonInteractiveUserSignInLogs tables.
    • Filters records to include only those created within the last 2 days and related to the specified applications.
    • Extracts and processes token protection status details.

  3. Join with Network Data:

    • Joins the sign-in data with NetworkAccessTraffic to get the initiating process name using a left outer join.

  4. Initial Sign-In Data:

    • Projects relevant fields such as sign-in time, application name, user principal name, session ID, IP address, and token type.

  5. Subsequent Sign-In Data:

    • Joins with the same sign-in logs to find subsequent sign-ins for the same session.
    • Ensures that these subsequent sign-ins occurred after the initial sign-in.

  6. Analysis and Scoring:

    • Calculates the time difference between initial and subsequent sign-ins.
    • Assigns a confidence score based on various conditions, such as changes in session status, ASN (Autonomous System Number), and whether the sign-in used a browser process.
    • Describes the use case based on the conditions met, such as changes in network compliance or authentication flow.

  7. Summarization:

    • Groups the data by initial sign-in details and aggregates subsequent sign-ins into a set.
    • Sorts the results by the initial sign-in time.

In essence, this query is used to detect and analyze unusual or potentially suspicious sign-in patterns for specific applications, considering changes in network conditions, authentication methods, and session statuses.

Details

Thomas Naunheim profile picture

Thomas Naunheim

Released: January 2, 2026

Tables

SigninLogsAADNonInteractiveUserSignInLogsNetworkAccessTraffic

Keywords

SigninLogsAADNonInteractiveUserSignInLogsNetworkAccessTrafficUserPrincipalNameAppDisplayNameResourceDisplayNameIPAddressAutonomousSystemNumberTokenProtectionStatusSignInSessionStatusTokenTypeBrowserProcesses

Operators

letunion|where>agoin~extendiffisemptytodynamicproject-rename==joinkindleftouteronprojecttostringinnerproject-awayproject-reorder->extendcasein~!=and>==><bag_pack_columnssummarizemake_setbysort by

Actions