Report how many Windows 7 operating systems per department?

[GoodGuy]

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!

[Daniel_B · Accepted solution]

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]

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]

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