Computers with Multiple versions of MS Office Installed

[keys_it]

We have recently moved to Office 365 for Enterprises and we have some users who are part of their E3 packages who get the Office 365 2013 package installed and the rest are still using our Volume licensing office 2010 installs. During the migration we had some of our techs not uninstall the 2010 versions. I have created a report but it does list any results. Any help would be greatly appreciated.

Here is my code:

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
Where tblSoftwareUni.softwareName Like '%Office%365%' And
tblSoftwareUni.softwareName Like '%Office%2010%'
Order By [Office Location],
tblAssets.AssetName,
tblAssets.Lastseen Desc

[Hemoco · Accepted solution]

Your report only lists software that meets BOTH of the criteria below. We would recommend reading up on SQL, so you know how the AND and OR operators work. There are several good SQL tutorials available online, e.g.: http://www.w3schools.com/sql/default.asp
- The software name includes "Office" and "365".
- The software name includes "Office" and "2010".

For what you are trying to do (list computers with both Office versions installed), you need something like this:

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%365%') 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%365%') Or
(tblSoftwareUni.softwareName Like '%Office%2010%')
Order By [Office Location],
tblAssets.AssetName,
tblAssets.Lastseen Desc