Tech Blog

Below is a SQL script that will show how many updates are missing that are classified as critical or security for servers on WSUS:

SET NOCOUNT ON DECLARE @TargetGroup nvarchar(30) 
DECLARE @Days int SELECT @TargetGroup = 'All Computers' 
SELECT @Days = 7 DECLARE @groups AS TABLE (Id uniqueidentifier NOT NULL) 
DECLARE @groupId uniqueidentifier SET @groupId = (
     SELECT ComputerTargetGroupId
     FROM PUBLIC_VIEWS.vComputerTargetGroup
     WHERE vComputerTargetGroup.Name = @TargetGroup )
WHILE @groupId IS NOT NULL BEGIN
     INSERT INTO @groups SELECT @groupId
     SET @groupId = (
         SELECT ParentTargetGroupId
        FROM PUBLIC_VIEWS.vComputerTargetGroup
         WHERE vComputerTargetGroup.ComputerTargetGroupId = @groupId     )
END 
DECLARE @updates AS TABLE (Id uniqueidentifier NOT NULL PRIMARY KEY) 
INSERT INTO @updates SELECT distinct vUpdate.UpdateId 
FROM     PUBLIC_VIEWS.vUpdate WHERE    vUpdate.MsrcSeverity is NOT NULL
        AND vUpdate.defaultTitle like '%Security%'
		OR vUpdate.defaultTitle like '%critical%'
SELECT vComputerTarget.Name as 'Computer Name', COUNT(*) AS 'Missing Updates' 
FROM PUBLIC_VIEWS.vComputerGroupMembership
     INNER JOIN PUBLIC_VIEWS.vComputerTarget 
on vComputerGroupMembership.ComputerTargetId = vComputerTarget.ComputerTargetId
     INNER JOIN PUBLIC_VIEWS.vComputerTargetGroup 
on vComputerGroupMembership.ComputerTargetGroupId = vComputerTargetGroup.ComputerTargetGroupId
     INNER JOIN PUBLIC_VIEWS.vUpdateInstallationInfoBasic 
on vUpdateInstallationInfoBasic.ComputerTargetId = vComputerTarget.ComputerTargetId
     INNER JOIN @updates GROUPS
on vUpdateInstallationInfoBasic.UpdateId = GROUPS.Id 
WHERE vComputerTarget.ComputerTargetId = vUpdateInstallationInfoBasic.ComputerTargetId
     AND vUpdateInstallationInfoBasic.State in (2, 3, 5, 6)
       AND vComputerTargetGroup.Name = @TargetGroup 
GROUP BY vComputerTarget.Name 
ORDER BY 'Missing Updates' DESC