SCCM Patch Reporting-Patch 0-Enterprise Summary

This is a fairly complex SCCM report for patching. It uses multiple table variables and a loop to process multiple Collections.

--SSM - Patch - 0 - Enterprise Summary (ver4)
 
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  
 
--Declare main table to store all results in
DECLARE @SSM TABLE
  (
  [CollectionID] varchar(64)  
  , [Collection Name] varchar(64)  
  , [UPDATE List] varchar(64)  
  , [Percentage Patched] decimal(5,2)
  , [Compliant] int  
  , [Non-Compliant] int 
  , [Total Reporting ON] int  
  , [Collection Total] int  
  , [Clients] int  
  , [Non-Clients] int  
  , [Patches Required] int 
  , [Unknown PatchState] int  
  , [PACS] int
  , [DenyPatch] int
  , [DenySCCMClient] int
  , [Servers] int
  ) 
 
--Build table of the collections we will process
DECLARE @CollTable TABLE
  (RowID int IDENTITY(1, 1),  
  CollID varchar(64)) 
 
INSERT INTO @CollTable  
  SELECT CollectionID 
  FROM v_Collection 
  WHERE Name LIKE '%: Systems' 
    OR Name LIKE '000: Deny Auto Patch' 
  ORDER BY Name 
 
--These 2 are used for looping
DECLARE @NumberRecords int; SELECT @NumberRecords = Count(*) FROM @CollTable 
DECLARE @RowCount int; SET @RowCount = 1 
 
 
WHILE @RowCount <= @NumberRecords 
BEGIN 
 
  DECLARE @CollID varchar(64); SELECT @CollID=CollID FROM @CollTable WHERE RowID = @RowCount 
 
  --@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.g_erating_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' 
        ) 
 
  --Misc info about this collections
  DECLARE @CollectionTotal int; SELECT @CollectionTotal=COUNT(*) FROM v_ClientCollectionMembers WHERE CollectionID = @CollID 
  DECLARE @ReportingTotal int; SELECT @ReportingTotal=COUNT(*) FROM @RESOURCEIDS 
  DECLARE @CollName varchar(256); SELECT @CollName=Name FROM v_Collection WHERE CollectionID=@CollID  
 
  --# of systems that are not workstations
  DECLARE @SERVERS int
  SELECT @SERVERS=COUNT(*) 
  FROM v_ClientCollectionMembers CCM JOIN v_R_System SYS ON CCM.ResourceID = SYS.ResourceID
  WHERE CCM.CollectionID = @CollID
    AND SYS.Operating_System_name_and0 NOT LIKE '%workstation%'
  --# of systems that are in the RA1000/CA1000 collection
  DECLARE @PACS int
  SELECT @PACS=COUNT(*) FROM v_ClientCollectionMembers
  WHERE CollectionID = @CollID
    AND ResourceID IN
      (SELECT CCM.ResourceID 
        FROM v_ClientCollectionMembers CCM 
        JOIN v_Collection COLL ON CCM.CollectionID = COLL.CollectionID 
        WHERE COLL.Name = '000: RA1000 and CA1000'
      )
  --# of systems that are in the DenyPatch Collection
  DECLARE @DENYPATCH int
   SELECT @DENYPATCH=COUNT(*) FROM v_ClientCollectionMembers
    WHERE CollectionID = @CollID
      AND ResourceID IN
        (SELECT CCM.ResourceID 
          FROM v_ClientCollectionMembers CCM 
          JOIN v_Collection COLL ON CCM.CollectionID = COLL.CollectionID 
          WHERE COLL.Name = '000: DenyPatch'
        )
  --# of systems that are in the DenySCCMClient Collection
  DECLARE @DENYSCCMCLIENT int
  SELECT @DENYSCCMCLIENT=COUNT(*) FROM v_ClientCollectionMembers
  WHERE CollectionID = @CollID
    AND ResourceID IN
      (SELECT CCM.ResourceID 
        FROM v_ClientCollectionMembers CCM 
        JOIN v_Collection COLL ON CCM.CollectionID = COLL.CollectionID 
        WHERE COLL.Name = '000: DenySCCMClient'
      )
 
  --# of systems with the client installed and working
  DECLARE @NumberClients int 
  SELECT  @NumberClients=isnull(sum(cast(SYS.Client0 AS int)), 0) 
    FROM @RESOURCEIDS RIDS LEFT JOIN v_R_System SYS ON RIDS.ResourceID = SYS.ResourceID  
  --# of systems with out an installed and working client
  DECLARE @NonClients int; SELECT @NonClients = @ReportingTotal - @NumberClients  
 
  --@RESOURCEIDS2 is a list of all ResourceID/CI_ID combos where a patch is missing/applicable
  --Used for performance reasons
  DECLARE @RESOURCEIDS2 TABLE 
    (ResourceID varchar(10) 
    , STATUS int) 
  INSERT @RESOURCEIDS2 
 
      SELECT  COLLMEM.ResourceID, UCSA.STATUS 
      FROM @RESOURCEIDS COLLMEM 
        LEFT JOIN v_Update_ComplianceStatusAll UCSA ON COLLMEM.ResourceID = UCSA.ResourceID 
        JOIN @CI CI ON UCSA.CI_ID = CI.CI_ID 
      WHERE 
        UCSA.STATUS IN (0,2) 
 
  --Misc summary info about patch levels
  DECLARE @NumBad int; SELECT @NumBad=Count(DISTINCT ResourceID) FROM @RESOURCEIDS2 
  DECLARE @NumRequired int; SELECT @NumRequired=Count(DISTINCT ResourceID) FROM @RESOURCEIDS2 WHERE STATUS = 2 
  DECLARE @NumUnknown int; SELECT @NumUnknown=@NumBad - @NumRequired 
  DECLARE @NumCompliant int; SELECT @NumCompliant=@ReportingTotal - @NumBad 
 
  --Add all of the info about this collection to the main table variable
  INSERT INTO @SSM 
  SELECT
    @CollID
    , @CollName
    , @AuthListName
    , LEFT(ROUND((@NumCompliant) / (@ReportingTotal + 0.0001) * 100, 2), 5)  AS 'Percentage Patched'
    , @NumCompliant AS 'Total Compliant' 
    , @NumBad AS 'Total Non-compliant' 
    , @ReportingTotal AS 'Total Reporting On' 
    , @CollectionTotal
    , @NumberClients AS 'Clients' 
    , @NonClients AS 'Non-Clients' 
    , @NumRequired AS 'Patches Required' 
    , @NumUnknown AS 'Unknown Patch State' 
    , @PACS AS 'CA1000-RA1000'
    , @DENYPATCH AS 'DenyPatch'
    , @DENYSCCMCLIENT AS 'DenySCCMClient'
    , @SERVERS AS 'Servers'
 
  --Cleanup befoore loop...
  DELETE FROM @RESOURCEIDS 
  DELETE FROM @RESOURCEIDS2 
  SET @RowCount = @RowCount + 1 
END 
 
--Display info for all collections
SELECT * FROM @SSM
 

• < Prev
• Next >