cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
TRAGAR
Engaged Sweeper II

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?

2 ACCEPTED SOLUTIONS
TRAGAR
Engaged Sweeper II

I figured this out.  ðŸ˜Š

View solution in original post

TRAGAR
Engaged Sweeper II

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

View solution in original post

5 REPLIES 5
TRAGAR
Engaged Sweeper II

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
Engaged Sweeper II

I figured this out.  ðŸ˜Š

Mercedes_O
Community Manager
Community Manager

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
Engaged Sweeper II

Hi Mercedes,

Absolutely! I will update that on Thursday.  ðŸ™‚

Mercedes_O
Community Manager
Community Manager

Amazing thank you!

New to Lansweeper?

Try Lansweeper For Free

Experience Lansweeper with your own data.
Sign up now for a 14-day free trial.

Try Now