Duplicate Signin Overlap

Query

// SignInLogs vs AADNonInteractiveUserSignInLogs Overlap
// These two tables often contain the SAME sign-in attempts.
// Non-interactive is typically 5-20x the volume of interactive.
// If you use MDE/Defender for Identity, much of this is redundant.
// =====================================================================

let _Window = 1d;
// --- Volume comparison ---
let _Interactive = 
    SigninLogs
    | where TimeGenerated > ago(_Window)
    | summarize 
        InteractiveCount = count(),
        InteractiveUsers = dcount(UserPrincipalName),
        InteractiveApps = dcount(AppDisplayName),
        InteractiveFailed = countif(ResultType != "0");
let _NonInteractive =
    AADNonInteractiveUserSignInLogs
    | where TimeGenerated > ago(_Window)
    | summarize 
        NonInteractiveCount = count(),
        NonInteractiveUsers = dcount(UserPrincipalName),
        NonInteractiveApps = dcount(AppDisplayName),
        NonInteractiveFailed = countif(ResultType != "0");
let _VolumeComparison = 
    _Interactive | extend placeholder = 1
    | join kind=inner (_NonInteractive | extend placeholder = 1) on placeholder
    | project 
        Section = "VOLUME_COMPARISON",
        InteractiveEvents = InteractiveCount,
        NonInteractiveEvents = NonInteractiveCount,
        VolumeRatio = strcat("Non-Interactive is ", tostring(round(toreal(NonInteractiveCount) / toreal(InteractiveCount), 1)), "x Interactive"),
        InteractiveUsers, NonInteractiveUsers,
        InteractiveFailed, NonInteractiveFailed,
        Recommendation = case(
            NonInteractiveCount > InteractiveCount * 10, "CRITICAL: Non-Interactive >10x volume - Filter successful token refreshes",
            NonInteractiveCount > InteractiveCount * 5, "HIGH: >5x volume - Filter top apps by AppId in DCR",
            "NORMAL: Acceptable ratio"
        );
// --- User overlap between tables ---
let _InteractiveUsers = SigninLogs | where TimeGenerated > ago(_Window) | distinct UserPrincipalName;
let _NonInteractiveUsers = AADNonInteractiveUserSignInLogs | where TimeGenerated > ago(_Window) | distinct UserPrincipalName;
let _UserOverlap =
    _InteractiveUsers
    | join kind=inner _NonInteractiveUsers on UserPrincipalName
    | summarize OverlappingUsers = dcount(UserPrincipalName);
// --- App overlap ---
let _InteractiveApps = SigninLogs | where TimeGenerated > ago(_Window) | distinct AppDisplayName;
let _NonInteractiveApps = AADNonInteractiveUserSignInLogs | where TimeGenerated > ago(_Window) | distinct AppDisplayName;
let _AppOverlap =
    _InteractiveApps
    | join kind=inner _NonInteractiveApps on AppDisplayName
    | summarize OverlappingApps = dcount(AppDisplayName);
// Output
_VolumeComparison

Explanation

This query is designed to compare and analyze the overlap between two sets of sign-in logs: SigninLogs and AADNonInteractiveUserSignInLogs. Here's a simplified breakdown:

Time Window: The analysis is conducted over a 1-day period (_Window = 1d).
Volume Comparison:
- It calculates the total number of sign-in events, distinct users, distinct applications, and failed sign-ins for both interactive and non-interactive sign-ins.
- It then compares the volume of non-interactive sign-ins to interactive sign-ins, expressing this as a ratio.
- Based on the ratio, it provides a recommendation:
  - If non-interactive sign-ins are more than 10 times the interactive ones, it suggests filtering successful token refreshes.
  - If they are more than 5 times, it suggests filtering top apps by AppId.
  - Otherwise, the ratio is considered normal.
User Overlap:
- It identifies users who appear in both interactive and non-interactive sign-in logs within the specified time window.
App Overlap:
- It identifies applications that appear in both sets of logs within the specified time window.
Output:
- The final output focuses on the volume comparison, including the number of events, users, failed sign-ins, and a recommendation based on the volume ratio.

In essence, this query helps to understand the relationship and overlap between interactive and non-interactive sign-ins, providing insights into user and application behavior, and offering recommendations for managing high volumes of non-interactive sign-ins.

Details

David Alonso

Released: April 8, 2026

Tables

SigninLogsAADNonInteractiveUserSignInLogs

Keywords

SigninLogsAADNonInteractiveUserSignInLogsTimeGeneratedUserPrincipalNameAppDisplayNameResultTypeMDEDefenderIdentityVolumeComparisonInteractiveCountNonInteractiveCountInteractiveUsersNonInteractiveUsersInteractiveFailedNonInteractiveFailedUserOverlapAppOverlapOverlappingUsersOverlappingApps

Operators

letwheresummarizecountdcountcountifextendjoinkindonprojectstrcattostringroundtorealcasedistinct

Actions

GitHub

KQL Search