Patching compliance report based on SUG

 SELECT 

sys.Name0 AS [Machine Name],

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],

v_AuthListInfo.Title AS [Update Group]

FROM v_ClientCollectionMembers 

INNER JOIN v_R_System AS sys ON v_ClientCollectionMembers.ResourceID = sys.ResourceID LEFT OUTER JOIN

v_CIRelation INNER JOIN

v_UpdateInfo AS ui INNER JOIN

v_UpdateComplianceStatus AS ucs ON ui.CI_ID = ucs.CI_ID ON v_CIRelation.ToCIID = ui.CI_ID INNER JOIN

v_AuthListInfo ON v_CIRelation.FromCIID = v_AuthListInfo.CI_ID ON sys.ResourceID = ucs.ResourceID

GROUP BY sys.Name0, ucs.Status, v_AuthListInfo.Title,   ui.Title,v_ClientCollectionMembers.CollectionID

HAVING  (v_AuthListInfo.Title LIKE 'Update Compliance Reporting - Jan 2025') AND (v_ClientCollectionMembers.CollectionID LIKE 'ABC02502')

ORDER BY [Machine Name]