The following sample queries demonstrate how to join hardware inventory views to other views that contain system data. Hardware inventory views use the ResourceID column when joining to other views.

Joining Hardware Inventory and Resource Views

The following query lists all inventoried Configuration Manager client computers and the operating system and service pack that are running on the client computer. The v_GS_OPERATING_SYSTEM hardware inventory view and v_R_System discovery view are joined by using the ResourceID column, and the results are sorted by the computer name.

SELECT SYS.Netbios_Name0, OS.Caption0, OS.CSDVersion0

FROM v_GS_OPERATING_SYSTEM OS INNER JOIN v_R_System SYS

  ON OS.ResourceID = OS.ResourceID

ORDER BY SYS.Netbios_Name0

Joining Hardware Inventory and Resource Views

The following query lists all active Configuration Manager clients that have not been scanned for hardware inventory in more than two days. The v_GS_WORKSTATIONSTATUS hardware inventory view and v_RA_System_SMSInstalledSites discovery view are joined to the v_R_System discovery view by using the ResourceID column.

SELECT SYS.Netbios_Name0 as 'Computer Name',

SIS.SMS_Installed_Sites0 as 'SMS Site', WS.LastHWScan,

DATEDIFF(day,WS.LastHWScan,GETDATE()) as 'Days Since HWScan'

FROM v_GS_WORKSTATION_STATUS WS INNER JOIN v_R_System SYS

ON WS.ResourceID = SYS.ResourceID INNER JOIN v_RA_System_SMSInstalledSites SIS

ON WS.ResourceID = SIS.ResourceID

WHERE SYS.Client_Type0 = 1 AND SYS.Active0 = 1 AND

WS.LastHWScan < DATEADD([day],-2,GETDATE())

See Also