Sample queries for software updates in Configuration Manager
The following sample queries demonstrate how to join software updates views to each other and to views from other view categories. Software updates views will most often use the CI_ID column when joining to other views.
Joining software updates, discovery, and status views
The following query retrieves the article ID, bulletin ID, software update title, last enforcement state for the update, the time of the last enforcement check, and the time that the last enforcement state message was sent by the Computer1 client. The results are sorted by state name and then by the last modified date for the software update. The query joins the v_UpdateComplianceStatus status view with the v_UpdateInfo software updates view by using the CI_ID column, the v_UpdateComplianceStatus status view with the v_R_System discovery view by using the ResourceID column, and the v_UpdateComplianceStatus status view with the v_StateNames status view by using the LastEnforcementStatus and StateID columns, respectively. The retrieved information is filtered by the topic type of 402, which includes state messages for configuration item enforcement, and a computer with the NetBIOS name of Computer1.
SELECT v_UpdateInfo.ArticleID, v_UpdateInfo.BulletinID, v_UpdateInfo.Title,
��v_StateNames.StateName, v_UpdateComplianceStatus.LastStatusCheckTime,
��v_UpdateComplianceStatus.LastEnforcementMessageTime
FROM v_R_System INNER JOIN v_UpdateComplianceStatus ON
��v_R_System.ResourceID = v_UpdateComplianceStatus.ResourceID INNER JOIN v_UpdateInfo ON
��v_UpdateComplianceStatus.CI_ID = v_UpdateInfo.CI_ID INNER JOIN v_StateNames ON
��v_UpdateComplianceStatus.LastEnforcementMessageID = v_StateNames.StateID
WHERE (v_StateNames.TopicType = 402) AND (v_R_System.Netbios_Name0 LIKE 'Computer1')
ORDER BY v_StateNames.StateName, v_UpdateInfo.DateLastModified
Joining software updates and compliance settings views
The following query retrieves the software update deployments, by assignment ID (software update deployment ID) and assignment name (deployment name); the software updates that are contained in the deployment, by article ID, bulletin ID, and software update title; and the target collection for the deployment. The results are sorted by the assignment ID and then by article ID. The query joins the v_UpdateInfo software updates view with the v_CIAssignmentToCI compliance settings view by using the CI_ID column, and it joins the v_CIAssignmentToCI view to the v_CIAssignment compliance settings view by using the AssignmentID column.
SELECT v_CIAssignment.AssignmentID, v_CIAssignment.AssignmentName,
��v_UpdateInfo.ArticleID, v_UpdateInfo.BulletinID, v_UpdateInfo.Title,
��v_CIAssignment.CollectionName, v_CIAssignment.CollectionID
FROM v_UpdateInfo INNER JOIN v_CIAssignmentToCI ON
��v_UpdateInfo.CI_ID = v_CIAssignmentToCI.CI_ID INNER JOIN v_CIAssignment ON
��v_CIAssignmentToCI.AssignmentID = v_CIAssignment.AssignmentID
ORDER BY v_CIAssignment.AssignmentID, v_UpdateInfo.ArticleID
See also
Feedback
https://aka.ms/ContentUserFeedback.
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see:Submit and view feedback for