Hardware Inventory View Sample Queries
The following sample queries demonstrate how to join hardware inventory views to other views that contain system data.
Joining Operating System and Resource Views
The following query lists all SMS clients with the operating system name and service pack that are running on them. The v_GS_OPERATING_SYSTEM and v_R_System views are joined by the ResourceID columns.
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 Workstation Status and Resource Views
The following query lists all active SMS Advanced clients that have not been scanned for hardware inventory in over two days. The v_GS_WORKSTATIONSTATUS, v_R_System, and v_RA_System_SMSInstalledSites views are all joined by the ResourceID columns.
SELECT SYS.Netbios_Name0 as 'NetBIOS 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())
Joining Patch Status and Resource Views
The following query lists the SMS clients that have been scanned for software updates, the name of the update, the last update state for the client, and a link to get more information about the update. The v_GS_PatchStatusEx and v_R_System views are joined by the ResourceID columns and the v_GS_PatchStatusEx and v_ApplicableUpdatesSummaryEx views are joined by the UpdateID, QNumbers, and Title columns. Joining views on multiple columns can be used when the data in a single column may not be unique.
SELECT SYS.Netbios_Name0, PSE.ID as UpdateID, PSE.QNumbers,
PSE.Product, PSE.Title, PSE.LastStateName, AUS.InfoPath
FROM v_R_System SYS INNER JOIN v_GS_PatchStatusEx PSE
ON SYS.ResourceID = PSE.ResourceID INNER JOIN
v_ApplicableUpdatesSummaryEx AUS ON PSE.UpdateID = AUS.UpdateID
AND PSE.QNumbers = AUS.QNumbers AND PSE.Title = AUS.Title
ORDER BY SYS.Netbios_Name0, PSE.ID