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

Report Center Microsoft Outlook email bug EX255650
by  Esben.D   Go to last post Go to first unread
Last post: Yesterday at 2:30:16 PM(UTC)
Lansweeper Patch Tuesday May 2021
by  Esben.D  
Go to last post Go to first unread
Last post: 5/11/2021 8:13:06 PM(UTC)
Lansweeper Report showing only Wi-Fi Devices and MAC addresses
by  Andy.S   Go to last post Go to first unread
Last post: 5/11/2021 2:23:24 PM(UTC)
Lansweeper Modifying Purchase Date / Yearly Refresh Report
by  Cripple.Zero   Go to last post Go to first unread
Last post: 5/7/2021 7:06:47 PM(UTC)
Lansweeper Tweak LanSweepers Bios Version Audit to show Last Seen
by  Tyler M.  
Go to last post Go to first unread
Last post: 5/6/2021 8:22:40 PM(UTC)
Lansweeper Patch Volume Across Fleet
by  darren.kimber  
Go to last post Go to first unread
Last post: 5/5/2021 11:29:33 PM(UTC)