Close Menu
    Facebook X (Twitter) Instagram
    Tuesday, May 20
    X (Twitter) LinkedIn
    All about Endpoint Management
    • Home
    All about Endpoint Management
    Home»Intune»Using KQL – Creating Custom Workbooks to Distinguish intune wufb Managed from Unmanaged Devices

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

    Eswar KonetiBy Eswar KonetiSeptember 07, 2:06 pm5 Mins Read Intune 1,596 Views
    Share
    Facebook Twitter LinkedIn Reddit

    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.

    EMS intune Intune reports KQL Kusto Schemas tables UCClient Windows update for business workbooks wufb wufb managed
    Share. Twitter LinkedIn Email Facebook Reddit

    Related Posts

    Optimize Your Intune Workflow with a Powerful Browser Extension

    March 22, 10:39 am

    Troubleshooting Windows Hello for Business PIN Reset Issues – Something went wrong

    March 06, 9:48 pm

    Migrate Microsoft 365 Updates from SCCM/MECM to Intune for Co-Managed Devices

    February 11, 9:50 pm

    2 Comments

    1. Patrick on October 24, 2023 4:04 AM

      Pls, where in Intune console can I run this KQL report?

      Reply
      • Eswar Koneti on October 28, 2023 12:53 PM

        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 ReplyCancel reply

    This site uses Akismet to reduce spam. Learn how your comment data is processed.

    Sign Up

    Get email notifications for new posts.

    Author

    I’m Eswar Koneti ,a tech enthusiast, security advocate, and your guide to Microsoft Intune and Modern Device Management. My goal? To turn complex tech into actionable insights for a streamlined management experience. Let’s navigate this journey together!

    Support

    Awards

    Archives

    © Copyright 2009-2024 Eswar Koneti, All rights reserved.

    Type above and press Enter to search. Press Esc to cancel.