Unable to add 'User' Column in Report

[TRAGAR]

This Report runs without issue:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Username,
tblAssetCustom.Model,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.Domain,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Where tblAssets.AssetID Not In (Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%CrowdStrike%') And
tblAssets.AssetName Like 'EW%' And tblAssets.Lastseen <> '' And
tblAssets.Assettype = -1 And tblAssetCustom.State = 1
Order By tblAssets.AssetUnique

I want to add a 'User' column after Username, so I added this after tblAssets.Username:

tblADusers.Displayname As [User],

I receive this error: "The multi-part identifier "tblADusers.Displayname" could not be bound."

What do I need to change to fix this?

[TRAGAR · Accepted solution]

I figured this out.  😊

[TRAGAR · Accepted solution]

SOLUTION:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblADusers.Displayname As [User],
tblAssets.Username,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.Domain,
tblAssets.Lastseen,
tblAssets.Lasttried
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tblADusers On tblADusers.Username = tblAssets.Username And
tblADusers.Userdomain = tblAssets.Userdomain
Left Outer Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Where tblAssets.AssetID Not In (Select tblSoftware.AssetID
From tblSoftware Inner Join tblSoftwareUni On tblSoftwareUni.SoftID =
tblSoftware.softID
Where tblSoftwareUni.softwareName Like '%CrowdStrike%') And
tblAssets.AssetName Like 'EW%' And tblAssets.Lastseen <> '' And
tblAssets.Assettype = -1 And tblAssetCustom.State = 1
Order By tblAssets.AssetUnique

[TRAGAR · Accepted solution]

I figured this out.  😊

[Mercedes_O]

Hi @TRAGAR that's great, would it be possible to share what your solution is? That way if someone has a similar issue in the future they will be able to refer to your answer, thanks!

[TRAGAR]

Hi Mercedes,

Absolutely! I will update that on Thursday.  🙂

[Mercedes_O]

Amazing thank you!