Patch Tuesday Improvement

[Esben_D]

Hey,

A few years ago I tried to improve the patch Tuesday report to make it less error prone and also to prevent it from marking devices as "out-of-date" when they get a new update next month.

The main issues I ran in were

1. Patch numbers are not consistent and cannot be used to determine which patch is the latest
2. Windows build numbers are only scannable from Server 2016 (or Windows 10) onward (this is a Windows issue).

Luckily, since time has passed and older operating systems have gone end-of-life. This is becoming less of an issue. The only remaining supported OS with no build number info is Server 2012.

So with that, I have created a new version of Patch Tuesday, that uses the old method for Server 2012, but the new method for all the rest. The new method is simply using the build number of the operating system to check whether it is up-to-date. Meaning that if you were to use it after Patch Tuesday August, the report should still be accurate (aside from Server 2012).

Before I use this to send it to everyone, please give it a try and let me know if you come across issues. I attached the code in a TXT file to this post.

[Mister_Nobody]

For legacy OSes I Suggest to get OS build via File Scan

%windir%\system32\ntoskrnl.exe

Builds have taken from here https://msrc.microsoft.com/update-guide/vulnerability

*But to get correct ntoskrnl.exe build is needed to view file list from KB description. Now I see some different 1..3 in builds

Select Top 1000000 tblAssets.assetid,
  tblAssets.domain,
  tblAssets.AssetName,
  tblFileVersions.FilePathfull,
  tblFileVersions.FileVersion,
  ParseName(tblFileVersions.FileVersion, 1) As buildnumber,
  tblOperatingsystem.Caption,
  tblOperatingsystem.Version As OS_Build,
  Case
    When tblOperatingsystem.Version = '6.3.9600' And
      ParseName(tblFileVersions.FileVersion, 1) >= 22074 Then
      'OS has newer update than July 2024'
    When tblOperatingsystem.Version = '6.2.9200' And
      ParseName(tblFileVersions.FileVersion, 1) >= 24975 Then
      'OS has newer update than July 2024'
    When tblOperatingsystem.Version Like '6.1.7601' And
      ParseName(tblFileVersions.FileVersion, 1) >= 27216 Then
      'OS has newer update than July 2024'
    When tblOperatingsystem.Version = '6.0.6003' And
      ParseName(tblFileVersions.FileVersion, 1) >= 22769 Then
      'OS has newer update than July 2024'
    When Coalesce(ParseName(tblFileVersions.FileVersion, 1), 0) = '0' Then
      'Unknown build status'
    Else 'Your OS must be updated'
  End As [Update status],
  Case
    When tblOperatingsystem.Version = '6.3.9600' And
      ParseName(tblFileVersions.FileVersion, 1) >= 22074 Then '#d4f4be'
    When tblOperatingsystem.Version = '6.2.9200' And
      ParseName(tblFileVersions.FileVersion, 1) >= 24975 Then '#d4f4be'
    When tblOperatingsystem.Version = '6.1.7601' And
      ParseName(tblFileVersions.FileVersion, 1) >= 27216 Then '#d4f4be'
    When tblOperatingsystem.Version = '6.0.6003' And
      ParseName(tblFileVersions.FileVersion, 1) >= 22769 Then '#d4f4be'
    Else '#ffadad'
  End As backgroundcolor
From tblAssets
  Inner Join tblFileVersions On tblAssets.AssetID = tblFileVersions.AssetID And
      tblFileVersions.FilePathfull Like '%\system32\ntoskrnl.exe'
  Inner Join tblOperatingsystem On
      tblAssets.AssetID = tblOperatingsystem.AssetID
Where tblOperatingsystem.Version Like '6.%'

[Esben_D]

Interesting find! I don't think I'll add this to my report though. I want to report to be as approachable as possible without requiring a lot of setup.

[Mister_Nobody]

@Esben_D 

Final report from me (only long term versions of Windows 10 and  Windows Server 2016/2019/2022):

