KQL Search

Query Details

HomeAI ToolsDevice Query

HUNT 21 M365 Inactive Human Users Null Activity 90d

Query

// Hunt    : M365 - Inactive Human Users with No M365 Activity (90d)
// Purpose : Find internally-credentialed user accounts (NOT guests, NOT service principals,
//           NOT shared/service mailboxes) that have produced zero OfficeActivity events
//           in the past 90 days. These dormant accounts are prime targets for credential
//           stuffing or password spray because they are unlikely to have active owners
//           watching for alerts. The query cross-references prior activity to distinguish
//           "never active" users from "formerly active / now dormant" ones.
// Tables  : OfficeActivity
// Period  : P180D (baseline) / P90D (inactivity window)
// Tactics : InitialAccess, Persistence
// MITRE   : T1078.004, T1098
// Scope   : Human users only — excludes #EXT# (guest), service accounts, shared mailboxes
//==========================================================================================

let BaselinePeriod   = 180d;
let InactivePeriod   = 90d;

// Patterns indicative of non-human identities - exclude from results
let ServiceAccountPatterns = dynamic([
    "svc-", "svc.", "srv-", "srv.", "service-", "service.",
    "noreply", "no-reply", "donotreply", "do-not-reply",
    "backup", "monitor", "alert", "scan", "admin@",
    "postmaster", "abuse@", "support@"]);

// --- Users active at ANY point in the full baseline window ---
let UsersWithPriorActivity = OfficeActivity
    | where TimeGenerated > ago(BaselinePeriod)
    | where UserId !has "#EXT#"                          // exclude guests
    | where UserId has "@"                               // must be a UPN
    | where not(UserId has_any (ServiceAccountPatterns)) // exclude service accounts
    | summarize
        TotalEvents      = count(),
        LastActivity     = max(TimeGenerated),
        FirstActivity    = min(TimeGenerated),
        Workloads        = make_set(RecordType, 10),
        LastOperation    = arg_max(TimeGenerated, Operation)
        by UserId;

// --- Users active in the RECENT inactivity window (still active) ---
let RecentlyActiveUsers = OfficeActivity
    | where TimeGenerated > ago(InactivePeriod)
    | where UserId !has "#EXT#"
    | summarize count() by UserId;

// --- Dormant = had prior activity but NOTHING in last InactivePeriod ---
UsersWithPriorActivity
| join kind=leftanti RecentlyActiveUsers on UserId
| extend
    DaysSinceActivity   = datetime_diff("day", now(), LastActivity),
    DaysEverActive      = datetime_diff("day", LastActivity, FirstActivity),
    DormancyCategory    = case(
        datetime_diff("day", now(), LastActivity) >= 180, "Abandoned (180d+)",
        datetime_diff("day", now(), LastActivity) >= 90,  "Dormant (90-179d)",
        "Fading (<90d)")
| extend WorkloadsSummary = tostring(Workloads)
| project
    UserId,
    DormancyCategory,
    DaysSinceActivity,
    LastActivity,
    FirstActivity,
    DaysEverActive,
    TotalEvents,
    WorkloadsSummary,
    LastOperation    = tostring(LastOperation)
| sort by DaysSinceActivity desc

Explanation

This query is designed to identify inactive human user accounts within a Microsoft 365 environment that have not shown any activity in the past 90 days. These accounts are considered potential security risks because they might be targeted for unauthorized access attempts, such as credential stuffing or password spraying, due to their inactivity.

Here's a breakdown of the query's logic:

  1. Baseline and Inactive Periods:

    • The query examines user activity over two timeframes: a baseline period of 180 days and an inactivity window of 90 days.

  2. Exclusion Criteria:

    • The query excludes guest accounts (identified by "#EXT#"), service accounts, and shared mailboxes. It uses specific patterns to identify and exclude service accounts (e.g., accounts with names like "svc-", "noreply", "admin@", etc.).

  3. Identifying Users with Prior Activity:

    • It first identifies users who have been active at any point during the 180-day baseline period. This includes capturing details like the total number of events, the last and first activity dates, the types of workloads accessed, and the last operation performed.

  4. Identifying Recently Active Users:

    • It then identifies users who have been active within the last 90 days.

  5. Determining Dormant Users:

    • The query finds users who had prior activity but have not been active in the last 90 days by comparing the two sets of users identified above.
    • It categorizes these dormant users based on how long they have been inactive:
      • "Abandoned (180d+)": No activity for 180 days or more.
      • "Dormant (90-179d)": No activity for 90 to 179 days.
      • "Fading (<90d)": Recently inactive but less than 90 days.

  6. Output:

    • The query outputs a list of dormant users with details such as their user ID, dormancy category, days since last activity, last and first activity dates, total events, a summary of workloads accessed, and the last operation performed.
    • The results are sorted by the number of days since the last activity, in descending order, highlighting the most inactive accounts.

This query helps in identifying potentially risky accounts that require attention to prevent unauthorized access.

Details

David Alonso profile picture

David Alonso

Released: March 18, 2026

Tables

OfficeActivity

Keywords

OfficeActivityUsersUserIdWorkloadsOperation

Operators

letagodynamicwhere!hashasnothas_anysummarizecountmaxminmake_setarg_maxbyjoinkind=leftantionextenddatetime_diffnowcase>=projecttostringsortdesc

Actions