Software Update View Sample Queries
The following sample queries demonstrate how to join the most common software update views to other views.
Joining Software Update, Resource, and Inventory Views
The following query lists all software updates applicable and installed for SMS clients running the Microsoft Windows XP operating system. The v_GS_PATCHSTATE view is joined to both the v_R_System and v_GS_OPERATING_SYSTEM views by using the ResourceID column.
SELECT SYS.Netbios_Name0 AS SMSClient, PS.ID0 AS BulletinID,
PS.QNumbers0, PS.Product0, PS.Title0, PS.Status0
FROM v_GS_PATCHSTATE PS INNER JOIN v_R_System SYS
ON PS.ResourceID = SYS.ResourceID INNER JOIN v_GS_OPERATING_SYSTEM OS
ON PS.ResourceID = OS.ResourceID
WHERE OS.Caption0 LIKE 'Microsoft Windows XP%'
ORDER BY PS.Status0, PS.QNumbers0, SYS.Netbios_Name0
Joining Software Update and Software Update Status Views
The following query lists all software updates that are applicable to more than one SMS client and where to get information about the update. The query is sorted in descending order by the updates that are applicable to the most clients. The v_GS_PatchStatusEx view is joined to the v_ApplicableUpdatesSummaryEx view by using the UpdateID column.
SELECT PSE.QNumbers, PSE.ID AS BulletinID, PSE.Title, PSE.Product,
AUS.Counts AS #Applicable, AUS.InfoPath
FROM v_GS_PatchStatusEx PSE INNER JOIN v_ApplicableUpdatesSummaryEx AUS
ON PSE.UpdateID = AUS.UpdateID
WHERE AUS.Counts > 0
ORDER BY AUS.Counts DESC, PSE.Product, PSE.QNumbers