PC Owner report (Some are missing)

[davdyv]

Hi, I have created this report. The report will give me PC owner based on "Owned by". But if there is no Owner of a PC then I will not see it in the report list, how can I change it so It also shows me the PC's without any owner?


Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Username As [Last logged-on user],
tblAssetCustom.Custom1 As [YLT-nr],
tsysIPLocations.IPLocation,
tblAssetCustom.Model,
tblADusers.Department As Costcenter,
tblADusers.Name As [PC Owner]
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblAssetUserRelations On tblAssets.AssetID =
tblAssetUserRelations.AssetID
Inner Join tblADusers On tblADusers.Username = tblAssetUserRelations.Username
And tblADusers.Userdomain = tblAssetUserRelations.Userdomain
Inner Join tsysAssetRelationTypes On tsysAssetRelationTypes.RelationTypeID =
tblAssetUserRelations.Type
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Where tblAssetCustom.State = 1

[Nick_VDB · Accepted solution]

We have modified your report and added it below. We created left joins for the tables that link the the tblADusers table and also added a criteria to only give back Windows computers.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetName,
  tblAssets.Username As [Last logged-on user],
  tblAssetCustom.Custom1 As [YLT-nr],
  tsysIPLocations.IPLocation,
  tblAssetCustom.Model,
  tblADusers.Department As Costcenter,
  tblADusers.Name As [PC Owner]
From tblAssets
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Left Join tblAssetUserRelations
    On tblAssets.AssetID = tblAssetUserRelations.AssetID
  Left Join tblADusers On tblADusers.Username = tblAssetUserRelations.Username
    And tblADusers.Userdomain = tblAssetUserRelations.Userdomain
  Left Join tsysAssetRelationTypes On tsysAssetRelationTypes.RelationTypeID =
    tblAssetUserRelations.Type
  Inner Join tsysIPLocations On tsysIPLocations.LocationID =
    tblAssets.LocationID
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tblAssetCustom.State = 1 And tsysAssetTypes.AssetTypename = 'Windows'