Report Showing Assets with Multiple Versions of Microsoft Office

[belloras]

I am currently using the below report to scan assets with two versions of MS Office installed, however we have licenses for several other versions of Office. I wondering if someone could help me out with if it's possible to adjust this report to check more than two versions of Office, and show assets that have at least two of the versions installed. For example: scan for Office 2007, Office 2010, Office 2013, Office 2016, and show results with at least two of the four installed. Is something like this possible? Any advice would be appreciated.

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tsysIPLocations.IPLocation As [Office Location],
tblAssets.Username As [Last Logon User],
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tsysOS.OSname As [Operating System],
tblAssets.Lastseen
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Outer Join tsysIPLocations On tblAssets.IPNumeric >=
tsysIPLocations.StartIP And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Inner Join (Select Top 1000000 tblAssets.AssetID,
Count(tblSoftwareUni.SoftID) As OfficeCount
From tblAssets
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where (tblSoftwareUni.softwareName Like '%Office%2007%') Or
(tblSoftwareUni.softwareName Like '%Office%2010%')
Group By tblAssets.AssetID
Having Count(tblSoftwareUni.SoftID) > 1) SubQuery1 On SubQuery1.AssetID =
tblAssets.AssetID
Where (tblSoftwareUni.softwareName Like '%Office%2007%') Or
(tblSoftwareUni.softwareName Like '%Office%2010%')
Order By [Office Location],
tblAssets.AssetName,
tblAssets.Lastseen Desc

[Esben_D]

My initial idea would be to create a subquery which counts the number of Office versions installed on a machine and put that in the report. You can then add a where clause to say you only want machines in the report where the count is higher than 2.

To be honest your current report is very close. I don't know who created it but they seem to already know how to do that

Step 1 would be adding the additional MS office versions to the subquery (first highlighted part). Then Remove the second highlight and replace the third highlight with Where SubQuery1.OfficeCount > 2

Select Top 1000000 tsysOS.Image As icon,
tblAssets.AssetID,
tblAssets.AssetName,
tsysIPLocations.IPLocation As [Office Location],
tblAssets.Username As [Last Logon User],
tblSoftwareUni.softwareName,
tblSoftware.softwareVersion,
tsysOS.OSname As [Operating System],
tblAssets.Lastseen
From tblAssets
Inner Join tblAssetCustom On tblAssets.AssetID = tblAssetCustom.AssetID
Inner Join tsysOS On tblAssets.OScode = tsysOS.OScode
Left Outer Join tsysIPLocations On tblAssets.IPNumeric >=
tsysIPLocations.StartIP And tblAssets.IPNumeric <= tsysIPLocations.EndIP
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Inner Join (Select Top 1000000 tblAssets.AssetID,
Count(tblSoftwareUni.SoftID) As OfficeCount
From tblAssets
Inner Join tblSoftware On tblAssets.AssetID = tblSoftware.AssetID
Inner Join tblSoftwareUni On tblSoftwareUni.SoftID = tblSoftware.softID
Where (tblSoftwareUni.softwareName Like '%Office%2007%' Or
tblSoftwareUni.softwareName Like '%Office%2010%')
Group By tblAssets.AssetID
Having Count(tblSoftwareUni.SoftID) > 1) SubQuery1 On SubQuery1.AssetID =
tblAssets.AssetID
Where (tblSoftwareUni.softwareName Like '%Office%2007%' Or
tblSoftwareUni.softwareName Like '%Office%2010%')
Order By [Office Location],
tblAssets.AssetName,
tblAssets.Lastseen Desc