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.

[gavinp]

Report shows my Server 2019 devices as "EOL, update to a higher Windows version". The 2019 condition needs to be above the build 17763 condition as below

WHEN tsysOS.OScode LIKE '10.0.17134%' THEN 'EOL, update to a higher Windows version'
WHEN tsysOS.OSname = 'Win 2019' And SubQuery2.Buildnumber >= 6054 Then 'Up to date'
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'

[Esben_D]

I see what happened, its because the change I did with the wildcards. The SQL case statement is performed from top to bottom. So it matches with the non version OS first and therefore gives a false result. 

The actual build number is correct though. The V3 version should fix that issue.

[gavinp]

Also needs another condition added to prevent out-of-date Server 2019 devices showing as EOL

WHEN tsysOS.OSname = 'Win 2019' And SubQuery2.Buildnumber < 6054 Then 'Out of date'

[Esben_D]

Here is v4 with some wildcards removed so it should solve that problem

[Mister_Nobody]

Issue with priority of OR/AND is presented again

[Esben_D]

Do you mean in the case statement where there are a few instances of an OR + AND in the same line causing issues?

I added parenthesis around them which should solve that

[Mister_Nobody]

Not fixed in backgroundcolor section

[Esben_D]

fixed it

[Esben_D]

you are right, but I think I'll mess with the wildcards instead.