For a software-update deployment, query v_UpdateAssignmentStatus, join v_StateNames with enforcement topic type 301, and return LastEnforcementMessageTime. If you need one row for every individual update on a device, use v_UpdateComplianceStatus and topic type 402 instead. The distinction determines whether your report is deployment-level or per-update.
Deployment-level query: one row per device and assignment
Use this version when the report asks how each targeted device is doing against a deployment identified by AssignmentID. Microsoft documents LastEnforcementMessageID in v_UpdateAssignmentStatus as an enforcement state ID for topic type 301. v_StateNames translates that ID into a readable state name.
DECLARE @AssignmentID INT = 12345678; -- Replace with the deployment AssignmentID
SELECT
rs.Name0 AS DeviceName,
uas.ResourceID,
uas.AssignmentID,
uas.LastEnforcementMessageID AS LastEnforcementStateID,
sn.StateName AS LastEnforcementState,
uas.LastEnforcementMessageTime,
uas.LastEnforcementErrorCode
FROM dbo.v_UpdateAssignmentStatus AS uas
INNER JOIN dbo.v_R_System AS rs
ON rs.ResourceID = uas.ResourceID
LEFT JOIN dbo.v_StateNames AS sn
ON sn.StateID = uas.LastEnforcementMessageID
AND sn.TopicType = 301
WHERE uas.AssignmentID = @AssignmentID
ORDER BY uas.LastEnforcementMessageTime DESC;The timestamp is the latest enforcement message recorded in the site database; it is not a guaranteed real-time timestamp from the client. The view and state-topic relationship are documented by Microsoft in Configuration Manager SQL views.
Per-update query: one row per update and device
Choose this query when a device can have different results for different updates in the same deployment. v_UpdateComplianceStatus stores per-update compliance and enforcement information. Join v_UpdateInfo for the article number, bulletin, and title.
Free tools Windows power users keep installed
One-click scans. No signup required.
#1 Best Overall
- Used Book in Good Condition
DECLARE @AssignmentID INT = 12345678;
SELECT
rs.Name0 AS DeviceName,
ucs.ResourceID,
ucs.CI_ID,
ui.ArticleID,
ui.BulletinID,
ui.Title AS UpdateTitle,
ucs.LastEnforcementMessageID AS LastEnforcementStateID,
sn.StateName AS LastEnforcementState,
ucs.LastEnforcementMessageTime,
ucs.LastStatusCheckTime
FROM dbo.v_UpdateComplianceStatus AS ucs
INNER JOIN dbo.v_R_System AS rs
ON rs.ResourceID = ucs.ResourceID
INNER JOIN dbo.v_UpdateInfo AS ui
ON ui.CI_ID = ucs.CI_ID
LEFT JOIN dbo.v_StateNames AS sn
ON sn.StateID = ucs.LastEnforcementMessageID
AND sn.TopicType = 402
INNER JOIN dbo.v_CIAssignmentToCI AS aci
ON aci.CI_ID = ucs.CI_ID
WHERE aci.AssignmentID = @AssignmentID
ORDER BY rs.Name0, ucs.LastEnforcementMessageTime DESC;Microsoft’s sample software-update queries use these joins and return StateName and LastEnforcementMessageTime; see Sample queries for software updates. Test the assignment-membership join in your site because adding assignment metadata can change the row grain.
Which view and topic type should you use?
| Report requirement | Primary view | State topic type |
|---|---|---|
| Last enforcement for a deployment assignment and device | v_UpdateAssignmentStatus |
301 |
| Last enforcement for an individual update and device | v_UpdateComplianceStatus |
402 |
| Software-update detection or compliance state | v_UpdateComplianceStatus.Status |
500 |
Do not join a deployment-assignment row with topic type 402 or a per-update row with 301. A state ID without its topic type can map to the wrong description or produce duplicate matches. Enforcement, detection, and compliance are separate signals; an enforcement message alone does not prove that an update is installed or compliant.
Rank #2
- Manage your employees' requests for days off in this 6-month, dated logbook / notebook
- Includes annual, monthly, and holiday calendars with space for 8 entries per day
- Pages and labeled monthly tabbed dividers are 8.5 x 11 inches.
- Front and back covers are UV coated for water resistence, providing needed durability
- Bound with durable plastic coil so book lays conveniently flat when open. Made in the U.S.A.
Add deployment or collection information
If the report needs a human-readable assignment name, extend the per-update query with the documented assignment views:
INNER JOIN dbo.v_CIAssignmentToCI AS aci
ON aci.CI_ID = ui.CI_ID
INNER JOIN dbo.v_CIAssignment AS a
ON a.AssignmentID = aci.AssignmentIDSelect the appropriate name and collection columns exposed in your site version. Microsoft describes these relationships in its software-update SQL samples.
Quick wins for a faster PC:
Repair Windows errors before they cause bigger problemsFix Now →Fix the driver behind crashes, sound loss and screen glitchesFind Drivers →Rank #3
- EASY TO USE - The manager notebook is easy-to-use that help you keep track of shift notes, employees, etc.
- MONITOR YOUR DATAS - Using a project manager notebook to store all your data, you can track your comps, sales, payments, and customer behavior,consult your records whenever needed.
- HIGH QUALITY - The manager office supplies is used to high quality 100gsm pure white paper, elastic band and a back pocket for extra space. Make sure you have enough space for all manager plan
- UNIQUE DESIGN & A4 SIZE - Manager log book cover is lovely, golden spiral bound design, size of 8.2" x 10.5". Just the perfectly size to fit in your backpack, purse or laptop case. Without taking up your space and always helping you keep track of your small business
- THE PERFECT GIFT - Management logbook as gift for woman & man. Use it to improve your management efficiency, make efficient adjustments whenever needed
Return only rows with an enforcement error
For the deployment-level query, add this predicate when you want nonzero recorded error codes:
AND ISNULL(uas.LastEnforcementErrorCode, 0) <> 0A null or zero error code does not establish compliance. It only means that this particular error-code test found no nonzero value; inspect the enforcement, detection, and compliance states separately.
Rank #4
Troubleshoot missing or unexpected results
No friendly state name
- Use a
LEFT JOINso the numeric state ID and timestamp remain visible. - Verify the topic predicate: 301 for
v_UpdateAssignmentStatus, 402 forv_UpdateComplianceStatus. - The ID may be null, unsummarized, or absent because the client has not sent a state message.
- Check that the reporting account can read
v_StateNames.
Null enforcement time
A null LastEnforcementMessageTime normally means that no enforcement message has been recorded for that row. Do not replace it with a made-up date. To list dated rows first:
ORDER BY
CASE WHEN LastEnforcementMessageTime IS NULL THEN 1 ELSE 0 END,
LastEnforcementMessageTime DESC;Duplicate rows
First decide whether the intended grain is one row per deployment and device or one row per update, deployment, and device. Assignment-membership joins can legitimately produce multiple rows. Fix the join or rank records at the desired grain rather than adding DISTINCT blindly. For example:
Best Value
- Made in USA - Proudly produced in Ohio by a Veteran-owned business
- This Wire-O book contains spaces for managers to keep track of shift notes, employees, etc
- There are spaces to keep lists of top level items as well as daily to-do lists
- You can track your comps, sales, payments, and customer behavior
- 100 Pages, Wire-O, 8.5" x 11" Reorder SKU: LOG-100-7CW-PP(ManagerNotebook)
WITH RankedStatus AS
(
SELECT
ucs.*,
ROW_NUMBER() OVER
(
PARTITION BY ucs.ResourceID, ucs.CI_ID
ORDER BY ucs.LastEnforcementMessageTime DESC
) AS rn
FROM dbo.v_UpdateComplianceStatus AS ucs
)
SELECT *
FROM RankedStatus
WHERE rn = 1;The view is not found or the query is slow
The documented software-update view is v_UpdateAssignmentStatus, not the frequently copied v_AssignmentStatus. A related v_UpdateAssignmentStatus_Live view contains a smaller subset of assignment information. Confirm local availability and columns before changing the query. Apply AssignmentID or ResourceID filters early, select only required columns, and test against the correct site database or reporting replica. For aggregated error counts, a built-in Configuration Manager report may be more efficient than raw client rows. See Microsoft’s view reference at status and alert SQL views.
Validate the schema in your environment
Microsoft documents the views, but product version, permissions, replication, and reporting connections can affect what is exposed. Check the objects first:
Quick Recap
SELECT name, type_desc
FROM sys.objects
WHERE name IN
(
'v_UpdateAssignmentStatus',
'v_UpdateAssignmentStatus_Live',
'v_UpdateComplianceStatus',
'v_StateNames',
'v_R_System',
'v_UpdateInfo'
);Then inspect columns:
SELECT
c.name AS ColumnName,
t.name AS DataType,
c.max_length
FROM sys.columns AS c
INNER JOIN sys.types AS t
ON t.user_type_id = c.user_type_id
WHERE c.object_id = OBJECT_ID('dbo.v_UpdateAssignmentStatus')
ORDER BY c.column_id;Practical selection rule
- Start with
v_UpdateAssignmentStatuswhen the key isAssignmentID + ResourceID. - Start with
v_UpdateComplianceStatuswhen the key isCI_ID + ResourceID. - Always include the raw enforcement ID, friendly state name, and message timestamp so unresolved mappings remain diagnosable.
- Use the correct state topic type and interpret enforcement separately from compliance.
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
