Notification

Icon
Error

Asset location report with count ?

Posted: Wednesday, May 6, 2015 7:59:54 PM(UTC)
stahly8

stahly8

Member Original PosterPosts: 19
0
Like
This issue has been solved! Click here to view the solution
I am currently adding in floor maps to our lansweeper db and I was wondering if a computer count can be added to a report like this?


Select Top 1000000 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'
Group By tblAssets1.AssetID,
tblAssets1.AssetName,
tblAssets1.Description



Right now the output of the report looks like this:

AssetName, Description
Administration
ServerRoom
Other


But I would like to include a number next to it for how many computers have been assigned to that asset location. example:

AssetName, Description, Number
Administration 11
ServerRoom 43
Other 22



thank you!
Daniel.B
#1Daniel.B Member Posts: 1,150  
posted: 5/7/2015 1:19:14 PM(UTC)
The only thing missing is a Count of records in tblAssets. In the report editor, select tblAssets.AssetID to be displayed in your report and choose Count as Aggregate function. You already enabled grouping of the other columns.
Code:

Select Top 1000000 tblAssets1.AssetID,
  tblAssets1.AssetName,
  tblAssets1.Description,
  Count(tblAssets.AssetID) As Number
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'
Group By tblAssets1.AssetID,
  tblAssets1.AssetName,
  tblAssets1.Description
Argon0
#2Argon0 Member Posts: 44  
posted: 10/14/2020 11:05:33 AM(UTC)
How does one include a count of asset types per location (e.g. I need to see numbers of Desktops, Printers, Network Switches, laotops, etc...).

Many thanks

Argon0

Active Discussions

Report Center Default Browser and Version
by  RC62N  
Go to last post Go to first unread
Last post: 11/26/2020 8:00:54 PM(UTC)
Lansweeper Trying to make a custom report that sorts assets by state
by  mzipperer   Go to last post Go to first unread
Last post: 11/25/2020 5:36:52 PM(UTC)
Lansweeper Report - All Assets with Specfic Default Gateway
by  RC62N  
Go to last post Go to first unread
Last post: 11/25/2020 4:18:08 PM(UTC)
Report Center Windows update report
by  ToeJoe   Go to last post Go to first unread
Last post: 11/24/2020 3:39:00 PM(UTC)
Lansweeper Report - assets
by  Andy.S  
Go to last post Go to first unread
Last post: 11/23/2020 2:42:01 PM(UTC)
Lansweeper Run Report on PCs from a list
by  RC62N   Go to last post Go to first unread
Last post: 11/16/2020 4:23:12 PM(UTC)