Last active
November 15, 2019 11:41
-
-
Save Ioan-Popovici/1bd7003f2f2ebb814f8c1acae9576c1c to your computer and use it in GitHub Desktop.
Summarizes the software update compliance in SCCM by Collection and All Updates.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* | |
.SYNOPSIS | |
Summarizes the software update compliance for a Collection in SCCM. | |
.DESCRIPTION | |
Summarizes the software update compliance in SCCM by Collection and All Updates. | |
.NOTES | |
Requires SQL 2012 R2. | |
Part of a report should not be run separately. | |
*/ | |
/*##=============================================*/ | |
/*## QUERY BODY */ | |
/*##=============================================*/ | |
/* #region QueryBody */ | |
/* Testing variables !! Need to be commented for Production !! */ | |
--DECLARE @UserSIDs AS NVARCHAR(10) = 'Disabled'; | |
--DECLARE @CollectionID AS NVARCHAR(10) = 'SMS00001'; | |
--DECLARE @Locale AS INT = '2'; | |
--DECLARE @UpdateClassifications AS NVARCHAR(250) = 'Security Updates'; | |
--DECLARE @ExcludeArticleIDs AS NVARCHAR(250) = '' --('915597,2267602,2461484') --AV Definitions; | |
/* Perform cleanup */ | |
IF OBJECT_ID('tempdb..#SummarizationInfo', 'U') IS NOT NULL | |
DROP TABLE #SummarizationInfo; | |
/* Variable declaration */ | |
DECLARE @LCID AS INT = dbo.fn_LShortNameToLCID (@Locale); | |
DECLARE @ClientDevices AS INT = ( | |
SELECT COUNT(ResourceID) | |
FROM fn_rbac_ClientCollectionMembers(@UserSIDs) AS ClientCollectionMembers | |
WHERE ClientCollectionMembers.CollectionID = @CollectionID | |
) | |
DECLARE @TotalDevices AS INT = ( | |
SELECT COUNT(ResourceID) | |
FROM fn_rbac_FullCollectionMembership(@UserSIDs) AS CollectionMembership | |
WHERE CollectionMembership.CollectionID = @CollectionID | |
AND CollectionMembership.ResourceType = 5 --Select devices only | |
) | |
DECLARE @NonClientDevices AS INT = @TotalDevices - @ClientDevices | |
/* Get compliance data data */ | |
; | |
WITH SummarizationInfo_CTE AS ( | |
SELECT DISTINCT | |
ComplianceStatus.ResourceID | |
, MissingUniqueUpdates = ( | |
DENSE_RANK() OVER(PARTITION BY CollectionMembers.CollectionID ORDER BY ComplianceStatus.Status, UpdateCIs.ArticleID) | |
+ | |
DENSE_RANK() OVER(PARTITION BY CollectionMembers.CollectionID ORDER BY ComplianceStatus.Status, UpdateCIs.ArticleID DESC) | |
- 1 | |
) | |
, NonCompliantDevices = ( | |
DENSE_RANK() OVER(PARTITION BY CollectionMembers.CollectionID ORDER BY ComplianceStatus.Status, ComplianceStatus.ResourceID) | |
+ | |
DENSE_RANK() OVER(PARTITION BY CollectionMembers.CollectionID ORDER BY ComplianceStatus.Status, ComplianceStatus.ResourceID DESC) | |
- 1 | |
) | |
, Classification = CICategory.CategoryInstanceName | |
FROM fn_rbac_R_System(@UserSIDs) AS Systems | |
JOIN v_UpdateComplianceStatus AS ComplianceStatus ON ComplianceStatus.ResourceID = Systems.ResourceID | |
AND ComplianceStatus.Status IN (0, 2) --Unknown, Required | |
JOIN v_ClientCollectionMembers AS CollectionMembers ON CollectionMembers.ResourceID = ComplianceStatus.ResourceID | |
JOIN fn_ListUpdateCIs(@LCID) AS UpdateCIs ON UpdateCIs.CI_ID = ComplianceStatus.CI_ID | |
AND UpdateCIs.CIType_ID IN (1, 8) --1 Software Updates, 8 Software Update Bundle (v_CITypes) | |
AND UpdateCIs.IsExpired = 0 | |
AND UpdateCIs.ArticleID NOT IN ( --Exclude Updates based on ArticleID | |
SELECT VALUE FROM STRING_SPLIT(@ExcludeArticleIDs, ',') | |
) | |
JOIN v_CICategoryInfo_All AS CICategory ON CICategory.CI_ID = ComplianceStatus.CI_ID | |
AND CICategory.CategoryTypeName = 'UpdateClassification' | |
AND CICategory.CategoryInstanceName IN (@UpdateClassifications) --Join only selected Update Classifications | |
LEFT JOIN v_CITargetedMachines AS Targeted ON Targeted.ResourceID = ComplianceStatus.ResourceID | |
AND Targeted.CI_ID = ComplianceStatus.CI_ID | |
WHERE CollectionMembers.CollectionID = @CollectionID | |
) | |
/* Insert into SummarizationInfo */ | |
SELECT | |
Classification | |
, MissingUniqueUpdates | |
, NonCompliantDevices | |
, NonComplianceByClassification = Count(*) | |
INTO #SummarizationInfo | |
FROM SummarizationInfo_CTE | |
GROUP BY | |
Classification | |
, MissingUniqueUpdates | |
, NonCompliantDevices | |
/* Display summarized result */ | |
IF (SELECT COUNT(1) FROM #SummarizationInfo) = 0 --If compliant (null result) | |
BEGIN | |
SELECT | |
Classification = 'Selected Classifications' | |
, MissingUniqueUpdates = 0 | |
, CompliantDevices = @ClientDevices | |
, NonCompliantDevices = 0 | |
, ComplianceByClassification = @ClientDevices | |
, NonComplianceByClassification = 0 | |
, ClientDevices = @ClientDevices | |
, NonClientDevices = @NonClientDevices | |
, TotalDevices = @TotalDevices | |
END | |
ELSE | |
BEGIN | |
SELECT | |
Classification = Classification | |
, MissingUniqueUpdates = MissingUniqueUpdates | |
, CompliantDevices = ISNULL(@ClientDevices - NonCompliantDevices, 0) | |
, NonCompliantDevices = ISNULL(NonCompliantDevices, 0) | |
, ComplianceByClassification = @ClientDevices - NonComplianceByClassification | |
, NonComplianceByClassification = NonComplianceByClassification | |
, ClientDevices = @ClientDevices | |
, NonClientDevices = @NonClientDevices | |
, TotalDevices = @TotalDevices | |
FROM #SummarizationInfo | |
GROUP BY | |
Classification | |
, MissingUniqueUpdates | |
, NonCompliantDevices | |
, NonComplianceByClassification | |
END | |
/* Perform cleanup */ | |
IF OBJECT_ID('tempdb..#SummarizationInfo', 'U') IS NOT NULL | |
DROP TABLE #SummarizationInfo; | |
/* #endregion */ | |
/*##=============================================*/ | |
/*## END QUERY BODY */ | |
/*##=============================================*/ |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment