cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
SimplyClueless
Engaged Sweeper III
Hello group- I have another software query I am trying to perfect that my admins are hounding me for; thanks as always for help- previously I was asked for a report for licenses per ip location for both MS office and MS Windows products. Now predictably; I am being asked for a count of all installations of Windows Operating system and Microsoft Office- per ip location.

Trying to make a software report that will elegantly push out to Excel that looks like :

Branch Windows OS Installs Office installs
Contoso HQ 11 12


hope that makes sense- thanks in advance
1 ACCEPTED SOLUTION
Daniel_B
Lansweeper Alumni
The report below should give you the result you are looking for. If you have any other Office editions installed on your network, you might need to add these to the filters. This is necessary as there are often additional software installations containing "Microsoft Office".

Select Top 1000000 OSInstalls.IPLocation,
OSInstalls.CountOSInstalls As [OS installations],
OfficeInstalls.CountOfficeInstalls As [Office installations]
From (Select Distinct Top 1000000 Count(tblAssets.AssetID) As CountOSInstalls,
tsysIPLocations.IPLocation
From tblAssets
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tsysIPLocations
On tblAssets.IPNumeric Between tsysIPLocations.StartIP And
tsysIPLocations.EndIP
Where tsysOS.OSname Like '%Win%'
Group By tsysIPLocations.IPLocation) OSInstalls
Left Join (Select Distinct Top 1000000 Count(tblSoftware.AssetID) As
CountOfficeInstalls,
tsysIPLocations.IPLocation
From tblSoftware
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Inner Join tblAssets On tblSoftware.AssetID = tblAssets.AssetID
Inner Join tsysIPLocations
On tblAssets.IPNumeric Between tsysIPLocations.StartIP And
tsysIPLocations.EndIP
Where (tblSoftwareUni.softwareName Like '%Microsoft Office%Standard%') Or
(tblSoftwareUni.softwareName Like '%Microsoft Office%Professional%') Or
(tblSoftwareUni.softwareName Like '%Microsoft Office%Business%') Or
(tblSoftwareUni.softwareName Like '%Microsoft Office%ProPlus%')
Group By tsysIPLocations.IPLocation) OfficeInstalls On OSInstalls.IPLocation =
OfficeInstalls.IPLocation
Order By OSInstalls.IPLocation

View solution in original post

1 REPLY 1
Daniel_B
Lansweeper Alumni
The report below should give you the result you are looking for. If you have any other Office editions installed on your network, you might need to add these to the filters. This is necessary as there are often additional software installations containing "Microsoft Office".

Select Top 1000000 OSInstalls.IPLocation,
OSInstalls.CountOSInstalls As [OS installations],
OfficeInstalls.CountOfficeInstalls As [Office installations]
From (Select Distinct Top 1000000 Count(tblAssets.AssetID) As CountOSInstalls,
tsysIPLocations.IPLocation
From tblAssets
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Inner Join tsysIPLocations
On tblAssets.IPNumeric Between tsysIPLocations.StartIP And
tsysIPLocations.EndIP
Where tsysOS.OSname Like '%Win%'
Group By tsysIPLocations.IPLocation) OSInstalls
Left Join (Select Distinct Top 1000000 Count(tblSoftware.AssetID) As
CountOfficeInstalls,
tsysIPLocations.IPLocation
From tblSoftware
Inner Join tblSoftwareUni On tblSoftware.softID = tblSoftwareUni.SoftID
Inner Join tblAssets On tblSoftware.AssetID = tblAssets.AssetID
Inner Join tsysIPLocations
On tblAssets.IPNumeric Between tsysIPLocations.StartIP And
tsysIPLocations.EndIP
Where (tblSoftwareUni.softwareName Like '%Microsoft Office%Standard%') Or
(tblSoftwareUni.softwareName Like '%Microsoft Office%Professional%') Or
(tblSoftwareUni.softwareName Like '%Microsoft Office%Business%') Or
(tblSoftwareUni.softwareName Like '%Microsoft Office%ProPlus%')
Group By tsysIPLocations.IPLocation) OfficeInstalls On OSInstalls.IPLocation =
OfficeInstalls.IPLocation
Order By OSInstalls.IPLocation