Identifying devices managed by Intune but not reporting to WUfB using KQL

I recently had a conversation with a customer about Windows Update for Business (WUfB) deployment services and devices managed by Intune. Customer using MECM/SCCM and all of the WUfB workloads are moved to Intune.  If you'd like to learn more about WUfB, you can refer to the official documentation here.

During our discussion, we focused on improving the security posture of devices through timely Windows updates. To enhance compliance with Windows Update policies, the first step is to gather statistics on the update status of Intune-managed devices.

A few months ago, I shared a post on how to identify whether Intune devices are managed or unmanaged by WUfB using KQL and Azure Workbooks. For details, you can read the blog post here. However, that post didn't cover devices managed by Intune but not reporting to WUfB services. Gaining visibility into these devices is crucial for ensuring they are properly patched and secured through Windows Update.

In this blog post, we will explore how to use Kusto Query Language (KQL) to identify Intune-managed devices that are not reporting to WUfB deployment services. This will help IT administrators ensure that all managed devices are up-to-date, compliant with organizational policies, and receiving timely Windows updates.

For this KQL, we will use 2 schema tables 1)UCClient and 2)IntuneDevices (This requires you to enable diagnostic settings in intune tenant administration)

Below is a detailed explanation of the query along with the steps and logic used to filter and categorize the devices.

Step-by-Step Breakdown of the KQL Query

1. Define the Date Range:
   • The query begins by defining the date range for the analysis. This is used in the query to identify of the device is active or inactive based on the last check-in period with intune.

   let startDate = ago(21d); let endDate = now();

2. Summarize the Latest Device Records:
   • We summarize the IntuneDevices table to get the most recent record for each device.

   IntuneDevices | summarize arg_max(TimeGenerated, *) by DeviceName

3. Filter the Devices:
   • We apply several filters to narrow down the list of devices:
     • OS == "Windows": Only include Windows devices.
     • DeviceName !contains "#" and isnotempty(DeviceName): Exclude devices with invalid names.
     • ManagedBy != "MDE": Exclude devices managed by Microsoft Defender for Endpoint.
     • CompliantState != "ConfigManager": Exclude devices managed by Configuration Manager due to tenant attach.
     • todatetime(LastContact) between (ago(30d) .. now()): Only include devices that have checked in within the last 30 days.

   | where OS == "Windows" and DeviceName !contains "#" and isnotempty(DeviceName) and ManagedBy != "MDE" and CompliantState != "ConfigManager"

4. Extend Device Properties (Add new columns):
   • Add additional columns to provide more context about each device:
     • LastCheckin, EnrollmentDate: Format the dates.
     • OS: Determine the OS version (Windows 10 or Windows 11).
     • Chassis: Identify if the device is virtual or physical.
     • DeviceUrl: Create a URL for the device in the Intune portal.
     • DeviceType: Determine if the device is a desktop or laptop based on the presence of a WiFi MAC address.
     • Active: Determine if the device is active based on the last contact date.
     • DaysOld: Calculate the number of days since the last contact of the device with Intune.
     • CheckinCategory: Categorize devices based on the number of days since the last contact.
5. Join with UCClient Table:
   • Perform a left anti join with the UCClient table to exclude devices that are already reporting to WUfB.

   | join kind=leftanti ( UCClient | where DeviceName !contains "#" and isnotempty(DeviceName)) on DeviceName

6. Project the Final Output:
   • Select the relevant columns for the final output.

   | project DeviceName, DeviceId, PrimaryUser = UserEmail, UPN, Chassis, DeviceType, OS, OSVersion, ManagedBy, CompliantState, StorageFreeGB = StorageFree / 1024, Model, SerialNumber, EnrollmentDate, LastCheckin, CheckinCategory, Active, Encryption = EncryptionStatusString, JoinType, DeviceUrl

7. Here is the complete KQL query code that you use to run against your Intune log analytics workspace. For more information about how to run KQL queries in Log analytics or integrate KQL with PowerBI, please refer Microsoft documentation. https://learn.microsoft.com/en-us/azure/azure-monitor/logs/get-started-queries

8. let startDate = ago(21d);
   let endDate = now();
   IntuneDevices
   | summarize arg_max(TimeGenerated, *) by DeviceName
   | where OS == "Windows" and DeviceName !contains "#" and isnotempty(DeviceName) and ManagedBy != "MDE" and CompliantState != "ConfigManager"
   | extend LastCheckin=format_datetime(todatetime(LastContact), 'M/dd/yyyy')
   | extend EnrollmentDate=format_datetime(todatetime(CreatedDate), 'M/dd/yyyy')
   | extend OS = iff((OSVersion startswith "10.0.2"), "Windows 11", "Windows 10")
   | extend Chassis = iff((Model startswith "Virtual"), "Virtual", "Physical")
   | extend DeviceUrl = strcat('https://endpoint.microsoft.com/#blade/Microsoft_Intune_Devices/DeviceSettingsBlade/overview/mdmDeviceId/', DeviceId)
   | extend DeviceType = iif(isempty(WifiMacAddress) or isnull(WifiMacAddress), "Desktop", "Laptop")
   | extend Active = iff((todatetime(LastContact) between (startDate .. endDate)), "Active", "Inactive")
   | extend DaysOld = datetime_diff('day', now(), todatetime(LastContact))
   | extend CheckinCategory = case (
                                   DaysOld >= 30,
                                   "30 Days or Older",
                                   DaysOld >= 21,
                                   "21 Days or Older",
                                   DaysOld >= 14,
                                   "14 Days or Older",
                                   DaysOld >= 7,
                                   "7 Days or Older",
                                   "Less than 7 Days"
   )
   | join kind=leftanti(UCClient| where DeviceName !contains "#" and isnotempty(DeviceName)) on DeviceName
   | project DeviceName,DeviceId,PrimaryUser=UserEmail,UPN,Chassis,DeviceTypeWifi,OS,OSVersion,ManagedBy,CompliantState,StorageFreeGB=StorageFree/1024,Model,SerialNumber,EnrollmentDate,LastCheckin,CheckinCategory,Active,Encryption=EncryptionStatusString,JoinType,DeviceUrl

9. References and troubleshooting:

   Configure Telemetry https://learn.microsoft.com/en-us/windows/deployment/update/wufb-reports-configuration-intune

   Troubleshoot update rings https://learn.microsoft.com/en-us/troubleshoot/mem/intune/device-protection/troubleshoot-update-rings

   Windows Update for Business reports with Azure monitor logs https://learn.microsoft.com/en-us/windows/deployment/update/wufb-reports-use

   Conclusion:

   This KQL query helps identify devices that are managed by Intune but are not reporting to Windows Update for Business deployment services. By filtering and categorizing devices, IT administrators can proactively ensure that all managed devices remain up-to-date and compliant with organizational policies.

   The query uses various filters and joins to refine the device list and provide detailed insights into each device's status. This allows administrators to take appropriate actions to address non-reporting devices and bring them into compliance.

   Feel free to adapt this query to suit your organization's specific requirements. Happy querying!

   If you have custom requirement about KQL reporting/Azure workbooks/PowerBI Integration with Intune, please reach out to me.