Summarizes the software update compliance in SCCM by Collection and All Updates.

SU DAS Compliance Summarization.sql

/*
.SYNOPSIS
    Summarizes the software update compliance for a Collection in SCCM.
.DESCRIPTION
    Summarizes the software update compliance in SCCM by Collection and All Updates.
.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..#SummarizationInfo', 'U') IS NOT NULL
    DROP TABLE #SummarizationInfo;

/* Variable declaration */
DECLARE @LCID                  AS INT = dbo.fn_LShortNameToLCID (@Locale);
DECLARE @ClientDevices         AS INT = (
    SELECT COUNT(ResourceID)
    FROM fn_rbac_ClientCollectionMembers(@UserSIDs) AS ClientCollectionMembers
    WHERE ClientCollectionMembers.CollectionID = @CollectionID
)
DECLARE @TotalDevices          AS INT = (
    SELECT COUNT(ResourceID)
    FROM fn_rbac_FullCollectionMembership(@UserSIDs) AS CollectionMembership
    WHERE CollectionMembership.CollectionID = @CollectionID
        AND CollectionMembership.ResourceType = 5 --Select devices only
)
DECLARE @NonClientDevices      AS INT  = @TotalDevices - @ClientDevices

/* Get compliance data data */
;
WITH SummarizationInfo_CTE AS (
    SELECT DISTINCT
        ComplianceStatus.ResourceID
        , MissingUniqueUpdates = (
            DENSE_RANK() OVER(PARTITION BY CollectionMembers.CollectionID ORDER BY ComplianceStatus.Status, UpdateCIs.ArticleID)
            +
            DENSE_RANK() OVER(PARTITION BY CollectionMembers.CollectionID ORDER BY ComplianceStatus.Status, UpdateCIs.ArticleID DESC)
            - 1
        )
        , NonCompliantDevices  = (
            DENSE_RANK() OVER(PARTITION BY CollectionMembers.CollectionID ORDER BY ComplianceStatus.Status, ComplianceStatus.ResourceID)
            +
            DENSE_RANK() OVER(PARTITION BY CollectionMembers.CollectionID ORDER BY ComplianceStatus.Status, ComplianceStatus.ResourceID DESC)
            - 1
        )
        , Classification       = CICategory.CategoryInstanceName
    FROM fn_rbac_R_System(@UserSIDs) AS Systems
        JOIN v_UpdateComplianceStatus AS ComplianceStatus ON ComplianceStatus.ResourceID = Systems.ResourceID
            AND ComplianceStatus.Status IN (0, 2)                           --Unknown, Required
        JOIN v_ClientCollectionMembers AS CollectionMembers ON CollectionMembers.ResourceID = ComplianceStatus.ResourceID
        JOIN fn_ListUpdateCIs(@LCID) AS UpdateCIs ON UpdateCIs.CI_ID = ComplianceStatus.CI_ID
            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, ',')
            )
        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
        LEFT JOIN v_CITargetedMachines AS Targeted ON Targeted.ResourceID = ComplianceStatus.ResourceID
            AND Targeted.CI_ID = ComplianceStatus.CI_ID
    WHERE CollectionMembers.CollectionID = @CollectionID
)

/* Insert into SummarizationInfo */
SELECT
    Classification
    , MissingUniqueUpdates
    , NonCompliantDevices
    , NonComplianceByClassification = Count(*)
INTO #SummarizationInfo
FROM SummarizationInfo_CTE
GROUP BY
    Classification
    , MissingUniqueUpdates
    , NonCompliantDevices

/* Display summarized result */
IF (SELECT COUNT(1) FROM #SummarizationInfo) = 0                            --If compliant (null result)
    BEGIN
        SELECT
            Classification                  = 'Selected Classifications'
            , MissingUniqueUpdates          = 0
            , CompliantDevices              = @ClientDevices
            , NonCompliantDevices           = 0
            , ComplianceByClassification    = @ClientDevices
            , NonComplianceByClassification = 0
            , ClientDevices                 = @ClientDevices
            , NonClientDevices              = @NonClientDevices
            , TotalDevices                  = @TotalDevices
    END
ELSE
    BEGIN
        SELECT
            Classification                  = Classification
            , MissingUniqueUpdates          = MissingUniqueUpdates
            , CompliantDevices              = ISNULL(@ClientDevices - NonCompliantDevices, 0)
            , NonCompliantDevices           = ISNULL(NonCompliantDevices, 0)
            , ComplianceByClassification    = @ClientDevices - NonComplianceByClassification
            , NonComplianceByClassification = NonComplianceByClassification
            , ClientDevices                 = @ClientDevices
            , NonClientDevices              = @NonClientDevices
            , TotalDevices                  = @TotalDevices
        FROM #SummarizationInfo
        GROUP BY
            Classification
            , MissingUniqueUpdates
            , NonCompliantDevices
            , NonComplianceByClassification
    END

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

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