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 End of Life Asset Report
by  Hendrik.VE   Go to last post Go to first unread
Last post: Today at 2:18:23 PM(UTC)
Lansweeper Translation settings not saved
by  Brandon  
Go to last post Go to first unread
Last post: Yesterday at 2:10:22 PM(UTC)
Lansweeper Combobox translation
by  sneagele   Go to last post Go to first unread
Last post: Yesterday at 2:03:45 PM(UTC)
Lansweeper Freelance for Lansweeper Deploy?
by  Brandon  
Go to last post Go to first unread
Last post: 5/7/2021 6:32:21 PM(UTC)
Lansweeper Installation issues
by  Richie_Rich   Go to last post Go to first unread
Last post: 5/6/2021 11:29:09 PM(UTC)
Lansweeper Cloud relay data stored time
by  Richie_Rich  
Go to last post Go to first unread
Last post: 5/6/2021 11:14:42 PM(UTC)
Lansweeper Custom attributes from AD
by  dhoward   Go to last post Go to first unread
Last post: 5/6/2021 11:10:45 PM(UTC)
Lansweeper User View Customization
by  DarkOne77  
Go to last post Go to first unread
Last post: 5/6/2021 10:50:30 PM(UTC)