Troubleshoot Patching Issues with Intune Pivot KQL Queries

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

Patch My PC

Why is Patching Important for Devices?

Troubleshoot-Patching-Issues-with-Intune-Pivot-Device-Queries

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.
Troubleshoot Patching Issues with Intune Pivot KQL Queries - Fig.1 - Sangeetha Visweswaran Microsoft
Troubleshoot Patching Issues with Intune Pivot KQL Queries – Fig.1 – Sangeetha Visweswaran Microsoft

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.

Adaptiva
  • 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
Troubleshoot Patching Issues with Intune Pivot KQL Queries - Fig.2 - Sangeetha Visweswaran Microsoft
Troubleshoot Patching Issues with Intune Pivot KQL Queries – Fig.2 – Sangeetha Visweswaran Microsoft

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()
Troubleshoot Patching Issues with Intune Pivot KQL Queries - Fig.3 - Sangeetha Visweswaran Microsoft
Troubleshoot Patching Issues with Intune Pivot KQL Queries – Fig.3 – Sangeetha Visweswaran Microsoft

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)
Troubleshoot Patching Issues with Intune Pivot KQL Queries - Fig.4 - Sangeetha Visweswaran Microsoft
Troubleshoot Patching Issues with Intune Pivot KQL Queries – Fig.4 – Sangeetha Visweswaran Microsoft

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
Troubleshoot Patching Issues with Intune Pivot KQL Queries - Fig.5 - Sangeetha Visweswaran Microsoft
Troubleshoot Patching Issues with Intune Pivot KQL Queries – Fig.5 – Sangeetha Visweswaran Microsoft

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
Troubleshoot Patching Issues with Intune Pivot KQL Queries Fig. 5.1
Troubleshoot Patching Issues with Intune Pivot KQL Queries Fig. 5.1

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
Troubleshoot Patching Issues with Intune Pivot KQL Queries - Fig.6 - Sangeetha Visweswaran Microsoft
Troubleshoot Patching Issues with Intune Pivot KQL Queries – Fig.6 – Sangeetha Visweswaran Microsoft

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
Troubleshoot Patching Issues with Intune Pivot Device Queries - Fig.7 - Sangeetha Visweswaran Microsoft
Troubleshoot Patching Issues with Intune Pivot KQL Queries – Fig.7 – Sangeetha Visweswaran Microsoft

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 NameDescription
Disable print spoolerThis action will temporarily disable the Windows Print Spooler service
Repair Windows Update ServiceEnable and restart NT services needed for Windows Update.
Restart stopped Office C2R svcIf 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 PoliciesIf GP refresh was > 7 days ago, then gpupdate. IT can customize the 7 day threshold. Important for reducing…
Troubleshoot Patching Issues with Intune Pivot KQL Queries – Table 2
Troubleshoot Patching Issues with Intune Pivot Device Queries - Fig.8 - Sangeetha Visweswaran Microsoft
Troubleshoot Patching Issues with Intune Pivot Device Queries – Fig.8 – Sangeetha Visweswaran Microsoft

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.

Leave a Comment

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