Version: SCCM 2012 SP1 CU2
This will generate a list of software update activity for a given computer. This may be helpful when troubleshooting and you need to know which software updates were recently installed. The results are displayed chronologically showing the most recent activity at the top.
If you are running this in SQL Management Studio, either declare and set the @variable or you can put the computer name directly in the where section. If you will use SSRS, then this will serve as a parameter for your report.
DECLARE @Variable nvarchar(15) SET @Variable = 'computer_name' select distinct ucs.ArticleID,ucs.BulletinID,ucs.LastEnforcementMessageName,case ucs.status when 3 then 'Installed' when 2 then 'Required' else 'Unknown' end as 'Status',ucs.LastEnforcementMessageTime,ucs.LocalizedDisplayName, ucs.LocalizedDescription,ucs.LocalizedInformativeURL from vSMS_Update_ComplianceStatus as ucs JOIN v_R_System sys on ucs.MachineID=sys.ResourceID where sys.Name0=@Variable and LastEnforcementMessageTime is not NULL Order by LastEnforcementMessageTime desc
The results display specific information for each software update entry: Article ID, Bulletin ID, Enforcement Message, Enforcement Time, Localized Display Name, Localized Description, URL, and Status.