Analytics Unexpected Entra ID Device

Query

// This query can help you to detect unexpected Entra ID devices.
//
// Click "Save as function", in Parameters write in the fields:
// "timespan" "query_frequency" "14d"
// "timespan" "query_period"    "14d"
// "timespan" "query_wait"      "1h"
//
// If you name the function "UnexpectedEntraIDDevice", you can check the function with queries like the following:
//
// UnexpectedEntraIDDevice()
//
// UnexpectedEntraIDDevice(1h, 14d, 1h)
//
// UnexpectedEntraIDDevice(14d, 14d, 1h)
//
// let query_frequency = 14d;
// let query_period = 14d;
// let query_wait = 1h;
//let Function = (query_frequency:timespan = 14d, query_period:timespan = 14d){
// TrustType possible values: Workplace (indicates bring your own personal devices), AzureAd (Cloud only joined devices), ServerAd (on-premises domain joined devices joined to Azure AD)
// let _ExpectedTrustTypes =
//     _GetWatchlist("Activity-ExpectedSignificantActivity")
//     | where Activity == "EntraIDDeviceTrustType"
//     | mv-expand DeviceOSType = split(tostring(DeviceTrustType = split(Auxiliar, " - ")[1]), ", ") to typeof(string)
//     | project DeviceTrustType = tostring(split(Auxiliar, " - ")[0]), DeviceOSType
// ;
// A device management app could be Microsoft Intune
let _ExpectedDeviceManagementIds = toscalar(
    _GetWatchlist("Activity-ExpectedSignificantActivity")
    | where Activity == "DeviceManagement"
    | summarize make_list(ActorId)
);
// Group Tags in Microsoft Intune
let _ExpectedIntuneOrderIds = toscalar(
    _GetWatchlist("Activity-ExpectedSignificantActivity")
    | where Activity == "IntuneOrderId"
    | summarize make_list(Auxiliar)
);
let _EntraConnectSyncAccounts = toscalar(
    _GetWatchlist("Activity-ExpectedSignificantActivity")
    | where Activity == "EntraConnectSync"
    | summarize make_list(ActorId)
);
// let _UserInfo =
//     IdentityInfo
//     | where TimeGenerated > ago(query_period) and isnotempty(AccountObjectId) //and UserType == "Member"
//     | summarize arg_max(TimeGenerated, AccountUPN, MailAddress, AdditionalMailAddresses) by AccountObjectId
//     | project AccountObjectId, UserPrincipalName = AccountUPN, MailAddress, AdditionalMailAddresses
// ;
let _AddDeleteDeviceEvents =
    AuditLogs
    | where TimeGenerated between (ago(query_frequency + query_wait) .. ago(query_wait))
    | where LoggedByService == "Core Directory" and Category == "Device" and AADOperationType in ("Add", "Delete")
    | where not(ResultDescription == "Microsoft.Online.Workflows.ObjectAlreadyExistsException")
    | where not(tostring(InitiatedBy["user"]["id"]) in (_EntraConnectSyncAccounts))
    | where not(tostring(InitiatedBy["app"]["servicePrincipalId"]) in (_ExpectedDeviceManagementIds))
    | extend
        ObjectId = tostring(TargetResources[0]["id"]),
        DeviceName = tostring(TargetResources[0]["displayName"]),
        InitiatedByUser = tostring(InitiatedBy["user"]["userPrincipalName"]),
        InitiatedByApp = tostring(InitiatedBy["app"]["displayName"])
    | project TimeGenerated, Type, InitiatedByUser, InitiatedByApp, OperationName, Result, ResultDescription, DeviceName, ObjectId, AdditionalDetails, InitiatedBy, TargetResources, CorrelationId
    | mv-apply ModifiedProperty = TargetResources[0]["modifiedProperties"] on (
        summarize BagToUnpack = make_bag(bag_pack(replace_string(tostring(ModifiedProperty["displayName"]), "TargetId.", ""), translate(@'["\]', "", tostring(ModifiedProperty["newValue"]))))
    )
    | evaluate bag_unpack(BagToUnpack, columnsConflict = "replace_source")
    // | join kind=leftanti _ExpectedTrustTypes on DeviceTrustType, DeviceOSType
    | extend
        AutopilotZTDID = extract(@"ZTDID\:([^\:\,]+)", 1, column_ifexists("DevicePhysicalIds", "")),
        AccountObjectId = extract(@"USER\-GID\:([^\:\,]+)", 1, column_ifexists("DevicePhysicalIds", "")),
        ["USER-HWID"] = extract(@"USER\-HWID\:([^\:\,]+)", 1, column_ifexists("DevicePhysicalIds", "")),
        IntuneOrderId = extract(@"OrderId:([^\:\,]+)", 1, column_ifexists("DevicePhysicalIds", "")),
        AzureResourceId = extract(@"AzureResourceId:([^\:\,]+)", 1, column_ifexists("DevicePhysicalIds", ""))
    // | lookup kind=leftouter _UserInfo on AccountObjectId
