Windows Feature and Quality update reporting made precise and simpel with Defender for Endpoint.

Creating Windows update reports for management has always been somewhat of a tricky thing to do. It can be a real headacke having to explain yet again, why some devices have not been updated yet, especially in these hybrid times where some devices might be managed by Configuration Manager and other devices are managed by Intune. The reporting in Intune not aligning with the reports in Configuration Manager or with Entra ID for that matter. It all adds to the confusion and lack of trust in the reports. But what other options do we have? Well, if you are using Defender for Endpoint and are onboarding all of your devices, then you are in luck. By utilizing Defender for Endpoint’s “Advanced Hunting” functionality, you can use KQL to generate the report for you without relying on CM, Intune or Entra.

Let’s start out by taking a look at how it looks. I personally like the Pie charts as they are easy for everyone to understand. These are examples of the Pie charts that I put into my report:

Feature updates:

Quality Updates:

The great thing with these reports are that:

• They only show the devices that has been active in the last 30 days (as default) which means you won’t have devices shown that has not been connected to your environment (Devices that you coulden’t possibly have managed). So people who are on leave and devices hidden in closets etc. won’t show in the report.
• Defender updates the inventory about once and hour where Configuration Manager and especially Intune is much slower (Intune automatically syncs about every 8 hours). So you have much more up-to-date information here.
• You can check up on your whole fleet in one view as Defender does not care from where the device is managed.
• You can combine the Pie chart with the actual tabels that the report generates. This is especially helpfull for the Quality update report in order to narrow down the exact build versions deployed in your environmnet.

So, time for the good stuff. How to make these reports?

1. Log in to the defender portal https://Security.microsoft.com
2. Browse to Hunting -> Advanced hunting
3. Click on “Create new”
4. Type in one of the queries below (Feature update or Quality update)

Feature update:
Make sure to update the <Your Domain> to match your domain name in line 5 & 6. For example: “contoso.com”.

// Windows version overview
// Start by selecting all laptops, then join with software inventory to detect Windows version
DeviceInfo
//| where (IsAzureADJoined == 1 and JoinType == "AAD Joined") // get all devices that are AAD Joined
//| where DeviceName contains "<Your Domain>" // get all devices that are AD joined
| where DeviceName contains "<Your Domain>" or (IsAzureADJoined == 1 and JoinType == "AAD Joined") // get all devices that are AD or AAD Joined
| project DeviceId, DeviceName, Timestamp
| join ( // join with Software inventory go the solid Windows version info (use regular join as laptops with no SoftwareInventory are not relevant for overall insights)
    DeviceTvmSoftwareInventory
    | where SoftwareName in ("windows_10", "windows_11")
    | project DeviceId, SoftwareName, SoftwareVersion, EndOfSupportStatus, EndOfSupportDate 
    ) on  DeviceId
| extend DeviceNameShort = tostring(array_slice(split(DeviceName, "."), 0, 1)[0]) // make sure we only see the laptop once, if been through onboarding to intune
| order by DeviceNameShort asc, Timestamp desc
| extend Rank = row_number(1, DeviceNameShort != prev(DeviceNameShort))
| where Rank == 1 // get the latest DeviceID and name
| extend DeviceIdent = strcat(DeviceName, " (", DeviceId, ")")
| extend NormalizedVersion = strcat_array(array_slice(split(SoftwareVersion, "."), 0, 2), ".") // get Windows technical version
| extend VersionName = // Map version to brand version
    replace("10.0.19045", "W10-22H2", 
    replace("10.0.19044", "W10-21H2", 
    replace("10.0.19043", "W10-21H1", 
    replace("10.0.19042", "W10-20H2",
    replace("10.0.19041", "W10-20H1",  
    replace("10.0.18363", "W10-1909", 
    replace("10.0.18362", "W10-1903", 
    replace("10.0.17134", "W10-1803", 
    replace("10.0.16299", "W10-1709", 
    replace("10.0.14393", "W10-1607", 
    replace("10.0.22000", "W11-21H2",
    replace("10.0.22621", "W11-22H2",
    replace("10.0.22631", "W11-23H2",
    NormalizedVersion
    )))))))))))))
