Created
November 15, 2019 11:46
-
-
Save Ioan-Popovici/ad5022f498830f18260ec177f6a5acc8 to your computer and use it in GitHub Desktop.
Summarizes the window update scan states in SCCM by Collection and Status Name.
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 update scan states for a Collection in SCCM. | |
.DESCRIPTION | |
Summarizes the window update scan states in SCCM by Collection and Status Name. | |
.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'; | |
/* Variable declaration */ | |
DECLARE @UpdateSearchID INT = ( | |
SELECT TOP 1 UpdateSource_ID FROM v_SoftwareUpdateSource WHERE IsPublishingEnabled = 1 | |
) --Get only the UpdateSource_ID where publishing is enabled | |
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 | |
) | |
/* Summarize device update scan states */ | |
SELECT | |
ScanState = ISNULL(StateNames.StateName, 'Unknown') | |
, Devices = COUNT(*) | |
, TotalDevices = @TotalDevices | |
FROM fn_rbac_FullCollectionMembership(@UserSIDs) AS CollectionMembers | |
LEFT JOIN v_UpdateScanStatus AS UpdateScanStatus ON UpdateScanStatus.ResourceID = CollectionMembers.ResourceID | |
AND ( | |
@UpdateSearchID IS NULL OR @UpdateSearchID = UpdateScanStatus.UpdateSource_ID | |
) | |
LEFT JOIN v_StateNames AS StateNames ON StateNames.StateID = UpdateScanStatus.LastScanState | |
AND StateNames.TopicType = 501 --Update source scan summarization TopicTypeID | |
WHERE CollectionMembers.CollectionID = @CollectionID | |
AND CollectionMembers.ResourceType = 5 --Select devices only | |
GROUP BY | |
StateNames.StateName | |
/* #endregion */ | |
/*##=============================================*/ | |
/*## END QUERY BODY */ | |
/*##=============================================*/ |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment