Select Distinct Top 1000000 Coalesce(tsysOS.Image,
  tsysAssetTypes.AssetTypeIcon10) As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblState.Statename As State,
  Case tblAssets.AssetID
    When SubQuery1.AssetID Then 'Up to date'
    Else 'Out of date'
  End As [Patch status],
  Case
    When tblComputersystem.Domainrole > 1 Then 'Server'
    Else 'Workstation'
  End As [Workstation/Server],
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tsysOS.OSname As OS,
  tblAssets.SP,
  tblAssets.Version,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  Case
    When tblErrors.ErrorText Is Not Null Or
      tblErrors.ErrorText != '' Then
      'Scanning Error: ' + tsysasseterrortypes.ErrorMsg
    Else ''
  End As ScanningErrors,
  Case
    When tblAssets.AssetID = SubQuery1.AssetID Then ''
    Else Case
        When tsysOS.OSname = 'Win 2008' Then 'KB5030271 or KB5030286'
        When tsysOS.OSname In ('Win 7', 'Win 7 RC', 'Win 2008 R2') Then
          'KB5030265 or KB5030261'
        When tsysOS.OSname = 'Win 8' Then
          'EOL, update to a higher Windows version'
        When tsysOS.OSname = 'Win 2012' Then 'KB5030278 or KB5030279'
        When tsysOS.OSname = 'Win 8.1' Then
          'EOL, update to a higher Windows version'
        When tsysOS.OSname = 'Win 2012 R2' Then 'KB5030269 or KB5030287'
        When tsysOS.OScode Like '10.0.10240' Then 'KB5030220'
        When tsysOS.OScode Like '10.0.10586' Then
          'EOL, update to a higher Windows version'
        When tsysOS.OScode Like '10.0.14393' Or
          tsysOS.OSname = 'Win 2016' Then 'KB5030213'
        When tsysOS.OScode Like '10.0.15063' Then
          'EOL, update to a higher Windows version'
        When tsysOS.OScode Like '10.0.16299' Then
          'EOL, update to a higher Windows version'
        When tsysOS.OScode Like '10.0.17134' Then
          'EOL, update to a higher Windows version'
        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%' Then 'KB5030214'
        When tsysOS.OSname = 'Win 2019' Then 'KB5030214'
        When tsysOS.OScode Like '10.0.18362' Then
          'EOL, update to a higher Windows version'
        When tsysOS.OScode Like '10.0.18363' Then
          'EOL, update to a higher Windows version'
        When tsysOS.OScode Like '10.0.19041' Then
          'EOL, update to a higher Windows version'
        When tsysOS.OScode Like '10.0.19042' Then
          'EOL, update to a higher Windows version'
        When tsysOS.OScode Like '10.0.19043' Then
          'EOL, update to a higher Windows version'
        When tsysOS.OScode Like '10.0.19044' Then 'KB5030211'
        When tsysOS.OScode Like '10.0.19045' Then 'KB5030211'
        When tsysOS.OSname = 'Win 2022' Then 'KB5030216'
        When tsysOS.OScode Like '10.0.22000%' Then 'KB5030217'
        When tsysOS.OScode Like '10.0.22621%' Then 'KB5030219'
      End
  End As [Install one of these updates],
  Convert(nvarchar,DateDiff(day, QuickFixLastScanned.QuickFixLastScanned,
  GetDate())) + ' days ago' As WindowsUpdateInfoLastScanned,
  Case
    When Convert(nvarchar,DateDiff(day, QuickFixLastScanned.QuickFixLastScanned,
      GetDate())) > 3 Then
      'Windows update information may not be up to date. We recommend rescanning this machine.'
    Else ''
  End As Comment,
  Case tblAssets.AssetID
    When SubQuery1.AssetID Then '#d4f4be'
    Else '#ffadad'
  End As backgroundcolor
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblOperatingsystem On tblOperatingsystem.AssetID =
      tblAssets.AssetID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Left Join (Select Top 1000000 tblQuickFixEngineering.AssetID
    From tblQuickFixEngineering
      Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID =
          tblQuickFixEngineering.QFEID
    Where tblQuickFixEngineeringUni.HotFixID In ('KB5030271', 'KB5030286',
      'KB5030265', 'KB5030261', 'KB5030278', 'KB5030279', 'KB5030269',
      'KB5030287', 'KB5030220', 'KB5030213', 'KB5030214', 'KB5030211',
      'KB5030216', 'KB5030217', 'KB5030219')) As SubQuery1 On
      tblAssets.AssetID = SubQuery1.AssetID
  Left Join tsysIPLocations On tblAssets.IPNumeric >= tsysIPLocations.StartIP
      And tblAssets.IPNumeric <= tsysIPLocations.EndIP
  Left Join (Select Distinct Top 1000000 TsysLastscan.AssetID As ID,
      TsysLastscan.Lasttime As QuickFixLastScanned
    From TsysWaittime
      Inner Join TsysLastscan On TsysWaittime.CFGCode = TsysLastscan.CFGcode
    Where TsysWaittime.CFGname = 'QUICKFIX') As QuickFixLastScanned On
      tblAssets.AssetID = QuickFixLastScanned.ID
  Left Join (Select Distinct Top 1000000 tblErrors.AssetID As ID,
      Max(tblErrors.Teller) As ErrorID
    From tblErrors
    Group By tblErrors.AssetID) As ScanningError On tblAssets.AssetID =
      ScanningError.ID
  Left Join tblErrors On ScanningError.ErrorID = tblErrors.Teller
  Left Join tsysasseterrortypes On tsysasseterrortypes.Errortype =
      tblErrors.ErrorType
Where tsysOS.OSname <> 'Win 2000 S' And tsysOS.OSname Not Like '%XP%' And
  tsysOS.OSname Not Like '%2003%' And (Not tsysOS.OSname Like 'Win 7%'
    Or Not tblAssets.SP = 0) And tblAssetCustom.State = 1 And
  tsysAssetTypes.AssetTypename Like 'Windows%'
Order By tblAssets.Domain,
  tblAssets.AssetName