Software update errors sccm sql query

The view name in the query is incorrect. For software update deployment status and error information, use the software‑update state and assignment views that are documented for Configuration Manager.

For deployment‑level status (per assignment and device), use v_UpdateAssignmentStatus or v_UpdateAssignmentStatus_Live. For per‑update compliance on each client, use v_UpdateComplianceStatus together with v_UpdateInfo and v_R_System. State names come from v_StateNames.

A pattern that matches what the query is trying to do is:

SELECT
    rs.Name0                    AS MachineName,
    ui.ArticleID                AS UpdateID,
    ui.Title                    AS UpdateTitle,
    uas.LastEnforcementMessageID AS LastEnforcementStateID,
    sn.StateName                AS LastEnforcementMessage,
    uas.LastEnforcementErrorCode,
    uas.LastEnforcementMessageTime AS LastEnforcementTime,
    uas.AssignmentID
FROM v_R_System              AS rs
JOIN v_UpdateAssignmentStatus AS uas
    ON rs.ResourceID = uas.ResourceID
JOIN v_UpdateInfo            AS ui
    ON uas.CI_ID = ui.CI_ID
LEFT JOIN v_StateNames       AS sn
    ON sn.StateID   = uas.LastEnforcementMessageID
   AND sn.StateType = 301      -- enforcement state for deployments
WHERE
    uas.LastEnforcementErrorCode <> 0
    AND uas.AssignmentID IN (/* list the 12 AssignmentIDs here */)
ORDER BY
    uas.LastEnforcementMessageTime DESC;

Key points:

• v_AssignmentStatus is not a documented view; use v_UpdateAssignmentStatus or v_UpdateAssignmentStatus_Live instead.
• LastEnforcementMessageID in v_UpdateAssignmentStatus is the state ID for topic type 301 (deployment enforcement). Join it to v_StateNames on StateID and StateType = 301 to get the friendly description.
• v_UpdateInfo provides ArticleID and Title for each update (CI_ID).
• Filter by AssignmentID for the specific deployments (the 12 deployments of the software update group).

If per‑update compliance per client is required rather than deployment‑level status, use v_UpdateComplianceStatus joined to v_UpdateInfo, v_R_System, and v_StateNames in a similar way, using CI_ID, ResourceID, Status, and LastEnforcementMessageID as documented.

For a ready‑made view of deployment errors similar to the built‑in “Troubleshooting 2 – Deployment errors” report, the recommended approach is to use the existing software update troubleshooting reports under Software Updates reports, especially Troubleshooting 2 - Deployment errors, which already aggregates deployment errors and affected computer counts.

References:

• Sample queries for software updates in Configuration Manager
• Status and alert views in Configuration Manager
• Monitor software updates in Configuration Manager