Sample Queries for Discovery in Configuration Manager
Updated: January 1, 2014
Applies To: System Center 2012 Configuration Manager, System Center 2012 Configuration Manager SP1, System Center 2012 R2 Configuration Manager
The following sample queries demonstrate how to join Configuration Manager discovery views to each other and views from other view categories. Discovery views use the ResourceID column when joining to other views.
Joining Discovery Views
The following query retrieves all resources and their associated IP addresses. The query joins the v_R_System and v_RA_System_IPAddresses discovery views by using the ResourceID column.
SELECT DISTINCT SYS.Netbios_Name0, SYSIP.IP_Addresses0
FROM v_R_System SYS INNER JOIN v_RA_System_IPAddresses SYSIP
ON SYS.ResourceID = SYSIP.ResourceID
ORDER BY SYS.Netbios_Name0
Joining Resource and Inventory Views
The following query retrieves all resources that have a local fixed disk listed in inventory and displays the NetBIOS name, the free disk space, and sorts the data in ascending order by free disk space. The query joins the v_R_System discovery view and the v_GS_LOGICAL_DISK hardware inventory view by using the ResourceID column.
SELECT DISTINCT SYS.Netbios_Name0, LD.FreeSpace0
FROM v_R_System SYS INNER JOIN v_GS_LOGICAL_DISK LD
ON SYS.ResourceID = LD.ResourceID
WHERE LD.Description0 LIKE 'Local fixed disk'
ORDER BY LD.FreeSpace0
Joining Resource and Collection Views
The following query retrieves all resources in the All Systems collection and displays the NetBIOS name, domain name, and associated IP addresses. The query results are sorted by NetBIOS name. The query joins the v_R_System and v_RA_System_IPAddresses discovery views, and joins the v_FullCollectionMembership collection view by using the ResourceID column.
SELECT DISTINCT SYS.Netbios_Name0, FCM.Domain, SYSIP.IP_Addresses0
FROM v_R_System SYS INNER JOIN v_FullCollectionMembership FCM
ON SYS.ResourceID = FCM.ResourceID
INNER JOIN v_RA_System_IPAddresses SYSIP
ON SYS.ResourceID = SYSIP.ResourceID
WHERE FCM.CollectionID = 'SMS00001'
ORDER BY SYS.Netbios_Name0
Joining Resource, Software Updates, and Status Views
The following query retrieves all resources that have performed a scan for software updates, the last scan time, the last scan state, and the Windows Update Agent version on the client. The query joins the v_R_System discovery view and v_UpdateScanStatus software updates view by using the ResourceID column, and it uses LEFT OUTER JOIN between the v_UpdateScanStatus software updates view and v_StateNames status view by using the LastScanState and StateID columns. The state message topic types are filtered by TopicType = 501, which indicates scan-state messages.
Note
The state topic type, state ID, state name, and state description for all Configuration Manager state messages are listed in the v_StateNames view.
SELECT DISTINCT v_R_System.Netbios_Name0 AS [Computer Name],
v_UpdateScanStatus.LastScanTime AS [Last Scan],
v_UpdateScanStatus.LastWUAVersion AS [WUA Version],
v_StateNames.StateName AS [Last Scan State]
FROM v_UpdateScanStatus INNER JOIN v_R_System ON
v_UpdateScanStatus.ResourceID = v_R_System.ResourceID LEFT OUTER JOIN
v_StateNames ON v_UpdateScanStatus.LastScanState = v_StateNames.StateID
WHERE (v_StateNames.TopicType = 501)