kusto

// Kusto queries
// if you have one you'd like to share add it as a comment and i will add it up top

// diff ways to filter time
| where env_time > ago(1d)
| where env_time between (datetime(2026-02-25 00:09:54) .. datetime(2026-03-6 00:09:54))

// tenant id => accountid, asmu
let cxTenantId = '';
DeviceManagementProvider
| where accountContextId == cxTenantId
| project accountId, asmu=env_cloud_name
| take 5

// device check-in logs
let cxDeviceID = '';
let cxAccountId = '';
DeviceManagementProvider
| where env_time > ago(1d)
| where accountId == cxAccountId
| where ActivityId == cxDeviceID
| where isnotempty(message)
| project env_time, message

// see enrollment related events
let cxAccountId = '';
let cxTenantId = '';
DeviceLifecycle
| where env_time >= ago(10d)
| where accountId == cxAccountId or accountContextId == cxTenantId
//| where TaskName contains "Fail"
| project env_time, deviceId, userId, aadDeviceId, TaskName, details, scenarioType, cV

// 1. returns when the device first started talking and last
let start = datetime(2026-08-20 19:20:00Z);
let end = datetime(2026-08-20 19:55:00Z);
let CxAadDeviceId = '';
let cxDeviceID = '';
cluster("intune.kusto.windows.net").database("Intune").IntuneEvent
| where env_time between (start .. end)
| where env_cloud_environment == "PE"
| where cxDeviceID == intuneDeviceId or ActivityId == CxAadDeviceId
| summarize Count=count(), FirstSeen=min(env_time), LastSeen=max(env_time),
  SampleCV=take_any(cV)
  by ServiceName, ComponentName, EventUniqueName, TraceLevel
| top 50 by Count desc

// 2. grab SampleCV and enter it into the query below
 let cxSampleCV = "fXkUz5Qcs0GRwcZKaNn8dA";
 cluster("intune.kusto.windows.net").database("Intune").IntuneEvent
 | where env_time between (
     datetime(2026-08-20 19:24:30Z) .. datetime(2026-08-20 19:26:00Z))
 | where cV startswith cxSampleCV
 | project env_time, env_cloud_name, ServiceName, ComponentName,
           EventUniqueName, TraceLevel, AccountId, ContextId, DeviceId,
           ActivityId, cV, ColMetadata, Col1, Col2, Col3, Message
 | order by env_time asc
 | take 200

// 3.
 let start = datetime(2026-08-20 19:20:00Z);
 let end = datetime(2026-08-20 19:55:00Z);
 let targetAsu = "AMSUA0401";
 let accountId = "b41d5774-a53c-4970-8733-cecfbb20e382";
 let tenantId = "9a307864-3e98-4f08-b90a-728b62cf32c5";
 cluster("intune.kusto.windows.net").database("Intune").IntuneEvent
 | where env_time between (start .. end)
 | where env_cloud_name == targetAsu
 | where AccountId == accountId or ContextId == tenantId
 | where EventUniqueName == "EspSlaIsDeviceSyncDone"
 | project env_time, ServiceName, ComponentName, EventUniqueName,
           TraceLevel, AccountId, ContextId, DeviceId, ActivityId, cV,
           ColMetadata, Col1, Col2, Col3, Message
 | order by env_time asc
 | take 100

// applicablilityState == Applicable means the device thinks the policy applies.
// reportComplianceState is the key state column you’re after (e.g., Compliant, Noncompliant, etc.)
let cxDeviceID = '';
let cxPolicyId = '';
DeviceManagementProvider
| where env_time > ago(1d)
| where TaskName == "DeviceManagementProviderCIReportDataEvent"
| where deviceId == cxDeviceID
| where policyId == cxPolicyId
| where applicablilityState == "Applicable"
| project env_time,
          deviceId,
          policyId,
          typeAndCategory,
          applicablilityState,
          reportComplianceState,
          errorCode,
          EventMessage
| order by env_time desc

// looking for policy conflicts
let cxDeviceID = '';
let cxAccountId = '';
IntuneEvent
| where env_time > ago(3d)
| where AccountId == cxAccountId
| where ApplicationName startswith "DeviceCheckin"
| where DeviceId == cxDeviceID
| where Col5 != "NotApplicable"
| where Col1 contains "conflict"
| project env_time, Col1, Col2, Col3, Col4, ScenarioInstanceId

