Select Distinct Top 1000000 Coalesce(tsysOS.Image,
  tsysAssetTypes.AssetTypeIcon10) As icon,
  tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tsysOS.OSname As OS,
  Case
    When tblComputersystem.Domainrole > 1 Then 'Server'
    Else 'Workstation'
  End As [Workstation/Server],
  Case
    When RDPConnectionState.Value = '0' Then 'Yes'
    When RDPConnectionState.Value = '1' Then 'No'
    Else 'Rescan needed'
  End As [RDPConnectionAllowed (Yes/No)],
  Case tblAssets.AssetID
    When PatchState.AssetID Then 'Yes'
    Else 'No'
  End As [Patched (Yes/No)],
  Case
    When NLAState.Value = '1' Then 'On'
    When NLAState.Value = '0' Then 'Off'
    Else 'Rescan needed'
  End As [NLA (On/Off)],
  tblServiceState.State As RDPServiceStatus,
  Case
    When tblAssets.AssetID = PatchState.AssetID Then 'No'
    When NLAState.Value = '1' Then 'Partially mitigated'
    Else Case
        When RDPConnectionState.Value = '0' Then 'Yes'
        When RDPConnectionState.Value = '1' Then 'No'
        Else 'Rescan needed'
      End
  End As [Vulnerable (Yes/No/Partially mitigated)],
  Case
    When RDPConnectionState.Value = '' Then ''
    When RDPConnectionState.Value = '1' Then ''
    When tblAssets.AssetID = PatchState.AssetID Then ''
    Else Case
        When tsysOS.OSname Like '%XP%' Or
          tsysOS.OSname Like '%2003%' Then 'Install KB4500331'
        When tsysOS.OSname = 'Win 2008' Then 'Install KB4503273 or KB4499180'
        When tsysOS.OSname = 'Win 7' Or tsysOS.OSname = 'Win 7 RC' Or
          tsysOS.OSname = 'Win 2008 R2' Then 'Install KB4503292 or KB4499175'
      End
  End As ActionRequired,
  Convert(nvarchar,DateDiff(day, QuickFixLastScanned.QuickFixLastScanned,
  GetDate())) + ' days ago' As WindowsUpdateInfoLastScanned,
  Case
    When Convert(nvarchar,DateDiff(day, QuickFixLastScanned.QuickFixLastScanned,
      GetDate())) > 7 Then 'Windows update information may not be up to date.'
    Else ''
  End As Comment,
  Case
    When tblAssets.AssetID = PatchState.AssetID Then '#d4f4be'
    When RDPConnectionState.Value = '1' Then '#d4f4be'
    When NLAState.Value = '1' Then '#fada5e'
    Else '#ffadad'
  End As backgroundcolor,
  tblAssetCustom.Custom1 As Office,
  tblAssetCustom.Custom2 As Country,
  tblAssets.Lastseen,
  tblAssetCustom.Custom3 As [User],
  tblAssets.Username As Lastuser,
  tblState.Statename As State
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.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 ('KB4503273', 'KB4499149',
        'KB4499180', 'KB4503292', 'KB4499164', 'KB4499175', 'KB4500331')) As
  PatchState On tblAssets.AssetID = PatchState.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
  Left Join (Select Distinct Top 1000000 tblAssets.AssetID As ID,
        TsysLastscan.Lasttime As QuickFixLastScanned
      From TsysWaittime
        Inner Join TsysLastscan On TsysWaittime.CFGCode = TsysLastscan.CFGcode
        Inner Join tblAssets On tblAssets.AssetID = TsysLastscan.AssetID
      Where TsysWaittime.CFGname = 'QUICKFIX') As QuickFixLastScanned On
    tblAssets.AssetID = QuickFixLastScanned.ID
  Left Join (Select Distinct Top 1000000 tblAssets.AssetID As ID,
        Max(tblErrors.Teller) As ErrorID
      From tblErrors
        Inner Join tblAssets On tblAssets.AssetID = tblErrors.AssetID
      Group By tblAssets.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 tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Inner Join tblServices On tblAssets.AssetID = tblServices.AssetID
  Inner Join tblServicesUni On tblServicesUni.ServiceuniqueID =
    tblServices.ServiceuniqueID
  Inner Join tblServiceState On tblServiceState.StateID = tblServices.StateID
  Left Join (Select tblRegistry.Value,
        tblRegistry.AssetID
      From tblRegistry
      Where tblRegistry.Valuename = 'UserAuthentication') NLAState On
    tblAssets.AssetID = NLAState.AssetID
  Left Join (Select tblRegistry.Value,
        tblRegistry.AssetID
      From tblRegistry
      Where tblRegistry.Valuename = 'fDenyTSConnections') RDPConnectionState On
    tblAssets.AssetID = RDPConnectionState.AssetID
Where tblAssets.AssetID Not In (Select Top 1000000 tblAssets.AssetID
      From tblAssets Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
      Where tsysOS.OSname Like 'Win 7%' And tblAssets.SP = 0) And
  tsysOS.OSname Not Like '%2000%' And tsysOS.OSname Not Like '%2016%' And
  tsysOS.OSname Not Like '%win 10%' And tsysOS.OSname Not Like '%2012%' And
  tsysOS.OSname Not Like '%win 8%' And tblServicesUni.Name Like '%TermService%'
  And tsysAssetTypes.AssetTypename Like 'Windows%'
Order By tblAssets.Domain,
  tblAssets.AssetName