→ The Lansweeper Customer Excellence Awards 2024 - Submit Your Project Now! Learn More & Enter Here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
dtsteinb
Engaged Sweeper II
I want to add this into an existing report. Which table is this a part of and is there anything out there tjat breaks down what is in each table?
1 ACCEPTED SOLUTION
Nick_VDB
Champion Sweeper III
Reply we gave to your ticket:

We added an example report below that will give back the asset location. To give back the asset location in a report you must make use a sub query that gives back the location map name the asset is assigned to. We highlighted the subquery in the report below.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
AssetLocation.Location
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join (Select Top 1000000 tblAssetRelations.ChildAssetID,
tblAssets.AssetName As Location
From tblAssetRelations
Inner Join tblAssets On tblAssets.AssetID = tblAssetRelations.ParentAssetID
Inner Join tsysAssetRelationTypes On tsysAssetRelationTypes.RelationTypeID =
tblAssetRelations.Type
Where tsysAssetRelationTypes.Name = 'is located in'
) As AssetLocation
On tblAssets.AssetID = AssetLocation.ChildAssetID
Where tblAssetCustom.State = 1

View solution in original post

2 REPLIES 2
dtsteinb
Engaged Sweeper II
Which part am I adding to my existing report? I tried the whole thing and just the highlighted portion and both give me SQL errors
I do appreciate the response
Nick_VDB
Champion Sweeper III
Reply we gave to your ticket:

We added an example report below that will give back the asset location. To give back the asset location in a report you must make use a sub query that gives back the location map name the asset is assigned to. We highlighted the subquery in the report below.

Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tblAssets.Lastseen,
tblAssets.Lasttried,
AssetLocation.Location
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join (Select Top 1000000 tblAssetRelations.ChildAssetID,
tblAssets.AssetName As Location
From tblAssetRelations
Inner Join tblAssets On tblAssets.AssetID = tblAssetRelations.ParentAssetID
Inner Join tsysAssetRelationTypes On tsysAssetRelationTypes.RelationTypeID =
tblAssetRelations.Type
Where tsysAssetRelationTypes.Name = 'is located in'
) As AssetLocation
On tblAssets.AssetID = AssetLocation.ChildAssetID
Where tblAssetCustom.State = 1