KQL Search

Query Details

HomeAI ToolsDevice Query

Audit Logs Unexpected Device OS Type Modification

Query

AuditLogs
| where OperationName == "Update device" and Result == "success"
| mv-apply modifiedProperty = TargetResources[0]["modifiedProperties"] on (
    summarize modifiedProperties = make_bag(bag_pack(tostring(modifiedProperty["displayName"]), bag_pack("oldValue", translate(@'["\]', "", tostring(modifiedProperty["oldValue"])), "newValue", translate(@'["\]', "", tostring(modifiedProperty["newValue"])))))
    )
| where isnotempty(tostring(modifiedProperties["CloudDeviceOSType"]["oldValue"])) or isnotempty(tostring(modifiedProperties["DeviceOSType"]["oldValue"]))
| extend
    CloudDeviceOSTypeOldValue = tolower(tostring(modifiedProperties["CloudDeviceOSType"]["oldValue"])),
    CloudDeviceOSTypeNewValue = tolower(tostring(modifiedProperties["CloudDeviceOSType"]["newValue"])),
    DeviceOSTypeOldValue = tolower(tostring(modifiedProperties["DeviceOSType"]["oldValue"])),
    DeviceOSTypeNewValue = tolower(tostring(modifiedProperties["DeviceOSType"]["newValue"])),
    DeviceObjectId = tostring(TargetResources[0]["id"]),
    DeviceId = tostring(modifiedProperties["TargetId.DeviceId"]["newValue"]),
    AuxiliarDeviceDisplayName = tostring(TargetResources[0]["displayName"])
| summarize
    StartTime = arg_min(TimeGenerated, *),
    (EndTime, DeviceDisplayName) = arg_max(TimeGenerated, AuxiliarDeviceDisplayName),
    OSTypeOldValues = array_sort_asc(make_set(pack_array(CloudDeviceOSTypeOldValue, DeviceOSTypeOldValue))),
    OSTypeNewValues = array_sort_asc(make_set(pack_array(CloudDeviceOSTypeNewValue, DeviceOSTypeNewValue))),
    CorrelationIds = make_set(CorrelationId)
    by DeviceObjectId, DeviceId
| where not(array_length(OSTypeOldValues) == 1 and OSTypeOldValues[0] == "unknown")
| extend
    OSTypeOldValuesReplaced = tostring(set_union(todynamic(replace_strings(tostring(OSTypeOldValues),
        dynamic(["macmdm",  "iphone",   "ipad", "androidaosp",  "androidenterprise",    "androidforwork"]),
        dynamic(["macos",   "ios",      "ios",  "android",      "android",              "android"])
    )), dynamic([]))),
    OSTypeNewValuesReplaced = tostring(set_union(todynamic(replace_strings(tostring(OSTypeNewValues),
        dynamic(["macmdm",  "iphone",   "ipad", "androidaosp",  "androidenterprise",    "androidforwork"]),
        dynamic(["macos",   "ios",      "ios",  "android",      "android",              "android"])
    )), dynamic([])))
| where not(OSTypeOldValuesReplaced == OSTypeNewValuesReplaced)
| extend
    Initiator = iif(isnotempty(InitiatedBy["app"]), tostring(InitiatedBy["app"]["displayName"]), tostring(InitiatedBy["user"]["userPrincipalName"])),
    InitiatorId = iif(isnotempty(InitiatedBy["app"]), tostring(InitiatedBy["app"]["servicePrincipalId"]), tostring(InitiatedBy["user"]["id"])),
    IPAddress = tostring(InitiatedBy[tostring(bag_keys(InitiatedBy)[0])]["ipAddress"])
| project
    StartTime,
    EndTime,
    LoggedByService,
    Category,
    AADOperationType,
    Initiator,
    IPAddress,
    OperationName,
    Result,
    DeviceDisplayName,
    DeviceId,
    DeviceObjectId,
    OSTypeOldValues,
    OSTypeNewValues,
    AdditionalDetails,
    Identity,
    InitiatorId,
    InitiatedBy,
    TargetResources,
    CorrelationIds

Explanation

This KQL query is designed to analyze audit logs for device updates. Here's a simplified breakdown of what it does:

  1. Filter Logs: It starts by filtering the logs to only include entries where the operation name is "Update device" and the result is "success".

  2. Extract Modified Properties: It examines the first target resource's modified properties to create a structured collection of these properties, focusing on changes in the device's operating system type (OS type).

  3. Check for OS Type Changes: It specifically looks for logs where there is a change in the OS type, either in "CloudDeviceOSType" or "DeviceOSType".

  4. Normalize OS Type Values: The query converts the old and new OS type values to lowercase and replaces certain OS type strings with standardized values (e.g., "macmdm" to "macos", "iphone" to "ios").

  5. Filter Out Unchanged OS Types: It removes entries where the old and new OS types are the same after normalization.

  6. Summarize Data: The query summarizes the data by device, capturing the earliest and latest timestamps of the updates, the old and new OS types, and any correlation IDs associated with the updates.

  7. Identify Initiator: It determines who initiated the update, whether it was an app or a user, and captures their ID and IP address.

  8. Project Relevant Information: Finally, it selects and displays relevant fields such as the start and end times of the update, the initiator's details, the device information, and the OS type changes.

In essence, this query is used to track successful device updates, focusing on changes in the operating system type, and provides detailed information about each update, including who initiated it and from where.

Details

Jose Sebastián Canós profile picture

Jose Sebastián Canós

Released: April 22, 2026

Tables

AuditLogs

Keywords

AuditLogsDevicesDeviceOSTypeCloudDeviceOSTypeDeviceObjectIdDeviceIdDeviceDisplayNameInitiatorInitiatorIdIPAddressOperationNameResultCorrelationIds

Operators

AuditLogswhere==andmv-applysummarizemake_bagbag_packtostringtranslateisnotemptyextendtolowerarg_minarg_maxarray_sort_ascmake_setpack_arraybynotarray_lengthset_uniontodynamicreplace_stringsdynamiciifproject

Actions