Notification

Icon
Error

Last Installed Security Update - Report on last security update installed using the OS Build Number and UBR registry key

Posted: Monday, April 5, 2021 7:52:47 PM(UTC)
parv

parv

Member Original PosterPosts: 2
1
Like
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

Code:

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





Esben.D
#1Esben.D Member Administration Posts: 1,986  
posted: 4/30/2021 1:50:41 PM(UTC)
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
#2Esben.D Member Administration Posts: 1,986  
posted: 4/30/2021 3:19:47 PM(UTC)
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.lansweep...tuesday-improvements/702
parv
#3parv Member Original PosterPosts: 2  
posted: 4/30/2021 7:14:09 PM(UTC)
Originally Posted by: Esben.D Go to Quoted Post
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.

Active Discussions

Lansweeper Report showing only Wi-Fi Devices and MAC addresses
by  Andy.S   Go to last post Go to first unread
Last post: Today at 2:23:24 PM(UTC)
Lansweeper Modifying Purchase Date / Yearly Refresh Report
by  Cripple.Zero   Go to last post Go to first unread
Last post: 5/7/2021 7:06:47 PM(UTC)
Lansweeper Tweak LanSweepers Bios Version Audit to show Last Seen
by  Tyler M.  
Go to last post Go to first unread
Last post: 5/6/2021 8:22:40 PM(UTC)
Lansweeper Patch Volume Across Fleet
by  darren.kimber  
Go to last post Go to first unread
Last post: 5/5/2021 11:29:33 PM(UTC)
Lansweeper Show Date Without Time
by  nhouse24   Go to last post Go to first unread
Last post: 5/4/2021 10:39:43 PM(UTC)
Lansweeper Report of list of assets
by  Carlos Montes  
Go to last post Go to first unread
Last post: 5/4/2021 2:52:15 PM(UTC)