// see wipe or retire actions against a specific device
let cxAccountID = "xxx";
let cxDeviceID = "xxx";
HttpSubsystem
| where env_time >= ago(10d)
| where accountId == cxAccountID
| where url contains cxAccountID
| where url contains "wipe" or url contains "retire"
| where httpVerb == "POST" or httpVerb == "ACTION" or httpVerb == "DELETE"
| project env_time, ActivityId, url, cVBase, statusCode, httpVerb
| take 500

// IOS SECTION
//To Know the Enrollment Type for iOS
let cxASU = "xxx";//use FXPASU01 for GCC High environments
let cxAccountID = "xxx";
let cxDeviceID = "xxx";
IntuneEvent
| where env_time > ago(8d)
| where SourceNamespace == "IntunePE" //IntuneFXP for GCC High
//| where env_time > datetime(2025-04-12 01:00) and env_time < datetime(2025-04-13 23:59)
//| where env_cloud_name == cxASU
//| where AccountId == cxAccountID
| where ActivityId == cxDeviceID
| where Col1 has "EnrollmentType" or Col1 has "OSVersion:" //for iOS Shows multiple device Details, like OSversion, Model and other hardware details
| project env_time, Col1, Col2, Col3, Col4, Col5, Col6,ActivityId, UserId, DeviceId,AccountId

//To get VPP token
let cxIntuneASU = ""; //use FXPASU01 for GCC High Environments
let cxIntuneAccountID = "";
let cxIntuneVPPToken = "";
IntuneEvent
| where env_time > ago(2d)
| where env_cloud_name == cxIntuneASU
| where AccountId == cxIntuneAccountID  // and PayLoadId == IntuneVPPToken
| where ApplicationName == "AppleVPP"
| where EventUniqueName == "ValidatingSToken"
| project env_time,EventUniqueName,Message,CurrentState=Col3,TokenId=PayLoadId,TokenLocationId=Col5,TokenLocationName=Col4

// For iOS/iPadOS and macOS app deployment (LOB, App Store and VPP Apps)
let cxDeviceID = "";
let whatsappmess = "310633997";
let oktaverify = "490179405" ;
let jabber = "1487776871" ;
let microsoftonedrive = "477537958" ;
let google = "284815942" ; // https://apps.apple.com/us/app/id284815942
IntuneEvent
| where env_time > ago(21d)
| where SourceNamespace == "IntunePE"//For GCC High Kusto comment this line out or use "IntuneFXP" SourceNamespace
| where ActivityId == cxDeviceID
| where EventUniqueName == "AppleAppSummary"
| where Col1 contains microsoftonedrive
| project env_time, Col1, Col2, Col3, Col4, Col5, Col6,ActivityId, UserId, DeviceId,AccountId,env_cloud_name,BuildVersion
| sort by env_time

// User check-in MAM per user ID
let UserID = '';
let _endTime = datetime(2026-04-20T18:00:53Z);
let _startTime = datetime(2026-04-20T17:00:53Z);
AmsActivityForUser(['UserID'], start=['_startTime'], end=['_endTime'])
| project env_time, ['type'], statusCode, AppId, Os, DeviceId, AppVersion, SdkVersion, ManagementLevel, AppFriendlyName, PolicyState, DeviceModel

// This query allows user to see policy status per device / per user
DeviceManagementProvider
| where env_time > ago(30d)
| where ActivityId == 'xxxxxxxxxxxxxxxxxxxxxx'
| where EventId == 5786
| where applicablilityState == 'Applicable'
| extend PolicyType = iff(tostring(split(typeAndCategory, ';')[1]) == 'None', 'GeneralConfiguration', iff(typeAndCategory == 'Application;Application', 'MangedDeviceAppConfig', tostring(split(typeAndCategory, ';')[1])))
| project env_time, tenantId, deviceId = ActivityId, PolicyName=name, PolicyType, ComplianceState=reportComplianceState, PolicyId = translate('_', '-', extract('_([a-z0-9_]{36})', 1, ['id'])), userId

