Created
November 15, 2019 11:44
-
-
Save Ioan-Popovici/05e7ac7c89755d8c52764bf088fbd0c9 to your computer and use it in GitHub Desktop.
Summarizes the unique required updates in SCCM by Collection and Update Classification.
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 unique required updates for a Collection in SCCM. | |
.DESCRIPTION | |
Summarizes the unique required updates in SCCM by Collection and Update Classification. | |
.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..#UniqueUpdatesInfo', 'U') IS NOT NULL | |
DROP TABLE #UniqueUpdatesInfo; | |
/* Variable declaration */ | |
DECLARE @LCID AS INT = dbo.fn_LShortNameToLCID (@Locale) | |
/* Get update info */ | |
; | |
WITH UpdateInfo_CTE AS ( | |
SELECT | |
ArticleID | |
, Title = DisplayName | |
, Classification = CategoryInstanceName | |
, InformationURL = CIInformativeURL | |
, UpdatesByClassification = ( | |
DENSE_RANK() OVER (PARTITION BY CICategory.CategoryInstanceName ORDER BY UpdateCIs.ArticleID) | |
+ | |
DENSE_RANK() OVER (PARTITION BY CICategory.CategoryInstanceName ORDER BY UpdateCIs.ArticleID DESC) | |
- 1 | |
) | |
, RowNumber = ( | |
DENSE_RANK() OVER (PARTITION BY UpdateCIs.ArticleID ORDER BY ComplianceStatus.ResourceID) | |
) | |
FROM fn_ListUpdateCIs(@LCID) AS UpdateCIs | |
JOIN v_UpdateComplianceStatus AS ComplianceStatus ON ComplianceStatus.CI_ID = UpdateCIs.CI_ID | |
AND ComplianceStatus.Status IN (0, 2) --Unknown, Required | |
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 | |
JOIN v_ClientCollectionMembers AS CollectionMembers ON CollectionMembers.ResourceID = ComplianceStatus.ResourceID | |
WHERE | |
CollectionMembers.CollectionID = @CollectionID | |
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, ',') | |
) | |
) | |
/* Display summarized result */ | |
SELECT | |
ArticleID | |
, Title | |
, Classification | |
, InformationURL | |
, UpdatesByClassification | |
INTO #UniqueUpdatesInfo | |
FROM UpdateInfo_CTE | |
WHERE RowNumber = 1 | |
IF (SELECT COUNT(1) FROM #UniqueUpdatesInfo) = 0 --If compliant (null result) | |
BEGIN | |
SELECT | |
ArticleID = 'N/A' | |
, Title = 'N/A' | |
, Classification = 'Selected Classifications' | |
, InformationURL = 'N/A' | |
, UpdatesByClassification = 0 | |
, TotalUpdates = 0 | |
END | |
ELSE | |
BEGIN | |
SELECT | |
ArticleID | |
, Title | |
, Classification | |
, InformationURL | |
, UpdatesByClassification | |
, TotalUpdates = (SELECT Count(*) FROM #UniqueUpdatesInfo) | |
FROM #UniqueUpdatesInfo | |
ORDER BY UpdatesByClassification | |
END | |
/* Perform cleanup */ | |
IF OBJECT_ID('tempdb..#UniqueUpdatesInfo', 'U') IS NOT NULL | |
DROP TABLE #UniqueUpdatesInfo; | |
/* #endregion */ | |
/*##=============================================*/ | |
/*## END QUERY BODY */ | |
/*##=============================================*/ |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment