Managing Intune managed device health, compliance, and updates across an organization can be a complex task, especially when dealing with various data sources. By integrating Intune device data with Windows Update for Business (WUfB) data in Log Analytics, you can gain powerful insights into the status of your devices, their security compliance, and update history. This approach simplifies reporting and troubleshooting by combining device inventory information with patch management in a centralized location.
Before diving into the combing of the Intune data and WUfB data, here’s what you’ll need:
Prerequisites:
- Intune Logs in Log Analytics: You must configure Intune to send its logs to Log Analytics. If you haven't set this up yet, check out the Microsoft documentation on routing logs to Azure Monitor using Intune or Identify Users’ Enrolled Mobile Devices (iOS and Android) with KQL – All about Endpoint Management for step-by-step instructions.
- WUfB Configured with Log Analytics: Similarly, ensure that Windows Update for Business (WUfB) is enabled and configured to send its data to the same Log Analytics workspace as Intune. If you haven't done so already, follow the WUfB setup guide to get started. This ensures both Intune and WUfB data exist in the same workspace, making it easier to correlate the data. If you have configured the WUfB log analytics to different workspace, it requires to combine 2 different log analytics workspace which is have not covered in this blog post and also not explored much.
Once you have both Intune and WUfB data in Log Analytics, you can unlock valuable insights. Here's an overview of the key device attributes covered in the blog post to analyze:
Key Insights from combined Intune and WUfB Data:
- Device Information: Device Name, Primary User, Model, Serial Number, OS version, Country, and Chassis type (Physical/Virtual).
- Security and Compliance: Compliant State, Encryption Status, Readiness Status, Patch Deferrals, and WUfB management status.
- User Activity: Enrollment Date, Last Check-in, Active Status (e.g., whether the device is currently active or inactive), and Last Patch Acknowledgment (when the device was offered a patch).
- Operational Insights: Free storage space, device URL for quick access, last Windows update scan time, and more.
Why Combine Intune and WUfB Data?
With the data from Intune and WUfB in one place, you can generate insights that are otherwise difficult to extract when required. For example, if someone inquires about the last update installed on a specific device or last device active status, you can easily find out when it was offered a patch, whether the patch was installed, or if there were any issues with the update.
When you bring together Intune device data and WUfB data, it’s much easier to answer questions like:
- What is the current status of this device's compliance?
- When was the last security update applied to this device?
- Is the device actively being used or is it inactive?
- What is the status of the device’s patch management?
Where does this data gets stored?
The listed below are the tables used to formulate the KQL query
IntuneDevices: Stores the intune device information
UCClient: Stores WUfB client policy configuration data
UCClientUpdateStatus: Stores update compliance information
UCClientReadinessStatus: Stores information about the update readiness for specific target OS version.
Kusto Query:
You can use this KQL query and Integrate with Power BI or Azure Workbooks for better visualizations and provide an interactive and dynamic way to analyze your device data. Support teams can view up-to-date metrics, compliance statuses, and patching information on-demand, allowing for quicker troubleshooting and decision-making.
The query excludes the devices that are not Intune managed (such as MDE only) and devices with tenant attach (SCCM) and devices with no hostnames/null. The query also had the country name based on the device hostname. You can make changes according to your needs how you want.
If the device last check-in time with Intune is more than 30 days,i mark it as Inactive otherwise Active. Date format shown in the report is dd/M/YYYY.
let DaysBack =ago(30d); // Declare a variable holding the number of days device last check-in with intune
IntuneDevices | summarize arg_max(TimeGenerated,) by SerialNumber,DeviceName
|where OS=="Windows" and not(DeviceName has_any("#")) and isnotempty(DeviceName) and ManagedBy != "MDE" and CompliantState !="ConfigManager"
| extend Country=
iff(DeviceName startswith "L-SG","Singapore",iff(DeviceName startswith "L-HK","HongKong",iff(DeviceName startswith "L-IN","India","Unknown")))
| 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 LastCheckin=format_datetime(todatetime(LastContact), 'dd/M/yyyy HH:mm:ss tt')
| extend EnrollmentDate=format_datetime(todatetime(CreatedDate), 'dd/M/yyyy HH:mm:ss tt')
| extend Active = iff( (todatetime(LastContact) between (DaysBack .. now() )), "Active", "Inactive")
//| where (Country =="Singapore")
| join kind=leftouter (UCClient | summarize arg_max(TimeGenerated,) by DeviceName| extend OSName=OSVersion) on $left.DeviceName==$right.DeviceName
| join kind=leftouter (UCClientUpdateStatus | where UpdateCategory == "WindowsQualityUpdate" and UpdateClassification == "Security"| where ClientState =="Installed"
| summarize arg_max(UpdateReleaseTime,TimeGenerated,) by DeviceName) on $left.DeviceName==$right.DeviceName
| join kind=leftouter (UCClientReadinessStatus | summarize arg_max(TimeGenerated,) by DeviceName ) on $left.DeviceName==$right.DeviceName
| extend LastCensusScanTime=format_datetime(todatetime(LastCensusScanTime), 'dd/M/yyyy HH:mm:ss tt')
| extend LastWUScanTime=format_datetime(todatetime(LastWUScanTime), 'dd/M/yyyy HH:mm:ss tt')
| extend WUfB = iff(isempty(WUQualityDeferralDays) or isempty(WUQualityDeadlineDays),
"Not Reporting",
iff((WUQualityDeferralDays != "-1" or WUQualityDeadlineDays != "-1"),
"Managed",
"Not Managed"))
| extend DeviceTypeWifi = iif(isempty(WifiMacAddress) or isnull(WifiMacAddress), "Desktop", "Laptop")
| project DeviceName,DeviceId,PrimaryUser=UserEmail,Country,Chassis,DeviceTypeWifi,OS,OSVersion,ManagedBy,CompliantState,StorageFreeGB=StorageFree/1024,Model,SerialNumber,EnrollmentDate,LastCheckin,Active,Encryption=EncryptionStatusString,
JoinType,ReadinessStatus,ReadinessReason,WUfB,LastWUScanTime,PatchDeferral=WUQualityDeferralDays
,LastPatchACK=UpdateDisplayName,DeviceUrl,OSFeatureUpdateStatus,OSQualityUpdateStatus,OSSecurityUpdateStatus,WUQualityDeferralDays,WUQualityDeadlineDays,WUQualityGracePeriodDays,WUQualityPauseState,IsDeviceHotpatchEnrolled
Output of the KQL Query:
References:
Log analytics tables Azure Monitor Logs reference - IntuneDevices - Azure Monitor | Microsoft Learn
If you have any custom report requirements of using intune to integrate with PowerBI or Azure workbook, please leave a comment.
Hope you find this article useful!