SCCM Patch Reporting-Patch 3-Update List Missing Patch counts grouped by Patch

--SSM - Patch - 3 - Update List Missing Patch counts grouped by Patch (ver3)
 
DECLARE @CollID varchar(64)
--SELECT @CollID = '00901930' --MS patch testing      2:45
--SELECT @CollID = '00901C56' -- 042: Patch Distribution  2:55          1:11 when i ADDED updateinfo
--SELECT @CollID = '00901C51' -- 009: Patch Distribution    2:44  4681  -changed to 1:14 when i ADDED updateinfo
--SELECT @CollID = '00900663' -- 042: Systems
--SELECT @CollID = '00901C47' -- 000: Monthly Patch Distribution
SELECT @CollID = '00900E33' --012: Systems
--SELECT @CollID = '0090001B' --009: Systems
 
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
 
--select @AuthListName, @AuthListID as 'AuthListID', @AuthList_CI_ID as 'AuthListCIID'
 
--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 
 
 
--SELECT SYS.Netbios_name0, Count(*) as 'Missing Patch Count'--UCSA.Status, CI.*, UI.*
SELECT
  Count(*) AS 'Missing Patch Count'
  ,SUM(CASE WHEN UCSA.STATUS = '0' THEN 1 ELSE 0 END) AS 'Unknown'
  --,SUM(CASE WHEN UCSA.Status = '1' THEN 1 ELSE 0 END) AS 'Not Required'
  ,SUM(CASE WHEN UCSA.STATUS = '2' THEN 1 ELSE 0 END) AS 'Required'
  --,SUM(CASE WHEN UCSA.Status = '3' THEN 1 ELSE 0 END) AS 'Installed'
  , UCSA.STATUS
  , UI.BulletinID
  , UI.ArticleID
  , UI.Title
  , UI.Description
  , UI.InfoURL
 
FROM @CI CI
  JOIN v_Update_ComplianceStatusAll UCSA ON UCSA.CI_ID = CI.CI_ID
  JOIN v_R_System SYS ON SYS.ResourceID = UCSA.ResourceID
  JOIN v_UpdateInfo UI ON UI.CI_ID = CI.CI_ID
WHERE
  UCSA.ResourceID IN
    (SELECT DISTINCT ResourceID
    FROM v_ClientCollectionMembers
    --FROM v_FullCollectionMembership
    WHERE    CollectionID = @CollID)
  --and UCSA.Status = 2
  AND UCSA.STATUS IN (0,2)
  --and SYS.Client0 = 1
  AND (SYS.Operating_System_name_and0 LIKE '%workstation%' 
    OR SYS.Operating_System_name_and0 IS NULL)
  AND SYS.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' 
    ) 
 
 
--GROUP BY SYS.Netbios_name0
GROUP BY UCSA.STATUS, UI.BulletinID, UI.ArticleID, UI.Title, UI.Description, UI.InfoURL
--ORDER BY Count(*) DESC
--ORDER BY UCSA.Status DESC, SYS.Netbios_Name0
ORDER BY Required DESC, Unknown DESC
 

• < Prev
• Next >