Count by Department

[JTempleton]

I am trying to get a count of our assets by department. I have created this report, but it only counts up to 554. When I look at our configuration/License page, it says we have 1779 licensed assets. I am not sure what is wrong with the report. The other weird thing I am seeing is two blank lines where the department is blank. One line shows a count of 0, the other a count of 10.



Select Top 1000000 tblAssetCustom.Department As [Business Unit],
Count(tblAssetCustom.Department) As [Asset Count]
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysAssetTypes On tsysAssetTypes.AssetType = tblAssets.Assettype
Inner Join lansweeperdb.dbo.tblState On tblState.State = tblAssetCustom.State
Where tblState.Statename = 'Active'
Group By tblAssetCustom.Department,
tblState.Statename
Order By [Business Unit]

[brandon_jones]

The table tblassetcustom is in the query. In order for that field to be populated, you have to bring up the record for that asset in LAnsweeper, click edit asset, and fill in the department field. You can then run this report and it will the number of assets in each department.

Are the departments you wanting in AD?

If you hover over the reports link then go to database documentation, it will give you more information about that field.

[brandon_jones]

You are querying the field tblAssetCustom.Department which is something in Lansweeper that has to be filled in manually. Are you looking for a department that is in AD?

[JTempleton]

Brandon wrote:
You are querying the field tblAssetCustom.Department which is something in Lansweeper that has to be filled in manually. Are you looking for a department that is in AD?

No. Each asset is is assigned to a department. It is like this because the table tblAssets is missing in the query?