KQL Search

Query Details

HomeAI ToolsDevice Query

Noise Noninteractive Signin

Query

// AADNonInteractiveUserSignInLogs - App Noise Analysis
// Non-interactive sign-ins are often the BIGGEST table in Entra ID environments.
// Most volume comes from a handful of apps (Outlook, Teams, OneDrive, SharePoint).
// This identifies apps that can be filtered via DCR transforms.
// =====================================================================

let _Window = 7d;
let _TotalSignIns = toscalar(
    AADNonInteractiveUserSignInLogs 
    | where TimeGenerated > ago(_Window) 
    | count
);
// --- Part 1: By Application ---
AADNonInteractiveUserSignInLogs
| where TimeGenerated > ago(_Window)
| summarize 
    EventCount = count(),
    DistinctUsers = dcount(UserPrincipalName),
    FailedCount = countif(ResultType != "0"),
    SuccessCount = countif(ResultType == "0"),
    DistinctIPs = dcount(IPAddress)
    by AppDisplayName, AppId
| extend 
    PctOfTotal = round(toreal(EventCount) * 100.0 / _TotalSignIns, 2),
    FailurePct = round(toreal(FailedCount) * 100.0 / EventCount, 1)
| extend 
    FilterAction = case(
        PctOfTotal > 20 and FailurePct < 1, "FILTER: >20% volume, near-zero failures - Strong DCR candidate",
        PctOfTotal > 10 and FailurePct < 5, "FILTER: >10% volume, low failures - DCR filter by AppId",
        PctOfTotal > 5 and FailurePct < 2, "REVIEW: >5% volume - Evaluate if token refresh noise",
        FailurePct > 30, "KEEP: High failure rate - Investigate potential brute force",
        "KEEP"
    ),
    RiskNote = case(
        AppDisplayName has_any ("Outlook", "Teams", "OneDrive", "SharePoint", "Exchange"), "Token refresh noise - Usually safe to filter success-only",
        AppDisplayName has_any ("Azure", "Portal", "Graph"), "Admin context - Keep for audit trail",
        AppDisplayName has "OIDC", "OIDC background flow - Usually safe to filter",
        ""
    )
| project 
    AppDisplayName, AppId, EventCount, PctOfTotal,
    SuccessCount, FailedCount, FailurePct,
    DistinctUsers, DistinctIPs,
    FilterAction, RiskNote
| order by EventCount desc
| take 30

Explanation

This query is designed to analyze non-interactive sign-in logs in Azure Active Directory (AAD) to identify and categorize applications based on their sign-in activity. Here's a simplified breakdown of what the query does:

  1. Time Window: It looks at sign-in data from the past 7 days.

  2. Total Sign-Ins Calculation: It calculates the total number of non-interactive sign-ins during this period.

  3. Data Aggregation by Application:

    • It summarizes the sign-in data by application, calculating:
      • Total number of sign-in events (EventCount).
      • Number of distinct users who signed in (DistinctUsers).
      • Number of failed sign-ins (FailedCount).
      • Number of successful sign-ins (SuccessCount).
      • Number of distinct IP addresses used (DistinctIPs).

  4. Percentage Calculations:

    • It calculates the percentage of total sign-ins each application represents (PctOfTotal).
    • It calculates the percentage of failed sign-ins for each application (FailurePct).

  5. Filtering Recommendations:

    • Based on the volume and failure rate of sign-ins, it suggests actions:
      • Filter: If an app has high volume but low failure rates, it might be a candidate for filtering to reduce noise.
      • Review: If an app has moderate volume, it suggests reviewing for potential token refresh noise.
      • Keep: If an app has a high failure rate, it suggests keeping the data for further investigation.

  6. Risk Notes:

    • It adds notes about certain apps, indicating whether they are typically safe to filter or should be kept for audit purposes.

  7. Output:

    • The query projects relevant fields and orders the results by the number of events, showing the top 30 applications.

This analysis helps identify which applications generate the most sign-in noise and provides guidance on whether to filter or keep the data based on the application's behavior and context.

Details

David Alonso profile picture

David Alonso

Released: April 8, 2026

Tables

AADNonInteractiveUserSignInLogs

Keywords

AADNonInteractiveUserSignInLogsAppNoiseAnalysisEntraIDEnvironmentsAppsOutlookTeamsOneDriveSharePointDCRTransformsApplicationTimeGeneratedEventCountDistinctUsersUserPrincipalNameFailedCountSuccessCountDistinctIPsAppDisplayNameAppIdPctOfTotalFailurePctFilterActionRiskNoteAzurePortalGraphOIDC

Operators

lettoscalaragowherecountsummarizecountifdcountbyextendroundtorealcasehas_anyhasprojectorder bytake

Actions