La requete SQL ci-dessous liste l'état d'application (Enforcement) pour toutes les machines et les deploiements correspondant.
/*** SCCM - QUERY TO DISPLAY ALL COMPUTERS 'LAST ENFORCEMENT STATE' FOR ALL DEPLOYMENTS ***/
DECLARE @StartDate DATETIME, @EndDate DATETIME
-- Since 12 months
SET @StartDate = DATEADD(mm, -12,GETDATE())
SET @EndDate = GETDATE()
SELECT
vrs.Name0,
CASE vrs.Active0
WHEN 0 THEN 'NO'
WHEN 1 THEN 'YES'
END AS Client_Active,
a.AssignmentName as DeploymentName,
a.StartTime as Deploy_Available,
a.EnforcementDeadline as Deploy_Deadline,
sn.StateName as LastEnforcementState,
wsus.LastErrorCode as 'LasErrorCode'
FROM v_CIAssignment a
JOIN v_AssignmentState_Combined assc on a.AssignmentID=assc.AssignmentID
JOIN v_StateNames sn on assc.StateType = sn.TopicType and sn.StateID=isnull(assc.StateID,0)
JOIN v_R_System vrs on vrs.ResourceID = assc.ResourceID
JOIN v_GS_WORKSTATION_STATUS wks on wks.ResourceID = assc.ResourceID
JOIN v_UpdateScanStatus wsus on wsus.ResourceID = assc.ResourceID
AND CreationTime BETWEEN @StartDate AND @EndDate -- IN A SPECIFIC TIME WINDOW FOR THE DEPLOYMENT CREATION TIME
ORDER BY Deploy_Available DESC