Windows Secure Boot certificate expiration and CA updates report?

[funkytechmonky]

I've been searching the support forums and really surprised to see there is nothing about the "Windows Secure Boot certificate expiration" that is happening in June 2026. This is HUGE and could cause a lot of issues.  Am I missing something? Has anyone built a report for this? (or even possible)

Microsoft's Article: 
https://support.microsoft.com/en-us/topic/windows-secure-boot-certificate-expiration-and-ca-updates-... 

[TobyLa]

EDIT: First of all: read this article to understand this topic a little better: https://support.microsoft.com/en-us/topic/secure-boot-certificate-updates-guidance-for-it-profession...

Hi, I just build a report for myself last week. You have to collect some reg-keys beforehand:

HKLM

SYSTEM\CurrentControlSet\Control\SecureBoot\Servicing
UEFICA2023Error
WindowsUEFICA2023Capable
UEFICA2023Status

and

SYSTEM\CurrentControlSet\Control\SecureBoot
AvailableUpdates

and

SYSTEM\CurrentControlSet\Control\SecureBoot\State
UEFISecureBootEnabled


This will give you enough information on whats going on. I don't know what youre looking for exactly but at the moment its working fine for me at last. Feel free to expand this report for your own purpose.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
  tblAssetCustom.Model,
  tsysOS.OSname As OS,
  Case
    When SubQuery1.Value = 0 Then 'No'
    When SubQuery1.Value = 1 Then 'Yes'
    When SubQuery1.Value Is Null Then 'No'
    When SubQuery1.Value = '' Then 'No'
    Else SubQuery1.value
  End As [SecBoot Enabled],
  SubQuery2.Value As 'CA2023 Status',
  Case
    When SubQuery3.Value Is Null Then 'No Error'
    When SubQuery3.Value = '' Then 'No Error'
    Else SubQuery3.value
  End As [CA2023 UpdateError],
  Case
    When SubQuery2.Value = 'InProgress' And SubQuery3.Value = 2147942750 Then
      '#8B6508'
    When SubQuery3.Value Is Not Null Or SubQuery3.Value != 0 Then '#8B1A1A'
    When SubQuery2.Value = 'Updated' And SubQuery3.Value Is Null Or
      SubQuery3.Value = 0 Then '#008B00'
    Else ''
  End As backgroundcolor,
  Case
    When SubQuery2.Value = 'InProgress' And SubQuery3.Value = 2147942750 Then
      '#FFFFFF'
    When SubQuery3.Value Is Not Null Or SubQuery3.Value != 0 Then '#FFFFFF'
    When SubQuery2.Value = 'Updated' And SubQuery3.Value Is Null Or
      SubQuery3.Value = 0 Then '#FFFFFF'
    Else ''
  End As foregroundcolor,
  SubQuery4.Value As 'CA2023 Capable',
  SubQuery5.Value As 'UpdateTrigger'
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tsysIPLocations On tsysIPLocations.LocationID =
      tblAssets.LocationID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Left Join (Select Distinct Top 1000000 tblErrors.AssetID As ID,
      Max(tblErrors.Teller) As ErrorID
    From tblErrors
    Group By tblErrors.AssetID) As ScanningError On tblAssets.AssetID =
      ScanningError.ID
  Left Join tblErrors On ScanningError.ErrorID = tblErrors.Teller
  Left Join tsysasseterrortypes On tsysasseterrortypes.Errortype =
      tblErrors.ErrorType
  Inner Join TsysLastscan On tblAssets.AssetID = TsysLastscan.AssetID
  Inner Join TsysWaittime On TsysWaittime.CFGCode = TsysLastscan.CFGcode
  Left Join (Select Top 1000000 tblRegistry.AssetID,
      tblRegistry.Valuename,
      tblRegistry.Value,
      tblRegistry.Lastchanged
    From tblRegistry
    Where tblRegistry.Valuename = 'UEFISecureBootEnabled' And
      tblRegistry.Regkey Like
      '%SYSTEM\CurrentControlSet\Control\SecureBoot\State') SubQuery1 On
      SubQuery1.AssetID = tblAssets.AssetID
  Left Join (Select Top 1000000 tblRegistry.AssetID,
      tblRegistry.Valuename,
      tblRegistry.Value,
      tblRegistry.Lastchanged
    From tblRegistry
    Where tblRegistry.Valuename = 'UEFICA2023Status' And
      tblRegistry.Regkey Like
      '%SYSTEM\CurrentControlSet\Control\SecureBoot\Servicing') SubQuery2 On
      SubQuery2.AssetID = tblAssets.AssetID
  Left Join (Select Top 1000000 tblRegistry.AssetID,
      tblRegistry.Valuename,
      tblRegistry.Value,
      tblRegistry.Lastchanged
    From tblRegistry
    Where tblRegistry.Valuename = 'UEFICA2023Error' And
      tblRegistry.Regkey Like
      '%SYSTEM\CurrentControlSet\Control\SecureBoot\Servicing') SubQuery3 On
      SubQuery3.AssetID = tblAssets.AssetID
  Left Join (Select Top 1000000 tblRegistry.AssetID,
      tblRegistry.Valuename,
      tblRegistry.Value,
      tblRegistry.Lastchanged
    From tblRegistry
    Where tblRegistry.Valuename = 'WindowsUEFICA2023Capable' And
      tblRegistry.Regkey Like
      '%SYSTEM\CurrentControlSet\Control\SecureBoot\Servicing') SubQuery4 On
      SubQuery4.AssetID = tblAssets.AssetID
  Left Join (Select Top 1000000 tblRegistry.AssetID,
      tblRegistry.Valuename,
      tblRegistry.Value,
      tblRegistry.Lastchanged
    From tblRegistry
    Where tblRegistry.Valuename = 'AvailableUpdates' And
      tblRegistry.Regkey Like '%SYSTEM\CurrentControlSet\Control\SecureBoot')
  SubQuery5 On SubQuery5.AssetID = tblAssets.AssetID
