Patching compliance based on all current month SUG - Dynamic

-- Step 1: Store Latest Software Update Groups (SUGs) in a temp table

DROP TABLE IF EXISTS #LatestSUG;

CREATE TABLE #LatestSUG (Title NVARCHAR(255));

INSERT INTO #LatestSUG (Title)

SELECT Title 

FROM v_AuthListInfo

WHERE 

    (YEAR(DateCreated) = YEAR(GETDATE()) AND MONTH(DateCreated) = MONTH(GETDATE())) 

    OR (YEAR(DateCreated) = YEAR(GETDATE()) AND MONTH(DateCreated) = MONTH(DATEADD(MONTH, -1, GETDATE())))

;

-- Step 2: Store Compliance Data in a temp table to reduce redundant joins

DROP TABLE IF EXISTS #ComplianceData;

CREATE TABLE #ComplianceData (

    ResourceID INT,

    [Patch Title] NVARCHAR(255),

    [Install Status] NVARCHAR(50),

    [Update Group] NVARCHAR(255)

);

INSERT INTO #ComplianceData (ResourceID, [Patch Title], [Install Status], [Update Group])

SELECT 

    ucs.ResourceID,

    ui.Title AS [Patch Title],

    CASE ucs.status 

        WHEN 2 THEN 'Required' 

        WHEN 1 THEN 'NOT REQUIRED' 

        WHEN 0 THEN 'UNKNOWN' 

        WHEN 3 THEN 'Installed' 

    END AS [Install Status],

    v_AuthListInfo.Title AS [Update Group]

FROM v_Update_ComplianceStatusAll AS ucs

INNER JOIN v_UpdateInfo AS ui ON ui.CI_ID = ucs.CI_ID

INNER JOIN v_CIRelation ON v_CIRelation.ToCIID = ui.CI_ID

INNER JOIN v_AuthListInfo ON v_CIRelation.FromCIID = v_AuthListInfo.CI_ID

WHERE v_AuthListInfo.Title IN (SELECT Title FROM #LatestSUG );

-- Step 3: Retrieve the final result set using optimized joins

SELECT 

    sys.Name0 AS [Machine Name],

    CASE 

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

        THEN SUBSTRING(

                sys.Distinguished_Name0, 

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

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

                - CHARINDEX('OU=', sys.Distinguished_Name0, CHARINDEX('OU=', sys.Distinguished_Name0) + 3) - 3

            )

        ELSE NULL

    END AS 'Business_Unit',

    cd.[Patch Title], 

    cd.[Install Status],

    cd.[Update Group]

FROM v_ClientCollectionMembers 

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

INNER JOIN #ComplianceData AS cd ON sys.ResourceID = cd.ResourceID

WHERE v_ClientCollectionMembers.CollectionID = 'ABC02BB3'

ORDER BY [Machine Name];

-- Cleanup: Drop temporary tables

DROP TABLE IF EXISTS #LatestSUG;

DROP TABLE IF EXISTS #ComplianceData;

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]

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')

);