Select Top 1000000 tblassets.AssetID,
  tblassets.AssetName,
  tblOperatingsystem.Caption,
  tblOperatingsystem.Version As OS_Build,
  tblassets.Version,
  tblassets.BuildNumber,
  Case
    When tblOperatingsystem.Version = '10.0.19044' And tblassets.BuildNumber >=
      4651 Then 'OS has newer update than July 2024'
    When tblOperatingsystem.Version = '10.0.10240' And tblassets.BuildNumber >=
      20710 Then 'OS has newer update than July 2024'
    When tblOperatingsystem.Version = '10.0.14393' And tblassets.BuildNumber >=
      7159 Then 'OS has newer update than July 2024'
    When tblOperatingsystem.Version = '10.0.17763' And tblassets.BuildNumber >=
      6054 Then 'OS has newer update than July 2024'
    When tblOperatingsystem.Version = '10.0.20348' And tblassets.BuildNumber >=
      2582 Then 'OS has newer update than July 2024'
    When tblassets.BuildNumber = '0' Then 'Unknown build status'
    Else 'Your OS must be updated'
  End As [Update status],
  Case
    When tblOperatingsystem.Version = '10.0.19044' And tblassets.BuildNumber >=
      4651 Then '#d4f4be'
    When tblOperatingsystem.Version = '10.0.10240' And tblassets.BuildNumber >=
      20710 Then '#d4f4be'
    When tblOperatingsystem.Version = '10.0.14393' And tblassets.BuildNumber >=
      7159 Then '#d4f4be'
    When tblOperatingsystem.Version = '10.0.17763' And tblassets.BuildNumber >=
      6054 Then '#d4f4be'
    When tblOperatingsystem.Version = '10.0.20348' And tblassets.BuildNumber >=
      2582 Then '#d4f4be'
    Else '#ffadad'
  End As backgroundcolor
From tblassets
  Inner Join tblOperatingsystem On
      tblassets.AssetID = tblOperatingsystem.AssetID
Where tblOperatingsystem.Version Like '10.0.%'

[Mister_Nobody]

@Esben_D 

Your code is cool:

Right(tblQuickFixEngineeringUni.HotFixID, 7) Not Like '%[^0-9]%

my code:

   IsNumeric(Replace(tblQuickFixEngineeringUni.HotFixID, 'KB', '')) = 1
          And Len(tblQuickFixEngineeringUni.HotFixID) = 9

[Mister_Nobody]

Patches for windows 2008/7 Embedded  are still being released.
https://catalog.update.microsoft.com/Search.aspx?q=KB5040497

So I suggest you to release separate Windows 10/2016+ report with builds filter and other for legacy OS.

[Esben_D]

I noticed, however I'm not sure why... from all resources I can find online these should be EOL and there should be no more updates...

I don't really want to create multiple reports, that would make things too complex and I also want to make sure this doesn't create more work. The goal is to make it simpler and better, not better and more complex 😁

[Mister_Nobody]

LS last years doesn't show correctly SP for Win 7, so you have to remove filter for Win 7 SP

[Mister_Nobody]

This code doesn't work with Windows 10 1607 LTSB

Any buildnumbers are 'Up to date'

WHEN tsysOS.OScode LIKE '10.0.14393' OR tsysOS.OSname = 'Win 2016' And SubQuery2.Buildnumber >= 7159 Then 'Up to date'

fix code:

When tsysOS.OScode Like '10.0.14393%' And SubQuery2.Buildnumber >= 7159 Then
      'Up to date'

[Esben_D]

I removed the Windows 7 condition at the bottom since the case statement should say its EOL regardless. I also added wildcards to all the Windows version numbers to fix the issue with 1607 LTSB you listed (And any other potential similar issues)

[Mister_Nobody]

Some fixes:

