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:

Table Category Description
UCClient Device
record
UCClient
acts as an individual device's record. It contains data such as the currently
installed build, the device's name, the operating system edition, and active
hours (quantitative).
UCClientReadinessStatus Device
record
UCClientReadinessStatus
is an individual device's record about its readiness for updating to Windows
11. If the device isn't capable of running Windows 11, the record includes
which Windows 11 hardware requirements the device doesn't meet.
UCClientUpdateStatus Device
record
Update
Event that combines the latest client-based data with the latest service-based
data to create a complete picture for one device (client) and one
update.
UCDeviceAlert Service
and device record
These
alerts are activated as a result of an issue that is device-specific. It isn't
specific to the combination of a specific update and a specific device. Like
UpdateAlerts, the AlertType indicates where the Alert comes from such as a
ServiceDeviceAlert or ClientDeviceAlert.
UCDOAggregatedStatus Device
record
UCDOAggregatedStatus
is an aggregation of all individual UDDOStatus records across the tenant and
summarizes bandwidth savings across all devices enrolled using Delivery
Optimization and Microsoft Connected Cache.
UCDOStatus Device
record
UCDOStatus
provides information, for a single device, on its bandwidth utilization across
content types in the event they use Delivery Optimization and Microsoft
Connected Cache.
UCServiceUpdateStatus Service
record
Update
Event that comes directly from the service-side. The event has only
service-side information for one device (client), and one update, in one
deployment.
UCUpdateAlert Service
and device records
Alert
for both client and service update. Contains information that needs attention,
relative to one device (client), one update, and one deployment, if relevant.
Certain fields may be blank depending on the UpdateAlert's AlertType field. For
example, ServiceUpdateAlert won't necessarily contain client-side statuses and
may be blank.

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")

image

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.

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

    1. Hi,
      If you have enabled windows update for business in log analytics and deployed the configuration profile with telemetry settings to your devices, you can run the KQL query in the log analytics.
      KQL is log analytics and not part of intune.

      Thanks,
      Eswar

      Reply

Leave a Reply