Office installation count as per Dept

[Ismail]

Hello

I would like office installations by count. As in:


Dept. Software (Office) Total
HR DEPT. MS Office Professional Edition 2003 10



Since computers don't have a Dept attribute you can substitute it with OU since all computers are placed in their respective ou's and named after their departments i.e. HR DEPT.

Any feedback would be appreciated.


I had a report that worked but we reinstalled Lansweeper on a new server and it doesnt run any more.

Select Distinct Top 1000000 upgrade_tblCompCustom.Department As Department,
  upgrade_tblSoftware.softwareName As Software,
  Count(upgrade_tblSoftware.ComputerName) As Total
From upgrade_tblSoftware
  Full Outer Join upgrade_tblCompCustom On (upgrade_tblSoftware.ComputerName =
      upgrade_tblCompCustom.Computername)
Group By upgrade_tblCompCustom.Department,
  upgrade_tblSoftware.softwareName
Having (upgrade_tblSoftware.softwareName = 'Microsoft Office 2000 Standard') Or
  (upgrade_tblSoftware.softwareName = 'Microsoft Office 2000 Professional') Or
  (upgrade_tblSoftware.softwareName =
  'Microsoft Office XP Professional with  FrontPage') Or
  (upgrade_tblSoftware.softwareName =
  'Microsoft Office Professional  Edition 2003') Or
  (upgrade_tblSoftware.softwareName =
  'Microsoft Office 2000 SR-1  Professional') Or
  (upgrade_tblSoftware.softwareName =
  'Microsoft Office Visio  Professional 2003') Or
  (upgrade_tblSoftware.softwareName = 'Microsoft Office  Enterprise 2007') Or
  (upgrade_tblSoftware.softwareName =
  'Microsoft Office Project  Professional 2003') Or
  (upgrade_tblSoftware.softwareName = 'Microsoft Office 2000  SR-1 Standard') Or
  (upgrade_tblSoftware.softwareName = 'Microsoft Office 2000  Premium') Or
  (upgrade_tblSoftware.softwareName =
  'Microsoft Office 97, Professional  Edition') Or
  (upgrade_tblSoftware.softwareName =
  'Microsoft Office Professional  Edition 2003') Or
  (upgrade_tblSoftware.softwareName = 'Microsoft Office FrontPage  2003') Or
  (upgrade_tblSoftware.softwareName =
  'Microsoft Office Project Professional  2003') Or
  (upgrade_tblSoftware.softwareName =
  'Microsoft Office Visio Professional  2003') Or
  (upgrade_tblSoftware.softwareName =
  'Microsoft Office Visio Professional  2007') Or
  (upgrade_tblSoftware.softwareName = 'Microsoft Office XP Professional') Or
  (upgrade_tblSoftware.softwareName = 'Microsoft Office OneNote 2003') Or
  (upgrade_tblSoftware.softwareName =
  'Microsoft Office XP Professional with  FrontPage') Or
  (upgrade_tblSoftware.softwareName = 'Microsoft Office Access 2003  Runtime')
  Or
  (upgrade_tblSoftware.softwareName =
  'Microsoft Office Access Runtime  (English) 2007') Or
  (upgrade_tblSoftware.softwareName = 'Microsoft Office Accounting  2009') Or
  (upgrade_tblSoftware.softwareName = 'Microsoft Office Enterprise 2007') Or
  (upgrade_tblSoftware.softwareName = 'Microsoft Office OneNote 2003') Or
  (upgrade_tblSoftware.softwareName =
  'Microsoft Office Professional Edition 2003') Or
  (upgrade_tblSoftware.softwareName =
  'Microsoft Office Project Professional 2007') Or
  (upgrade_tblSoftware.softwareName = 'Microsoft Office Project Standard 2007')
  Or
  (upgrade_tblSoftware.softwareName =
  'Microsoft Office Small Business Edition 2003') Or
  (upgrade_tblSoftware.softwareName = 'Microsoft Office Standard 2007') Or
  (upgrade_tblSoftware.softwareName =
  'Microsoft Office Visio Professional 2007')
Order By Count(upgrade_tblSoftware.ComputerName) Desc,
  Department

[Technut27]

Remove the spaces after the comma's in your OU's, I ran into that too.

(tblADComputers.OU = 'OU=Computers,OU=Exchange Division,DC=amgdom,DC=com')
Or
(tblADComputers.OU = 'OU=Computers,OU=FTZ,DC=amgdom,DC=com' And
tblAssetCustom.State = 1)

[Ismail]

Thanks for your help Technut27.

I tried your report and I got zero results.

This is what I tried exactly.

Select Top 1000000 tblSoftwareUni.softwareName As Software,
  tblSoftwareUni.SoftwarePublisher As Publisher,
  Count(tblSoftware.AssetID) As Total,
  tblSoftware.softwareVersion
From tblSoftware
  Inner Join tblAssets On tblSoftware.AssetID = tblAssets.AssetID
  Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
  Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
  Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
  Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Where
  (tblADComputers.OU = 'OU=Computers, OU=Exchange Division, DC=amgdom, DC=com')
  Or
  (tblADComputers.OU = 'OU=Computers, OU=FTZ, DC=amgdom, DC=com' And
  tblAssetCustom.State = 1)
Group By tblSoftwareUni.softwareName,
  tblSoftwareUni.SoftwarePublisher,
  tblSoftware.softwareVersion
Order By Total Desc

[Technut27 · Accepted solution]

I just did something similar to this, here is my report. In my scenario I didn't write it to include only specific software, I wanted to see everything installed on all the computers in the OU so I could manipulate it in Excel. In my case there were two OU's I needed to check for each department, 1 for desktops, 1 for laptops... This give you a large list and the total install count of every piece of software.

The main issue I have is I had to create 30 of these reports because I had to target every department's OU individually. It also returns different versions of the same software title as a separate row. I imagine those could be combined in the report but my SQL knowledge is limited. Instead I just clean it up in Excel.

Select Top 1000000 tblSoftwareUni.softwareName As Software,
tblSoftwareUni.SoftwarePublisher As Publisher,
Count(tblSoftware.AssetID) As Total,
tblSoftware.softwareVersion
From tblSoftware
Inner Join tblAssets On tblSoftware.AssetID = tblAssets.AssetID
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tblComputersystem On tblAssets.AssetID = tblComputersystem.AssetID
Inner Join tblADComputers On tblAssets.AssetID = tblADComputers.AssetID
Where
(tblADComputers.OU =
'OU=Laptops,INSERT YOUR OU HERE') Or
(tblADComputers.OU =
'OU=Desktops,INSERT YOUR OU HERE' And
tblAssetCustom.State = 1)
Group By tblSoftwareUni.softwareName,
tblSoftwareUni.SoftwarePublisher,
tblSoftware.softwareVersion
Order By Total Desc