Select Top 1000000 Case
    When adc.AssetID Is Null Then 'False'
    Else 'True'
  End As ExistsInAD,
  Case
    When Coalesce(adc.IsEnabled, 0) = 0 Then 'False'
    Else 'True'
  End As EnabledInAD,
  at.AssetTypename As Type,
  Coalesce(os.Image, at.AssetTypeIcon10) As icon,
  a.AssetID,
  a.AssetName,
  a.Domain,
  a.Username,
  a.Userdomain,
  a.IPAddress,
  a.Description,
  ac.Manufacturer,
  ac.Model,
  ac.Location,
  Case (a.Assettype)
    When -1 Then os.OSname
    When 11 Then ls.OSRelease
    When 13 Then mi.SystemVersion
    Else ''
  End As OS,
  a.SP As SP,
  a.Firstseen,
  a.Lastseen
From tblAssets a
  Left Join tblADComputers adc On a.AssetID = adc.AssetID
  Inner Join tblAssetCustom ac On a.AssetID = ac.AssetID
  Inner Join tsysAssetTypes at On at.AssetType = a.Assettype
  Inner Join tblState s On s.State = ac.State
  Left Join tsysOS os On a.OScode = os.OScode
  Left Join tblLinuxSystem ls On ls.AssetID = a.AssetID
  Left Join tblMacOSInfo mi On mi.AssetID = a.AssetID
Where s.Statename = 'Active' And a.Assettype In ( -1, 11, 13)
Order By EnabledInAD Desc,
  ExistsInAD Desc,
  a.Assettype,
  a.AssetName