cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
targholihr
Engaged Sweeper

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:

Report for not owned assets 

1 ACCEPTED SOLUTION
KevinA-REJIS
Champion Sweeper III

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

View solution in original post

2 REPLIES 2
KevinA-REJIS
Champion Sweeper III

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

It is what I wanted. Thank you