07-12-2024 09:51 PM - edited 07-23-2024 02:19 PM
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
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.
07-17-2024 07:31 AM - edited 07-17-2024 09:06 AM
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.%'
07-18-2024 02:45 PM
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.
07-17-2024 06:50 AM - edited 07-17-2024 08:58 AM
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.%'
07-17-2024 06:01 AM - edited 07-17-2024 06:01 AM
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
07-17-2024 05:51 AM
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.
07-18-2024 02:44 PM
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 😁
07-16-2024 01:22 PM
LS last years doesn't show correctly SP for Win 7, so you have to remove filter for Win 7 SP
07-16-2024 01:07 PM - edited 07-16-2024 01:12 PM
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'
07-16-2024 02:43 PM
07-17-2024 05:35 AM
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
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now