Use SCCM CMPivot Query to Find Installed Patches in Days

Prajwal Desai
Posted by Prajwal Desai
SCCM CMPivot Query to Find Installed Patches in Days

In this post, you’ll learn how to use a SCCM CMPivot query to find installed patches in the last 30 days, 60 days or 90 days on your computers. You can also determine the installed date of the updates and hotfix ID, along with other information.

With CMPivot tool, you can find out the list of devices that have received the updates through SCCM in last x days. This will help you determine if a specific KB update is installed on the computer, or it’s missing. You can also run CMPivot queries from Intune to determine the patches installed on tenant attached devices.

There are multiple ways to determine if the updates are installed on a computer:

  • Use a PowerShell script to list the patches installed on remote computers.
  • Manually check the updates installed on your Windows computer.
  • Run a CMPivot query to find the patches installed in the last 30/60/90 days.

Most organizations use SCCM to deploy Windows updates. ConfigMgr reports are also useful in determining the list of updates that were installed on the computers. However, to get the real-time data on installed patches, CMPivot is the best choice.

Ad imageAd image

Note: Make sure the devices are online before running the CMPivot query. Now, occasionally, users in various time zones prevent all the computers in the SCCM device collection from being online. The CMPivot query will make an effort to run on every device, but if a device is offline or cannot be reached, you will be informed in the query output.

CMPivot Query to Find Installed Patches in last 30 Days

We will now launch the CMPivot tool and run a query to find the installed patches in X days. The “X” here can be any value of your choice. You can substitute X with the values such as 30 days, 60 days, 90 days etc.

Launch the SCCM console and navigate to Assets and Compliance > Overview > Device Collections. Right-click a device collection and select Start CMPivot. Switch to the Query tab and enter the below CMPivot query to find installed patches in last 30 days.

QuickFixEngineering | where InstalledOn >= ago(30d)
CMPivot Query to Find Installed Patches in last 30 Days
CMPivot Query to Find Installed Patches in last 30 Days

When you run the above query, the CMPivot query output shows the following details:

  • Computer Name
  • Caption
  • Description
  • FixComments
  • HotfixID
  • InstallDate
  • InstalledBy
  • InstalledOn
  • Name
  • ServicePackIneffect
  • Status

Note: Some columns are blank in the output because there are no details available with SCCM. But the most important details are hotfix ID and installed date.

CMPivot Query to find installed patches in last 60 days

Launch the SCCM console and navigate to Assets and Compliance > Overview > Device Collections. Right-click a device collection and select Start CMPivot. Switch to the Query tab and enter the below CMPivot query to find installed patches in last 60 days.

QuickFixEngineering | where InstalledOn >= ago(60d)
CMPivot Query to Find Installed Patches in last 60 Days
CMPivot Query to Find Installed Patches in last 60 Days

CMPivot Query to find installed patches in last 90 days

Launch the SCCM console and navigate to Assets and Compliance > Overview > Device Collections. Right-click a device collection and select Start CMPivot. Switch to the Query tab and enter the below CMPivot query to find installed patches in last 90 days.

QuickFixEngineering | where InstalledOn >= ago(90d)
CMPivot Query to Find Installed Patches in last 90 days
CMPivot Query to Find Installed Patches in last 90 days
Share This Article
Prajwal Desai
Posted by Prajwal Desai
Follow:
Prajwal Desai is a Microsoft MVP in Intune and SCCM. He writes articles on SCCM, Intune, Windows 365, Windows Server, Windows 11, WordPress and other topics, with the goal of providing people with useful information.
3 Comments