Location maps and their assets

[Susan_A]

Lansweeper allows you to place assets on a map, as explained in this knowledge base article. The two sample queries below show how you can include this information in reports.

• The first report lists your location maps and the assets placed on them. Only maps that contain assets are listed.
  
• The second report lists your assets and the location maps they were placed on. Only assets that were placed on a location map are listed.

Location maps and the assets placed on them

Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.AssetName As Location,
  tblAssets.Description,
  tblAssets1.AssetID,
  tblAssets1.AssetName,
  tblAssets1.Domain,
  tsysAssetTypes1.AssetTypename As AssetType,
  tblAssets1.Username,
  tblAssets1.Userdomain,
  tblAssets1.IPAddress,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssetCustom.Serialnumber,
  tblAssets1.Firstseen,
  tblAssets1.Lastseen,
  tblAssets1.Lasttried
From tblAssets
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblAssetRelations
    On tblAssets.AssetID = tblAssetRelations.ParentAssetID
  Inner Join tsysAssetRelationTypes On tsysAssetRelationTypes.RelationTypeID =
    tblAssetRelations.Type
  Inner Join tblAssets tblAssets1 On tblAssets1.AssetID =
    tblAssetRelations.ChildAssetID
  Inner Join tsysAssetTypes tsysAssetTypes1 On tsysAssetTypes1.AssetType =
    tblAssets1.Assettype
  Inner Join tblAssetCustom On tblAssets1.AssetID = tblAssetCustom.AssetID
Where tsysAssetRelationTypes.ReverseName = 'has location of'
Order By Location,
  tblAssets1.Domain,
  tblAssets1.AssetName

Assets and the location maps they were placed on

Select Top 1000000 tsysAssetTypes.AssetTypeIcon10 As icon,
  tblAssets.AssetName As Asset,
  tblAssets.Domain,
  tsysAssetTypes.AssetTypename As AssetType,
  tblAssets.Username,
  tblAssets.Userdomain,
  tblAssets.IPAddress,
  tblAssetCustom.Manufacturer,
  tblAssetCustom.Model,
  tblAssetCustom.Serialnumber,
  tblAssets.Firstseen,
  tblAssets.Lastseen,
  tblAssets.Lasttried,
  tsysAssetRelationTypes.Name As Relation,
  tblAssets1.AssetID,
  tblAssets1.AssetName,
  tblAssets1.Description
From tblAssets
  Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
  Inner Join tblAssetRelations On tblAssetRelations.ChildAssetID =
    tblAssets.AssetID
  Inner Join tsysAssetRelationTypes On tsysAssetRelationTypes.RelationTypeID =
    tblAssetRelations.Type
  Inner Join tblAssets tblAssets1 On tblAssets1.AssetID =
    tblAssetRelations.ParentAssetID
  Inner Join tblAssetCustom On tblAssetCustom.AssetID = tblAssets.AssetID
Where tsysAssetRelationTypes.Name = 'is located in'
Order By tblAssets1.AssetName,
  tblAssets.Domain,
  Asset