Report request - Microsoft office and Operating system installs

[SimplyClueless]

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

[Daniel_B · Accepted solution]

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