07-01-2023 10:19 AM - edited 07-01-2023 10:21 AM
Hey guys 😉
I need a report that can show me assets that do not have an Owned By relationship. I read this topic but it didn't work:
Solved! Go to Solution.
07-05-2023 04:23 PM
This might get you what you need:
Select Top (1000000) tblAssets.AssetID,
tsysAssetTypes.AssetTypeIcon10 As icon,
tsysAssetTypes.AssetTypename,
tblAssets.AssetName,
tsysIPLocations.IPLocation
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
Where tblAssets.AssetID Not In (Select Top (1000000) tblAssets.AssetID
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
Where tsysAssetRelationTypes.Name = 'Owned By')
Order By tblAssets.AssetName,
tblAssets.Domain
07-05-2023 04:23 PM
This might get you what you need:
Select Top (1000000) tblAssets.AssetID,
tsysAssetTypes.AssetTypeIcon10 As icon,
tsysAssetTypes.AssetTypename,
tblAssets.AssetName,
tsysIPLocations.IPLocation
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
Where tblAssets.AssetID Not In (Select Top (1000000) tblAssets.AssetID
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
Where tsysAssetRelationTypes.Name = 'Owned By')
Order By tblAssets.AssetName,
tblAssets.Domain
07-25-2023 08:34 AM
It is what I wanted. Thank you
Experience Lansweeper with your own data. Sign up now for a 14-day free trial.
Try Now