// This query allows me to actively investigate SyncML 500 issues for the ongoing compliance issues and to feed data to the Product Group with that regard
DmpLogs
| where env_time > ago(7d)
| where env_cloud_name == "AMSUB0101" // AMSU Here
| where message startswith "New Session" or message contains "Discovery error: Status=Failed;" or message contains "Status.Data for get command not '200'"
| where ActivityId == "deviceidhere" // DeviceId Here
| project env_time, userId, scenarioInstanceId, message

// This query allows me to check for Compliance Patching of the corresponding Azure Objects from Intune
let cxDeviceID = "xxx";
GraphApiProxyLibrary
| where env_time >= ago(3d)
| where url contains cxDeviceID

// This query checks all Non-Compliance against a singular device
IntuneEvent
| where env_time > ago(3d)
| where env_cloud_name == "AMSUB0501"
| where ServiceName == "StatelessComplianceCalculationService"
| where EventUniqueName == "ComplianceDetail-GetComplianceDetailsForDeviceAction-FinalResult"
| where Col1 contains "DEVICEIDHERE" // DeviceId
| project env_time, ServiceName, ComponentName, EventUniqueName, ColMetadata, Col1, Col2, Col3, Col4, Col5, Col6, Message, cV, ScenarioType, TraceLevel, ActivityId

// This query check the tenant for a specific Non-compliant event; I.e Firewall_Enabled
let cxAccountID = "xxx";
IntuneEvent // CHECK THIS FOR FIREWALL ISSUES AGAINST A TENANT (SYNCML500)
| where env_cloud_name == "AMSUB0201"
| where env_time > ago(12h)
| where ServiceName == "StatelessComplianceCalculationService"
| where EventUniqueName == "ComplianceDetail-GetComplianceDetailsForDeviceAction-FinalResult"
| where Col1 contains cxAccountID
| project env_time, ServiceName, ComponentName, EventUniqueName, ColMetadata, Col1, Col2, Col3, Col4, Col5, Col6, Message, cV, ScenarioType, TraceLevel, ActivityId, env_cloud_name
| summarize argmax(env_time, Col5) by Col1
| project max_env_time_Col5, Col1
| extend ComplianceState = extract("ComplianceState:(.*?);", 1, max_env_time_Col5)
| extend ComplianceReasons = extractall("SettingId:(.*?);", max_env_time_Col5)
|where ComplianceReasons contains "Firewall_Enabled" // CHANGE THIS TO THE COMPLIANCE FAILURE YOU'RE SEEING AGAINST A TENANT
//| where ComplianceReasons contains "DefenderEnabled"
| summarize count() by ComplianceState , tostring(ComplianceReasons)
| order by count_

// AUTOPILOT RELATED
//////////////////////////////////////

// Find device registrations
let cxSerialNumber = "xxx";
IntuneEvent
| where env_time > ago(180d)
| where ServiceName == "WindowsAutopilotWorkItemProcessorService"
| where EventUniqueName in ("DeviceRegistered","DeviceInBatchProcessed","HardwareHashResolved")
| where Col1 has cxSerialNumber
| project env_time, EventUniqueName, Col1
| order by env_time asc

// TO investigate the AP Fix Pending / Attention Scenario Issues, you will need to run 2 Kusto queries and compare.
let cxAccountID = "xxx";
ZTDDevice_Snapshot
| where ZTDDeviceRemediationStateLastModifiedDateTime  > ago(30d)
| where AccountId == cxAccountID
| where ZTDDeviceRemediationState == '1' // Change to 2 for Fix Pending, or 3 for Attention required. 1 obviously means normal

let cxDeviceID = "xxx";
Device_Snapshot
| where * has cxDeviceID

//  Once you have ran the first query, scroll right to the end and look for the column "ZTDDeviceRemediationStateLastModifiedDateTime"
//    Take down the times and then run
IntuneEvent
| where env_time > ago(30d)  // Insert time range
| where ApplicationName == "WindowsAutopilot"
| where EventUniqueName == 'ProfileDownloadBlocked'
//| where Col1 has 'isHardwareMatch: True'
| where Col1 has 'CONTEXTIDHERE' // CONTEXT Tenant ID
| project env_time, env_cloud_name, env_cloud_role, ComponentName, EventUniqueName,DeviceId,ExceptionDetails = Message, EventDetails = Col1, env_seqNum, ActivityId

