Discovery View Sample Queries
The following sample queries demonstrate how to join resource views to each other and other views. Each sample query uses the ResourceID column to join the views.
Joining Resource Views
The following query retrieves data from the v_R_System and v_RA_System_IPAddresses resource views by joining them to the ResourceID column. In this example, the query will retrieve all resources and their associated IP addresses.
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 data from the v_R_System discovery view and the v_GS_LOGICAL_DISK view by joining them to the ResourceID column. In this example, the query will retrieve all resources that have a local fixed disk listed in inventory, display the NetBIOS name and free disk space, and sort the data in ascending order by free disk space.
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 data from the v_R_System and v_RA_System_IPAddresses discovery views and the v_FullCollectionMembership collection view by joining them to the ResourceID column. In this example, the query will retrieve all resources in the All Systems collection, display the NetBIOS name, domain name, and associated IP addresses, and sort the data in ascending order by NetBIOS name.
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