Using KQL – Creating Custom Workbooks to Distinguish intune wufb Managed from Unmanaged Devices

Windows Update for Business (WUfB) a feature within Microsoft Intune and Azure Log Analytics that allows organizations to generate and view detailed reports and insights related to the management of Windows updates for the devices.

WUfB reports workbooks are a part of the broader Windows Update for Business service and are designed to provide valuable data and analytics regarding Windows update deployments in an organization's environment.

For more information about the intune wufb reporting, please refer https://learn.microsoft.com/en-us/mem/intune/protect/windows-update-reports

While Intune offers some built-in reports that cover aspects of policy management, they often fall short when it comes to client-side WUfB patching statistics. This is where custom workbooks (KQL) shine, providing detailed data on patch install status, WUfB deferral days, grace periods, Intune last sync connection dates, and much more.

To embark on your journey of creating custom reports for WUfB, let's explore the treasure trove of Windows Update for Business tables available within Azure Log Analytics. Below, you'll find a list of essential tables

The following are the list of wufb tables available for you.

• UCClient
• UCClientReadinessStatus
• UCClientUpdateStatus
• UCDeviceAlert
• UCDOAggregatedStatus
• UCDOStatus
• UCServiceUpdateStatus
• UCUpdateAlert

Summary:

These tables form the foundation for creating custom workbooks using KQL, enabling you to share valuable insights with management and your technicians for investigative purposes.

In addition to the above wufb tables, there are intune tables (requires diagnostics to be enabled) that can be used to create powerful workbooks when joined with the WUfB tables.

Now that we've covered the basics of WUfB tables, let's focus on a specific requirement.

In this blog post, we'll demonstrate how to create a report distinguishing devices managed by WUfB from those that are unmanaged.

This request comes from a customer who also utilize SCCM and need to monitor which devices remain Intune WUfB managed versus SCCM managed (unmanaged in terms of WUfB).

When your WUfB workload is set to pilot mode (not all devices), monitoring patching statistics becomes tricky. This is because Intune or WUfB tables lack indications to determine whether a device is patched by WUfB or SCCM. Telemetry data is sent daily, but it doesn't reveal details about WUfB or Intune patching unless you've implemented a custom solution to gather this information.

In this post, we'll explore how to meet this requirement using built-in tables without the need for custom table creation.

To identify devices managed by WUfB through Intune policy deployment, we can rely on the UCClient table, which contains essential details like WUQualityDeferralDays, WUQualityGracePeriodDays, WUQualityDeadlineDays, and more. Any Intune-managed device targeted with a WUfB policy should have these parameters available, which are included in telemetry data.

After thorough testing, I've discovered that some devices managed by Intune WUfB may not have values for GracePeriodDays and DeadlineDays consistently which could be a bug or limitation. However, we can focus on WUQualityDeferralDays, which is reliably available for every Intune WUfB-managed device.

To find out if the device is managed by wufb (with intune wufb policy deployment), we can pick the table UCClient https://learn.microsoft.com/en-us/windows/deployment/update/wufb-reports-schema-ucclient

Below, you'll find a KQL query that you can use to create custom workbooks and employ this logic to generate patch statistics for Intune WUfB-managed devices exclusively.

UCClient

|where DeviceName !contains "#" and isnotempty(DeviceName)

| extend Wufb = iff( (WUQualityDeferralDays != "-1"), "Managed", "Not Managed")

| join kind=inner (UCClientUpdateStatus

|where DeviceName !contains "#" and isnotempty(DeviceName))

on DeviceName

| summarize Count=count_distinct (DeviceName) by Wufb

| as hint.materialized=true T

| union (T | summarize Count = sum(Count) by Wufb = "Total")

Stay tuned for more blog posts on using tables to create custom workbooks for both technicians and management, allowing you to monitor device statistics just as seamlessly as you do in SCCM SQL reporting.

I welcome your comments and insights into how you're utilizing custom KQL outside of default Intune reports to meet your unique requirements.