Windows updates deployed through an update group but missing from a system or collection

This shows what updates should have been applied but are still missing, based on a deployment to an update group.  Using an Update Group as a filter, show me all the missing updates that SCCM thinks are required.

Given:

  • A computer or list of computers
    • Change the computer name(s) in Where clause at the bottom
  • or a collection name or list of collections
    • Change the collection name(s) in Where clause at the bottom
  • An update group name (only updates in this group are returned)
    • Change the Update Group name in the sub-select

Return: system name and list of missing patches

[SQL Code]
SELECT DISTINCT
	SYS.Name0 AS [System Name], 
	Case
		When SYS.[Operating_System_Name_and0] = 'Microsoft Windows NT Workstation 5.1' Then 'Windows XP'
		When SYS.[Operating_System_Name_and0] = 'Microsoft Windows NT Workstation 5.2' Then 'Windows XP x64'
		When SYS.[Operating_System_Name_and0] = 'Microsoft Windows NT Workstation 6.0' Then 'Windows Vista'
		When SYS.[Operating_System_Name_and0] = 'Microsoft Windows NT Workstation 6.1' Then 'Windows 7'
		When SYS.[Operating_System_Name_and0] = 'Microsoft Windows NT Workstation 6.2' Then 'Windows 8'
		When SYS.[Operating_System_Name_and0] = 'Microsoft Windows NT Server 4.0'      Then 'Windows NT'
		When SYS.[Operating_System_Name_and0] = 'Microsoft Windows NT Server 5.0'      Then 'Server 2000'
		When SYS.[Operating_System_Name_and0] = 'Microsoft Windows NT Server 5.2'      Then 'Server 2003'
		When SYS.[Operating_System_Name_and0] = 'Microsoft Windows NT Server 6.0'      Then 'Server 2008'
		When SYS.[Operating_System_Name_and0] = 'Microsoft Windows NT Server 6.1'      Then 'Server 2008 R2'
		When SYS.[Operating_System_Name_and0] = 'Microsoft Windows NT Server 6.2'      Then 'Server 2012'
		When SYS.[Operating_System_Name_and0] = 'Microsoft Windows NT Server 6.3'      Then 'Server 2012 R2'
	Else SYS.[Operating_System_Name_and0]
	End as [Operating System],

	CR.Title as [Update Group],
	CASE 
		WHEN UCS.Status = '0' THEN 'Unknown'
		WHEN UCS.Status = '1' THEN 'Not Required'  
		WHEN UCS.Status = '2' THEN 'Required (missing)' 
		WHEN UCS.Status = '3' THEN 'Installed'  
		ELSE '' 
	END AS [Patch Status], 
	IsNull(UI.BulletinID,'(Update Group)') AS [Bulletin ID], 
	IsNUll(UI.ArticleID,'') AS [Article ID], 
	CONVERT(date, UI.DatePosted) as [Date Posted], 
	CASE(UI.Severity)
    	When 0 Then 'N/A'
    	When 2 Then 'Low'
    	When 6 Then 'Moderate'
    	When 8 Then 'Important'
    	When 10 Then 'Critical'
    	Else concat('N/A (' , UI.Severity, ')') 
	End as 'Severity',
	CASE(ui.IsSuperseded)
	    When 0 Then 'No'
	    When 1 Then 'Yes'
	    Else concat('?? (' , UI.IsSuperseded, ')') 
    End as 'Superseded',
	concat (UI.MaxExecutionTime / 60, ' Minutes') as 'MaxExecutionTime',

	UI.Title,
	CASE
		WHEN LastEnforcementMessageID = 1  THEN '1 Enforcement started'
		WHEN LastEnforcementMessageID = 3  THEN '3 Waiting for another installation to complete'
		WHEN LastEnforcementMessageID = 6  THEN '6 General failure'
		WHEN LastEnforcementMessageID = 8  THEN '8 Installing update'
		WHEN LastEnforcementMessageID = 9  THEN '9 Pending system restart'
		WHEN LastEnforcementMessageID = 10 THEN '10 Successfully installed update'
		WHEN LastEnforcementMessageID = 11 THEN '11 Failed to install update'
		WHEN LastEnforcementMessageID = 12 THEN '12 Downloading update'
		WHEN LastEnforcementMessageID = 13 THEN '13 Downloaded update'
		WHEN LastEnforcementMessageID is null THEN '(none)'
		ELSE concat('', LastEnforcementMessageID)
	END AS 'LastEnforcementMessageID',
	UI.InfoURL
FROM         
	v_R_System AS SYS 

	LEFT OUTER JOIN v_Update_ComplianceStatusAll AS UCS ON SYS.ResourceID = UCS.ResourceID 

	INNER JOIN v_UpdateInfo AS UI ON UCS.CI_ID = UI.CI_ID

	Join ( 	select distinct
					AL.Title,
					upd.ArticleID,
					upd.BulletinID
					--,
					--upd.Description,
					--upd.Severity					
			from vSMS_CIRelation as cr
			INNER JOIN fn_ListUpdateCIs(1033) upd ON  upd.CI_ID = cr.ToCIID AND cr.RelationType = 1
			INNER JOIN v_CIToContent CC ON cc.CI_ID=upd.CI_ID
			INNER JOIN v_AuthListInfo AL ON al.CI_ID = cr.FromCIID
			where AL.Title like 'Server Updates%July%2017%'
	) as CR ON  CR.ArticleID = UI.ArticleID

WHERE 
	UI.BulletinID is not null
	---LastEnforcementMessageID = 9
	-- and(UCS.Status IN ('2', '3')) 
	and (UCS.Status IN ('2'))
	AND 
	(SYS.Name0 IN (
			SELECT DISTINCT 
				v_FullCollectionMembership.Name 
			FROM 
				v_FullCollectionMembership 
			INNER JOIN v_R_System ON v_R_System.ResourceID = v_FullCollectionMembership.ResourceID 
			AND v_R_System.Active0 = 1 
			--and v_R_System.Name0 in  ('DECPSQ01')
			AND v_FullCollectionMembership.CollectionID IN ('OR20022E')
		)
	)
order by SYS.Name0, CONVERT(date, UI.DatePosted)
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s