Summarizes the unique required updates in SCCM by Collection and Update Classification.

SU DAS Unique Required Updates.sql

/*
.SYNOPSIS
    Summarizes the unique required updates for a Collection in SCCM.
.DESCRIPTION
    Summarizes the unique required updates in SCCM by Collection and Update Classification.
.NOTES
    Requires SQL 2012 R2.
    Part of a report should not be run separately.
*/

/*##=============================================*/
/*## QUERY BODY                                  */
/*##=============================================*/
/* #region QueryBody */

/* Testing variables !! Need to be commented for Production !! */
--DECLARE @UserSIDs              AS NVARCHAR(10)  = 'Disabled';
--DECLARE @CollectionID          AS NVARCHAR(10)  = 'SMS00001';
--DECLARE @Locale                AS INT           = '2';
--DECLARE @UpdateClassifications AS NVARCHAR(250) = 'Security Updates';
--DECLARE @ExcludeArticleIDs     AS NVARCHAR(250) = '' --('915597,2267602,2461484') --AV Definitions;

/* Perform cleanup */
IF OBJECT_ID('tempdb..#UniqueUpdatesInfo', 'U') IS NOT NULL
    DROP TABLE #UniqueUpdatesInfo;

/* Variable declaration */
DECLARE @LCID AS INT = dbo.fn_LShortNameToLCID (@Locale)

/* Get update info */
;
WITH UpdateInfo_CTE AS (
SELECT
    ArticleID
    , Title                           = DisplayName
    , Classification                  = CategoryInstanceName
    , InformationURL                  = CIInformativeURL
    , UpdatesByClassification         = (
        DENSE_RANK() OVER (PARTITION BY CICategory.CategoryInstanceName ORDER BY UpdateCIs.ArticleID)
        +
        DENSE_RANK() OVER (PARTITION BY CICategory.CategoryInstanceName ORDER BY UpdateCIs.ArticleID  DESC)
        - 1
    )
    , RowNumber                       = (
        DENSE_RANK() OVER (PARTITION BY UpdateCIs.ArticleID ORDER BY ComplianceStatus.ResourceID)
    )

FROM fn_ListUpdateCIs(@LCID) AS UpdateCIs
    JOIN v_UpdateComplianceStatus AS ComplianceStatus ON ComplianceStatus.CI_ID = UpdateCIs.CI_ID
        AND ComplianceStatus.Status IN (0, 2)                           --Unknown, Required
    JOIN v_CICategoryInfo_All AS CICategory ON CICategory.CI_ID = ComplianceStatus.CI_ID
        AND CICategory.CategoryTypeName = 'UpdateClassification'
        AND CICategory.CategoryInstanceName IN (@UpdateClassifications) --Join only selected Update Classifications
    JOIN v_ClientCollectionMembers AS CollectionMembers ON CollectionMembers.ResourceID = ComplianceStatus.ResourceID
WHERE
    CollectionMembers.CollectionID = @CollectionID
        AND UpdateCIs.CIType_ID IN (1, 8)                               --1 Software Updates, 8 Software Update Bundle (v_CITypes)
        AND UpdateCIs.IsExpired = 0
        AND UpdateCIs.ArticleID NOT IN (                                --Exclude Updates based on ArticleID
            SELECT VALUE FROM STRING_SPLIT(@ExcludeArticleIDs, ',')
        )
)

/* Display summarized result  */
SELECT
    ArticleID
    , Title
    , Classification
    , InformationURL
    , UpdatesByClassification
INTO #UniqueUpdatesInfo
FROM UpdateInfo_CTE
WHERE RowNumber = 1

IF (SELECT COUNT(1) FROM #UniqueUpdatesInfo) = 0                        --If compliant (null result)
    BEGIN
        SELECT
            ArticleID                 = 'N/A'
            , Title                   = 'N/A'
            , Classification          = 'Selected Classifications'
            , InformationURL          = 'N/A'
            , UpdatesByClassification = 0
            , TotalUpdates            = 0
    END
ELSE
    BEGIN
        SELECT
            ArticleID
            , Title
            , Classification
            , InformationURL
            , UpdatesByClassification
            , TotalUpdates            = (SELECT Count(*) FROM #UniqueUpdatesInfo)
        FROM #UniqueUpdatesInfo
        ORDER BY UpdatesByClassification
    END

/* Perform cleanup */
IF OBJECT_ID('tempdb..#UniqueUpdatesInfo', 'U') IS NOT NULL
    DROP TABLE #UniqueUpdatesInfo;

/* #endregion */
/*##=============================================*/
/*## END QUERY BODY                              */
/*##=============================================*/