SCCM Patch Reporting-Patch 1-Update List Missing Patch counts by Computer

This will give a breakdown of patch compliancy for a specific Collection.

--SSM - Patch - 1 - Update List Missing Patch counts by Computer (ver5)
 
DECLARE @CollID varchar(256)
--Set @CollID = '009014AE'  --023: Systems
--Set @CollID = '00900E33'  --012: Systems
--Set @CollID = '0090095B'  --122: Systems
--Set @CollID = '00900383'  --011: Systems
--Set @CollID = '009000EE'  --801: Systems
--SELECT @CollID = '00900E33' --012: Systems
--SELECT @CollID = '0090001B' --009: Systems
SELECT @CollID = '0090189F'  --007:Systems
 
SET NOCOUNT ON
 
DECLARE @AuthListName varchar(128) 
SELECT @AuthListName = 'Windows-Critical and Security Updates' 
 
DECLARE @AuthListID varchar(128); SELECT @AuthListID = CI_UniqueID FROM v_AuthListInfo WHERE Title = @AuthListName 
DECLARE @AuthList_CI_ID int; SELECT @AuthList_CI_ID=CI_ID FROM v_AuthListInfo WHERE Title = @AuthListName 
 
 
--Get the CI_IDs from the AuthList and put in a table variable
DECLARE @CI TABLE(CI_ID int PRIMARY KEY) 
INSERT @CI 
SELECT CIR.ToCIID 
FROM v_CIRelation CIR 
WHERE CIR.FromCIID = @AuthList_CI_ID 
 
--@RESOURCEIDS is the list of systems that we are reporting on
DECLARE @RESOURCEIDS TABLE(ResourceID varchar(10) PRIMARY KEY)
INSERT @RESOURCEIDS
  SELECT COLLMEM.ResourceID
  FROM v_ClientCollectionMembers COLLMEM
  LEFT JOIN v_R_System SYS ON COLLMEM.ResourceID = SYS.ResourceID
  WHERE COLLMEM.CollectionID = @CollID
    AND (SYS.Operating_System_name_and0 LIKE '%workstation%' 
      OR SYS.Operating_System_name_and0 IS NULL)
    AND COLLMEM.ResourceID NOT IN
      (SELECT CCM.ResourceID
      FROM v_ClientCollectionMembers CCM
      JOIN v_Collection COLL ON CCM.CollectionID = COLL.CollectionID
      WHERE COLL.Name = '000: RA1000 and CA1000'
        OR COLL.Name = '000: DenyPatch'
        OR COLL.Name = '000: DenySCCMClient'
      )
 
 
--Build Temp Table of patch state for each system in the collection
--Don't include v_r_system here or it makes the query take 20 times as long (Last query will get that data)
DECLARE @TEMPNUMS TABLE
  (ResourceID varchar(30)
  , TotMissing int
  , Unknown int
  , Required int
  )
INSERT @TEMPNUMS
  SELECT RIDS.ResourceID
    , Count(*)-- as 'Missing Patch Count'
    , SUM(CASE WHEN UCSA.STATUS = '0' THEN 1 ELSE 0 END)-- AS 'Unknown'
    , SUM(CASE WHEN UCSA.STATUS = '2' THEN 1 ELSE 0 END)-- AS 'Required'
  FROM @RESOURCEIDS RIDS
    LEFT JOIN v_Update_ComplianceStatusAll UCSA ON RIDS.ResourceID = UCSA.ResourceID
    JOIN @CI CI ON UCSA.CI_ID = CI.CI_ID
  WHERE
    UCSA.STATUS IN (0,2)
  GROUP BY RIDS.ResourceID
 
 
--Display the Results (join with v_R_System now...)
SELECT --@AuthListName AS 'Update List'
  SYS.Netbios_name0 AS 'Name'
  , SYS.ResourceID
  , SYS.Operating_System_name_and0
  , SYS.Client0 AS 'Client'
  --, SYS.User_Name0 AS 'UserName'
  , SCU.TopConsoleUser0 AS 'User'
  , TEMPNUMS.TotMissing AS 'Total Missing Patch Count'
  , TEMPNUMS.Unknown AS 'Unknown Patch Count'
  , TEMPNUMS.Required AS 'Required Patch Count'
  , SYS.Description0 AS 'Description'
FROM @TEMPNUMS TEMPNUMS
  LEFT JOIN v_R_System SYS ON TEMPNUMS.ResourceID = SYS.ResourceID
  LEFT JOIN v_GS_SYSTEM_CONSOLE_USAGE SCU ON TEMPNUMS.ResourceID = SCU.ResourceID
ORDER BY SYS.Client0, TEMPNUMS.Unknown DESC, TEMPNUMS.Required DESC, SYS.Name0
 

• < Prev
• Next >