→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
fpd2dc
Engaged Sweeper II
Good Morning
The SQL below was copied from the edit screen of a custom report I wrote. The report works with the exception of the location field. These assets are located in vehicles and I have added them as locations on their asset page. I just can not get the report to display the text.

I must have the wrong field in the select statement.

Thanks for any assistance.

Bruce





Select Top (1000000) tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tblAssets.Username,
tblAssets.Description,
tblAssetCustom.Location,
tblAssetGroups.AssetGroup
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblAssetGroupLink.AssetGroupID
Where tblAssetGroups.AssetGroup = 'rugged laptops' And tblAssetCustom.State = 1
1 ACCEPTED SOLUTION
MikeMc
Champion Sweeper II
Thanks for the screenshot. It helps clear up the problem. I don't use that feature, but here's a query that should pull the 'Asset Location' for you.

Select Top (1000000) tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tblAssets.Username,
tblAssets.Description,
T1.Location,
tblAssetGroups.AssetGroup
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblAssetGroupLink.AssetGroupID And tblAssetGroups.Builtin = 0
Left Join tblAssetRelations On tblAssetRelations.ChildAssetID =
tblAssets.AssetID And tblAssetRelations.Type = 14
Left Join (Select a.AssetID,
a.AssetName As Location
From tblAssets a) T1 On T1.AssetID = tblAssetRelations.ParentAssetID
Where tblAssetGroups.AssetGroup = 'rugged laptops' And tblAssetCustom.State = 1
Order By tblAssets.AssetName

View solution in original post

5 REPLIES 5
fpd2dc
Engaged Sweeper II
Thank you I will give that a go

Bruce
MikeMc
Champion Sweeper II
Thanks for the screenshot. It helps clear up the problem. I don't use that feature, but here's a query that should pull the 'Asset Location' for you.

Select Top (1000000) tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tblAssets.Username,
tblAssets.Description,
T1.Location,
tblAssetGroups.AssetGroup
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblAssetGroupLink.AssetGroupID And tblAssetGroups.Builtin = 0
Left Join tblAssetRelations On tblAssetRelations.ChildAssetID =
tblAssets.AssetID And tblAssetRelations.Type = 14
Left Join (Select a.AssetID,
a.AssetName As Location
From tblAssets a) T1 On T1.AssetID = tblAssetRelations.ParentAssetID
Where tblAssetGroups.AssetGroup = 'rugged laptops' And tblAssetCustom.State = 1
Order By tblAssets.AssetName
Champ14
Engaged Sweeper II
MikeMc wrote:
Thanks for the screenshot. It helps clear up the problem. I don't use that feature, but here's a query that should pull the 'Asset Location' for you.

Select Top (1000000) tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename,
tblAssets.Username,
tblAssets.Description,
T1.Location,
tblAssetGroups.AssetGroup
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join tblAssetGroupLink On tblAssets.AssetID = tblAssetGroupLink.AssetID
Inner Join tblAssetGroups On tblAssetGroups.AssetGroupID =
tblAssetGroupLink.AssetGroupID And tblAssetGroups.Builtin = 0
Left Join tblAssetRelations On tblAssetRelations.ChildAssetID =
tblAssets.AssetID And tblAssetRelations.Type = 14
Left Join (Select a.AssetID,
a.AssetName As Location
From tblAssets a) T1 On T1.AssetID = tblAssetRelations.ParentAssetID
Where tblAssetGroups.AssetGroup = 'rugged laptops' And tblAssetCustom.State = 1
Order By tblAssets.AssetName


Hi,

I am also having the same issue... The Asset Location is empty..


Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tsysOS1.OSname As [Not Scanned],
tsysOS.OSname As OS,
tblOperatingsystem.Caption,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssets.SP As SP,
tblAssets.Username,
tblAssetCustom.Model,
tblAssetCustom.Manufacturer,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssetCustom1.Location
From tblAssets
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tsysOS On tsysOS.OScode = tblAssets.OScode
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Left Join tsysIPLocations On tblAssets.LocationID = tsysIPLocations.LocationID
Inner Join tblAssetCustom tblAssetCustom1 On tblAssets.AssetID =
tblAssetCustom1.AssetID,
tsysOS tsysOS1
Where tsysOS1.OSname Like '%Not Scanned%' And tblAssetCustom.State = 1
Order By tsysIPLocations.IPLocation,
OS,
tblAssets.AssetName,
tblOperatingsystem.Caption


Thanks!
fpd2dc
Engaged Sweeper II
MikeMc
After thinking about your response I went back and looked again, I found the location field on a different page that is in the report, To get to the right field you have to edit the asset. the second image is on or the first field I was using.

Thanks for the assist.


Bruce
MikeMc
Champion Sweeper II
You have the correct column for the location value in your report. Are you sure you populated the Location field and not another field by mistake?