SCCM SQL Query for Custom Patch Compliance

Patch Compliance nowadays is one of the most talked-about topics in the IT Infrastructure field.

Looking at the high number of ransomware targeting corporations all over the world, there is a high demand to keep a close track of the current patch compliance in any organization.

Microsoft’s System Center Configuration Manager(SCCM) tool is one of the top applications used by organizations across the globe to manage end-user workstations and Patch deployment is one of the core features it provides. The default compliance reports provided by SCCM are pretty comprehensive and detailed, however, there might be a few environments where any of the reports in SCCM don’t give all the required information related to patch compliance in a single datasheet.

There was a similar case where one of our customers demanded us to create a custom patch compliance report which displays the list of Installed patches, Missing patches, not-required patches for a set of computers.

Although we do have default reports in SCCM which can display this data, however, our customer wanted a single report to get data for multiple machines. The customer also wanted some additional columns such as patch supersede info, expiry info, currently logged in user, Date released of the patch, Date Installed, Patch Severity, etc.

Eventually, we were able to create this custom query, which we later used to build a report using Report Builder. Hope it helps some of our fellow administrators using SCCM for patch compliance 😊

SQL Query showing Patches Installed, not-Installed per Software Update Group.

QUERY:

Select name0 AS [Machine Name], sys.User_Name0, ui.Title AS [Patch Title], (CASE ucs.status WHEN 2 THEN 'Required' WHEN 1 THEN 'NOT REQUIRED' WHEN 0 THEN 'INSTALL STATE UNKNOWN' WHEN 3 THEN 'Installed' END) AS [Install Status] , dbo.v_AuthListInfo.Title as [Update Group],
ui.ArticleID, (CASE UI.IsExpired WHEN 0 THEN 'NOT EXPIRED' WHEN 1 THEN 'EXPIRED' END) AS [EXPIRY], (CASE UI.IsSuperseded WHEN 0 THEN 'NOT Superseded' WHEN 1 THEN 'Superseded' END) AS [SEPERSEDENCE] ,  (CASE ui.Severity WHEN 0 then 'none' when 2 then 'low' WHEN 6 then 'MODERATE' WHEN 8 THEN 'IMPORTANT' WHEN 10 THEN 'CRITICAL' END) AS [SEVERITY] , ui.DatePosted AS DateReleased, ucs.LastStatusChangeTime AS DateInstalled
FROM dbo.v_ClientCollectionMembers INNER JOIN
dbo.v_R_System AS sys ON dbo.v_ClientCollectionMembers.ResourceID = sys.ResourceID LEFT OUTER JOIN
dbo.v_CIRelation INNER JOIN
dbo.v_UpdateInfo AS ui INNER JOIN
dbo.v_UpdateComplianceStatus AS ucs ON ui.CI_ID = ucs.CI_ID ON dbo.v_CIRelation.ToCIID = ui.CI_ID INNER JOIN
dbo.v_AuthListInfo ON dbo.v_CIRelation.FromCIID = dbo.v_AuthListInfo.CI_ID ON sys.ResourceID = ucs.ResourceID
GROUP BY sys.Name0, ui.IsExpired, UI.IsSuperseded, ucs.status, ui.Severity, dbo.v_AuthListInfo.Title, dbo.v_ClientCollectionMembers.CollectionID, sys.User_Name0, ui.ArticleID, ui.Title, ui.DatePosted , ucs.LastStatusChangeTime
HAVING (ui.IsExpired = 0) AND
(dbo.v_AuthListInfo.Title like 'Name of SUG') AND (dbo.v_ClientCollectionMembers.CollectionID like 'collectionID')
ORDER BY sys.Name0

Note: The Date Installed Column is basically the "Last Status Change Date" of the patch. After a patch gets installed, its status gets changed and that should be the last time that the status is changed. Therefore, we can assume that this will be the date/time of the patch getting installed. The only thing to consider is that you can ignore the Install-Date published for non-installed patches, as it’s just a last status change date. If anyone else has a better logic to get this info, can comment and let me know.