Narrow down report results to asset group

[PiotrL]

Hello,

I'm trying to narrow results of this report to particular static asset group, but no luck. All my tries with utilizing  tblAssetGroups shows errors like this: The multi-part identifier "tblAssetGroupLink.AssetGroupID" could not be bound...

Having reports per asset group seems to be something obvious, but so far I haven't found any solution for this, can somebody help with this please?

Thanks

Report:

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
Coalesce(tsysOS.Image, tsysAssetTypes.AssetTypeIcon10) As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname As OS,
tblAssets.Version,
tblAssets.BuildNumber,
Max(tblQuickFixEngineeringUni.HotFixID) As lastPatch,
Max(Convert(date,tblQuickFixEngineering.InstalledOn)) As lastPatchDate,
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) / 3600 /
24))) + ' days ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) / 3600 %
24))) + ' hours ' +
Convert(nvarchar(10),Ceiling(Floor(Convert(integer,tblAssets.Uptime) % 3600 /
60))) + ' minutes' As UptimeSinceLastReboot,
Case
When tblErrors.ErrorText Is Not Null Or
tblErrors.ErrorText != '' Then
'Scanning Error: ' + tsysasseterrortypes.ErrorMsg
Else ''
End As ScanningErrors,
tblAssets.Lastseen,
tblAssets.Lasttried
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblQuickFixEngineering On tblQuickFixEngineering.AssetID =
tblAssets.AssetID
Inner Join tblQuickFixEngineeringUni On tblQuickFixEngineeringUni.QFEID =
tblQuickFixEngineering.QFEID
Left Join (Select Distinct Top 1000000 tblErrors.AssetID As ID,
Max(tblErrors.Teller) As ErrorID
From tblErrors
Group By tblErrors.AssetID) As ScanningError On tblAssets.AssetID =
ScanningError.ID
Left Join tblErrors On ScanningError.ErrorID = tblErrors.Teller
Left Join tsysasseterrortypes On tsysasseterrortypes.Errortype =
tblErrors.ErrorType
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblAssetGroupLink.AssetGroupID
Where tblState.Statename = 'Active'
Group By tblAssets.AssetID,
tblAssets.AssetName,
tblAssets.Domain,
tblAssets.Username,
tblAssets.Userdomain,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tsysOS.OSname,
tblAssets.Version,
tblAssets.BuildNumber,
tsysOS.Image,
tsysAssetTypes.AssetTypeIcon10,
tblAssets.Uptime,
tblErrors.ErrorText,
tsysasseterrortypes.ErrorMsg,
tblAssets.Lastseen,
tblAssets.Lasttried
Order By tblAssets.Domain,
tblAssets.AssetName

[PiotrL]

Hi,

That's first thing I tried, adding this just before Where tblState.Statename = 'Active' but all I got is error:

The multi-part identifier "tblAssetGroupLink.AssetGroupID" could not be bound.

[KevinA-REJIS · Accepted solution]

Try putting it right before the tblAssetGroupLink.AssetGroupID join statement, I tested in our Lansweeper and that cleared up the error. 

[PiotrL]

Hi,

I'm not sure what you mean, tblAssetGroupLink.AssetGroupID is what I'm supposed  to add but where?

[KevinA-REJIS]

Sorry, I meant add the join statement I posted right before the tblAssetGroups join statement, like this:

Left Join tsysasseterrortypes On tsysasseterrortypes.Errortype =
tblErrors.ErrorType
Inner Join tblAssetGroupLink on tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblAssetGroupLink.AssetGroupID
Where tblState.Statename = 'Active'

[KevinA-REJIS]

Try adding this join statement:

Inner Join tblAssetGroupLink on tblAssets.AssetID = tblAssetGroupLink.AssetID