SCCM CMPivot Query Patches Installed in Last 90 Days | ConfigMgr

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:

Patch My PC

SCCM CMPivot Architecture Fast Channel Making | ConfigMgr.

Index
Launch SCCM CMPivot Query Tool
Results -SCCM CMPivot Query Patches Installed in Last 90 Days
SCCM CMPivot Query Patches Installed in Last 90 Days | ConfigMgr – Table 1

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.

Adaptiva
  • Select Start CMPivot.
SCCM CMPivot Query Patches Installed in Last 90 Days | ConfigMgr - Fig.1
SCCM CMPivot Query Patches Installed in Last 90 Days | ConfigMgr – Fig.1

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.
SCCM CMPivot Query Patches Installed in Last 90 Days | ConfigMgr - Fig.2
SCCM CMPivot Query Patches Installed in Last 90 Days | ConfigMgr – Fig.2

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.

PeriodQuery
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)
SCCM CMPivot Query Patches Installed in Last 90 Days | ConfigMgr – Table 2

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.

DeviceCaptionDescriptionFix CommentsHotFix IDInstall DateInstalled ByInstalled On
PROD-WIN20http://support.microsoft.com/?kbid=4552925UpdateKB45529255/11/2020
PROD-WIN20http://support.microsoft.com/?kbid=4537759Security UpdateKB45377595/11/2020
PROD-WIN20http://support.microsoft.com/?kbid=4557968Security UpdateKB45579685/11/2020
PROD-WIN20http://support.microsoft.com/?kbid=4556803UpdateKB45568035/11/2020
SCCM CMPivot Query Patches Installed in Last 90 Days | ConfigMgr – Table 3
SCCM CMPivot Query Patches Installed in Last 90 Days | ConfigMgr - Fig.3
SCCM CMPivot Query Patches Installed in Last 90 Days | ConfigMgr – Fig.3

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.

5 thoughts on “SCCM CMPivot Query Patches Installed in Last 90 Days | ConfigMgr”

  1. Patches Installed within the last 30 Days! (1 Month)
    QuickFixEngineering | where InstalledOn >= ago(60d) —> correct for 30 days 🙂

    Reply
  2. 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.

    Reply

Leave a Comment

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