SCCM Modified Compliance Report

Query for Compliance report include the unknown Device.

DECLARE @CI_ID INT;
SELECT @CI_ID = CI_ID 
FROM fn_rbac_ConfigurationItems(@UserSIDs)  
WHERE CIType_ID = 9 AND CI_UniqueID = @AuthListID;

SELECT
    CollectionID = @CollID,
    Status = CASE 
                WHEN sn.StateID = 1 THEN 'Compliant'
                WHEN sn.StateID = 2 THEN 'Non-Compliant'
             END,
    cs.NumberOfComputers,
    PComputers = CONVERT(FLOAT, ISNULL(cs.NumberOfComputers, 0) * 100.00) / ISNULL(NULLIF(cs.NumTotal, 0), 1),
    AuthListID = @AuthListID
FROM (
    SELECT CI_ID, 
           NumTotal, 
           [1] = NumUnknown + NumPresent + NumNotApplicable, -- Combine unknown and compliant
           [2] = NumMissing -- Non-compliant
    FROM fn_rbac_UpdateSummaryPerCollection(@UserSIDs)  
    WHERE CI_ID = @CI_ID AND CollectionID = @CollID
) cnt
UNPIVOT (NumberOfComputers FOR [Status] IN ([1], [2])) cs
LEFT JOIN fn_rbac_StateNames(@UserSIDs) sn 
    ON sn.TopicType = 300 AND sn.StateID = cs.Status
WHERE cs.NumberOfComputers > 0
ORDER BY cs.NumberOfComputers DESC;