Custom Report to Include the Asset Location

[fpd2dc]

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

[fpd2dc]

Thank you I will give that a go

Bruce

[MikeMc · Accepted solution]

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]

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]

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]

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?