SCCM SQL query that reproduces what the Deployment Status node shows for a Software Update deployment:

Question

Hello Team,

There is a software update group deployed. I require a SCCM SQL data to generate data which displayed in console. I tried searching in Google search and i am not getting the exact requirement.

Please share the SCCM SQL query to get the device list which are In Progress, Error, Unknown along with Last Enforcement State, Last Enforcement Message Time, Last Enforcement Error Code

Answer 1

Use the software updates status views and state views that expose deployment assignment and enforcement information, then filter by the deployment (AssignmentID) and map the state IDs to the console buckets (In Progress, Error, Unknown).

A typical pattern is:

-- Replace @AssignmentID with the deployment ID of the Software Update Group
DECLARE @AssignmentID INT = 12345678;  -- your deployment ID

SELECT
    rs.Name0                    AS DeviceName,
    uas.AssignmentID,
    uas.LastEnforcementMessageID,
    sn.StateName                AS LastEnforcementState,   -- text, e.g. "Failed to install update(s)"
    uas.LastEnforcementMessageTime,
    uas.LastEnforcementErrorCode,
    CASE sn.StateName
         WHEN 'Succeeded'                      THEN 'Compliant'
         WHEN 'Failed to install update(s)'    THEN 'Error'
         WHEN 'Waiting for another installation to complete'
              THEN 'In Progress'
         -- add other state name → bucket mappings as needed
         ELSE 'Unknown'
    END                         AS DeploymentStatusBucket
FROM v_UpdateAssignmentStatus AS uas
JOIN v_R_System              AS rs
      ON uas.ResourceID = rs.ResourceID
LEFT JOIN v_StateNames       AS sn
      ON sn.TopicType = 301              -- enforcement state messages
     AND sn.StateID   = uas.LastEnforcementMessageID
WHERE uas.AssignmentID = @AssignmentID;

Key points from the views used:

• v_UpdateAssignmentStatus (or v_UpdateAssignmentStatus_Live) lists, per deployment and device:
  • AssignmentID (deployment ID)
  • ResourceID
  • LastEnforcementMessageID
  • LastEnforcementMessageTime
  • LastEnforcementErrorCode
• v_StateNames provides the human‑readable state name for a given StateID and TopicType.
  • For enforcement state, TopicType = 301 when using LastEnforcementMessageID from v_UpdateAssignmentStatus.
• v_R_System gives the device name (Name0).

To reproduce the console’s In Progress / Error / Unknown counts, adjust the CASE expression so that each StateName (from v_StateNames) is mapped into the desired bucket according to the state definitions used in the environment.

For per‑update enforcement/compliance (rather than per‑deployment), use v_UpdateComplianceStatus joined to v_UpdateInfo, v_R_System, and v_StateNames on CI_ID, ResourceID, and LastEnforcementMessageID as shown in the sample queries.

---

References:

• Software updates views in Configuration Manager
• Status and alert views in Configuration Manager
• Sample queries for software updates in Configuration Manager
• Sample queries for status and alerts in Configuration Manager
• Get Deployment Status activity for Configuration Manager Integration Pack