cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
proxykeeper
Engaged Sweeper III
I have been struggling with the patch Tuesday audit reports because they are only valid for a short period of time on a large enterprise. To resolve the issue, I created a report on the Windows Server build number and UBR registry key. The column labeled "Latest Security Update" will need to be manually updated each month as the new security patch and build numbers are released. To use this report, you will also need to have Lansweeper scanning include the registry HKEY_Local_Machine\SOFTWARE\Microsoft\WindowNT\CurrentVersion regvalue UBR


Select Top 1000000 tblassets.AssetID,
tblassets.AssetName,
tsysassettypes.AssetTypeIcon10 As icon,
tblassets.IPAddress,
tblassets.Domain,


Case
When tblOperatingsystem.Version Like '10.0.17763' Then
Case
When tRegUBR.Value Like '107' Then '2018-11'
When tRegUBR.Value Like '379' Then '2019-03'
When tRegUBR.Value Like '557' Then '2019-06'
When tRegUBR.Value Like '720' Then '2019-08'
When tRegUBR.Value Like '914' Then '2019-12'
When tRegUBR.Value Like '1131' Then '2020-03'
When tRegUBR.Value Like '1397' Then '2020-08'
When tRegUBR.Value Like '1457' Then '2020-09'
When tRegUBR.Value Like '1490' Then '2020-09'
When tRegUBR.Value Like '1554' Then '2020-10'
When tRegUBR.Value Like '1577' Then '2020-11'
When tRegUBR.Value Like '1637' Then '2020-12'
When tRegUBR.Value Like '1697' Then '2021-01'
When tRegUBR.Value Like '1757' Then '2021-02'
When tRegUBR.Value Like '1817' Then '2021-03'
When tRegUBR.Value Like '1823' Then '2021-03'
End
When tblOperatingsystem.Version Like '10.0.14393' Then
Case
When tRegUBR.Value Like '1884' Then '2017-11'
When tRegUBR.Value Like '2068' Then '2018-02'
When tRegUBR.Value Like '2214' Then '2018-04'
When tRegUBR.Value Like '2248' Then '2018-05'
When tRegUBR.Value Like '2363' Then '2018-07'
When tRegUBR.Value Like '2395' Then '2018-07'
When tRegUBR.Value Like '2485' Then '2018-09'
When tRegUBR.Value Like '2551' Then '2018-10'
When tRegUBR.Value Like '2724' Then '2019-01'
When tRegUBR.Value Like '2759' Then '2019-01'
When tRegUBR.Value Like '3025' Then '2019-06'
When tRegUBR.Value Like '3181' Then '2019-08'
When tRegUBR.Value Like '3204' Then '2019-09'
When tRegUBR.Value Like '3206' Then '2019-09'
When tRegUBR.Value Like '3242' Then '2019-09'
When tRegUBR.Value Like '3243' Then '2019-10'
When tRegUBR.Value Like '3274' Then '2019-10'
When tRegUBR.Value Like '3326' Then '2019-11'
When tRegUBR.Value Like '3384' Then '2019-12'
When tRegUBR.Value Like '3443' Then '2020-01'
When tRegUBR.Value Like '3474' Then '2020-01'
When tRegUBR.Value Like '3504' Then '2020-02'
When tRegUBR.Value Like '3542' Then '2020-02'
When tRegUBR.Value Like '3564' Then '2020-03'
When tRegUBR.Value Like '3595' Then '2020-03'
When tRegUBR.Value Like '3630' Then '2020-04'
When tRegUBR.Value Like '3686' Then '2020-05'
When tRegUBR.Value Like '3750' Then '2020-06'
When tRegUBR.Value Like '3755' Then '2020-06'
When tRegUBR.Value Like '3808' Then '2020-07'
When tRegUBR.Value Like '3866' Then '2020-08'
When tRegUBR.Value Like '3930' Then '2020-09'
When tRegUBR.Value Like '3986' Then '2020-10'
When tRegUBR.Value Like '4046' Then '2020-11'
When tRegUBR.Value Like '4048' Then '2021-03'
When tRegUBR.Value Like '4104' Then '2020-12'
When tRegUBR.Value Like '4169' Then '2021-01'
When tRegUBR.Value Like '4225' Then '2021-02'
When tRegUBR.Value Like '4283' Then '2021-03'
When tRegUBR.Value Like '4288' Then '2021-03'
End
When tblOperatingsystem.Version Like '6.3.9600' Then
Case
When cast(tRegUBR.Value As Int) < 19756 Then '2020-06 or prior'
When tRegUBR.Value Like '19756' Then '2020-07'
When tRegUBR.Value Like '19785' Then '2020-08'
When tRegUBR.Value Like '19817' Then '2020-09'
When tRegUBR.Value Like '19847' Then '2020-09'
When tRegUBR.Value Like '19756' Then '2020-10'
When tRegUBR.Value Like '19873' Then '2020-11'
When tRegUBR.Value Like '19893' Then '2020-12'
When tRegUBR.Value Like '19920' Then '2021-01'
When tRegUBR.Value Like '19941' Then '2021-02'
When tRegUBR.Value Like '19968' Then '2021-03'
End
When tblOperatingsystem.Version Like '6.2.9200' Then
Case
When tRegUBR.Value Like '23149' Then '2020-10'
When tRegUBR.Value Like '' Then '2020-11'
When tRegUBR.Value Like '19893' Then '2020-12'
When tRegUBR.Value Like '23255 ' Then '2021-01'
When tRegUBR.Value Like '23274' Then '2021-02'
When tRegUBR.Value Like '23298' Then '2021-03'
End
When Cast(Replace(tblOperatingsystem.Version, '.', '') As BIGINT) < 620000 Then 'EOL'

