Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tsysOS.OSname,
  tblAssets.Username,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssets.Description,
  tblAssetCustom.Manufacturer,
  tblAssetUserRelations.Username As 'Validator',
  tsysAssetTypes.AssetTypename,
  Max(Convert(date,tblQuickFixEngineering.InstalledOn)) As lastPatchDate,
  tblAssets.Lastseen As [Last Scanned],
  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 [Last Reboot],
  tblAssets.Lastseen,
  Case
    When soft01.AssetID Is Null Then 'NO'
    Else 'YES'
  End As FortiClient,
  Case
    When soft02.AssetID Is Null Then 'NO'
    Else 'YES'
  End As TrendMicro,
  Case
    When soft03.AssetID Is Null Then 'NO'
    Else 'YES'
  End As Sophos,
  Case
    When Max(Convert(date,tblQuickFixEngineering.InstalledOn)) >= DateAdd(day,
      -7, GetDate()) Then '#d4f4be'
    When Max(Convert(date,tblQuickFixEngineering.InstalledOn)) < DateAdd(day,
      -7, GetDate()) And Max(Convert(date,tblQuickFixEngineering.InstalledOn))
      >= DateAdd(day, -30, GetDate()) Then '#f7f0ca'
    When Max(Convert(date,tblQuickFixEngineering.InstalledOn)) < DateAdd(day,
      -31, GetDate()) And Max(Convert(date,tblQuickFixEngineering.InstalledOn))
      >= DateAdd(day, -90, GetDate()) Then '#f2d59f'
    Else '#f7caca'
  End As backgroundcolor,
  Case tblOperatingsystem.Version
    When '10.0.10240' Then '1507'
    When '10.0.10586' Then '1511'
    When '10.0.14393' Then '1607 - Support Ends - Jan 2027'
    When '10.0.15063' Then '1703'
    When '10.0.16299' Then '1709'
    When '10.0.17134' Then '1803'
    When '10.0.17763' Then '1809 - no longer supported - April 2020'
    When '10.0.18362' Then '1903 - no longer supported - December 2020'
    When '10.0.18363' Then '1909 - no longer supported - May 2021'
    When '10.0.19041' Then '2004 - no longer supported - December 2021'
    When '10.0.19042' Then '20H2 - no longer supported - August 9, 2022'
    When '10.0.19043' Then '21H1 - Support ends - December 2022'
    When '10.0.19044' Then '21H2 - Support ends - June 2023'
    When '10.0.19045' Then '22H2'
    When '10.0.22000' Then '21H2'
    Else '?'
  End As Version,
  Case SubQuery1.Value
    When '5' Then
      'Automatic Updates is required, but end users can configure it'
    When '4' Then 'Automatic download and scheduled installation'
    When '3' Then 'Automatic download and notify of installation'
    When '2' Then 'Notify before download'
    When '1' Then 'Never check for updates'
    Else 'Default settings, Not set by GPO'
  End As UpdateSettings,
  SubQuery2.Value As [Target Group],
  tblADComputers.OU,
  tblAssets.Uptime,
  tblAssets.Firstseen,
  tblOperatingsystem.Version As Version1
From tblQuickFixEngineering
  Left Join tblAssets On tblAssets.AssetID = tblQuickFixEngineering.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Left Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Left Join tsysIPLocations On tsysIPLocations.LocationID = tblAssets.LocationID
  Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
  Left Join (Select tblSoftware.AssetID
    From tblSoftware
      Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
    Where tblSoftwareUni.softwareName Like '%forti%%') As soft01 On
      soft01.AssetID = tblAssets.AssetID
  Left Join (Select tblSoftware.AssetID
    From tblSoftware
      Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
    Where tblSoftwareUni.softwareName Like '%Trend%%') As soft02 On
      soft02.AssetID = tblAssets.AssetID
  Left Join (Select tblSoftware.AssetID
    From tblSoftware
      Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
    Where tblSoftwareUni.softwareName Like '%Sophos%%') As soft03 On
      soft03.AssetID = tblAssets.AssetID
  Left Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Left Join (Select Top 1000000 tblRegistry.AssetID,
      tblRegistry.Regkey,
      tblRegistry.Valuename,
      tblRegistry.Value,
      tblRegistry.Lastchanged
    From tblRegistry
    Where
      tblRegistry.Regkey Like
      '%SOFTWARE\Policies\Microsoft\Windows\WindowsUpdate\AU' And
      tblRegistry.Valuename = 'auoptions') SubQuery1 On SubQuery1.AssetID =
      tblAssets.AssetID
  Left Join (Select Top 1000000 tblRegistry.AssetID,
      tblRegistry.Regkey,
      tblRegistry.Valuename,
      tblRegistry.Value,
      tblRegistry.Lastchanged
    From tblRegistry
    Where
      tblRegistry.Regkey Like
      '%SOFTWARE\Policies\Microsoft\Windows\WindowsUpdate' And
      tblRegistry.Valuename = 'TargetGroup') SubQuery2 On SubQuery2.AssetID =
      tblAssets.AssetID
  Inner Join tblRegistry On tblAssets.AssetID = tblRegistry.AssetID
  Inner Join tblOperatingsystem On
      tblAssets.AssetID = tblOperatingsystem.AssetID
  Full Join tblAssetUserRelations On
      tblAssets.AssetID = tblAssetUserRelations.AssetID
  Inner Join tsysAssetRelationTypes On tblAssetUserRelations.Type =
      tsysAssetRelationTypes.RelationTypeID
  Full Join tblADusers On tblADusers.Username = tblAssetUserRelations.Username
Where tsysOS.OSname Not Like 'win 10' And (tblADComputers.OU Like '%%servers%%'
    Or tblADComputers.OU Like '%%domain controller%%') And
  tblADComputers.OU Not Like '%%citrix_delivered%%' And
  tsysAssetRelationTypes.Name Not Like 'owned by' And
  tsysAssetRelationTypes.Name Not Like 'assigned to' And
  tsysAssetRelationTypes.Name Not Like 'is housing' And
  tsysAssetRelationTypes.Name Not Like 'backed up to' And tblAssetCustom.State =
  1
Group By tblAssets.AssetID,
  tblAssets.AssetName,
  tsysOS.OSname,
  tblAssets.Username,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssets.Description,
  tblAssetCustom.Manufacturer,
  tblAssetUserRelations.Username,
  tsysAssetTypes.AssetTypename,
  tblAssets.Lastseen,
  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',
  Case
    When soft01.AssetID Is Null Then 'NO'
    Else 'YES'
  End,
  Case
    When soft02.AssetID Is Null Then 'NO'
    Else 'YES'
  End,
  Case
    When soft03.AssetID Is Null Then 'NO'
    Else 'YES'
  End,
  Case SubQuery1.Value
    When '5' Then
      'Automatic Updates is required, but end users can configure it'
    When '4' Then 'Automatic download and scheduled installation'
    When '3' Then 'Automatic download and notify of installation'
    When '2' Then 'Notify before download'
    When '1' Then 'Never check for updates'
    Else 'Default settings, Not set by GPO'
  End,
  SubQuery2.Value,
  tblADComputers.OU,
  tblAssets.Uptime,
  tblAssets.Firstseen,
  tblOperatingsystem.Version,
  tsysAssetRelationTypes.Name,
  tblAssets.Lastseen
Order By lastPatchDate