Create a report for overall update compliance for deployments with a deadline (Now()-MaxDays < Deadline Date < Now()-MinDays) for clients that are active and healthy (they have status "active/pass") belonging to a selected collections. If there are more than onde deployments for the clients its their status in the report is set to the worst one. All possible statuses, from the worst to the best, are: "Unknown"-> "Not Compliant" -> "Compiant".
SOLUTION:
The required report has three parameters:
- Collection ID (e.g. 'SMS000001')
- MaxDays (e.g. 30)
- MinDays (e.g. 7)
DECLARE @ToDate DATETIME = dateadd(day,-@MinDays,GetDate());
SELECT
AssignmentStatus.IsCompliant,
CASE
WHEN AssignmentStatus.IsCompliant IS NULL THEN 'Unknown'
WHEN AssignmentStatus.IsCompliant = 0 THEN 'Not Compliant'
WHEN AssignmentStatus.IsCompliant = 1 THEN 'Compliant'
END AS Compliant,
Count(CollectionMembers.ResourceID) AS ClientCount
FROM
(
SELECT fnFCM.ResourceID
FROM fn_rbac_FullCollectionMembership('disabled') fnFCM
INNER JOIN v_CH_ClientSummary vCS ON fnFCM.ResourceID = vCS.ResourceID
WHERE fnFCM.CollectionID=@CollectionID
AND vCS.ClientActiveStatus = 1
AND vCS.ClientState = 1
) CollectionMembers
LEFT JOIN
(
--Determine the compliance state for all devices. Us NullIf/Min/Coalesce to detect if anything is null, if anything is 0, or if everything is 1 for the RessourceID.
SELECT fnUASL.ResourceID , NULLIF(MIN(COALESCE(cast(fnUASL.IsCompliant as int),-2^31)),-2^31) AS IsCompliant
FROM fn_rbac_UpdateAssignmentStatus_Live('disabled') fnUASL
INNER JOIN fn_rbac_CIAssignment('disabled') fnCIA
ON fnUASL.AssignmentID=fnCIA.AssignmentID
WHERE fnCIA.EnforcementDeadline > @FromDate
AND fnCIA.EnforcementDeadline < @ToDate
GROUP BY fnUASL.ResourceID
) AssignmentStatus
ON CollectionMembers.ResourceID = AssignmentStatus.ResourceID
GROUP BY IsCompliant