Sample Queries for Compliance Settings in Configuration Manager

The following query retrieves all configuration items with the type of Software Updates (CIType_ID = 1) or Software Updates Bundle (CIType_ID = 8) that have been deployed to clients (IsDeployed =1), listing the article ID, software update name, and software update description. The results are sorted in descending order by article ID. The query joins the v_ConfigurationItems and v_CITypes compliance settings views by using the CIType_ID column, joins the v_ConfigurationItems and v_LocalizedCIProperties compliance settings views by using the CI_ID column, and joins the v_ConfigurationItems view with the v_UpdateInfo software updates view by using the CI_ID column.

SELECT v_UpdateInfo.ArticleID, v_LocalizedCIProperties.DisplayName, v_LocalizedCIProperties.Description 
FROM v_ConfigurationItems INNER JOIN v_CITypes ON v_ConfigurationItems.CIType_ID = v_CITypes.CIType_ID 
  INNER JOIN v_LocalizedCIProperties ON v_ConfigurationItems.CI_ID = v_LocalizedCIProperties.CI_ID 
  INNER JOIN v_UpdateInfo ON v_ConfigurationItems.CI_ID = v_UpdateInfo.CI_ID 
WHERE (v_CITypes.CIType_ID = 1 OR v_CITypes.CIType_ID = 8) AND (v_ConfigurationItems.IsDeployed = 1) 
ORDER BY v_UpdateInfo.ArticleID DESC 

The following query retrieves the configuration baselines that have been evaluated on clients, the configuration baseline description, a list of the clients that have a non-compliant state for the configuration baseline, the IP address for the client, and the date and time for the last compliance state message. The results are sorted by configuration baseline name and then computer name. The query joins the v_CIComplianceStatusDetail status message with the v_RA_System_IPAddresses discovery view by using the ResourceID column, and it joins the v_CI_ComplianceStatusDetail view with the v_LocalizedCIProperties compliance settings view by using the CI_ID column. A filter could be added to the query to specify the client computer or the configuration baseline to reduce the query results.

SELECT DISTINCT v_LocalizedCIProperties.DisplayName AS [Baseline Name], 
  v_LocalizedCIProperties.Description AS [Baseline Description], 
  v_CIComplianceStatusDetail.Netbios_Name0 AS [Computer Name], 
  v_RA_System_IPAddresses.IP_Addresses0 AS [IP Address], v_CIComplianceStatusDetail.Severity, 
  v_CIComplianceStatusDetail.LastComplianceMessageTime AS [Last Compliance Message] 
FROM v_CIComplianceStatusDetail INNER JOIN v_RA_System_IPAddresses ON 
  v_CIComplianceStatusDetail.ResourceID = v_RA_System_IPAddresses.ResourceID 
  INNER JOIN v_LocalizedCIProperties ON v_CIComplianceStatusDetail.CI_ID = v_LocalizedCIProperties.CI_ID 
ORDER BY [Baseline Name], [Computer Name] 

The following query retrieves the names of computers that have been targeted for an assignment, the configuration item name assigned to the computer, the compliance state for the item, the assignment name that contains the item, and the target collection for the assignment. The results are sorted by the compliance state, assigned configuration item, and then the computer name. The query joins the v_CICurrentComplianceStatus status view to the v_CIAssignmentToCI compliance settings view by using the CI_ID column; joins the v_CIAssignment and v_CIAssignmentToCI compliance settings views by using the AssignmentID column; joins the v_LocalizedCIProperties compliance settings view to the v_CICurrentComplianceStatus view by using the CI_ID column; joins the v_StateNames and v_CICurrentComplianceStatus status views by using the StateID and ComplianceState columns, respectively; and joins the v_R_System discovery view to the v_CICurrentComplianceStatus view by using the ResourceID column. The retrieved information is filtered by the topic type of 401, which includes state messages for configuration item compliance.

SELECT v_R_System.Netbios_Name0 AS [Computer Name], v_LocalizedCIProperties.DisplayName AS [Assigned Item], 
  v_StateNames.StateName, v_CIAssignment.AssignmentName, v_CIAssignment.CollectionID 
FROM v_CICurrentComplianceStatus 
  INNER JOIN v_CIAssignmentToCI ON v_CICurrentComplianceStatus.CI_ID = v_CIAssignmentToCI.CI_ID 
  INNER JOIN v_CIAssignment ON v_CIAssignmentToCI.AssignmentID = v_CIAssignment.AssignmentID 
  INNER JOIN v_LocalizedCIProperties ON v_CICurrentComplianceStatus.CI_ID = v_LocalizedCIProperties.CI_ID 
  INNER JOIN v_StateNames ON v_CICurrentComplianceStatus.ComplianceState = v_StateNames.StateID 
  INNER JOIN v_R_System ON v_CICurrentComplianceStatus.ResourceID = v_R_System.ResourceID 
WHERE (v_StateNames.TopicType = 401) 
ORDER BY v_StateNames.StateName, [Assigned Item], [Computer Name]