;
let _AddObjectIds = toscalar(_AddDeleteDeviceEvents
    | where OperationName == "Add device"
    | summarize make_set(ObjectId)
);
let _UpdateDeviceEvents =
    AuditLogs
    | where TimeGenerated > ago(query_frequency + query_wait)
    //| where OperationName in ("Update device", "Add member to group", "Remove member from group", "Device no longer compliant", "Device no longer managed")
    | where not(LoggedByService == "Core Directory" and Category == "Device" and AADOperationType in ("Add", "Delete", "Assign", "Unassign"))
    | where not(ResultDescription == "Microsoft.Online.DirectoryServices.DirectoryValueExistsException")
    | where isnotempty(_AddObjectIds) and tostring(TargetResources[0]["id"]) in (_AddObjectIds)
    | where not(tostring(InitiatedBy["user"]["id"]) in (_EntraConnectSyncAccounts))
    | extend
        ObjectId = tostring(TargetResources[0]["id"]),
        DeviceName = tostring(TargetResources[0]["displayName"]),
        InitiatedByUser = tostring(InitiatedBy["user"]["userPrincipalName"]),
        InitiatedByApp = tostring(InitiatedBy["app"]["displayName"])
    | project TimeGenerated, Type, InitiatedByUser, InitiatedByApp, OperationName, Result, ResultDescription, DeviceName, ObjectId, AdditionalDetails, InitiatedBy, TargetResources, CorrelationId
    | mv-apply ModifiedProperty = TargetResources[0]["modifiedProperties"] on (
        summarize BagToUnpack = make_bag(
            bag_pack(replace_string(tostring(ModifiedProperty["displayName"]), "TargetId.", ""),
                iff(OperationName has "Remove" and tostring(ModifiedProperty["displayName"]) startswith "Group",
                    translate(@'["\]', "", tostring(ModifiedProperty["oldValue"])),
                    translate(@'["\]', "", tostring(ModifiedProperty["newValue"])))))
    )
    | evaluate bag_unpack(BagToUnpack, columnsConflict = "replace_source")
    | extend
        AutopilotZTDID = extract(@"ZTDID\:([^\:\,]+)", 1, column_ifexists("DevicePhysicalIds", "")),
        AccountObjectId = extract(@"USER\-GID\:([^\:\,]+)", 1, column_ifexists("DevicePhysicalIds", "")),
        ["USER-HWID"] = extract(@"USER\-HWID\:([^\:\,]+)", 1, column_ifexists("DevicePhysicalIds", "")),
        IntuneOrderId = extract(@"OrderId:([^\:\,]+)", 1, column_ifexists("DevicePhysicalIds", "")),
        AzureResourceId = extract(@"AzureResourceId:([^\:\,]+)", 1, column_ifexists("DevicePhysicalIds", ""))
    // | lookup kind=leftouter _UserInfo on AccountObjectId