// Find support ticket ID, if restricted, or rCRI from ICM number
// If IsCustomerSupportEngagement AND IsRestricted == TRUE, then it is an rCRI
// If IsCustomerSupportEngagement == TRUE and IsRestricted == FALSE then normal CRI
// If IsCustomerSupportEngagement == FALSE and IsRestricted == TRUE then a 'restricted incident'
cluster('icmcluster.kusto.windows.net').database('IcmDataWarehouse').IncidentsSnapshotV2
//| where OwningTenantName == "Microsoft Intune"
| where SupportTicketId startswith "2603030040001811"
| project IncidentId, CreateDate, ModifiedDate, Status, IsCustomerSupportEngagement, IsRestricted, SupportTicketId, Title, SourceCreatedBy, OriginatingTenantName, OwningTeamName, OwningTenantName
//| take 1000

// RBAC related - relies on har info - bwatt
//WOMBOCOMBO - OHBABYATRIPLE
let _creationTime = datetime(2026-04-30T21:21:54);  // paste Date: value from HAR. Kusto will parse as datetime
let _clientRequestID = "xxx" ;                      // client-request-id: from HAR
let _start = datetime_add('minute', -3, _creationTime);
let _end = datetime_add('minute', 3, _creationTime);
let RBAC = union IntuneEvent, SecuritySubsystem, HttpSubsystem;
RBAC
| where env_time between (_start .. _end )
| where SourceNamespace == "IntunePE"  //IntuneFXP for GCCH
| where ActivityId == _clientRequestID
| where (Message startswith "Microsoft." or isnotempty(exception))
or (TaskName  == 'EffectiveUserPermissionAuthorizationValidationFailureEvent')
or (isnotempty(httpVerb))
| project env_time, TaskName, EventUniqueName, message = Message, exception ,callerMethod, ColMetadata, Col1,Col2, Col3, userId = UserId, deviceId = DeviceId, tenantId = AccountId, requiredPermission, FunctionName, ApplicationName, ComponentName, httpVerb, statusCode_long, url, cVBase, ActivityId, scenarioType, collectionName
| sort by env_time asc

// AUTOPILOT
// RUN in a365
// shows if a device has an assigned deployment profile
// IntuneProfileIdentifier => GUID of AP deployment profile assigned
// IntuneProfileAssignmentStatus => 0/1 shows if profile is assigned
let currentTenantId = '';
let DeviceID = '';
ZTDDevice_Snapshot_AMSUA0401 //update ASU
| where AccountId == currentTenantId
| where ZTDRegistrationId == DeviceID or DeviceDataStoreAzureADDeviceId == DeviceID or AADObjectId == DeviceID or CreateIntuneDeviceId == DeviceID
| project ZTDCreatedTime, ZTDRegistrationId, IntuneProfileIdentifier, IntuneProfileAssignmentStatus, ZTDOemManufacturer, ZTDModelName, IsDeleted, DeviceDeletionStatus, DeviceId, DeviceDataStoreAzureADDeviceId, AADObjectId

// grab correlationvector from registry
// in MdmDiagReport_RegistryDump.reg in mdmdiag.cab
//[HKEY_LOCAL_MACHINE\software\microsoft\provisioning\AutopilotPolicy]
//"AutopilotUpdateTimeout"=DWORD:001b7740
//"AutopilotCorrelationVector"="I/ZkJltpUkqYZUir.0"
//"AutopilotMode"=DWORD:00000000
// in my case it was confirming a "RequestErrorCode: 807 - ZtdDeviceIsNotRegistered" was being tossed
let apsCv = 'ZkJltpUkqYZUir.0';
let downloadDateTime = datetime('2026-05-08');
let timeBeforeMinutes = 600;
let timeAfterMinutes = 600;
let startTime = datetime_add('minute', 0 - timeBeforeMinutes, downloadDateTime);
let endTime = datetime_add('minute', timeAfterMinutes, downloadDateTime);
cluster('intune.kusto.windows.net').database('Intune').IntuneEvent
| where env_time between(startTime..endTime)
| where ApplicationName == 'WindowsAutopilot'
| where env_cloud_environment == 'PE'
| where Col2 contains apsCv
| order by env_time desc
| project env_time, ActivityId, ServiceName, EventUniqueName, ColMetadata, Col1, Col2, Col3, FunctionName, FileName, LineNumber

