Following on from “Don’t believe Windows Updates”, SMS ships with a report that lists the “Software updates for a specific system”. This isn’t exactly what I want as it lists both installed and uninstalled updates. However, it’s a pretty simple matter to clone that report and produce one called “Software updates not installed for a specific system” which is more like what I want:
select summ.ID, summ.QNumbers, summ.Title, summ.Product, summ.LocaleID,
summ.Language, summ.InfoPath from v_R_System sys
join v_GS_PatchStatus ps on sys.ResourceID=ps.ResourceID
join v_ApplicableUpdatesSummary summ on ps.ID=summ.ID and ps.QNumbers=summ.QNumbers and ps.Product=summ.Product and ps.LocaleID=summ.LocaleID and ps.Title=summ.Title where sys.Netbios_Name0 like @Name and ps.LastState!=105
order by summ.ID
But never satisfied until I can get one report that does it all, I cloned and massaged the above to give me “Software updates not installed for a specific collection”:
select sys.Name0, summ.ID, summ.QNumbers, summ.Title, summ.Product,
summ.LocaleID, summ.Language, summ.InfoPath from v_R_System sys
join v_FullCollectionMembership fcm on sys.ResourceID=fcm.ResourceID
join v_GS_PatchStatus ps on sys.ResourceID=ps.ResourceID
join v_ApplicableUpdatesSummary summ on ps.ID=summ.ID and ps.QNumbers=summ.QNumbers and ps.Product=summ.Product and ps.LocaleID=summ.LocaleID and ps.Title=summ.Title
where fcm.CollectionID = @CollID and ps.LastState!=105
order by sys.Name0
Where you need to remove the prompt for Name and add a prompt for CollID:
Name: CollID
Prompt text: Collection ID
Allow an empty value:
Provide a SQL statement:
begin
if (@__filterwildcard = '')
select CollectionID, Name from v_Collection order by Name
else
select CollectionID, Name from v_Collection
WHERE CollectionID like @__filterwildcard
order by Name
end
Thus it’s an easily matter to find all those computers that aren’t patched 
!
(and now all I need is a collection containing all the servers 
.)
—
(Modified 2004–12–14 to list and sort by computer name)