Are you wondering why that particular SCCM hotfix or rollup update is not available in SCCM console? The availability of SCCM CB versions and Hotfixes in SCCM console is based on the applicability of your environment, and Microsoft dynamically manages this.
NOTE ! – If you want to know more about the SCCM 1906 new features & what’s new SCCM 1906, you can refer to the post here.
Table of Contents
I will cover the details about SCCM applicability checks & applicability SQL scripts available for each version and hotfixes of SCCM. SCCM hotfix is not available in the console? If so, this post will help you to understand the reason for SCCM hotfix not showing up in the console.
Are you wondering why one of the hotfixes is not visible in SCCM console? If so, this is the magic of dynamic SCCM applicability checks.
Subscribe to this Blog via eMail
Hieracrhy Manager & SCCM Applicability Checks
Which is SCCM Component Responsible for applicability checks? Hieracrhy Manager is the one checks the applicability of an update package.
What is SCCM Applicability Checks?
SCCM CB updates and hotfixes will be available only when all the applicable conditions are met. For each SCCM updates and hotfixes, there will be an applicability SQL script.
All those applicability SQL Scripts are stored in the folder “C:\Program Files\Microsoft Configuration Manager\CMUStaging\ApplicabilityChecks.”
I think, based on the below criteria mentioned in the SQL Applicability rules the SCCM CB updates will show up in the console.
I don’t have any confirmation (Microsoft documentation) on the above point, but this is just guesswork based on the analysis on the script. So, I could be 100% wrong:)
APPLICABILITY_SUCCESS = 0x00050002, // 327682
APPLICABILITY_HIDE = 0x0005FFFD, // 393213
APPLICABILITY_NA = 0x0005FFFE, // 393214
APPLICABILITY_NOT_CB = 0x0005FFFC, // 393212
Major Versions – SCCM Applicability Check
Let’s check the logic behind the Applicability SQL scripts for major version changes like SCCM 1902, 1906, etc..
The applicability checks SQL scripts use the following tables to validate and confirm whether the update is applicable to an SCCM infra.
- Select * from dbo.SetupInfo
- Select * from SC_COMPONENT
- select * from SC_Component_Property
- Select * from SC_SiteDefinition
- SELECT * FROM dbo.SMSData
- select * from CM_UpdatePackageSiteStatus
Legacy Logic Behind SCCM Applicablity Checks?
I think, the SCCM applicability check logic for SCCM CB upgrade (for example upgrade from SCCM 1902 to SCCM 1906) is just simple and it’s nothing but build numbers. For example:
- 8790 = SCCM 1902
- 8740 = SCCM 1810
After comparing the two SCCM applicability SQL scripts of major version releases 1902 and 1906, I came to know that the one of the logic is SMSBuildNumber.
NOTE ✔ – SMSBuildNumber validation is the old method of applicability check (it applies to 1610 and below). In the newer versions, it’s changed for good.
|Applicability_1906Release1.SQL -> AND EXISTS (select * from SMSData where SMSBuildNumber <= 8790)||Applicability_1902Release1.SQL-> AND EXISTS (select * from SMSData where SMSBuildNumber <= 8740)|
New Logic Applicability Checks
I think the new SCCM applicability logic is more robust, and one of the criteria I can see in the 1906 SQL Script is SHA256 hash value. Following is the SQL condition used in SCCM 1906 applicability script.
IF EXISTS (SELECT * FROM SetupInfo WHERE id=N'Type' AND Value1!=4)
AND EXISTS (SELECT * FROM SC_COMPONENT sc
INNER JOIN SC_SiteDefinition scd ON sc.SiteNumber=scd.SiteNumber
LEFT JOIN SC_Component_Property scp ON scp.ComponentID=sc.ID AND scp.Name=N'UpdateRing'
WHERE sc.ComponentName=N'SMS_DMP_DOWNLOADER' AND ISNULL(scd.ParentSiteCode, N'')=N''
AND dbo.fnCalculateDocumentHash(convert(varbinary(max),ISNULL(scp.Value3, 0)), N'SHA256')='8524076CF24A0FF93F9F2C1EBB0FAA31249B4FB53D2A7AF3452E977980D346B1')
Applicability Checks for SCCM CB Hotfixes
Another set of applicability SQL scripts handles the availability of SCCM CB hotfixes in the console. This Applicability SQL script logic is mainly based on the PackageGuid (from CM_UpdatePackages table) of the SCCM update version.
Sample Applicability SQL Script
Following is the sample SQL Applicability script used to confirm to make the SCCM 1902 hotfix KB4500571 visible in SCCM console. As you can see, it’s based on the PackageGuid = ‘936C9D3E-5065-4C95-BAB6-7DB24F64083A’.
-- -- Applicability check -- -- -- APPLICABILITY_SUCCESS = 0x00050002, // 327682 -- APPLICABILITY_HIDE = 0x0005FFFD, // 393213 -- APPLICABILITY_NA = 0x0005FFFE, // 393214 SET NOCOUNT ON DECLARE @Applicable INT -- check whether it is a eligble 1902 baseline IF EXISTS ( (select * from CM_UpdatePackages where PackageGuid in ( '4588EAF0-7848-419E-9FAD-9E9A25763F38', -- FR1 'EDD93252-AECD-4199-BA16-A39B8F89F133', -- FR2 '216C3489-839A-438A-815E-60C8F90DF667', -- FR3 '936C9D3E-5065-4C95-BAB6-7DB24F64083A', -- SR '9AC4BB21-0C87-47D7-A444-806C681BF0DE' -- TAP Rollup ) and state= 196612)) BEGIN PRINT 'This is a 1902 FR or SR build, or TAP with TAP Rollup installed' SET @Applicable = 327682 END ELSE BEGIN IF NOT EXISTS (select * from CM_UpdatePackages where PackageGuid = '936C9D3E-5065-4C95-BAB6-7DB24F64083A') -- NO 1902 SR BEGIN PRINT 'This is a 1902 CD installation' SET @Applicable = 327682 END ELSE BEGIN PRINT 'This is not a 1902 FR or SR build, or TAP with TAP Rollup installed, skip this hotfix' SET @Applicable = 393213 END END SELECT @Applicable
- List of SCCM CB DMP Downloader Troubleshooting with State Messages
- Flowchart – Download updates for SCCM