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

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

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

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]

  v_UpdateScanStatus.LastWUAVersion AS [WUA Version],

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)

Concepts