Software update group based Patch compliance report + SQL QUERY

Found useful so sharing it.

=======================================================================

SQL 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
========================================================================

Ref: https://www.attosol.com/sccm-custom-sql-query-for-patch-compliance/