SCCM Patch scan Status SQL Query

Select

Distinct VRS.Name0 as 'MachineName',

Os.Caption0 as 'OperatingSystem',

St.SystemType00 as 'OSType',

VRS.AD_Site_Name0 as 'ADSite',

VRS.Full_Domain_Name0 as 'Domain',

VRS.User_Name0 as 'UserName',

UI.ArticleID as 'ArticleID',

UI.BulletinID as 'BulletinID',

UI.Title as 'Title',

CASE WHEN UCS.Status = 2 THEN 'Required'

WHEN UCS.Status = 3 THEN 'Installed'

ELSE 'Unknown' END AS 'KBStatus',

UI.InfoURL as 'InformationURL'

FROM v_UpdateComplianceStatus UCS

INNER JOIN v_UpdateInfo UI ON UCS.CI_ID = UI.CI_ID

INNER JOIN v_CICategories_All CIC ON UI.CI_ID = CIC.CI_ID

INNER JOIN v_CategoryInfo CI ON CIC.CategoryInstanceID = CI.CategoryInstanceID

INNER JOIN v_R_System VRS ON UCS.ResourceID = VRS.ResourceID

INNER JOIN v_GS_OPERATING_SYSTEM Os on UCS.ResourceID = Os.ResourceID

INNER JOIN Computer_System_DATA St on UCS.ResourceID = st.MachineID

INNER Join v_FullCollectionMembership Col on UCS.ResourceID = Col.ResourceID

WHERE 

--Col.CollectionID = xxxxxxxx and 

VRS.Name0 in(' xxx', 'xxx') and

UI.articleid in ('4561600','4557957','4025338')