→ Celebrate SysAdmin Day 2024 with Lansweeper Enter our Giveaway here

cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Ismail
Engaged Sweeper II
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
1 ACCEPTED SOLUTION
Technut27
Champion Sweeper
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

View solution in original post

3 REPLIES 3
Technut27
Champion Sweeper
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
Engaged Sweeper II
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
Champion Sweeper
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