Select Top (1000000) tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Domain,
  tsysAssetTypes.AssetTypename,
  Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
  tblAssets.IPAddress,
  tsysIPLocations.IPLocation,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  Case
    When tsysOS.osname Is Not Null Then tsysOS.osname
    When tblLinuxSystem.OSRelease Is Not Null Then tblLinuxSystem.OSRelease
    When tblMacOSInfo.KernelVersion Is Not Null Then tblMacOSInfo.KernelVersion
    When tsysAssetTypes.AssetType = -1 And Coalesce(tblSccmAsset.OsCaption,
      tblSccmAsset.OperatingSystemNameandVersion, '') <> '' Then
      Coalesce(tblSccmAsset.OsCaption,
      tblSccmAsset.OperatingSystemNameandVersion)
    Else ''
  End As OS,
  tblAssets.SP,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tsysAssetRelationTypes.RelationTypeIcon10 As Icon3,
  tsysAssetRelationTypes.Name As RelationType,
  tblAssetUserRelations.Comments As RelationComments,
  tblAssetUserRelations.StartDate As RelationStartDate,
  tblAssetUserRelations.EndDate As RelationEndDate,
  Case
    When tblAssetUserRelations.Upn Is Not Null Then tblAzureAdUser.DisplayName
    When tblAssetUserRelations.Userdomain Is Null Then ''
    When tblADusers.Displayname Is Null Or
      tblADusers.Displayname = '' Then tblAssetUserRelations.Userdomain + '\' +
      tblAssetUserRelations.Username
    Else tblADusers.Displayname
  End As hyperlink_name_RelationUser,
  Case
    When tblAssetUserRelations.Upn Is Not Null Then '/user.aspx?upn=' +
      tblAssetUserRelations.Upn
    When tblAssetUserRelations.Userdomain Is Null Then ''
    Else '/user.aspx?username=' + tblAssetUserRelations.Username +
      '&userdomain=' + tblAssetUserRelations.Userdomain
  End As hyperlink_RelationUser,
  Replace(tblAssetUserRelations.Ou, ',', ', ') As hyperlink_name_Ou,
  Case
    When tblAssetUserRelations.Ou Is Null Then ''
    Else '/Report/report.aspx?det=web50findusersbyOU&title=' +
      Replace(tblAssetUserRelations.Ou, '&', '%26') + '&@OU=' +
      Replace(tblAssetUserRelations.Ou, '&', '%26')
  End As hyperlink_Ou,
  tblADGroups.Name As [hyperlink_name_AD Group],
  Case
    When tblAssetUserRelations.AdObjectId Is Null Then ''
    Else '/Report/report.aspx?det=web50usersbyadgroup&title=' + tblADGroups.Name
      + '&@id=' + Cast(tblAssetUserRelations.AdObjectId As nvarchar(10))
  End As [hyperlink_AD Group],
  Case
    When tblAssetUserRelations.EndDate < GetDate() Then '#dddddd'
    Else '#ffffff'
  End As backgroundcolor
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblAssetUserRelations On
      tblAssets.AssetID = tblAssetUserRelations.AssetID
  Inner Join tblState On tblState.State = tblAssetCustom.State
  Inner Join tsysIPLocations On tsysIPLocations.LocationID =
      tblAssets.LocationID
  Left Outer Join tsysOS On tsysOS.OScode = tblAssets.OScode
  Inner Join tsysAssetRelationTypes On tblAssetUserRelations.Type =
      tsysAssetRelationTypes.RelationTypeID
  Left Outer Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
  Left Outer Join tblMacOSInfo On tblAssets.AssetID = tblMacOSInfo.AssetID
  Left Outer Join tblADusers On
      tblADusers.Userdomain = tblAssetUserRelations.Userdomain And
      tblADusers.Username = tblAssetUserRelations.Username
  Left Outer Join tblADGroups On tblADGroups.ADObjectID =
      tblAssetUserRelations.AdObjectId
  Left Outer Join tblAzureAdUser On tblAzureAdUser.UserPrincipalName =
      tblAssetUserRelations.Upn
  Left Outer Join tblSccmAsset On tblAssets.AssetID = tblSccmAsset.AssetId
Where tblState.Statename = 'Active'
Order By Case
    When tblAssetUserRelations.EndDate Is Null Then 1
    Else 0
  End Desc,
  RelationEndDate Desc,
  RelationStartDate Desc,
  tblAssets.AssetName,
  tblAssets.Domain