cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Ryan2k
Engaged Sweeper
Hi,

I've created a range of reports in the past however this one has me stumped!

Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Username,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
TsysChassisTypes.ChassisName,
tblAssets.IPAddress As IP,
tblAssets.Lastseen
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblSystemEnclosure
On tblAssets.AssetID = tblSystemEnclosure.AssetID
Inner Join TsysChassisTypes On TsysChassisTypes.Chassistype =
tblSystemEnclosure.ChassisTypes
Where tblAssets.Lastseen <> '' And (TsysChassisTypes.Chassistype = 8 Or
TsysChassisTypes.Chassistype = 9 Or TsysChassisTypes.Chassistype = 10) And
tblAssets.Assettype = -1
Order By tblAssets.AssetUnique


The report shows the data that I expect, however upon adding tblADusers.Displayname it generates a report for every user that has logged onto the machine, records from 118 to over >9000.

I would like to show the username of the last user on each asset, as you can see I've customised the report to adopt to chasis types which match portable criteria.

I think that this is to do with the way that the table relationship operates but my SQL capability is still amaateur.

Any help greatly appreciated.
1 ACCEPTED SOLUTION
Hemoco
Lansweeper Alumni
TblADusers is still incorrectly linked to tblAssets. These tables must be linked on BOTH the Username and Userdomain fields. I.e.:
Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Username,
tblADusers.Displayname,
tblAssets.Lastseen,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
TsysChassisTypes.ChassisName,
tblAssets.IPAddress As IP
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblSystemEnclosure
On tblAssets.AssetID = tblSystemEnclosure.AssetID
Inner Join TsysChassisTypes On TsysChassisTypes.Chassistype =
tblSystemEnclosure.ChassisTypes
Inner Join tblADusers On tblADusers.Username = tblAssets.Username And
tblADusers.Userdomain = tblAssets.Userdomain
Where tblAssets.Lastseen <> '' And (TsysChassisTypes.Chassistype = 8 Or
TsysChassisTypes.Chassistype = 9 Or TsysChassisTypes.Chassistype = 10) And
tblAssets.Assettype = -1
Order By tblAssets.AssetUnique

View solution in original post

2 REPLIES 2
Hemoco
Lansweeper Alumni
TblADusers is still incorrectly linked to tblAssets. These tables must be linked on BOTH the Username and Userdomain fields. I.e.:
Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Username,
tblADusers.Displayname,
tblAssets.Lastseen,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
TsysChassisTypes.ChassisName,
tblAssets.IPAddress As IP
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblSystemEnclosure
On tblAssets.AssetID = tblSystemEnclosure.AssetID
Inner Join TsysChassisTypes On TsysChassisTypes.Chassistype =
tblSystemEnclosure.ChassisTypes
Inner Join tblADusers On tblADusers.Username = tblAssets.Username And
tblADusers.Userdomain = tblAssets.Userdomain
Where tblAssets.Lastseen <> '' And (TsysChassisTypes.Chassistype = 8 Or
TsysChassisTypes.Chassistype = 9 Or TsysChassisTypes.Chassistype = 10) And
tblAssets.Assettype = -1
Order By tblAssets.AssetUnique
Ryan2k
Engaged Sweeper
Foolishy didn't add the relationship.

Code for anyone interested:

Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.AssetID,
tblAssets.AssetUnique,
tblAssets.Username,
tblADusers.Displayname,
tblAssets.Lastseen,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
TsysChassisTypes.ChassisName,
tblAssets.IPAddress As IP
From tblAssets
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblSystemEnclosure
On tblAssets.AssetID = tblSystemEnclosure.AssetID
Inner Join TsysChassisTypes On TsysChassisTypes.Chassistype =
tblSystemEnclosure.ChassisTypes
Inner Join tblADusers On tblADusers.Username = tblAssets.Username
Where tblAssets.Lastseen <> '' And (TsysChassisTypes.Chassistype = 8 Or
TsysChassisTypes.Chassistype = 9 Or TsysChassisTypes.Chassistype = 10) And
tblAssets.Assettype = -1
Order By tblAssets.AssetUnique