Gets the software update compliance in SCCM by Device and All Updates.

SU Compliance by Device.sql

/*
.SYNOPSIS
    Gets the software update compliance for a device in SCCM.
.DESCRIPTION
    Gets the software update compliance in SCCM by Device 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 @Locale                 AS INT           = 2;
--DECLARE @DeviceNameOrResourceID AS NVARCHAR(50)  = 'ULB-CM-MDP-01';
--DECLARE @UpdateClassifications  AS NVARCHAR(250) = 'Security Updates';
--DECLARE @ExcludeArticleIDs      AS NVARCHAR(250) = '' --('915597,2267602,2461484) --AV Definitions;
--DECLARE @SupersededStatus       AS NVARCHAR(10)  = '1';
--DECLARE @ComplianceStatus       AS NVARCHAR(10)  = '2';

/* Variable declaration */
DECLARE @LCID                   AS INT           = dbo.fn_LShortNameToLCID (@Locale);
DECLARE @DeviceFQDN             AS NVARCHAR(50);
DECLARE @ResourceID             AS INT;

/* Check if @DeviceNameOrResourceID is positive Integer (ResourceID) */
IF @DeviceNameOrResourceID LIKE '%[^0-9]%'
    BEGIN

        /* Get ResourceID from Device Name */
        SET @ResourceID = (
            SELECT TOP 1 ResourceID
            FROM fn_rbac_R_System(@UserSIDs) AS Systems
            WHERE Systems.Name0 = @DeviceNameOrResourceID
        )
    END
ELSE
    BEGIN
        SET @ResourceID = @DeviceNameOrResourceID
    END

/* Get Device FQDN from ResourceID */
SET @DeviceFQDN = (
    SELECT
        IIF(Systems.Full_Domain_Name0 IS NOT NULL, Systems.Name0 + '.' + Systems.Full_Domain_Name0, Systems.Name0)
    FROM fn_rbac_R_System(@UserSIDs) AS Systems
    WHERE Systems.ResourceID = @ResourceID
)

SELECT
    DeviceFQDN            = @DeviceFQDN
    , Title               = UpdateCIs.DisplayName
    , Classification      = Category.CategoryInstanceName
    , ArticleID           = UpdateCIs.ArticleID
    , IsTargeted          = IIF(Targeted.ResourceID    != NULL, '*', NULL)
    , IsDeployed          = IIF(UpdateCIs.IsDeployed    = 1, '*', NULL)
    , IsRequired          = IIF(ComplianceStatus.Status = 2, '*', NULL)
    , IsInstalled         = IIF(ComplianceStatus.Status = 3, '*', NULL)
    , IsSuperseded        = IIF(UpdateCIs.IsSuperseded  = 1, '*', NULL)
    , IsExpired           = IIF(UpdateCIs.IsExpired     = 1, '*', NULL)
    , EnforcementDeadline = CONVERT(NVARCHAR(16), EnforcementDeadline, 120)
    , EnforcementSource   = (
        CASE ComplianceStatus.EnforcementSource
            WHEN 0 THEN 'NONE'
            WHEN 1 THEN 'SMS'
            WHEN 2 THEN 'USER'
        END
    )
    , LastErrorCode       = ComplianceStatus.LastErrorCode
    , MaxExecutionTime    = UpdateCIs.MaxExecutionTime / 60
    , DateRevised         = CONVERT(NVARCHAR(16), UpdateCIs.DateRevised, 120)
    , UpdateUniqueID      = UpdateCIs.CI_UniqueID
    , InformationUrl      = UpdateCIs.CIInformativeURL
FROM fn_rbac_UpdateComplianceStatus(@UserSIDs) AS ComplianceStatus
    JOIN fn_ListUpdateCIs(@LCID) AS UpdateCIs ON UpdateCIs.CI_ID = ComplianceStatus.CI_ID
        AND UpdateCIs.IsSuperseded IN (@SupersededStatus)
        AND UpdateCIs.CIType_ID IN (1, 8)                             --1 Software Updates, 8 Software Update Bundle (v_CITypes)
        AND UpdateCIs.ArticleID NOT IN (                              --Exclude updates based on ArticleID
            SELECT VALUE FROM STRING_SPLIT(@ExcludeArticleIDs, ',')
        )
    JOIN v_CICategoryInfo_All AS Category ON Category.CI_ID = UpdateCIs.CI_ID
        AND Category.CategoryTypeName = 'UpdateClassification'
        AND Category.CategoryInstanceName IN (@UpdateClassifications) --Join only on selected classifications
    LEFT JOIN v_CITargetedMachines AS Targeted ON Targeted.CI_ID = UpdateCIs.CI_ID
        AND Targeted.ResourceID = ComplianceStatus.ResourceID
    OUTER APPLY (
        SELECT EnforcementDeadline = MIN(Assignment.EnforcementDeadline)
        FROM v_CIAssignment AS Assignment
            JOIN v_CIAssignmentToCI AS AssignmentToCI ON AssignmentToCI.AssignmentID = Assignment.AssignmentID
                AND AssignmentToCI.CI_ID = UpdateCIs.CI_ID
    ) AS EnforcementDeadline
WHERE ComplianceStatus.ResourceID = @ResourceID
    AND ComplianceStatus.Status IN (@ComplianceStatus)                --Select only when ComplianceStatus is Unknown or Required

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