Collection Query to show all clients where authorized patches are missing (SMS2003 ITMU)
This SMS2003 Collection-Query (WQL) shows all clients where authorized security patches (authorize date > 15 days) are "applicable":
select * from SMS_R_System
inner join SMS_G_System_PATCHSTATEEX on SMS_G_System_PATCHSTATEEX.ResourceID = SMS_R_System.ResourceId
where SMS_G_System_PATCHSTATEEX.QNumbers in (select distinct QNumbers from SMS_ApplicableUpdatesSummaryEx where SMS_ApplicableUpdatesSummaryEx.TimeAuthorized != null and ScanAgent = "Microsoft Updates Tool" and DATEDIFF(dd,TimeAuthorized, GETDATE()) > 15)
and SMS_G_System_PATCHSTATEEX.Status = "Applicable"
Caution: The authorize date will not synchronized to child sites...
Requirements: SMS2003, ITMU