SCCM SQL Current Month Patching report

-- edge will get superseded very frequently that may not come in this report

WITH LatestPatches AS (

    SELECT DISTINCT

        v_R_System.Name0 AS 'Device Name',

        CASE 

            WHEN CHARINDEX('OU=', v_R_System.Distinguished_Name0) > 0 

            THEN SUBSTRING(

                    v_R_System.Distinguished_Name0, 

                    CHARINDEX('OU=', v_R_System.Distinguished_Name0) + 3, 

                    CHARINDEX(',', v_R_System.Distinguished_Name0, CHARINDEX('OU=', v_R_System.Distinguished_Name0)) - CHARINDEX('OU=', v_R_System.Distinguished_Name0) - 3

                )

            ELSE NULL

        END AS 'OU',

case 

when v_R_System.Operating_System_Name_and0 like '%server%' then 'Server' 

when v_R_System.Operating_System_Name_and0 like '%workstation 6%' then 'Windows 7'

when v_R_System.Operating_System_Name_and0 like '%workstation 5%' then 'Windows XP'

when v_R_System.Operating_System_Name_and0 like '%workstation 10%' and v_R_System.Build01 like '%26100%'  then 'Windows 11 Build 24H2'

when v_R_System.Operating_System_Name_and0 like '%workstation 10%' and v_R_System.Build01 like '%22631%'  then 'Windows 11 Build 23H2'

when v_R_System.Operating_System_Name_and0 like '%workstation 10%' and v_R_System.Build01 like '%19045%'  then 'Windows 10 Build 22H2'

when v_R_System.Operating_System_Name_and0 like '%workstation 10%' and v_R_System.Build01 like '%22621%'  then 'Windows 11 Build 22H2'

when v_R_System.Operating_System_Name_and0 like '%workstation 10%' and v_R_System.Build01 like '%17134%'  then 'Windows 10 Build 1803'

when v_R_System.Operating_System_Name_and0 like '%workstation 10%' and v_R_System.Build01 like '%22000%'  then 'Windows 11 Build 21H2'

when v_R_System.Operating_System_Name_and0 like '%workstation 10%' and v_R_System.Build01 like '%19044%'  then 'Windows 10 Build 21H2'

when v_R_System.Operating_System_Name_and0 like '%workstation 10%' and v_R_System.Build01 like '%19043%'  then 'Windows 10 Build 21H1'

when v_R_System.Operating_System_Name_and0 like '%workstation 10%' and v_R_System.Build01 like '%19042%'  then 'Windows 10 Build 20H2'

when v_R_System.Operating_System_Name_and0 like '%workstation 10%' and v_R_System.Build01 like '%19041%'  then 'Windows 10 Build 2004'

when v_R_System.Operating_System_Name_and0 like '%workstation 10%' and v_R_System.Build01 like '%18363%'  then 'Windows 10 Build 1909'

when v_R_System.Operating_System_Name_and0 like '%workstation 10%' and v_R_System.Build01 like '%18362%'  then 'Windows 10 Build 1903'

when v_R_System.Operating_System_Name_and0 like '%workstation 10%' and v_R_System.Build01 like '%17763%'  then 'Windows 10 Build 1809'

when v_R_System.Operating_System_Name_and0 like '%workstation 10%' and v_R_System.Build01 like '%16299%'  then 'Windows 10 Build 1709'

when v_R_System.Operating_System_Name_and0 like '%workstation 10%' and v_R_System.Build01 like '%15063%'  then 'Windows 10 Build 1703'

when v_R_System.Operating_System_Name_and0 like '%workstation 10%' and v_R_System.Build01 like '%14393%'  then 'Windows 10 Build 1607'

when v_R_System.Operating_System_Name_and0 like '%workstation 10%' and v_R_System.Build01 like '%10240%'  then 'Windows 10 Build 1507'

when v_R_System.Operating_System_Name_and0 like '%workstation 10%' and v_R_System.Build01 like '%10586%'  then 'Windows 10 Build 1511'

when v_R_System.Operating_System_Name_and0 like '%workstation 10%' and v_R_System.Build01 is NULL then 'Windows 10 Build not specified'

else v_R_System.Operating_System_Name_and0

end as 'O/S',

cls.CategoryInstanceName 'Patch Type',

        V_UpdateInfo.Title AS 'Update Title',

        CASE

            WHEN v_Update_ComplianceStatus.Status = '2' THEN 'MISSING'

            WHEN v_Update_ComplianceStatus.Status = '3' THEN 'INSTALLED'

            ELSE 'UNKNOWN'

        END AS 'Patch Status',

        V_UpdateInfo.DateCreated

    FROM v_R_System

    -- Join Compliance Status

    LEFT JOIN v_Update_ComplianceStatus 

        ON v_R_System.ResourceID = v_Update_ComplianceStatus.ResourceID

    -- Join Updates

    LEFT JOIN V_UpdateInfo 

        ON v_Update_ComplianceStatus.CI_ID = V_UpdateInfo.CI_ID

    -- Join Update Categories

    JOIN v_CICategoryInfo_All cls 

        ON cls.CI_ID = V_UpdateInfo.CI_ID 

        AND cls.CategoryTypeName = 'UpdateClassification'

    -- Join Collection Filter

    JOIN v_FullCollectionMembership fcm 

        ON v_R_System.ResourceID = fcm.ResourceID

    JOIN v_Collection col

        ON fcm.CollectionID = col.CollectionID

    -- Filters for Updates

    WHERE 

        V_UpdateInfo.IsDeployed = 1 

        AND V_UpdateInfo.IsExpired = 0 

        AND V_UpdateInfo.IsSuperseded = 0 

  --  AND v_UpdateInfo.CIType_ID = 8  -- Only software updates

    AND V_UpdateInfo.Title NOT LIKE '%Server%'  -- Exclude Server patches

    AND V_UpdateInfo.Title NOT LIKE '%Feature Update%'  -- Exclude Feature Updates

    AND V_UpdateInfo.Title NOT LIKE '%Driver%'  -- Exclude Drivers

AND V_UpdateInfo.Title NOT LIKE '%HP Services Scan for Commercial%'  -- Exclude Drivers

    AND V_UpdateInfo.Title NOT LIKE '%Seamless Firmware Update Service%'  -- Exclude Drivers

AND (cls.CategoryInstanceName ='Critical Updates' 

OR cls.CategoryInstanceName ='Security Updates' 

OR cls.CategoryInstanceName ='Update Rollups' 

OR cls.CategoryInstanceName ='Updates'

)

AND fcm.CollectionID = 'ABC01022'

)

-- Check if current month's patches exist

SELECT * 

FROM LatestPatches 

WHERE FORMAT(DateCreated, 'yyyy-MM') = FORMAT(GETDATE(), 'yyyy-MM')

UNION ALL

-- If no current month patches exist, take last month's patches

SELECT * 

FROM LatestPatches 

WHERE FORMAT(DateCreated, 'yyyy-MM') = FORMAT(DATEADD(MONTH, -1, GETDATE()), 'yyyy-MM')

AND NOT EXISTS (

    SELECT 1 

    FROM LatestPatches 

    WHERE FORMAT(DateCreated, 'yyyy-MM') = FORMAT(GETDATE(), 'yyyy-MM')

);