// VPP app license troubleshooting
let IntuneASU        = "";
let IntuneVPPToken   = "";
let IntuneDeviceID   = "";
let SerialNumber     = "";
let AdamId           = "544007664";              // YouTube (swap per app)
let AppBundleId      = "com.google.ios.youtube"; // must match AdamId
let tStart           = datetime(2026-06-16 20:00);
let tEnd             = datetime(2026-06-19 20:35);
// 1) Intune -> Apple: license association request (AppleVPP service)
let s1 = IntuneEvent
    | where env_time between (tStart..tEnd) and env_cloud_name == IntuneASU
    | where EventUniqueName == "ManageLicensesFinished" and Col1 == IntuneVPPToken
    | where Col4 == SerialNumber and Col3 has AdamId
    | project env_time, Seq=1, Source="1 Intune Associate", Action=tostring(Col2), Detail=strcat("adamIds=", tostring(Col3));
// 2) Apple result: raw notification (type + result)
let s2 = IntuneEvent
    | where env_time between (tStart..tEnd) and env_cloud_name == IntuneASU
    | where EventUniqueName == "RawAppleNotification" and Col3 has SerialNumber and Col3 has AdamId
    | extend n = parse_json(tostring(Col3))
    | project env_time, Seq=2, Source="2 Apple Result",
              Action=strcat(tostring(n.notification.type)," / ",tostring(n.notification.result)),
              Detail=strcat("adamId=", tostring(n.notification.assignments[0].adamId));
// 3) Device check-in: install command issue + result (joined on CmdUUID)
let inst = DeviceManagementProvider
    | where env_time between (tStart..tEnd) and env_cloud_name == IntuneASU and ActivityId == IntuneDeviceID
    | where commandType == "InstallApplication" and subType == "DeepLink (VPP)"
    | extend CmdUUID = extract(@"CmdUUID:'([^']+)'",1,message),
             BundleId = extract(@"identifier ([^ ]+) with CmdUUID",1,message);
let s3 = inst | where EventId==5782 | project CmdUUID, BundleId, env_time
    | join kind=inner (inst | where EventId==5783 | project CmdUUID, rt=env_time, commandResultStatus, errorCode) on CmdUUID
    | where BundleId == AppBundleId
    | project env_time=rt, Seq=3, Source="3 Device InstallApp",
              Action=commandResultStatus, Detail=strcat("errorCode=", errorCode);
// 4a) AppleVPP sync: EXACT per-device license removed (token / asset / target-serial)
let s4 = IntuneEvent
    | where env_time between (tStart..tEnd) and env_cloud_name == IntuneASU
    | where EventUniqueName == "DeletedCosmosEntityBatch"
    | where (Col1 == IntuneVPPToken or PayLoadId == IntuneVPPToken or Col3 has IntuneVPPToken)
    | where Col3 has AdamId and Col3 has SerialNumber
    | mv-expand ent = parse_json(tostring(Col3)) to typeof(string)
    | extend target = extract(@"TargetId='([^']+)'",1,ent), asset = extract(@"AdamId='([^']+)'",1,ent)
    | where target == SerialNumber and asset == AdamId
    | summarize env_time = min(env_time) by target, asset
    | project env_time, Seq=4, Source="4 AppleVPP LicenseSync",
              Action="REMOVE license",
              Detail=strcat("token=", IntuneVPPToken, "  asset=", asset, "  target(serial)=", target);
// 4b) (context) the reconciliation summary line with the delete count
let s4b = IntuneEvent
    | where env_time between (tStart..tEnd) and env_cloud_name == IntuneASU
    | where EventUniqueName == "LicenseUpdateStatus" and (Col1 == IntuneVPPToken or PayLoadId == IntuneVPPToken)
    | where Col2 == AdamId and Col3 == "DeviceLicenseDto"
    | project env_time, Seq=5, Source="4 AppleVPP LicenseSync (count)", Action="LicenseUpdate", Detail=tostring(Message);
union s1, s2, s3, s4, s4b
| sort by env_time asc, Seq asc
| project env_time, Source, Action, Detail