Number of critical and security updates needed

Posted by robd on August 09, 2017

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 )
     INSERT INTO @groups SELECT @groupId
     SET @groupId = (
         SELECT ParentTargetGroupId
        FROM PUBLIC_VIEWS.vComputerTargetGroup
         WHERE vComputerTargetGroup.ComputerTargetGroupId = @groupId     )
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



Tags: ,

2 Comments to Number of critical and security updates needed

  • Hey this is a very helpful script thanks a lot
    I have still a challenge would it be possible to adjust the script still so far that one per server synonymous eagerly sees which updates (security critical) are needed?
    I am not able to do this myself because I have no knowledge about sql scripting

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.