Select Distinct Top 1000000 Coalesce(tsysOS.Image,
  tsysAssetTypes.AssetTypeIcon10) As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblAssets.IPAddress,
  Case
    When tblComputersystem.Domainrole > 1 Then 'Server'
    Else 'Workstation'
  End As [Workstation/Server],
  tsysOS.OSname As OS,
  tblAssets.SP,
  tblAssets.Version,
  SubQuery2.Buildnumber,
  SubQuery1.PatchIDMax As [Highest KB Patch found],
  Case
    When tsysOS.OSname = 'Win 2008' Then
      'EOL, update to a higher Windows version'
    When tsysOS.OSname In ('Win 7', 'Win 7 RC', 'Win 2008 R2') Then
      'EOL, update to a higher Windows version'
    When tsysOS.OSname = 'Win 8' Then 'EOL, update to a higher Windows version'
    When tsysOS.OSname = 'Win 2012' And SubQuery3.AssetID = tblAssets.AssetID
    Then 'Up to date'
    When tsysOS.OSname = 'Win 8.1' Then
      'EOL, update to a higher Windows version'
    When tsysOS.OSname = 'Win 2012 R2' And SubQuery3.AssetID = tblAssets.AssetID
    Then 'Up to date'
    When tsysOS.OScode Like '10.0.10240%' And SubQuery2.Buildnumber >= 20710
    Then 'Up to date'
    When tsysOS.OScode Like '10.0.10586%' Then
      'EOL, update to a higher Windows version'
    When (tsysOS.OScode Like '10.0.14393%' Or
      tsysOS.OSname = 'Win 2016') And SubQuery2.Buildnumber >= 7159 Then
      'Up to date'
    When tsysOS.OScode Like '10.0.15063%' Then
      'EOL, update to a higher Windows version'
    When tsysOS.OScode Like '10.0.16299%' Then
      'EOL, update to a higher Windows version'
    When tsysOS.OScode Like '10.0.17134%' Then
      'EOL, update to a higher Windows version'
    When tsysOS.OScode Like '10.0.17763%' And tblOperatingsystem.Caption Not
      Like '%LTSC%' Then 'EOL, update to a higher Windows version'
    When tsysOS.OScode Like '10.0.17763%' And tblOperatingsystem.Caption Like
      '%LTSC%' And SubQuery2.Buildnumber >= 6054 Then 'Up to date'
    When tsysOS.OSname = 'Win 2019' And SubQuery2.Buildnumber >= 6054 Then
      'Up to date'
    When tsysOS.OScode Like '10.0.18362%' Then
      'EOL, update to a higher Windows version'
    When tsysOS.OScode Like '10.0.18363%' Then
      'EOL, update to a higher Windows version'
    When tsysOS.OScode Like '10.0.19041%' Then
      'EOL, update to a higher Windows version'
    When tsysOS.OScode Like '10.0.19042%' Then
      'EOL, update to a higher Windows version'
    When tsysOS.OScode Like '10.0.19043%' Then
      'EOL, update to a higher Windows version'
    When tsysOS.OScode Like '10.0.19044%' And SubQuery2.Buildnumber >= 4651 Then
      'Up to date'
    When tsysOS.OScode Like '10.0.19045%' And SubQuery2.Buildnumber >= 4651 Then
      'Up to date'
    When tsysOS.OSname = 'Win 2022' And SubQuery2.Buildnumber >= 2582 Then
      'Up to date'
    When tsysOS.OScode Like '10.0.22000%' And SubQuery2.Buildnumber >= 3079 Then
      'Up to date'
    When (tsysOS.OScode Like '10.0.22621%' Or
      tsysOS.OScode Like '10.0.22631%') And SubQuery2.Buildnumber >= 3880 Then
      'Up to date'
    When tsysOS.OScode Like '10.0.25398%' And SubQuery2.Buildnumber >= 1009 Then
      'Up to date'
    Else 'Out of date'
  End As [Patch status],
  tblAssets.Username,
  tblAssets.Userdomain,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblState.Statename As State,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  QuickFixLastScanned.QuickFixLastScanned,
  Case
    When tblErrors.ErrorText Is Not Null Or
      tblErrors.ErrorText != '' Then
      'Scanning Error: ' + tsysasseterrortypes.ErrorMsg
    Else ''
  End As ScanningErrors,
  Convert(nvarchar,DateDiff(day, QuickFixLastScanned.QuickFixLastScanned,
  GetDate())) + ' days ago' As WindowsUpdateInfoLastScanned,
  Case
    When Convert(nvarchar,DateDiff(day, QuickFixLastScanned.QuickFixLastScanned,
      GetDate())) > 3 Then
      'Windows update information may not be up to date. We recommend rescanning this machine.'
    Else ''
  End As Comment,
  Case
    When tsysOS.OSname = 'Win 2008' Then '#ffadad'
    When tsysOS.OSname In ('Win 7', 'Win 7 RC', 'Win 2008 R2') Then '#ffadad'
    When tsysOS.OSname = 'Win 8' Then '#ffadad'
    When tsysOS.OSname = 'Win 2012' And SubQuery3.AssetID = tblAssets.AssetID
    Then '#d4f4be'
    When tsysOS.OSname = 'Win 8.1' Then '#ffadad'
    When tsysOS.OSname = 'Win 2012 R2' And SubQuery3.AssetID = tblAssets.AssetID
    Then '#d4f4be'
    When tsysOS.OScode Like '10.0.10240%' And SubQuery2.Buildnumber >= 20710
    Then '#d4f4be'
    When tsysOS.OScode Like '10.0.10586%' Then '#ffadad'
    When (tsysOS.OScode Like '10.0.14393%' Or
      tsysOS.OSname = 'Win 2016') And SubQuery2.Buildnumber >= 7159 Then
      '#d4f4be'
    When tsysOS.OScode Like '10.0.15063%' Then '#ffadad'
    When tsysOS.OScode Like '10.0.16299%' Then '#ffadad'
    When tsysOS.OScode Like '10.0.17134%' Then '#ffadad'
    When tsysOS.OScode Like '10.0.17763%' And tblOperatingsystem.Caption Not
      Like '%LTSC%' Then '#ffadad'
    When tsysOS.OScode Like '10.0.17763%' And tblOperatingsystem.Caption Like
      '%LTSC%' And SubQuery2.Buildnumber >= 6054 Then '#d4f4be'
    When tsysOS.OSname = 'Win 2019' And SubQuery2.Buildnumber >= 6054 Then
      '#d4f4be'
    When tsysOS.OScode Like '10.0.18362%' Then '#ffadad'
    When tsysOS.OScode Like '10.0.18363%' Then '#ffadad'
    When tsysOS.OScode Like '10.0.19041%' Then '#ffadad'
    When tsysOS.OScode Like '10.0.19042%' Then '#ffadad'
    When tsysOS.OScode Like '10.0.19043%' Then '#ffadad'
    When tsysOS.OScode Like '10.0.19044%' And SubQuery2.Buildnumber >= 4651 Then
      '#d4f4be'
    When tsysOS.OScode Like '10.0.19045%' And SubQuery2.Buildnumber >= 4651 Then
      '#d4f4be'
    When tsysOS.OSname = 'Win 2022' And SubQuery2.Buildnumber >= 2582 Then
      '#d4f4be'
    When tsysOS.OScode Like '10.0.22000%' And SubQuery2.Buildnumber >= 3079 Then
      '#d4f4be'
    When (tsysOS.OScode Like '10.0.22621%' Or
      tsysOS.OScode Like '10.0.22631%') And SubQuery2.Buildnumber >= 3880 Then
      '#d4f4be'
    When tsysOS.OScode Like '10.0.25398%' And SubQuery2.Buildnumber >= 1009 Then
      '#d4f4be'
    Else '#ffadad'
  End As backgroundcolor
