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