End As 'Latest Security Update',


Replace(Replace(tblassets.OScode, 'S', ''), 'R', '') + '.' + tRegUBR.Value As Build,
tblassets.Version,
tblassets.SP,
tblOperatingsystem.Caption As OSName,
tblOperatingsystem.Version As OSVersion,
Cast(tRegUBR.Value As INT) As BuildNumber,

Cast(Replace(tblOperatingsystem.Version, '.', '') As BIGINT) As VersionNumeric,

tblassets.Description,
tblassets.Lastseen,
tblassets.Lasttried

From tblassets
Inner Join tblassetcustom On tblassets.AssetID = tblassetcustom.AssetID
Inner Join tsysassettypes On tsysassettypes.AssetType = tblassets.Assettype
Inner Join tsysOS On tblassets.OScode = tsysOS.OScode
Inner Join tblOperatingsystem On
tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID


Inner Join (Select tblRegistry.AssetID,
tblRegistry.Value,
tblRegistry.Valuename
From tblRegistry
Where
tblRegistry.Regkey =
'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion' And
tblRegistry.Valuename='UBR') As tRegUBR
On tblAssets.AssetID = tRegUBR.AssetID



Where tblassetcustom.State = 1 and tblComputersystem.Domainrole > 1
order by tblassets.AssetName







Was this post helpful? Leave a Kudo!
Did you have a similar issue and a different solution? Share your work in the comments below and help your fellow IT Hero's!
More questions? Browse our  Quick Tech Solutions  or  Community Forum.


If you can't find what you're looking for, create a post in our Community Forum.



5 Comments
Esben_D
Lansweeper Employee
Lansweeper Employee
Seems like you can do the same for regular windows 10. The only downside is that you have to configure the registry key first.
Esben_D
Lansweeper Employee
Lansweeper Employee
I've started a research topic on our Insider Community based on the information you provided here. If anyone is interested in helping out to improve the Patch Tuesday reports in the future, you can join here: https://insider.lansweeper.com/t/patch-tuesday-improvements/702
proxykeeper
Engaged Sweeper III
Esben.D wrote:
Seems like you can do the same for regular windows 10. The only downside is that you have to configure the registry key first.



The built in tblassets.BuildNumber can be used for Windows 10 server OS's (2016 and 2019). I'm not sure about workstations, we do not use Lansweeper for workstations. The UBR registry key is needed to get the build number for Windows 2012 servers.

Hendrik_VE
Champion Sweeper III
Does anyone know where to find an up to date / complete list of the UBR versions?
proxykeeper
Engaged Sweeper III
You can get the UBR numbers for Windows 10 by version in Wikipedia. Search for Windows 10 version xxxx where xxxx is the version you are looking for.

For version 1809 (Windows Server 2019) https://en.wikipedia.org/wiki/Windows_10_version_1809
At the bottom, the list of References show the OS Build. The last part is the UBR. As an example, on the link above, reference 127 for July 6, 2021-KB5004947 (OS Build 17763.2029) 2029 is the UBR.

Also, https://www.windowslatest.com/2021 and search for Patch Tuesday and the month (example Patch Tuesday June) https://www.windowslatest.com/2021/06/10/windows-10-june-2021-updates-whats-new-and-improved/




New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now