| summarize DeviceCount = count(), 
    DeviceNames=make_set(DeviceName) , DeviceIds=make_set(DeviceId) 
//    Devices = make_set(DeviceIdent)
    by VersionName, NormalizedVersion, EOS_Status = iff(EndOfSupportStatus == "", EndOfSupportStatus, strcat(EndOfSupportStatus, " (", format_datetime(EndOfSupportDate, "dd-MM-yyyy"), ")"))
| order by DeviceCount desc, VersionName asc

Quality update:
Make sure to update the <Your Domain> to match your domain name in line 5 & 6. For example: “contoso.com”.

// Windows version / QualityVersion overview
// Start by selecting all laptops, then join with software inventory to detect Windows version
DeviceInfo
//| where (IsAzureADJoined == 1 and JoinType == "AAD Joined") // get all devices that are AAD Joined
//| where DeviceName contains "<Your Domain>" // get all devices that are AD joined
| where DeviceName contains "<Your Domain>" or (IsAzureADJoined == 1 and JoinType == "AAD Joined") // get all devices that are AD or AAD Joined
| project DeviceId, DeviceName, Timestamp
| join ( // join with Software inventory go the solid Windows version info (use regular join as laptops with no SoftwareInventory are not relevant for overall insights)
    DeviceTvmSoftwareInventory
    | where SoftwareName in ("windows_10", "windows_11")
    | project DeviceId, SoftwareName, SoftwareVersion, EndOfSupportStatus, EndOfSupportDate 
    ) on  DeviceId
| extend DeviceNameShort = tostring(array_slice(split(DeviceName, "."), 0, 1)[0]) // make sure we only see the laptop once, if been through onboarding to intune
| order by DeviceNameShort asc, Timestamp desc
| extend Rank = row_number(1, DeviceNameShort != prev(DeviceNameShort))
| where Rank == 1 // get the latest DeviceID and name
| extend DeviceIdent = strcat(DeviceName, " (", DeviceId, ")")
| extend NormalizedVersion = strcat_array(array_slice(split(SoftwareVersion, "."), 0, 2), ".") // get Windows technical version
| extend QualityVersion = strcat_array(array_slice(split(SoftwareVersion, "."), 3, 3), ".")
| extend VersionName = // Map version to brand version
    replace("10.0.19045", "W10-22H2",
    replace("10.0.19044", "W10-21H2", 
    replace("10.0.19043", "W10-21H1", 
    replace("10.0.19042", "W10-20H2",
    replace("10.0.19041", "W10-20H1",  
    replace("10.0.18363", "W10-1909", 
    replace("10.0.18362", "W10-1903", 
    replace("10.0.17134", "W10-1803", 
    replace("10.0.16299", "W10-1709", 
    replace("10.0.14393", "W10-1607", 
    replace("10.0.22000", "W11-21H2",
    replace("10.0.22621", "W11-22H2",
    replace("10.0.22631", "W11-23H2",
    NormalizedVersion
    )))))))))))))
| summarize DeviceCount = count(), 
    DeviceNames=make_set(DeviceName) , DeviceIds=make_set(DeviceId) 
//    Devices = make_set(DeviceIdent)
    by VersionName, NormalizedVersion, QualityVersion, EOS_Status = iff(EndOfSupportStatus == "", EndOfSupportStatus, strcat(EndOfSupportStatus, " (", format_datetime(EndOfSupportDate, "dd-MM-yyyy"), ")"))
| order by DeviceCount desc, VersionName asc
| order by VersionName desc, QualityVersion desc

When you run the query you will get a result similar to this:

If you click “Chart Type” (highlighted in red above) you can go and select a “Pie” chart and get your nice looking Pie chart directly in defender. Screen grab it and put it into your report.

Bonus:
If you only want to show device that are only AD joned or only AAD joined, you can play around with Line 4,5 and 6. But only have one of those lines active when you run the query.

Note that the query will also show you EOS_Status (End of Service) which is very handy for chasing the devices that are no longer being supported.

Also remember to update the KQL query when a new Feature update or a new Operating System is released.

Now go have fun with greating some simple, trustworthy and decent looking reports for your management :).