Notification

Icon
Error

Count by Department

Posted: Wednesday, April 21, 2021 5:43:19 PM(UTC)
JTempleton

JTempleton

Member Original PosterPosts: 46
0
Like
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
#1Brandon Member Posts: 136  
posted: 4/21/2021 6:09:50 PM(UTC)
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
#2JTempleton Member Original PosterPosts: 46  
posted: 4/21/2021 7:31:30 PM(UTC)
Originally Posted by: Brandon Go to Quoted Post
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?
Brandon
#3Brandon Member Posts: 136  
posted: 4/21/2021 7:42:00 PM(UTC)
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.

Active Discussions

Lansweeper Automatic Follow-Up for Tickets
by  Francis Lee Mondia - Endace   Go to last post Go to first unread
Last post: Yesterday at 11:06:51 PM(UTC)
Lansweeper Can't see devices on Lansweeper
by  vqT4cDoP9iXyMZwoDUWU  
Go to last post Go to first unread
Last post: Yesterday at 8:33:21 PM(UTC)
Lansweeper LAPS managed password
by  SystemAdmin   Go to last post Go to first unread
Last post: Yesterday at 6:08:42 PM(UTC)
Lansweeper Install Adobe Reader Error
by  Brandon  
Go to last post Go to first unread
Last post: Yesterday at 2:46:54 PM(UTC)
Lansweeper Stop deployment
by  Tyler M.   Go to last post Go to first unread
Last post: Yesterday at 1:59:22 PM(UTC)
Lansweeper "Out of memory" in errorlog.txt
by  dua  
Go to last post Go to first unread
Last post: Yesterday at 1:00:42 PM(UTC)
Lansweeper Installation issues
by  Troy   Go to last post Go to first unread
Last post: Yesterday at 9:24:54 AM(UTC)
Lansweeper End of Life Asset Report
by  DJ Bradley  
Go to last post Go to first unread
Last post: 5/11/2021 4:09:33 PM(UTC)