narrow default width wide
colour style colour style colour style colour style

SCCM Patch Reporting-Patch 2-Update List Missing Patch Detail for all Computers




--SSM - Patch - 2 - Update List Missing Patch Detail for all Computers (ver2)
 
SET NOCOUNT ON
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 = '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
 
 
DECLARE @CI TABLE
  (
  CI_ID int PRIMARY KEY
  , BulletinID varchar(255)
  , ArticleID varchar(255)
  , Title varchar(255)
  , InfoURL varchar(255))
 
INSERT INTO @CI
SELECT CIR.ToCIID
  ,UI.BulletinID, UI.ArticleID, UI.Title, UI.InfoURL
FROM v_CIRelation CIR
  JOIN v_UpdateInfo UI ON UI.CI_ID = CIR.ToCIID
WHERE CIR.FromCIID = @AuthList_CI_ID
 
SELECT
  SYS.Netbios_name0
  , CASE UCSA.STATUS
    WHEN '0' THEN 'Unknown'
    WHEN '2' THEN 'Required'
    END
    AS 'Status'
  , CI.*
 
FROM v_R_System SYS
  LEFT JOIN v_Update_ComplianceStatusAll UCSA ON SYS.ResourceID = UCSA.ResourceID
  JOIN @CI CI ON UCSA.CI_ID = CI.CI_ID
WHERE
  UCSA.ResourceID IN
    (SELECT DISTINCT ResourceID
    FROM v_ClientCollectionMembers
    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' 
    ) 
 
ORDER BY UCSA.STATUS DESC, SYS.Netbios_Name0