Hello everybody
I'm trying to create a report that lists the Office version installed on the clients. Unfortunately I get multiple entries because Microsoft Office is required in several languages on the clients. Is there a possibility to display only one entry per client here?
Thanks in advance for your help.
Best regards
Bernhard
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
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%' Or
tblSoftwareUni.softwareName Like '%Office%2016%' Or
tblSoftwareUni.softwareName Like '%Office%2019%' Or
tblSoftwareUni.softwareName Like '%Microsoft%365%')
Order By [Office Location],
tblAssets.AssetName,
tblAssets.Lastseen Desc
