cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
GoodGuy
Engaged Sweeper
Hi all,

I'm trying to get Lansweeper to report the following for the quantities of Windows 7 per department (we have over 80 departments)

Marketing Department ---- 23
Information Technology --- 12
Shipping ------------------3
Customer Service ----------15

I'll be needing this for other Microsoft operating systems as well, but I'm sure once I get the ball rolling I can figure it out. Any help is greatly appreciated!
1 ACCEPTED SOLUTION
Daniel_B
Lansweeper Alumni
You can do it by adding tblADusers to your report. Please find an example report below.

Select Top 1000000 tblADusers.Department As [User Department],
tblOperatingsystem.Caption As OS,
Count(tblAssets.AssetID) As Number
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Left Join tblADusers On tblAssets.Username = tblADusers.Username And
tblAssets.Userdomain = tblADusers.Userdomain
Where tblAssetCustom.State = 1
Group By tblADusers.Department,
tblOperatingsystem.Caption
Order By [User Department],
OS

View solution in original post

3 REPLIES 3
Daniel_B
Lansweeper Alumni
You can do it by adding tblADusers to your report. Please find an example report below.

Select Top 1000000 tblADusers.Department As [User Department],
tblOperatingsystem.Caption As OS,
Count(tblAssets.AssetID) As Number
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Left Join tblADusers On tblAssets.Username = tblADusers.Username And
tblAssets.Userdomain = tblADusers.Userdomain
Where tblAssetCustom.State = 1
Group By tblADusers.Department,
tblOperatingsystem.Caption
Order By [User Department],
OS
GoodGuy
Engaged Sweeper
I gave this a shot, it's not showing anything for departments. Is it a quick fix to add departments based on AD users?
Daniel_B
Lansweeper Alumni
You need to add the columns you wish to group on and use Count as aggregate function on tblAssets.AssetID. Enable grouping for all other columns in your report. Not sure how you define departments in your database. Departments could come from the asset database itself or from AD users. The following uses the department field from the Lansweeper database.

Select Top 1000000 tblAssetCustom.Department,
tblOperatingsystem.Caption As OS,
Count(tblAssets.AssetID) As Number
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblOperatingsystem
On tblAssets.AssetID = tblOperatingsystem.AssetID
Where tblAssetCustom.State = 1
Group By tblAssetCustom.Department,
tblOperatingsystem.Caption
Order By tblAssetCustom.Department,
OS