philipnet

There was a request on the MSSMS list for “a way to see which patches a computer is missing”. I thought this was an easy one (and needing to keep my SQL/SMS Reporting skill sharp) so I started on it straight away.

Firstly, I took stock of the additional reports I had previously written: http://windowsadvice.com/blogs/philipnet/archive/2004/12/11/839.aspx and cloned it to give me “Software updates with a list of applicable updates for a computer” (apologies for the title, but I couldn’t come up with anything better). I amended the comment and massaged the SQL to look like:

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 ps.LastState!=105 and sys.Name0 = @Name
order by summ.ID

You also need to change the prompt, so that it asks for the name of the computer (that’s much friendlier than using a the ResourceID of a system):
Name: Name
Prompt text: Name
Allow an empty value:
Provide a SQL statement:
begin
 if (@__filterwildcard = '')
  select Name0 from v_R_System order by Name0
 else
  select Name0 from v_R_System
  WHERE Name0 like @__filterwildcard
  order by Name0
end

But then the MSSMS guys go and produce a MOF – I guess SQL is old hat nowadays!