Where tblAssetCustom.State = 1 And TsysWaittime.CFGname = 'registry'
Order By tblAssets.Domain,
  tblAssets.AssetName

Some word on this report:

• The highlighting colors will probably only work for you if youre running in dark mode
• First 4 rows are selfexplaining
• Row "SecBoot Enabled" > shows if sec boot is enabled on the machine
• Row "CA2023 Status" > shows the current state of the rollout, NotStarted, Updated and InProgress are the usual values. InProgress is also the value if something is running bad and also if its just waiting for a reboot or smt.
• Row "CA2023 UpdateError" > "No Error" means not startet yet or already started and succeded. If there's a number its a work in progress thing. I marked the line yellowish if the value is 2147942750 bc this means "waiting for reboot". I am sure ther's a lot of other values but I dont care, if the values not empty, 0 or 2147942750 then the line goes red > you have to take a look into eventvwr.msc (filter for tpm). 
• "CA2023 Capable" is just another value Micorsoft is delivering but it's not that important as long as everything wents fine. When the PI-task updated the cert and a reboot is done this value is 2 most of the time, but could also be 2 if something only updated in parts sucessfully.
• "Update Trigger" is the value thats controlling if a domain machine should update or not. Its 0 or empty if not started and its 16384 if everything went fine and is up to date. 
• Coloring the lines ony takes look at Ca2023 Status and UpdateError.

I did this for me to capture what is going on after I enabled the Uefi-cert-update for my machines.

[Esben_D]

Check back Jan 30, the new pro tip with resources should be live then 😊

[funkytechmonky]

I even got a shout out... how awesome is that!. Thank you for putting in the time to do this.
Love me some LanSweeper!! 

[funkytechmonky]

@Esben_D  Any update on this?

[Thomas]

I was also looking forward to this.

There has just been a reply here in which the solution should have been, but the post has been deleted

[KevinA-REJIS]

@Esben_D published this on 1/30:

https://www.lansweeper.com/blog/pro-tips/secure-boot-certificate-expiration/ 

[Thomas]

Awesome, Thanks you : I missed it there somehow

[funkytechmonky]

What in the world. I looked for that everywhere... thanks!! 

Yea.. I was looking here. 
https://community.lansweeper.com/t5/pro-tips/bg-p/Pro_Tips

[funkytechmonky]

That is awesome. Thank you for helping out with this!! Can you give a little sneak peak of how this will be accomplished? 

We are a dell shop and as part of our prep I am using the "BIOS Version Audit" to get a list of workstations that have outdated BIOS versions. Dell has provided a list of "Minimum BIOS Versions with 2023 Certificate". I've used this info to help find devices that need BIOS updated. I really wish I understood SQL reports better so I could create my own report with this information but I am horrible with SQL and really wish I understood it better.