;
let _RegisterUserToDeviceEvents =
    AuditLogs
    | where TimeGenerated > ago(query_frequency + query_wait)
    | where LoggedByService == "Core Directory" and Category == "Device" and AADOperationType in ("Assign", "Unassign")
    | where not(ResultDescription in ("Microsoft.Online.Workflows.ObjectAlreadyExistsException", "Microsoft.Online.DirectoryServices.DirectoryValueExistsException"))
    | where isnotempty(_AddObjectIds) and TargetResources[1]["id"] in (_AddObjectIds)
    | where not(tostring(InitiatedBy["user"]["id"]) in (_EntraConnectSyncAccounts))
    | extend
        ObjectId = tostring(TargetResources[1]["id"]),
        InitiatedByUser = tostring(InitiatedBy["user"]["userPrincipalName"]),
        InitiatedByApp = tostring(InitiatedBy["app"]["displayName"]),
        DeviceUser = pack(iff(OperationName has "registered owner", "Owner", "User"), tostring(TargetResources[0]["userPrincipalName"]))
    | project TimeGenerated, Type, InitiatedByUser, InitiatedByApp, OperationName, Result, ResultDescription, ObjectId, AdditionalDetails, InitiatedBy, TargetResources, CorrelationId, DeviceUser
    | extend ["Device.DisplayName"] = "", DeviceId = ""
    | mv-apply ModifiedProperty = TargetResources[0]["modifiedProperties"] on (
        summarize BagToUnpack = make_bag(
            bag_pack(tostring(ModifiedProperty["displayName"]),
                iff(OperationName has "Remove",
                    translate(@'["\]', "", tostring(ModifiedProperty["oldValue"])),
                    translate(@'["\]', "", tostring(ModifiedProperty["newValue"])))))
    )
    | evaluate bag_unpack(BagToUnpack, columnsConflict = "replace_source", ignoredProperties=dynamic(["Device.ObjectID"]))
    | project-rename DeviceName = ["Device.DisplayName"]
;
let _DeviceEvents = materialize(
    union _AddDeleteDeviceEvents, _UpdateDeviceEvents, _RegisterUserToDeviceEvents
    | extend
        DeviceTrustType = column_ifexists("DeviceTrustType", ""),
        CloudDisplayName = column_ifexists("CloudDisplayName", ""),
        DisplayName = column_ifexists("DisplayName", ""),
        ["Included Updated Properties"] = column_ifexists("Included Updated Properties", "")
    | project-rename IncludedUpdatedProperties = ["Included Updated Properties"]
    | as _Auxiliar
    | project-away DeviceId
    | lookup kind=inner (
        _Auxiliar
        | sort by TimeGenerated asc
        | summarize
            OperationNames = make_set(OperationName),
            DeviceId = take_any(column_ifexists("DeviceId", "")),
            take_anyif(AccountObjectId, OperationName == "Add device"),
            take_anyif(AzureResourceId, OperationName == "Add device"),
            take_anyif(AutopilotZTDID, OperationName == "Add device"),
            AddIntuneOrderId = take_anyif(IntuneOrderId, OperationName == "Add device"),
            UpdateIntuneOrderId = take_anyif(IntuneOrderId, OperationName == "Update device"),
            DeviceTrustTypes = make_set_if(DeviceTrustType, isnotempty(DeviceTrustType)),
            //LastAddTimeGenerated = maxif(TimeGenerated, OperationName == "Add device"),
            //LastDeleteTimeGenerated = maxif(TimeGenerated, OperationName == "Delete device"),
            LastDeviceName = tostring(make_list_if(coalesce(CloudDisplayName, DisplayName, DeviceName), IncludedUpdatedProperties has_any ("CloudDisplayName", "DisplayName"))[-1])
            by ObjectId
        | where OperationNames has "Add device"
        // Custom conditions
        //| where not(isnotempty(AccountObjectId) and isnotempty(AzureResourceId))
        //| where not(AddIntuneOrderId in (_ExpectedIntuneOrderIds) or UpdateIntuneOrderId in (_ExpectedIntuneOrderIds))
        //| where not(isnotempty(AutopilotZTDID))
        //| where not(array_length(DeviceTrustTypes) == 0 and OperationNames has_all ("Add device", "Delete device"))
        //| where not(array_length(DeviceTrustTypes) == 1 and tostring(DeviceTrustTypes[0]) == "Workplace") and isnotempty(LastDeleteTimeGenerated) and isnotempty(LastAddTimeGenerated) and LastDeleteTimeGenerated > LastAddTimeGenerated)
        | project ObjectId, DeviceId
    ) on ObjectId
);
let _DeviceIds = toscalar(_DeviceEvents | summarize make_set(DeviceId));
let _SignInEvents =
    union
        (SigninLogs
        | where TimeGenerated > ago(query_frequency + query_wait)
        | where DeviceDetail["deviceId"] in (_DeviceIds)
        ),
        (AADNonInteractiveUserSignInLogs
        | where TimeGenerated > ago(query_frequency + query_wait)
        | where DeviceDetail has_any (_DeviceIds)
        | extend DeviceDetail = todynamic(DeviceDetail)
        )
    | summarize
        arg_min(TimeGenerated,
            DeviceName = tostring(DeviceDetail["displayName"]),
            DeviceDetail,
            UserPrincipalName,
            CorrelationId,
            Type)
        by DeviceId = tostring(DeviceDetail["deviceId"]), UserId, IPAddress