From tblAssets
  Left Join (Select tblAssets.AssetID,
      Try_Convert(bigint,tblAssets.BuildNumber) As Buildnumber
    From tblAssets
    Where tblAssets.Assettype = -1) As SubQuery2 On tblAssets.AssetID =
      SubQuery2.AssetID
  Left Join (Select Top 1000000 tblQuickFixEngineering.AssetID
    From tblQuickFixEngineering
      Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID =
          tblQuickFixEngineering.QFEID
    Where tblQuickFixEngineeringUni.HotFixID In ('KB5040485', 'KB5040456')) As
  SubQuery3 On tblAssets.AssetID = SubQuery3.AssetID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblOperatingsystem On tblOperatingsystem.AssetID =
      tblAssets.AssetID
  Left Join (Select Top 1000000 tblQuickFixEngineering.AssetID,
      Max(Cast(Right(tblQuickFixEngineeringUni.HotFixID, 7) As bigint)) As
      PatchIDMax
    From tblQuickFixEngineering
      Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID =
          tblQuickFixEngineering.QFEID
    Where Right(tblQuickFixEngineeringUni.HotFixID, 7) Not Like '%[^0-9]%'
    Group By tblQuickFixEngineering.AssetID) As SubQuery1 On tblAssets.AssetID =
      SubQuery1.AssetID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
      And tblAssets.IPNumeric <= tsysIPLocations.EndIP
  Left Join (Select Distinct Top 1000000 TsysLastscan.AssetID As ID,
      TsysLastscan.Lasttime As QuickFixLastScanned
    From TsysWaittime
      Inner Join TsysLastscan On TsysWaittime.CFGCode = TsysLastscan.CFGcode
    Where TsysWaittime.CFGname = 'QUICKFIX') As QuickFixLastScanned On
      tblAssets.AssetID = QuickFixLastScanned.ID
  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
Where tsysOS.OSname <> 'Win 2000 S' And tsysOS.OSname Not Like '%XP%' And
  tsysOS.OSname Not Like '%2003%' And tblAssetCustom.State = 1 And
  tsysAssetTypes.AssetTypename Like 'Windows%'
Order By tblAssets.Domain,
  tblAssets.AssetName