03-01-2022 01:50 PM
Select Top (1000000) tblAssets.AssetID,
tsysAssetTypes.AssetTypeIcon10 As icon,
tsysAssetTypes.AssetTypename,
tblAssets.AssetName,
tsysAssetRelationTypes.RelationTypeIcon10 As Icon3,
tsysAssetRelationTypes.Name As RelationType,
Case
When tblAssetUserRelations.Userdomain Is Null Then ''
Else '/user.aspx?username=' + tblAssetUserRelations.Username +
'&userdomain=' + tblAssetUserRelations.Userdomain
End As hyperlink_RelationUser,
tblADusers.email,
tblAssetUserRelations.Comments As RelationComments,
tsysIPLocations.IPLocation,
tblAssetUserRelations.StartDate As RelationStartDate,
tblAssetUserRelations.EndDate As RelationEndDate,
Case
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.EndDate < GetDate() Then '#dddddd'
Else '#ffffff'
End As backgroundcolor
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Outer Join tblAssetUserRelations On tblAssets.AssetID =
tblAssetUserRelations.AssetID
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Left Outer Join tsysAssetRelationTypes On tblAssetUserRelations.Type =
tsysAssetRelationTypes.RelationTypeID
Left Outer Join tblADusers On
tblADusers.Userdomain = tblAssetUserRelations.Userdomain And
tblADusers.Username = tblAssetUserRelations.Username
Order By Case
When tblAssetUserRelations.EndDate Is Null Then 1
Else 0
End Desc,
RelationEndDate Desc,
RelationStartDate Desc,
tblAssets.AssetName,
tblAssets.Domain
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now