cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
joled
Engaged Sweeper
Hello,

Does anyone have the query used for the "Assets" tab/link in the menu? I would like to create a report similar but also include the asset location that was pinned to each asset.
Thanks in advance.
Joe
1 ACCEPTED SOLUTION
Nick_VDB
Champion Sweeper III
We have added an example report that will give back the same information as on the 'Assets' page. However, with the inclusion of the Asset location you will only be given back assets that have an asset location assigned to them.

Instructions for adding this report to your Lansweeper installation can be found here. If you are interested in building or modifying reports, we do recommend:
  • Reviewing some SQL tutorials, as the Lansweeper report builder is a standard SQL editor. If you know SQL, you know how to build Lansweeper reports as well. This seems like a good tutorial.
  • Making use of our database dictionary, which explains in great detail what each database table and field stores. More information on the dictionary can be found here.


Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename As Type,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssets1.AssetName As AssetLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssets.Domain,
Case tsysAssetTypes.AssetTypename When 'Windows' Then tsysOS.OSname
When 'Linux' Then tblLinuxSystem.OSRelease
When 'Apple Mac' Then tblMacOSInfo.KernelVersion Else '' End As OS,
tblAssets.Mac As [MAC Address],
tblADComputers.OU,
tblState.Statename,
tblAssets.Description,
tblAssetCustom.PurchaseDate,
tblAssetCustom.Warrantydate,
tblAssets.FQDN,
tblAssetCustom.DNSName,
tblAssetCustom.LastPatched,
tblAssetCustom.LastFullbackup,
tblAssetCustom.LastFullimage,
tblAssetCustom.Location,
tblAssetCustom.Building,
tblAssetCustom.Department,
tblAssetCustom.Branchoffice,
tblAssetCustom.BarCode,
tblAssetCustom.Contact,
tblAssetCustom.Serialnumber,
tblAssetCustom.OrderNumber,
tblAssetCustom.Custom1,
tblAssetCustom.Custom2,
tblAssetCustom.Custom3,
tblAssetCustom.Custom4,
tblAssetCustom.Custom5,
tblAssetCustom.Custom6,
tblAssetCustom.Custom7,
tblAssetCustom.Custom8,
tblAssetCustom.Custom9,
tblAssetCustom.Custom10,
tblAssetCustom.Custom11,
tblAssetCustom.Custom12,
tblAssetCustom.Custom13,
tblAssetCustom.Custom14,
tblAssetCustom.Custom15,
tblAssetCustom.Custom16,
tblAssetCustom.Custom17,
tblAssetCustom.Custom18,
tblAssetCustom.Custom19,
tblAssetCustom.Custom20,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried
From tblAssets
Left Join tblAssetRelations On tblAssetRelations.ChildAssetID =
tblAssets.AssetID
Left Join tsysAssetRelationTypes On tsysAssetRelationTypes.RelationTypeID =
tblAssetRelations.Type
Left Join tblAssets tblAssets1 On tblAssets1.AssetID =
tblAssetRelations.ParentAssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tblOperatingsystem On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tblMacOSInfo On tblAssets.AssetID = tblMacOSInfo.AssetID
Where tsysAssetRelationTypes.Name = 'is located in'
Order By tblAssets.AssetName

View solution in original post

3 REPLIES 3
joled
Engaged Sweeper
Thank you much!
Nick_VDB
Champion Sweeper III
We have added an example report that will give back the same information as on the 'Assets' page. However, with the inclusion of the Asset location you will only be given back assets that have an asset location assigned to them.

Instructions for adding this report to your Lansweeper installation can be found here. If you are interested in building or modifying reports, we do recommend:
  • Reviewing some SQL tutorials, as the Lansweeper report builder is a standard SQL editor. If you know SQL, you know how to build Lansweeper reports as well. This seems like a good tutorial.
  • Making use of our database dictionary, which explains in great detail what each database table and field stores. More information on the dictionary can be found here.


Select Top 1000000 tblAssets.AssetID,
tblAssets.AssetName,
tsysAssetTypes.AssetTypename As Type,
tsysAssetTypes.AssetTypeIcon10 As icon,
tblAssets.IPAddress,
tsysIPLocations.IPLocation,
tblAssets1.AssetName As AssetLocation,
tblAssetCustom.Manufacturer,
tblAssetCustom.Model,
tblAssets.Domain,
Case tsysAssetTypes.AssetTypename When 'Windows' Then tsysOS.OSname
When 'Linux' Then tblLinuxSystem.OSRelease
When 'Apple Mac' Then tblMacOSInfo.KernelVersion Else '' End As OS,
tblAssets.Mac As [MAC Address],
tblADComputers.OU,
tblState.Statename,
tblAssets.Description,
tblAssetCustom.PurchaseDate,
tblAssetCustom.Warrantydate,
tblAssets.FQDN,
tblAssetCustom.DNSName,
tblAssetCustom.LastPatched,
tblAssetCustom.LastFullbackup,
tblAssetCustom.LastFullimage,
tblAssetCustom.Location,
tblAssetCustom.Building,
tblAssetCustom.Department,
tblAssetCustom.Branchoffice,
tblAssetCustom.BarCode,
tblAssetCustom.Contact,
tblAssetCustom.Serialnumber,
tblAssetCustom.OrderNumber,
tblAssetCustom.Custom1,
tblAssetCustom.Custom2,
tblAssetCustom.Custom3,
tblAssetCustom.Custom4,
tblAssetCustom.Custom5,
tblAssetCustom.Custom6,
tblAssetCustom.Custom7,
tblAssetCustom.Custom8,
tblAssetCustom.Custom9,
tblAssetCustom.Custom10,
tblAssetCustom.Custom11,
tblAssetCustom.Custom12,
tblAssetCustom.Custom13,
tblAssetCustom.Custom14,
tblAssetCustom.Custom15,
tblAssetCustom.Custom16,
tblAssetCustom.Custom17,
tblAssetCustom.Custom18,
tblAssetCustom.Custom19,
tblAssetCustom.Custom20,
tblAssets.Firstseen,
tblAssets.Lastseen,
tblAssets.Lasttried
From tblAssets
Left Join tblAssetRelations On tblAssetRelations.ChildAssetID =
tblAssets.AssetID
Left Join tsysAssetRelationTypes On tsysAssetRelationTypes.RelationTypeID =
tblAssetRelations.Type
Left Join tblAssets tblAssets1 On tblAssets1.AssetID =
tblAssetRelations.ParentAssetID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Left Join tblOperatingsystem On tblAssets.AssetID = tblOperatingsystem.AssetID
Inner Join tsysIPLocations On tsysIPLocations.LocationID =
tblAssets.LocationID
Left Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Inner Join tblState On tblState.State = tblAssetCustom.State
Left Join tblLinuxSystem On tblAssets.AssetID = tblLinuxSystem.AssetID
Left Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Join tblMacOSInfo On tblAssets.AssetID = tblMacOSInfo.AssetID
Where tsysAssetRelationTypes.Name = 'is located in'
Order By tblAssets.AssetName
rinks
Champion Sweeper

This pulled only 13 assets of the over 16,000 we should have...

UHM's "Lansweeper Guy"