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.
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)
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 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)
Hello Prajal thanks, according to that query it showing one KB installed.. Shall I assume that its installed by SCCM or manually by windowsNT system.
I doubt that can be determined by CMPivot.
Thank you for the useful query, in ConfigMgr 2207 the installedon is in text format and we can’t use age() function. It doesn’t filter the output. The installeddate is in datetime format but unfortunately, it’s blank for most of the installed updates.