Patch Compliance report for current month

This report runs on based on the KB downloaded date for the month in SCCM and deployed a particular (collection ID can be added or changed depending upon the environment) gets the status as installed or missing on the machine. Here only required KB for the machine will come as missing



Select
        v_R_System.name0 as 'Device Name',
        v_RA_System_ResourceNames.Resource_Names0 as 'FQDN',
        v_R_System.Resource_Domain_OR_Workgr0 as 'DOMAIN',
V_GS_OPERATING_SYSTEM.Caption0 as 'Operating System',
        V_UpdateInfo.DateCreated,
V_UpdateInfo.ArticleID,
        V_UpdateInfo.Title,
    CASE
                     WHEN v_Update_ComplianceStatus.Status = '2' THEN 'MISSING'
                     WHEN v_Update_ComplianceStatus.Status = '3' THEN 'INSTALLED'
              else 'UNKNOWN'
              END AS 'PatchStatus'
                             
from v_R_System
left join v_Update_ComplianceStatus on v_R_System.ResourceID = v_Update_ComplianceStatus.ResourceID
JOIN v_FullCollectionMembership fcm on v_R_System.ResourceID=fcm.ResourceID
left join V_UpdateInfo on v_Update_ComplianceStatus.CI_ID = V_UpdateInfo.CI_ID
left join v_RA_System_ResourceNames on v_R_System.ResourceID = v_RA_System_ResourceNames.ResourceID
left join v_UpdateScanStatus on v_R_System.ResourceID = v_UpdateScanStatus.ResourceID
left join v_GS_WORKSTATION_STATUS on v_R_System.ResourceID = v_GS_WORKSTATION_STATUS.ResourceID
left Join v_GS_OPERATING_SYSTEM on v_R_System.ResourceID = v_GS_OPERATING_SYSTEM.ResourceID
where month(V_UpdateInfo.DateCreated) like month(GetDATE())  and  year(V_UpdateInfo.DateCreated) like year(GetDATE()) and  v_UpdateInfo.IsDeployed = '1' and v_UpdateInfo.CIType_ID = '8' and v_UpdateInfo.IsSuperseded = '0' and V_r_System.Operating_System_Name_and0 not like '%server%' and fcm.CollectionID = 'CollectionID'
order by v_R_System.name0