Report Showing same data for tblADObjects.ADObjectID & tblADusers.ManagerADObjectId

[nmjohns]

Below is my report. It shows the same data for User as it does Manager and I'm not sure why. Can someone show me what I did wrong?

Edit:

The idea is to show all the assets from tblAssets, the user of that computer, and the manager of that user.

OK, I think I know what is wrong, but not how to fix it. I've got two tables that are connected to each other but not to the other tables in the report. What should these be connected to? As it stands I'm just connecting random entries(not sure the correct word) to others and getting a wide range of results. Not sure if I'm getting warmer or colder :).

Edit 15: From the other tables it looks like I'm supposed to match something inside of tblADusers to one of the other tables but the only same one is username. When doing that the report fails to return any results.

[img=https://i.imgur.com/16UUep8.png][/img]

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetUnique,
  tsysOS.Image As icon,
  tblAssets.Domain,
  tsysOS.OSname,
  tblAssets.Memory,
  Cast(Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 / 1024 As
  numeric) As nvarchar) As [Disk size],
  tblAssets.NrProcessors As #CPU,
  tblAssets.Processor,
  tblAssetCustom.Model,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Serialnumber As Serial,
  tblAssets.Username As [User],
  tblADusers.Username,
  tblADObjects.sAMAccountName As Manager,
  tblAssets.IPAddress As [IP Address],
  tsysIPLocations.IPLocation,
  tblAssets.Mac,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblAssetCustom.PurchaseDate,
  tblAssetCustom.Warrantydate
From tblAssets
  Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
  Left Outer Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Left Join tsysIPLocations On tsysIPLocations.LocationID = tblAssets.LocationID
    And tsysIPLocations.StartIP <= tblAssets.IPNumeric And
    tsysIPLocations.EndIP >= tblAssets.IPNumeric,
  tblADObjects
  Right Join tblADusers On tblADObjects.ADObjectID = tblADusers.ADObjectID And
    tblADObjects.ADObjectID = tblADusers.ManagerADObjectId
Where tblDiskdrives.Caption = 'c:' And tblAssetCustom.State = 1
Order By tblAssets.AssetID

[MikeMc]

Disclaimer: I am not a SQL expert by any means. I know enough to be dangerous. 😛

The primary issue is your second to last join to the tblADObjects table does not link back to the tblAssets table. I am not familiar with using commas to join tables, but they appear to be the equivalent of an inner join with no limiting criteria. This resulted in each asset record being joined to all of the tblADObjects records. Your right join with its criteria limited the results to actual AD users only.

I added one left join linked to the tblADUsers table with the last user recorded for the asset and then I added one more join to the same table using the manager id from the previous join to look up the manager.

I hope that explanation helps.

[nmjohns]

Looks to be fixed. Can you tell me what I was doing wrong?

[MikeMc · Accepted solution]

This should be what you are needing.

Select Top 1000000 tblAssets.AssetID,
  tblAssets.AssetUnique,
  tsysOS.Image As icon,
  tblAssets.Domain,
  tsysOS.OSname,
  tblAssets.Memory,
  Cast(Cast(Cast(tblDiskdrives.Size As bigint) / 1024 / 1024 / 1024 As
  numeric) As nvarchar) As [Disk size],
  tblAssets.NrProcessors As #CPU,
  tblAssets.Processor,
  tblAssetCustom.Model,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Serialnumber As Serial,
  tblAssets.Username As [User],
  adu.Username As [Manager],
  tblAssets.IPAddress As [IP Address],
  tsysIPLocations.IPLocation,
  tblAssets.Mac,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tblAssetCustom.PurchaseDate,
  tblAssetCustom.Warrantydate
From tblAssets
  Inner Join tblDiskdrives On tblAssets.AssetID = tblDiskdrives.AssetID
  Left Outer Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
  Left Join tsysIPLocations On tsysIPLocations.LocationID = tblAssets.LocationID
    And tsysIPLocations.StartIP <= tblAssets.IPNumeric And
    tsysIPLocations.EndIP >= tblAssets.IPNumeric
  Left Join tblADUsers on tblADUsers.Userdomain = tblAssets.Userdomain AND tblADUsers.Username = tblAssets.Username
  Left Join tblADUsers adu on adu.ADObjectID = tblADUsers.ManagerADObjectId
Where tblDiskdrives.Caption = 'c:' And tblAssetCustom.State = 1
Order By tblAssets.AssetID