Select Distinct Top 1000000 Coalesce(tsysOS.Image,
  tsysAssetTypes.AssetTypeIcon10) As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tblState.Statename As State,
  Case
    When tsysOS.OSname = 'Win 2008' And SubQuery1.PatchIDMax >= 4601360 Then
      'Up to date'
    When tsysOS.OSname = 'Win 7' And SubQuery1.PatchIDMax >= 4601347 Then
      'Up to date'
    When tsysOS.OSname = 'Win 7 RC' And SubQuery1.PatchIDMax >= 4601347 Then
      'Up to date'
    When tsysOS.OSname = 'Win 2008 R2' And SubQuery1.PatchIDMax >= 4601347 Then
      'Up to date'
    When tsysOS.OSname = 'Win 2012' And SubQuery1.PatchIDMax >= 4601348 Then
      'Up to date'
    When tsysOS.OSname = 'Win 8' And SubQuery1.PatchIDMax >= 4601348 Then
      'Up to date'
    When tsysOS.OSname = 'Win 8.1' And SubQuery1.PatchIDMax >= 4601384
    Then 'Up to date'
    When tsysOS.OSname = 'Win 2012 R2' And SubQuery1.PatchIDMax >= 4601384 Then
      'Up to date'
    When tsysOS.OScode Like '10.0.10240' And SubQuery1.PatchIDMax >= 4601331
    Then 'Up to date'
    When tsysOS.OScode Like '10.0.14393' And SubQuery1.PatchIDMax >= 4601318
    Then 'Up to date'
    When tsysOS.OSname = 'Win 2016' And SubQuery1.PatchIDMax >= 4601318 Then
      'Up to date'
    When tsysOS.OScode Like '10.0.17134' And SubQuery1.PatchIDMax >= 4601354
    Then 'Up to date'
    When tsysOS.OScode Like '10.0.17763' And SubQuery1.PatchIDMax >= 4601345
    Then 'Up to date'
    When tsysOS.OSname = 'Win 2019' And SubQuery1.PatchIDMax >= 4601345 Then
      'Up to date'
    When tsysOS.OScode Like '10.0.18363' And SubQuery1.PatchIDMax >= 4601315
    Then 'Up to date'
    When tsysOS.OScode Like '10.0.19041' And SubQuery1.PatchIDMax >= 4601319
    Then 'Up to date'
    When tsysOS.OScode Like '10.0.19042' And SubQuery1.PatchIDMax >= 4601319
    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],
  Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) / 3600 /
  24))) + ' days ' +
  Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) / 3600 %
  24))) + ' hours ' +
  Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) % 3600 /
  60))) + ' minutes' As [Uptime Since Last Reboot],
  tblAssets.Uptime,
  tblADComputers.OU,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tsysOS.OSname As OS,
  tblAssets.SP,
  Case
    When tsysOS.OScode Like '10.0.10240%' Then '1507'
    When tsysOS.OScode Like '10.0.10586%' Then '1511'
    When tsysOS.OScode Like '10.0.14393%' Then '1607'
    When tsysOS.OScode Like '10.0.15063%' Then '1703'
    When tsysOS.OScode Like '10.0.16299%' Then '1709'
    When tsysOS.OScode Like '10.0.17134%' Then '1803'
    When tsysOS.OScode Like '10.0.17763%' Then '1809'
    When tsysOS.OScode Like '10.0.18362%' Then '1903'
    When tsysOS.OScode Like '10.0.18363%' Then '1909'
    When tsysOS.OScode Like '10.0.19041%' Then '2004'
    When tsysOS.OScode Like '10.0.19042%' Then '20H2'
  End As 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 tsysOS.OSname = 'Win 2008' And SubQuery1.PatchIDMax >= 4601360 Then ''
    When tsysOS.OSname = 'Win 7' And SubQuery1.PatchIDMax >= 4601347 Then ''
    When tsysOS.OSname = 'Win 7 RC' And SubQuery1.PatchIDMax >= 4601347 Then ''
    When tsysOS.OSname = 'Win 2008 R2' And SubQuery1.PatchIDMax >= 4601347 Then
      ''
    When tsysOS.OSname = 'Win 2012' And SubQuery1.PatchIDMax >= 4601348 Then ''
    When tsysOS.OSname = 'Win 8' And SubQuery1.PatchIDMax >= 4601348 Then ''
    When tsysOS.OSname = 'Win 8.1' And SubQuery1.PatchIDMax >= 4601384 Then ''
    When tsysOS.OSname = 'Win 2012 R2' And SubQuery1.PatchIDMax >= 4601384 Then
      ''
    When tsysOS.OScode Like '10.0.10240' And SubQuery1.PatchIDMax >= 4601331
    Then ''
    When tsysOS.OScode Like '10.0.10586' Then
      'EOL, update to a higher Windows version'
    When tsysOS.OScode Like '10.0.14393' And SubQuery1.PatchIDMax >= 4601318
    Then ''
    When tsysOS.OSname = 'Win 2016' And SubQuery1.PatchIDMax >= 4601318 Then ''
    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' And SubQuery1.PatchIDMax >= 4601354
    Then ''
    When tsysOS.OScode Like '10.0.17763' And SubQuery1.PatchIDMax >= 4601345
    Then ''
    When tsysOS.OSname = 'Win 2019' And SubQuery1.PatchIDMax >= 4601345 Then ''
    When tsysOS.OScode Like '10.0.18362' Then
      'EOL, update to a higher Windows version'
    When tsysOS.OScode Like '10.0.18363' And SubQuery1.PatchIDMax >= 4601315
    Then ''
    When tsysOS.OScode Like '10.0.19041' And SubQuery1.PatchIDMax >= 4601319
    Then ''
    When tsysOS.OScode Like '10.0.19042' And SubQuery1.PatchIDMax >= 4601319
    Then ''
    Else Case
        When tsysOS.OSname = 'Win 2008' Then 'KB4601360 or KB4601366'
        When tsysOS.OSname = 'Win 7' Or tsysOS.OSname = 'Win 7 RC' Or
          tsysOS.OSname = 'Win 2008 R2' Then 'KB4601347 or KB4601363'
        When tsysOS.OSname = 'Win 2012' Or
          tsysOS.OSname = 'Win 8' Then 'KB4601348 or KB4601357'
        When tsysOS.OSname = 'Win 8.1' Or
          tsysOS.OSname = 'Win 2012 R2' Then 'KB4601384 or KB4601349'
        When tsysOS.OScode Like '10.0.10240' Then 'KB4601331'
        When tsysOS.OScode Like '10.0.14393' Or
          tsysOS.OSname = 'Win 2016' Then 'KB4601318'
        When tsysOS.OScode Like '10.0.17134' Then 'KB4601354'
        When tsysOS.OScode Like '10.0.17763' Or
          tsysOS.OSname = 'Win 2019' Then 'KB4601345'
        When tsysOS.OScode Like '10.0.18363' Then 'KB4601315'
        When tsysOS.OScode Like '10.0.19041' Then 'KB4601319'
        When tsysOS.OScode Like '10.0.19042' Then 'KB4601319'
      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
    When tsysOS.OSname = 'Win 2008' And SubQuery1.PatchIDMax >= 4601360 Then
      '#d4f4be'
    When tsysOS.OSname = 'Win 7' And SubQuery1.PatchIDMax >= 4601347 Then
      '#d4f4be'
    When tsysOS.OSname = 'Win 7 RC' And SubQuery1.PatchIDMax >= 4601347 Then
      '#d4f4be'
    When tsysOS.OSname = 'Win 2008 R2' And SubQuery1.PatchIDMax >= 4601347 Then
      '#d4f4be'
    When tsysOS.OSname = 'Win 2012' And SubQuery1.PatchIDMax >= 4601348 Then
      '#d4f4be'
    When tsysOS.OSname = 'Win 8' And SubQuery1.PatchIDMax >= 4601348 Then
      '#d4f4be'
    When tsysOS.OSname = 'Win 8.1' And SubQuery1.PatchIDMax >= 4601384
    Then '#d4f4be'
    When tsysOS.OSname = 'Win 2012 R2' And SubQuery1.PatchIDMax >= 4601384 Then
      '#d4f4be'
    When tsysOS.OScode Like '10.0.10240' And SubQuery1.PatchIDMax >= 4601331
    Then '#d4f4be'
    When tsysOS.OScode Like '10.0.14393' And SubQuery1.PatchIDMax >= 4601318
    Then '#d4f4be'
    When tsysOS.OSname = 'Win 2016' And SubQuery1.PatchIDMax >= 4601318 Then
      '#d4f4be'
    When tsysOS.OScode Like '10.0.17134' And SubQuery1.PatchIDMax >= 4601354
    Then '#d4f4be'
    When tsysOS.OScode Like '10.0.17763' And SubQuery1.PatchIDMax >= 4601345
    Then '#d4f4be'
    When tsysOS.OSname = 'Win 2019' And SubQuery1.PatchIDMax >= 4601345 Then
      '#d4f4be'
    When tsysOS.OScode Like '10.0.18363' And SubQuery1.PatchIDMax >= 4601315
    Then '#d4f4be'
    When tsysOS.OScode Like '10.0.19041' And SubQuery1.PatchIDMax >= 4601319
    Then '#d4f4be'
    When tsysOS.OScode Like '10.0.19042' And SubQuery1.PatchIDMax >= 4601319
    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 Max(SubQuery2.PatchID) As PatchIDMax,
        tblAssets.AssetID
      From tblAssets
        Left Join (Select Cast(subquery.PatchNumber As bigint) As PatchID,
              tblAssets.AssetID
            From tblAssets
              Left Join (Select Top 1000000 tblQuickFixEngineering.AssetID,
                    Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(Replace(tblQuickFixEngineeringUni.HotFixID, 'A', ''), 'B', ''), 'C', ''), 'D', ''), 'E', ''), 'F', ''), 'G', ''), 'H', ''), 'I', ''), 'J', ''), 'K', ''), 'L', ''), 'M', ''), 'N', ''), 'O', ''), 'P', ''), 'Q', ''), 'R', ''), 'S', ''), 'T', ''), 'U', ''), 'V', ''), 'W', ''), 'X', ''), 'Y', ''), 'Z', ''), '-', ''), ',', ''), ' ', ''), '_', '') As PatchNumber
                  From tblQuickFixEngineering
                    Inner Join tblQuickFixEngineeringUni On
                      tblQuickFixEngineeringUni.QFEID =
                      tblQuickFixEngineering.QFEID) As subquery On
                subquery.AssetID = tblAssets.AssetID
            Where subquery.PatchNumber Not Like '%[^0-9]%'
            Group By tblAssets.AssetID,
              subquery.PatchNumber) As SubQuery2 On tblAssets.AssetID =
          SubQuery2.AssetID
      Group By tblAssets.AssetID) 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
  Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
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 tblAssets.Lastseen > GetDate() - 30 And
  tblAssetCustom.State = 1 And tsysAssetTypes.AssetTypename Like 'Windows%'
Order By tblAssets.Uptime Desc