;
union _DeviceEvents, _SignInEvents
| sort by DeviceId asc, TimeGenerated asc
| project-reorder
    TimeGenerated,
    Type,
    InitiatedByApp,
    OperationName,
    Result, 
    DeviceName,
    DeviceTrustType,
    UserPrincipalName,
    IPAddress,
    ObjectId,
    DeviceId,
    AutopilotZTDID,
    AccountObjectId,
    IntuneOrderId,
    AzureResourceId,
    AdditionalDetails,
    InitiatedBy,
    TargetResources,
    CorrelationId,
    IncludedUpdatedProperties
//};
//Function(query_frequency, query_period, query_wait)

Explanation

This KQL query is designed to help detect unexpected devices in Entra ID (formerly Azure Active Directory). Here's a simplified breakdown of what the query does:

Parameters Setup: The query uses parameters like query_frequency, query_period, and query_wait to define the time range for the analysis. By default, these are set to 14 days and 1 hour.
Expected Values: It retrieves expected values for device trust types, device management IDs, Intune order IDs, and Entra Connect sync accounts from a watchlist called "Activity-ExpectedSignificantActivity". These are used to filter out expected activities.
Audit Logs Analysis:
- It examines audit logs for device-related operations like adding or deleting devices.
- It filters out operations initiated by known accounts or applications (like Entra Connect sync accounts or expected device management apps).
- It extracts and processes details about the devices and operations.
Device Events:
- It collects events related to adding, updating, and registering users to devices.
- It processes these events to extract relevant details like device IDs, names, trust types, and associated user information.
Sign-in Events:
- It checks sign-in logs for activities involving the identified devices within the specified time range.
- It summarizes these sign-in events to capture details like device names, user principal names, and IP addresses.
Result Compilation:
- It combines device events and sign-in events into a single dataset.
- The results are sorted and organized to highlight key information such as the time of the event, operation name, result, device details, and user information.

The query is intended to be saved as a function named UnexpectedEntraIDDevice, allowing users to easily execute it with different time parameters to monitor for unexpected device activities in their Entra ID environment.

Details

Jose Sebastián Canós

Released: December 16, 2025

Tables

_GetWatchlistAuditLogsSigninLogsAADNonInteractiveUserSignInLogs

Keywords

DevicesEntraIDIntuneUserAuditLogsSigninLogsAADNonInteractiveUserSignInLogs

Operators

letmv-expandsplittostringprojecttoscalarsummarizemake_listwhereinextendproject-awaymv-applymake_bagbag_packreplace_stringtranslateevaluatebag_unpackextractcolumn_ifexistsiffpackproject-renamematerializeunionsort byarg_minhashas_anymake_settake_anytake_anyifmake_set_ifcoalescemake_list_ifarray_lengthtodynamicproject-reorder

Actions

GitHub

KQL Search