Let’s find a list of devices that have SCCM CMPivot Query Patches Installed in last 90 Days. The ConfigMgr CMPivot provides real-time data based on fast-channel architecture.
The Configuration Manager CMPivot tool allows us to assess the state of devices quickly. When a query is executed against a device collection, CMPivot promptly runs the query in real-time on all currently connected (online) devices within the selected collection.
CM Pivot in SCCM allows real-time queries on devices for troubleshooting and compliance without network disruptions. However, it is initially challenging due to using the Kusto Query Language and an unfamiliar data flow model for the tabular expression statement.
The following blog post provides details on CMPivot troubleshooting and log files. It also includes details on CMPivot architecture and communication:
SCCM CMPivot Architecture Fast Channel Making | ConfigMgr.
| Index |
|---|
| Launch SCCM CMPivot Query Tool |
| Results -SCCM CMPivot Query Patches Installed in Last 90 Days |
Launch SCCM CMPivot Query Tool
Navigate to the device collection against which you want to run the CMPivot query.
- Select All Systems device collection.
NOTE! – I don’t recommend using the All System collection in a production environment with more than a few devices. Instead, use the collection with fewer devices to try out the query.
Right-click on All Systems collection.
- Select Start CMPivot.
CMPivot Query tool was launched. Get familiarized with new query language and CMPivot tool.
- Copy any of the following queries as required.
- Check out the Run Query button from the SCCM CMPivot tool.
Depending on the number of devices and complexity of the query, the CMPivot query shall take time to return the results. More details are available in the following blog post. SCCM CMPivot Architecture Fast Channel Making | ConfigMgr.
| Period | Query |
|---|---|
| Last 90 Days (3 Months) | QuickFixEngineering | where InstalledOn >= ago(90d) |
| Last 60 Days (2 Months) | QuickFixEngineering | where InstalledOn >= ago(60d) |
| Last 30 Days (1 Month) | QuickFixEngineering | where InstalledOn >= ago(30d) |
Results -SCCM CMPivot Query Patches Installed in Last 90 Days
When is the patch installed? You can check the column Installed On from the table below. As you can see above, we used the CMPivot query to discover the patches installed on Windows 10 devices 90 days before.
| Device | Caption | Description | Fix Comments | HotFix ID | Install Date | Installed By | Installed On |
|---|---|---|---|---|---|---|---|
| PROD-WIN20 | http://support.microsoft.com/?kbid=4552925 | Update | KB4552925 | 5/11/2020 | |||
| PROD-WIN20 | http://support.microsoft.com/?kbid=4537759 | Security Update | KB4537759 | 5/11/2020 | |||
| PROD-WIN20 | http://support.microsoft.com/?kbid=4557968 | Security Update | KB4557968 | 5/11/2020 | |||
| PROD-WIN20 | http://support.microsoft.com/?kbid=4556803 | Update | KB4556803 | 5/11/2020 | |||
We are on WhatsApp. To get the latest step-by-step guides and news updates, Join our Channel. Click here –HTMD WhatsApp.
Author
Anoop C Nair is Microsoft MVP! He is a Device Management Admin with more than 20 years of experience (calculation done in 2021) in IT. He is a Blogger, Speaker, and Local User Group HTMD Community leader. His primary focus is Device Management technologies like SCCM 2012, Current Branch, and Intune. He writes about ConfigMgr, Windows 11, Windows 10, Azure AD, Microsoft Intune, Windows 365, AVD, etc.
Thanks for sharing the excellent information
Patches Installed within the last 30 Days! (1 Month)
QuickFixEngineering | where InstalledOn >= ago(60d) —> correct for 30 days 🙂
Thank you for the article, do you perhaps know how to implement this: https://configurationmanager.uservoice.com/forums/300492-ideas/suggestions/39003364-cmpivot-add-an-option-to-provide-a-code-signing-c
It’s been completed for a while, but no mention of the version that supports it or how to configure it.
Wait, this doesn’t function as shown. InstalledOn is a string, this comparison doesn’t do what you think it does. Please correct me if I’m wrong.
This is what I think https://docs.microsoft.com/en-us/windows/win32/cimwin32prov/win32-quickfixengineering