→ Having trouble accessing our new support portal or creating a ticket? Please notify our team here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
nmjohns
Engaged Sweeper II
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
1 ACCEPTED SOLUTION
MikeMc
Champion Sweeper II
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

View solution in original post

3 REPLIES 3
MikeMc
Champion Sweeper II
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
Engaged Sweeper II
Looks to be fixed. Can you tell me what I was doing wrong?
MikeMc
Champion Sweeper II
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