SMS 2003 - Reports - Compliance Contents of Advertisements and Packages
There was a question posed on the forums today asking if there was a method in which an administrator could identify both the advertisement and package which contained a particular security update, identified by MS ID, QNumber or Description. To me, this sounded like a fantastic idea for a report and so I decided to tackle it and see if it was possible.
I don't know about the rest of the SMS community, but I personally am a BIG fan of SQL Server Enterprise Manager. Virtually all my reports are tested within this tool, plus it's a great method to scroll through all the views to see what the beast we call SMS contains. There were several views I noted which pertained to security updates but only two that contained the fields for which I was looking:
v_ApplicableUpdatesSummaryEx
v_UpdatePrograms
The first view contains information about the security updates themselves and has a unique identifier for each update under the field UpdateID. The second table contains information about the programs which contain the updates and the field UpdateID - so the two views can be joined easily. In addition, v_UpdatePrograms also contains the PackageID field, which can be joined to the field of the same name within v_Advertisement.
One thing to remember when using this report is that you MUST identify and exclude the package in your environment which pertains to the Microsoft Updates Tool package for the ITMU. If you do not, you will have a fairly large report, as all updates are tied to this package as well as those you create. Find the package ID for this package and place it's ID number in the report query below.
SELECT VAUSE.ID, VAUSE.QNumbers, VAUSE.Title, VA.AdvertisementID, VUP.PackageID, VUP.ProgramName
FROM v_UpdatePrograms VUP INNER JOIN
v_ApplicableUpdatesSummaryEx VAUSE ON VUP.UpdateID = VAUSE.UpdateID INNER JOIN
v_Advertisement VA ON VUP.PackageID = VA.PackageID
WHERE (VUP.PackageID <> 'PackageIDforMSUTpackage') AND (VAUSE.ID LIKE @msid) AND (VAUSE.QNumbers LIKE @qnumber)
ORDER BY VAUSE.ID
Be sure to add the prompts for msid and qnumber. If you want to be creative you can create prompt queries to list all the MS ID numbers and QNumbers in your environment.
I hope this report helps a few people.