Let’s discuss the best troubleshooting of patching issues with Intune Pivot KQL queries if you are in charge of sending updates, like patches, to many devices. You and your team have been working hard to make this updating process.
Now, around 90% of devices smoothly get the updates. But the 10% is not working well. What do you do now? You are probably writing reports, building complex queries, using your Power BI skills, and jumping whatever to get to the last 10%.
Device query can make that simple. With the help of a device query, you can easily find and fix the last 10% without doing all the complicated and confusing stuff—no need for complex reports. Sangeetha Visweswaran talked about device queries at a Microsoft Ignite event.
This Intune Pivot query, a particular search or investigation tool, helped her find out the root cause of her investigation. Intune Pivot Query is a helpful tool that provides real-time reporting and troubleshooting capabilities. It is a beneficial tool for detecting your computer or device issues. It’s like a troubleshooting assistant similar to the SCCM CMPivot tool.
- SCCM Collection for Windows 11 22H2 Devices | CMPivot | SQL Queries
- Intune Anomaly Detection Device and Advanced Analytics
- Use SCCM CMPivot to Perform Security Audits Created New Local User Account | Tried to Reset Password
Why is Patching Important for Devices?
Patching helps keep devices up-to-date and secure by fixing vulnerabilities and improving performance.
Troubleshoot Patching Issues with Intune Pivot KQL Queries
We have seen several posts about troubleshooting Intune patching issues using Log Analytics and KQL queries. We have also seen Copilot troubleshooting options to make admins life easy. This is a bit different and this is real time troubleshooting and remediation using Intune Pivot.
- To start, go to the Intune Admin Center portal by visiting https://intune.microsoft.com/.
- Once there, click “Devices” and then choose “Intune Pivot.” This takes you to a window with all sorts of information about your devices.
Intune Pivot KQL Query to find Patch is missing from a device
Intune Pivot KQL Query to find Patch is missing from a device. After selecting Intunepivot, you will get the inventory information in the page below.
You want to know how many devices are missing this particular software update or upgrade package. To find out, you use a “Kusto/KQL Query. ” Example given below is KB5029263.
- Where isPatched ==0
- Click the Run button
- The Results section shows all the results
Device| join os_version on device
| where platform == "windows"
| join (Windows_update_history | project-rename patch_title = ['title'])
on device
| extend has8b = iff(patch_title contains "KB5029263", 1, 0)
| summarize isPatched=max(has8b) by device
where ispallched == 0
Realtime Intune KQL Query to find missing patches
The page below shows KQL query and results to find out how many devices are missing the patch (KB5029263). The summarize count() query allows you to check how many devices are missing the patch or update.
- 122 devices don’t have the patch
Device| join os_version on device
where plattorm == "windows"
| join (Windows_update_history | project-rename patch_title = ['title'])
on device
| extend has8b = iff(patch_title contains "KB5029263", 1, 0)
| summarize isPatched=max(has8b) by device
where isPatched == 0
summarize count()
Intune Realtime KQL Query to find out Last Check-in Time
The query “summarize devicecount = dcount(device) by lastcheckinRange=bin(lastcheckin, id)” helps you to find the lastcheckinRange details and the Device count. There are 2 last CheckinRange 2023-08-30T00.00.00Z and 2023-08-29T00.00.
Device| join os_version on device
where plattorm == "windows"
| join (Windows_update_history | project-rename patch_title = ['title'])
on device
| extend has8b = iff(patch_title contains "KB5029263", 1, 0)
summarize isPatched=max(has8b) by device
| where isPatched == 0
| join Device on device| extend lastCheckin = todatetime(last_check_in)
summarize deviceCount = dcount(device) by lastCheckinRange=bin(lastCheckin, 1d)
Find out Reboot Pending Devices after Patching using Intune Pivot
Let’s Find out Reboot Pending Devices after Patching using Intune Pivot. When the query to find out why patches are not applied, it discovered that the most common reason is pending reboots.
After running the query, 9 devices are currently pending a reboot. This suggests that these devices may not have completed the necessary restart to finalize the patching process.
Device| join os_version on device
where plattorm == "windows"
| join (Windows_update_history | project-rename patch_title = ['title'])
on device
extend has8b = iff(patch_title contains "KB5029263", 1, 0)
summarize isPatched=max(has8b) by device
| where isPatched == 0
join Device on device| extend lastcheckin = todatetime(last_check_in)
where lastCheckin >= ago(1d)
summarize dcount(device) by reboot_pending
Windows Update Service is not Running
Let’s find out the devices which are having issues with Windows Update related services. You can use Intune Pivot and KQL query to find these details as shown below.
Device| join os_version on device
where plattorm == "windows"
| join (Windows_update_history | project-rename patch_title = ['title'])
on device
extend has8b = iff(patch_title contains "KB5029263", 1, 0)
summarize isPatched=max(has8b) by device
| where isPatched == 0
| join Device on device| extend IastCheckin = todatetime(last_check_in)
| where lastcheckin >= ago(1d)
| where reboot_pending == 'false'
| join (Services | project-rename service_name = name, service_status = status) on device
where service_name in ("TrustedInstaller", "wuauserv", "DoSvc")
where service_status != "RUNNING"
distinct device
Fix the Reboot Pending Issue with Intune Pivot Remediation option
Let’s fix the Reboot Pending Issue with Intune Pivot Remediation option as shown below. This approach allows you to identify the specific devices that need attention.
In the extended query, you can specifically check for the 9 devices that were last checked in within the past day and are currently pending a reboot.
With this information, You can immediately reboot these devices and ensure the pending patches are applied successfully. You can efficiently address the issue and keep the systems current.
NOTE! – “Are you sure you want to restart these devices? Users will not be automatically notified of the restart and might lose unsaved work”
- Enter the Query that “Where reboot_pending == ‘true‘
- Select the Reboot device tab from the below window
- From the pop-up window, select Yes.
Device| join os_version on device
where plattorm == "windows"
| join (Windows_update_history | project-rename patch_title = ['title'])
on device
extend has8b = iff(patch_title contains "KB5029263", 1, 0)
summarize isPatched=max(has8b) by device
| where isPatched == 0
| join Device on device| extend IastCheckin = todatetime(last_check_in)
| where lastcheckin >= ago(1d)
| where reboot_pending == 'true'
distinct device
Enable Windows Update Services using Intune Pivot Remediation Option
The 9 devices were fixed with the above reboot remediation option. But many more still need fixing. Now, let’s check how to Enable Windows Update Services using Intune Pivot Remediation Option.
The second common issue is that the update services are not running on these devices. It is essential to find out how many devices are affected, and from there, you can take action to fix the updated services.
After figuring out which devices have the update services issue, you can take action to fix them. There is a one-click solution to repair Windows update services, making it easy to sort out the remaining 10 per cent of devices causing trouble.
- Click the Run remediation menu option from the below window
Run Intune Pivot Remediation options
Deploy a remediation script package to this device using detection and remediation scripts. To manage the script packages on this screen, go to Proactive Remediations.
- Select the Repair Windows Update Service option from the below window
- After that, click the Run Remediation Button
| Intune Pivot Remediation Script Name | Description |
|---|---|
| Disable print spooler | This action will temporarily disable the Windows Print Spooler service |
| Repair Windows Update Service | Enable and restart NT services needed for Windows Update. |
| Restart stopped Office C2R svc | If service is stopped, try to start it. If not auto-start, change to automatic. Important since Win32 OPP won’t… |
| Sample health script | |
| Update stale Group Policies | If GP refresh was > 7 days ago, then gpupdate. IT can customize the 7 day threshold. Important for reducing… |
Fortified security and simplicity come together with Microsoft Intune
We are on WhatsApp. To get the latest step-by-step guides and news updates, Join our Channel. Click here – HTMD WhatsApp.
Author
About the Author – Vidya is a computer enthusiast. She is here to share quick tips and tricks with Windows 11 or Windows 10 users. She loves writing on Windows 11 and related technologies. She is also keen to find solutions and write about day-to-day tech problems.