KQL Search

Query Details

HomeAI ToolsDevice Query

Removed Device Events

Query

let query_period = 15d;
union
    (
    SecurityEvent
    | where TimeGenerated > ago(query_period)
    | where EventID in (4722, 4725) and TargetAccount endswith "$"
    | extend DisabledInAD = EventID == 4725
    | project
        TimeGenerated,
        Account,
        Activity,
        TargetDomainName,
        TargetUserName = trim_end(@'\$', TargetUserName),
        TargetSid,
        EventData,
        DisabledInAD
    | summarize arg_max(TimeGenerated, DisabledInAD) by TargetUserName, TargetDomainName
    )
    ,
    (
    SecurityEvent
    | where TimeGenerated > ago(query_period)
    | where EventID == 5136 and EventData has_all ('<Data Name="AttributeLDAPDisplayName">dNSTombstoned</Data>', '<Data Name="AttributeValue">TRUE</Data>')
    | extend
        TombstonedInAD = EventData has '<Data Name="OperationType">%%14674</Data>',
        TargetUserName = tostring(extract(@'\"ObjectDN\"\>[Dd][Cc]\=([^\,]+)\,', 1, EventData))
    | project
        TimeGenerated,
        Account,
        Activity,
        SubjectAccount,
        TargetDomainName = SubjectDomainName,
        TargetUserName,
        EventData,
        TombstonedInAD
    | summarize arg_max(TimeGenerated, TombstonedInAD) by TargetUserName, TargetDomainName
    ),
    (
    AuditLogs
    | where TimeGenerated > ago(query_period)
    | where OperationName in ("Add device", "Delete device") and Result == "success"
    | extend
        DeviceName = tostring(TargetResources[0]["displayName"]),
        DeletedInEntraID = OperationName has "Delete device"
    | project
        TimeGenerated,
        Identity,
        OperationName,
        Result,
        DeviceName,
        InitiatedBy,
        AdditionalDetails,
        TargetResources,
        TargetUserName = toupper(DeviceName),
        DeletedInEntraID
    | summarize arg_max(TimeGenerated, DeletedInEntraID) by TargetUserName
    )
| summarize
    ADTimeGenerated = minif(TimeGenerated, isnotempty(DisabledInAD) or isnotempty(TombstonedInAD)),
    EntraIDTimeGenerated = minif(TimeGenerated, isnotempty(DeletedInEntraID)),
    take_any(TargetDomainName),
    take_anyif(DisabledInAD, isnotempty(DisabledInAD)),
    take_anyif(TombstonedInAD, isnotempty(TombstonedInAD)),
    take_anyif(DeletedInEntraID, isnotempty(DeletedInEntraID))
    by TargetUserName
| where DisabledInAD or DeletedInEntraID
| sort by ADTimeGenerated desc, EntraIDTimeGenerated desc
| project
    TargetDomainName,
    Computer = TargetUserName,
    ADTimeGenerated,
    DisabledInAD,
    TombstonedInAD,
    EntraIDTimeGenerated,
    DeletedInEntraID

Explanation

This KQL query is designed to identify and summarize specific security-related events involving computer accounts over the past 15 days. Here's a simplified breakdown of what the query does:

  1. Define the Time Period: The query looks at events from the last 15 days.

  2. Union of Three Data Sources:

    • Security Events for Account Disabling: It checks for security events where computer accounts (those ending with "$") are either disabled (EventID 4725) or enabled (EventID 4722). It tracks whether the account was disabled.
    • Security Events for Tombstoned Accounts: It looks for events indicating that a computer account has been marked as "tombstoned" in Active Directory (EventID 5136 with specific attributes).
    • Audit Logs for Device Operations: It examines audit logs for successful operations where devices were added or deleted, focusing on deletions.

  3. Data Processing:

    • Each data source is processed to extract relevant information such as the time of the event, account details, and specific flags indicating if an account was disabled, tombstoned, or deleted.
    • The query uses summarize to get the most recent event for each computer account and domain combination.

  4. Final Summarization:

    • It combines the results from the three data sources to provide a summary for each computer account.
    • It identifies the earliest time an account was disabled or tombstoned in Active Directory and the earliest time it was deleted in Entra ID.
    • The results are filtered to include only accounts that were either disabled in Active Directory or deleted in Entra ID.

  5. Sorting and Projection:

    • The results are sorted by the time the account was disabled or deleted, in descending order.
    • The final output includes the domain name, computer name, and timestamps for when the account was disabled, tombstoned, or deleted.

This query helps in monitoring and auditing changes to computer accounts, particularly focusing on those that have been disabled or deleted, which could be significant for security and compliance purposes.

Details

Jose Sebastián Canós profile picture

Jose Sebastián Canós

Released: September 23, 2025

Tables

SecurityEventAuditLogs

Keywords

SecurityEventAuditLogsDevicesUser

Operators

letunionwhereinendswithextendprojectsummarizearg_maxbyhas_alltostringextracthastoupperminifisnotemptytake_anytake_anyiforsort bydesc

Actions